이번 포스팅은 빅쿼리 다루기(여러 값 다루기)편!
데이터 분석을 위한 SQL 레시피 책을 참고하여 작성
문자열을 배열로 분해
문자열은 범용적인 자료형이므로 세부적으로 분해해서 사용해야하는 경우가 많다. 배열의 인덱스는 일반적으로 1부터 시작하지만 BigQuery의 경우 배열의 값에 접근하는 방식이 특이하다.(OFFSET : 0부터 시작, ORDINAL : 1부터 시작)
날짜와 타임스탬프
-로그데이터를 처리할때는 날짜 또는 타임스탬프등의 시간정보가 많이 활용된다고 한다.
빅쿼리의 경우 UTC 시간을 리턴하며 CURRENT_DATE()와 CURRENT_TIMESTAMP()를 사용하여 현재 날짜와 타임스탬프를 출력할 수 있다.
현재시각이 아니라 문자열로 지정한 날짜와 시각을 기반으로 날짜 자료형과 타임스탬프 자료형의 데이터를 만드는 경우도 있다.
CAST 함수를 사용하는 방법이 가장 범용적이다.
타임스탬프 자료형의 데이터에서 년과 월 등의 특정 필드값을 추출할 때는 EXTRACT함수를 사용한다.
날짜자료형과 타임스탬프자료형을 사용하지 않아도 단순한 문자열처럼 취급하여 문자열 조작을 통해 필드를 추출할 수 있다.
연과 월을 동시에 추출해서 월별 리포트를 만들 때 substring함수로 문자열을 추출하여 사용하기도 한다.
날짜, 시간정보는 로그데이터에서 빠지지않는 정보. (타임존을 고려해야하고 미들웨어의 차이를 잘 알아야한다.)
결측치를 디폴트값으로 대치하기
문자열 또는 숫자르 다룰 때 중간에 NULL이 들어가있는 경우 주의해야한다.
처리 대상인 데이터가 우리가 원하는 형태가 아닐 경우 데이터를 반드시 가공해야한다.
NULL과 문자열을 결합해도, 숫자로 사칙연산을 해도 결국 값은 NULL이 된다.
NULL일 때 COALESCE함수를 사용하여 디폴트값을 지정해주어 대치할 수 있다.
여러 값들 다루기
데이터를 분석할 때 여러 값을 하나의 값으로 만들거나 다양한 값을 비교하는 경우가 많다.
단순하게 숫자로 비교하면 숫자가 큰 데이터만 주목하게 되지만, ’개인별‘ 또는 ’비율‘등의 지표를 사용하면 다양한 관점에서 데이터를 바라볼 수 있다.
- 문자열 연결 함수 – CONCAT() (원하는 만큼의 문자열을 연결할 수 있다)
- 각 컬럼의 값의 크고 작음을 비교하려면 CASE식을 사용해서 조건을 기술하고 조건에 맞게 값을 지정하면 된다.
컬럼 값에서 최댓값, 최솟값을 찾을 때는 greatest 함수 또는 least 함수를 사용한다.
하나의 레코드 내부에 있는 값끼리 연산할 때는 여러 개의 컬럼에 있는 비교/계산 처리가 간단하다.
여러 레코드에 걸쳐있는 값들을 처리할 때는 집약함수를 이용해서 데이터를 가공하고 처리해야한다.
두 값의 거리 계산하기
절댓값 계산 : ABS함수(abstract)사용
- -제곱 평균 제곱근(두 값의 차이를 제곱한 뒤 제곱근을 적용해서 나오는 값)
- :제곱을 할 때는 POWER함수, 제곱근을 구할때는 SQRT 함수를 사용
- -값이 일차원이면 절댓값과 제곱 평균 제곱근은 같은 결과
- : 유클리드 거리는 물리적인 공간에서 거리를 구할 때 사용하는 일반적인 방법
날짜/시간 계산하기
: 나이는 시간의 경과에 따라 변하므로, 생년월일을 저장하고 이후에 계산해서 나이를 구하게 된다. 나이를 사용하면 제품의 구매 연령대, 서비스를 사용하는 사용자의 연령 분포 등을 확인할 수 있다.
전용함수를 사용하지 않고 나이를 계산하려면 날짜를 고정자리 수의 정수로 표현하고 그 차이를 계산하면 된다.
IP주소 다루기
- 일반적인 웹서비스는 로그 데이터에 사용자 IP주소를 저장한다.(보통 IP주소를 로그로 저장할 때는 문자열로 저장)
IP주소 전용 자료형이 제공되지않는 미들웨어의 경우
1) IP주소를 정수형으로 변환하여 사용 10진수 부분을 2^24, 2^16, 2^8, 2^0 만큼 곱하고 더하면 정수형 표기가 된다. IP주소가 정수형으로 변환되므로, 대소 비교 또는 범위 판정 등을 할 수 있다. 2) 10진수 부분을 3자리 숫자가 되도록 앞부분을 0으로 채우기 lpad 함수 사용(지정한 문자 수가 되도록 문자열의 왼쪽을 채우는 함수) IP주소의 각 10진수를 0으로 채워서 고정길이 문자열을 만들면 문자열 상태로 대소를 비교할 수 있다.
하나의 테이블에 대한 조작
프로그래밍 언어로서 SQL의 특징 : 데이터를 집합으로 다룬다는 점 대량의 데이터를 집계하고, 몇가지 지표를 사용해 데이터 전체의 특징을 파악할 수 있어야한다.
문자열, 날짜, 결측치, 타입변경하기 끝