3장 데이터 가공을 위한 SQL

6강 여러 개의 값 조작하기

• 새로운 지표 정의하기
  CTR(클릭 비율), CVR(컨버전 비율) 등

1. 문자열 연결하기

코드 6-1

SELECT user_id
	 , concat(pref_name, city_name) AS pref_city
FROM mst_user_location

CONCAT( ) 함수는 concatenate two or more strings except NULL specified in the arguments (링크)

2. 여러 개의 값 비교하기

분기별 매출 증감 판정하기

코드 6-2

SELECT year, q1, q2
	   -- Q1과 Q2의 매출 변화 평가하기
	 , CASE
	 	WHEN q1 < q2 THEN '+'
	 	WHEN q1 = q2 THEN ' '
	 	ELSE '-'
	   END AS judge_q1_q2
	   -- Q1과 Q2의 매출액의 차이 계산하기
	 , q2 - q1 AS diff_q2_q1
	   -- Q1과 Q2의 매출 변화를 1, 0, -1로 표현하기
	 , sign(q2-q1) AS sign_q2_q1
FROM quarterly_sales
ORDER BY year

SIGN( ) 함수는 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1 리턴
return the sign of a given number (링크)

연간 최대/최소 4분기 매출 찾기

코드 6-3

SELECT year
	 , greatest(q1, q2, q3, q4) AS greatest_sales
	 , least(q1, q2, q3, q4) AS least_sales
FROM quarterly_sales
ORDER BY year

greatest( ) 와 least( ) 함수는 최대/최소 값을 선택. 목록의 NULL 값은 무시한다. (링크)

연간 평균 4분기 매출 계산하기

코드 6-6

SELECT year
     , (COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0))
       / (SIGN(COALESCE(q1, 0)) + SIGN(COALESCE(q2, 0)) + SIGN(COALESCE(q3, 0)) + SIGN(COALESCE(q4, 0)))
       AS average
FROM quarterly_sales
ORDER BY year

3. 2개의 값 비율 계산하기

정수 자료형의 데이터 나누기

코드 6-7

SELECT dt, ad_id
	 -- , clicks / impressions
	   -- postgreSQL의 경우 정수를 나누면 소수점이 잘리므로 명시적으로 자료형 변환
	 , CAST(clicks AS double precision) / impressions AS ctr
	   -- 실수를 상수로 앞에 두고 계산하면 암묵적으로 자료형 변환이 일어남
	 , 100.0 * clicks / impressions AS ctr_as_percent
FROM advertising_stats
WHERE dt = '2017-04-01'
ORDER BY dt, ad_id

0으로 나누는 것 피하기

코드 6-8

SELECT dt, ad_id
       -- 방법1
	 , CASE
	 	WHEN impressions > 0 THEN 100.0 * clicks / impressions
	 END AS ctr_as_percent_by_case
	   -- 방법2
	 , 100.0 * clicks / NULLIF(impressions, 0) AS ctr_as_percent_by_null    
FROM advertising_stats
ORDER BY dt, ad_id

4. 두 값의 거리 계산하기

sql abs함수, power함수, sqrt함수

• 물리적인 공간의 길이가 아닌 거리라는 개념. 떨어진 정도, 차이가 있는지 등을 거리라고 한다.

숫자 데이터의 절댓값, 제곱 평균 제곱근(RMS) 계산하기

코드 6-9

SELECT abs(x1-x2) AS abs
     , sqrt(power(x1-x2, 2)) AS rms
FROM location_1d

xy 평면 위에 있는 두 점의 유클리드 거리 계산하기

코드 6-10

SELECT sqrt(power(x1-x2, 2) + power(y1-y2, 2)) AS dist
FROM location_2d

• 거리 계산은 7장 유사도 계산, 8장 추천 구현의 기초가 되는 개념임.

5. 날짜/시간 계산하기

코드 6-11

SELECT user_id
       -- postgreSQL의 경우 interval 자료형의 데이터에 사칙 연산 적용하기
     , register_stamp::timestamp AS register_stamp
     , register_stamp::timestamp + '1 hour'::interval AS after_1_hour
     , register_stamp::timestamp - '30 minutes'::interval AS before_30_minutes
FROM mst_users_with_dates

사용자의 생년월일로 나이 계산하기

코드 6-16

SELECT user_id
	 , substring(register_stamp, 1, 10) AS register_date
	 , birth_date
	   -- 등록 시점의 나이 계산하기
	 , floor(
	 	(CAST(REPLACE(substring(register_stamp, 1, 10), '-', '') AS integer)
	 	 - CAST(REPLACE(birth_date, '-', '') AS integer)
	 	 ) / 10000
	   ) AS register_age
	   -- 현재 시점의 나이 계산하기
	 , floor(
	 	(CAST(REPLACE(CAST(current_date AS text), '-', '') AS integer)
	 	 - CAST(REPLACE(birth_date, '-', '') AS integer)
	 	 ) / 10000
	   ) AS current_age
FROM mst_users_with_dates

• 날짜/시간 데이터 계산은 미들웨어에 따라 표현에 차이가 크다.
  실무에서는 수치 또는 문자열 등으로 변환해 다루는 것이 편한 경우도 많다.