[SQL 레시피] 여러 개의 값에 대한 조작

6강. 여러 개의 값에 대한 조작

여러 값을 집약해서 하나의 값으로 만들거나, 다양한 값을 비교하는 경우

새로운 지표 정의

• 방문한 사용자 수 중에서 특정한 행동 (클릭 또는 구매 등)을 실행한 사용자의 비율
  • CTR (클릭 비율 : Click Through Rate)
  • CVR (컨버전 비율 : Conversion Rate)

1. 문자열 연결하기 - concat
2. 여러 개의 값 비교하기
   1. 분기별 매출 증감 판정 - case when , sign(q2-q1)
   2. 연간 최대/최소 4분기 매출 찾기 - greatest(q1,q2,q3,q4) , least(q1,q2,q3,q4) (컬럼끼리 비교)
   3. 연간 평균 4분기 매출 계산하기 - coalesce , sign 활용 (NULL의 경우 coalesce로 0으로 만든 후 sign으로 0을 호출하여 평균 계산에 포함시키지 않음)
3. 2개의 값 비율 계산하기
   1. 정수 자료형의 데이터 나누기
      1. CTR (clicks / impression) 의 경우 분모의 자료형을 double로 변환해주어야한다.
      2. percent값이기에 100.0을 곱해준다. (자동으로 자료형 변환)
   2. 0으로 나누는 것 피하기
      1. NULL전파 사용 : NULL을 포함한 데이터의 연산 결과가 모두 NULL이 되는 SQL성질
      2. NULLIF(impressions,0) , CASE WHEN impressions = 0 THEN NULL ELSE impressions END
4. 두 값의 거리 계산하기
   • 데이터 분석이라는 분야에서 물리적인 공간의 길이가 아닌 거리라는 개념이 많이 등장
   • 평균에서 어느 정도 떨어져 있는지, 작년 매출과 올해 매출에 어느 정도의 차이가 있는지 등
   • 사용자와 구매 경향이 비슷한 사용자를 뽑는 상황에서도 거리의 개념이 중요함
   1. sqrt(power(x1-x2,2)
5. 날짜/시간 계산하기
   1. 날짜 변환 timestamp(register_stamp)
   2. 날짜 더하기 timestamp_add(timestamp(register_stamp), interval 1 day)
   3. 날짜 빼기 timestamp_sub(timestamp(register_stamp), interval 1 month)
   4. 날짜 차이 구하기 date_diff(CURRENT_DATE, date(timestamp(register_stamp)), day)
   5. 등록나이 구하기 floor((CAST(replace(substring(register_stamp, 1 ,10), '-', '') AS integer) - CAST(replace(birth_date, '-', '') AS integer) ) / 10000)
   6. 현재나이 구하기 floor((CAST(replace(CAST(CURRENT_DATE AS string), '-', '') AS int64) - CAST(replace(birth_date, '-', '') AS int64) ) / 10000)
6. IP 주소 다루기
   • 보통 문자열로 저장됨
   1. 정수로 IP 주소 다루기
      1. CAST(split(ip,’-’)[SAFE_ORDINAL(1)] AS int64) AS ip_part_1
      2. CAST(split(ip,’-’)[SAFE_ORDINAL(2)] AS int64) AS ip_part_2
      3. CAST(split(ip,’-’)[SAFE_ORDINAL(3)] AS int64) AS ip_part_3
      4. CAST(split(ip,’-’)[SAFE_ORDINAL(4)] AS int64) AS ip_part_4
   2. 정수 자료형으로 변환
      1. CAST(split(ip,’-’)[SAFE_ORDINAL(1)] AS int64) * pow(2,24) AS ip_part_1
      2. CAST(split(ip,’-’)[SAFE_ORDINAL(2)] AS int64) * pow(2,16) AS ip_part_2
      3. CAST(split(ip,’-’)[SAFE_ORDINAL(3)] AS int64) * pow(2,8) AS ip_part_3
      4. CAST(split(ip,’-’)[SAFE_ORDINAL(4)] AS int64) * pow(2,0) AS ip_part_4
   3. IP 주소를 0으로 메우기
      1. CONCAT(lpad(split(ip,'.')[SAFE_ORDINAL(1)], 3, '0'), lpad(split(ip,'.')[SAFE_ORDINAL(2)], 3, '0'), lpad(split(ip,'.')[SAFE_ORDINAL(3)], 3, '0'), lpad(split(ip,'.')[SAFE_ORDINAL(4)], 3, '0'))
      2. lpad 함수 : 지정한 문자 수가 되게 문자열의 왼쪽을 메우는 함수