[데이터 분석을 위한 SQL 레시피] - 3장 데이터 가공을 위한 SQL 02

3장의 내용이 많아서 2개로 나눠서 정리하였습니다.

---

2개의 값 비율 계산하기

여기서는 하나의 레코드에 포함된 값을 조합해서 비율을 계산하는 방법을 소개하겠습니다.

다음은 광고 통계 정보(advertising_stats)를 나타내는 테이블입니다. 매일의 광고 노출 수와 클릭 수를 집계합니다. 2017-04-02의 ad_id:001 데이터는 광고를 하지 않은 날이라 노출 수와 클릭 수가 모두 0으로 되어 있다고 생각해주세요.

 

6-3 광고 통계 정보 테이블

dt	ad_id	impressions	clicks
2017-04-01	001	100000	3000
2017-04-01	002	120000	1200
2017-04-01	003	500000	10000
2017-04-02	001	0	0
2017-04-02	002	130000	1400
2017-04-02	003	620000	15000

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

다음 코드 예에서는 하루(2017-04-01) 데이터에서 각 광고의 CTR(Click Through Rate)을 계산합니다. CTR이란 '클릭/ 노출수'

입니다.

 

하나의 레코드에 포함된 값을 나눌 때는 SELECT 구문 내부에서 '/'를 사용합니다.  다만 PostgreSQL의 경우 advertising_stats 테이블의 clicks와 impression 컬럼이 정수 자료형이므로 계산 결과도 정수 자료형이 되어버려 0이 나옵니다. CAST 함수를 사용해 clicks를 '더블 프리시전(double precision) 자료형'으로 변환하고 계산해야 결과도 double precision 자료형으로 나옵니다.

 

결과를 퍼센트로 나타낼 때는 ctr 컬럼의 결과에 100을 곱합니다. 참고로 하나하나 구할 때 ctr_as_percent 컬럼처럼 click에 100.0을 곱해 계산하면, 자료형 변환이 자동으로 이루어지므로 쿼리가 간단해집니다.

 

6-7 정수 자료형의 데이터를 나누는 쿼리

SELECT
	dt
, ad_id


  -- Hive, Redshift, Bigquery, SparkSQL의 경우
  -- 정수를 나눌 때는 자동적으로 실수로 변환
, clicks / impressions AS ctr
  -- 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
;

 

실행결과

dt	ad_id	ctr	ctr_as_percent
2017-04-01	001	0.03	3.00
2017-04-01	002	0.01	1.00
2017-04-01	003	0.02	2.00

 

0으로 나누는 것 피하기

2017-04-02 데이터는 impression이 0입니다. 따라서 앞의 코드 예를 적용하면 0으로 나누게 되어 오류가 발생합니다.

0으로 나누는 것을 피하는 첫 번째 방법은 CASE 식을 사용해 impressions가 0인지 확인하는 것입니다. 다음 [코드 6-8]의 ctr_as_percent_by_case 컬럼은 impressions가 0보다 큰 경우에는 CTR을 계산하고, 이외의 경우에는 NULL을 출력합니다.

 

추가로 NULL 전파를 사용하면 0으로 나누는 것을 피할 수 있습니다. NULL 전파란 NULL을 포함한 데이터의 연산 결과가 모두 NULL이 되는 SQL의 성질입니다. [코드 6-8]의 ctr_as_percent_by_null처럼 NULLIF(impressions, 0) 부분은 impressions 값이 0이라면 NULL이 됩니다. 그 결과 impressions의 값이 0이라면 NULL 전반으로 CTR 값도 NULL이 되어, CASE 식을 사용한 방법과 같은 결과를 얻을 수 있습니다.

 

6-8 0으로 나누는 것을 피해 CTR을 계산하는 쿼리

SELECT
   dt
  ,ad_id
  -- CASE 식으로 분모가 0일 경우를 분기해서, 0으로 나누지 않게 만드는 방법
  , CASE
    WHEN impressions > 0 THEN 100.0 * clicks / impressions
   END AS ctr_as_percent_by_case
   
   -- 분모가 0이라면 NULL로 변환해서, 0으로 나누지 않게 만드는 방법
   -- PostgreSQL, Redshift, BigQuery, SparkSQL의 경우 NULLIF 함수 사용하기
  , 100.0 * clicks / NULLIF(impressins, 0) AS ctr_as_percent_by_null
  -- Hive의 경우 NULLIF 대신 CASE 식 사용하기
  -- , 100.0 * clicks
  -- / CASE WHEN impressions = 0 THEN NULL ELSE impressions END
  -- AS ctr_as_percent_by_null
  
FROM
 advertising_stats
ORDER BY
 dt, ad_id
;

 

실행결과

dt	ad_id	ctr_as_percent_by_case	ctr_as_percent_by_null
2017-04-01	001	3.00	3.00
2017-04-01	002	1.00	1.00
2017-04-01	003	2.00	2.00
2017-04-02	001
2017-04-02	002	1.07	1.07
2017-04-02	003	2.41	2.41

 

정리

이번에 소개한 비율도 데이터분석에서 자주 사용되는 계산이지만, 정수로 나누거나 0으로 나누는 등의 실수를 할수 있습니다. 따라서 이러한 부분을 꼭 기억해 주시기 바랍니다.

 

두 값의 거리 계산하기

이번에는 두 값을 입력하고, 이들 값이 서로 어느 정도 덜어져 있는지 나타내는 '거리'를 계산하는 방법을 소개하겠습니다.

'거리'라고 하면 물리적인 공간의 길이를 상상하기 쉽지만, 데이터 분석이라는 분야에서는 물리적인 공간의 길이가 아닌 거리라는 개념이 많이 등장합니다. 예를 들어 시험을 보았을 때 평균에서 어느정도 덜어져 있는지, 작년 매출과 올해 매출에 어느 정도의 차이가 있는지 등을 모두 거리라고 부릅니다. 추가로 어떤 사용자가 있을 때, 해당 사용자와 구매 경향이 비슷한 사용자를 뽑는 등의 응용 상황에서도 거리라는 개념이 굉장히 중요하게 작용합니다.

 

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

6-4 일차원 위치 정보(location_1d)테이블

x1	x2
5	10
10	5
-2	4
3	3
0	1

 

앞의 location_1d 테이블에서 숫자 데이터 x1, x2를 기반으로 거리를 구합니다. 이때 절댓값을 사용하는 방법과 제곱 평균 제곱근을 사용하는 방법을 모두 사용해보겠습니다. 절댓값을 계산할 때는 ABS함수(abstract)를 사용합니다. 제곱 평균 제곱근은 두 값의 차이를 제곱한 뒤 제곱근을 적용해서 나오는 값을 의미합니다. 제곱을 할 때는 POWER 함수, 제곱근을 구할 때는 SQRT 함수를 사용합니다. 값이 일차원이라면 절댓값과 제곱 평균 제곱근은 같은 결과를 냅니다.

 

6-9 일차원 데이터의 절댓값과 제곱 평균 제곱근을 계산하는 쿼리

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

실행결과

abs	rms
5	5
5	5
6	6
0	0
1	1

 

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

6-5 이차원 위치 정보(location_2d) 테이블

x1	y1	x2	y2
0	0	2	2
3	5	1	2
5	3	2	1

 

이어서 xy 평면 위에 있는 두 점(x1, y1)과 (x2, y2) 사이의 유클리드 거리를 계산해봅시다. 유클리드 거리는 물리적인 공간에서 거리를 구할 때 사용하는 일반적인 방법입니다. 방금 살펴 보았던 제곱 평균 제곱근을 사용해 구하면 됩니다.

 

추가로 PostgreSQL에는 POINT 자료형이라고 불리는 좌표를 다루는 자료 구조가 있습니다.

따라서 POINT 자료형 데이터로 변환하고 거리 연산자 <->를 사용하면 됩니다. 이때 내부적으로 유클리드 거리와 완전히 같은 계산이 이루어집니다.

 

6-10 이차원 테이블에 대해 제곱 평균 제곱근(유클리드 거리)을 구하는 쿼리

SELECT
  sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) AS dist
  -- PostgreSQL의 경우는 point 자료형과 거리연산자 <-> 사용하기
  -- , point(x1, y1) <-> point(x2, y2) AS dist
FROM location_2d
;

실행결과

dist

2.82842712474619

3.60555127546399

3.60555127546399

 

정리

거리 계산을 어떻게 데이터 활용에 사용하는지 감이 잡히지 않을 것입니다. 거리 계산은 이후 7장에서 소개할 유사도 계산, 8장에서 다룰 추천 구현의 기초가 되는 개념입니다. 따라서 계산하는 방법을 꼭 기억하고 넘어가기 바랍니다.

 

날짜/시간 계산하기

앞서 5강 4절에서는 날짜 자료형과 타임스탬프 자료형을 만드는 방법과 요소를 추출하는 방법 등을 살펴보았습니다. 이번 절에서는 두 날짜 데이터의 차이를 구하거나, 시간 데이터를 기준으로 1시간 후의 시간을 구하는 방법을 소개하겠습니다.

 

다음 테이블은 서비스 사용자의 등록 시간과 생일 정보를 포함하는 사용자 마스터 테이블입니다. 이러한 샘플을 기반으로 회원 등록일 시점의 나이를 계산하는 쿼리를 생각해봅시다. 나이는 시간의 경과에 따라서 변화하므로, 일반적으로 생년월일을 저장하고, 이후에 계산해서 나이를 구하게 됩니다. 나이를 사용하면 제품의 구매 연령대, 서비스를 사용하는 사용자의 연령 분포 등을 확인할 수 있습니다.

 

6-6 등록시간과 생일을 포함하는 사용자 마스터(mst_users_with_dates)테이블

user_id	register_stamp	birth_date
U001	2016-02-28 10:00:00	2000-02-29
U002	2016-02-29 10:00:00	2000-02-29
U003	2016-03-01 10:00:00	2000-02-29

 

기본적인 날짜/시간 데이터 계산 방법으로 정수의 덧셈과 뺄셈을 사용하는 방법을 살펴봅시다. 코드 예로 회원 등록 시간 1시간 후와 30분 전의 시간, 등록일의 다음날과 1달 전의 날짜를 계산하는 쿼리를 소개하면 다음과 같습니다. 날짜/시간 계산 방법은 미들웨어에 따라 다릅니다.

 

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

, register_stamp::date AS register_date
, register_stamp::date + '1 day' :: interval ):: date AS after_1_day
, register_stamp::date - '1 month' :: interval) ::date AS before_1_month

-- Redshift의 경우 dateadd 함수 사용하기
-- , register_stamp::timestamp AS register_stamp
-- , dateadd(hour, 1, register_stamp::timestamp) AS after_1_hour
-- , dateadd(minute, -30, register_stamp::timestamp) AS before_30_minutes
--
-- , register_stamp::date AS register_date
-- , dateadd(day, 1, register_stamp::date) AS after_1_day
-- , dateadd(month, -1, register_stamp::date) AS before_1_month

-- BigQuery의 경우 timestamp_add/sub, date_add/sub 등의 함수 사용하기
-- , timestamp(register_stamp) AS register_stamp
-- , timestamp_add(timestamp(register_stamp), interval 1 hour) AS after_1_hour
-- , timestamp_sub(timestamp(register_stamp), interval 30 minute) AS before_30_minutes
--
-- 타임스탬프 문자열을 기반으로 직접 날짜 계산을 할 수 없으므로 타임스탬프 자료형을 날짜/시간
-- 자료형으로 변환한 뒤 계산하기
-- , date(timestamp(register_stamp)) AS register_date
-- , date_add(date(timestamp(register_stamp)), interval 1 day) AS after_1_day
-- , date_sub(date(timestamp(register_stamp)), interval 1 month) AS before_1_month

-- Hive, SparkSQL의 경우 날짜/시각을 계산하기 함수가 제공되지 않으므로
-- 한 번 unixtime으로 변환하고 초 단위로 계산을 적용한 뒤 다시 타임스탬프로 변환하기
-- , CAST(register_stamp AS timestamp) AS register_stamp
-- , from_unixtime(unix_timestamp(register_stamp) + 60 * 60) AS after_1_hour
-- , from_unixtime(unix_timestamp(register_stamp) - 30 * 60) AS before_30_minutes
--
-- 타임스탬프 문자열을 날짜로 변환할 때는 to_date 함수 사용하기
-- * 다만 HIVE 2.1.0 이전 버전에서는 문자열 자료형으로 리턴함
-- , to_date(register_stamp) AS register_date
-- 
-- 일과 월을 계산할 때는 date_add 함수와 add_months 함수 사용하기
-- 다만 연을 계산하는 함수는 구현되어 있지 않음
-- , date_add(to_date(register_stamp), 1) AS after_1_day
-- , add_months(to_date(register_stamp), -1) AS before_1_month
FROM mst_users_with_dates
;

 

실행결과

-[ RECORD 1] ------+----------

USER_ID | U001
register_stamp | 2016-02-28 10:00:00
after_1_hour | 2016-02-28 11:00:00
before_30_minutes | 2016-02-28 09:30:00
register_date | 2016-02-28
after_1_day | 2016-02-29
before_1_month | 2016-01-28
-[ RECORD 2] ------+----------
...

 

날짜 데이터들의 차이 계산하기

두 날짜 데이터를 사용해서 날짜의 차이를 계산하는 방법을 소개하겠습니다. 다음 코드 예는 회원 등록일과 현재 날짜의 날짜 차이, 그리고 회원 등록일과 생년월일과의 날짜 차이를 계산하는 쿼리입니다. 날짜 차이를 계산하는 방법은 미들웨어에 따라 다르지만, 대부분 간단합니다.

 

6-12 두 날짜의 차이를 계산하는 쿼리

SELECT
	user_id


-- PostgreSQL, Redshift의 경우 날짜 자료형끼리 뺄 수 있음
, CURRENT_DATE AS today
, register_stamp::date AS register_date
, CURRENT_DATE - register_stamp::date AS diff_days

-- Bigquery의 경우 date_diff 함수 사용하기
--, CURRENT_DATE AS today
--, date(timestamp(register_stamp)) AS register_date
--, date_diff(CURRENT_DATE, date(timestamp(register_stamp)), day) AS diff_days

-- Hive, SparkSQL의 경우 datediff 함수 사용하기
--, CURRENT_DATE() AS today
--, to_date(register_stamp) AS register_date
--, datediff(CURRENT_DATE(), to_date(register_stamp)) AS diff_days
FROM mst_users_with_dates
;

 

실행결과

user_id	today	register_date	diff_days
U001	2017-02-05	2016-02-28	343
U002	2017-02-05	2016-02-29	342
U003	2017-02-05	2016-03-01	341

 

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

6-13 age 함수를 사용해 나이를 계산하는 쿼리

SELECT
   user_id
   
   
-- PostgreSQL의 경우 age 함수와 EXTRACT 함수를 사용해 나이 집계하기
, CURRENT_DATE AS today
, register_stamp::date AS register_date
, birth_date::date AS birth_date
, EXTRACT(YEAR FROM age(birth_date::date)) AS current_age
, EXTRACT(YEAR FROM age(register_stamp::date, birth_date::date)) AS register_age
FROM mst_users_with_dates
;

실행결과

user_id	today	register_date	birth_date	current_age	register_age
U001	2017-02-05	2016-02-28	2000-02-29	16	15
U002	2017-02-05	2016-02-29	2000-02-29	16	16
U003	2017-02-05	2016-03-01	2000-02-29	16	16

 

Redshift의 datediff 함수, Bigquery의 date_diff 함수는 day단위가 아니라 year 단위로 출력하도록 출력 단위를 지정할 수 있습니다. 이를 사용하면 나이를 계산할 수 있을 것 같습니다. 하지만 다음 코드처럼 이러한 함수로 year를 지정한 경우, 연 부분만의 차이가 계산될 뿐입니다. 따라서 해당 연의 생일을 넘었는지 등이 계산되는 것은 아니므로, 제대로 된 나이가 계산되지 않습니다.

 

6-14 연 부분 차이를 계산하는 쿼리

SELECT
   user_id
   
   
-- Redshift의 경우 datediff 함수로 year을 지정하더라도, 연 부분에서의 차이를 계산할 수 없음
, CURRENT_DATE AS today
, register_stamp::date AS register_date
, birth_date::date AS birth_date
, datediff(year, birth_date::date, CURRENT_DATE)
, datediff(year, birth_date::date, register_stamp::date)

-- BigQuery의 경우 date_diff 함수로 year을 지정하더라도, 연 부분에서의 차이를 계산할수 없음
-- , CURRENT_DATE AS today
-- , date(timestamp(register_stamp)) AS register_date
-- , date(timestamp(birth_date)) AS birth_date
-- , date_diff(CURRENT_DATE, date(timestamp(birth_date)), year) AS current_age
-- , date_diff(date(timestamp(register_stamp)), date(timestamp(birth_date)), year)
	AS register_age
FROM mst_users_with_dates
;

 

실행결과

user_id	today	register_date	birth_date	current_age	register_age
U001	2017-02-05	2016-02-28	2000-02-29	17	16
U002	2017-02-05	2016-02-29	2000-02-29	17	16
U003	2017-02-05	2016-03-01	2000-02-29	17	16

 

전용 함수를 사용하지 않고 나이를 계산하려면, 날짜를 고정 자리 수의 정수로 표현하고 그 차이를 계산하면 됩니다. 예를 들어 다음 코드는 날짜를 정수로 표현해서 나이를 계산하는 쿼리입니다. 날짜의 정수 표현이란 예를 들어 2016년 2월 28일은 '20160228', 2000년 2월 29일은 '20000229'처럼 나타내는 것을 의미합니다. 이러한 날짜를 빼고 10.000으로 나누면 나이가 계산됩니다.

 

6-15 날짜를 정수로 표현해서 나이를 계산하는 함수

-- 생일이 2000년 2월 29일인 사람의 2016년 2월 28일 시점의 나이 계산하기
SELECT floor((20160228 - 20000229) / 10000) AS age;

 

AGE

15

 

다음 예는 샘플데이터를 기반으로, 등록 시점과 현재 시점의 나이를 문자열 변환을 사용해 계산하는 쿼리입니다. 문자열에서 하이픈을 제거하고 정수로 캐스트 하는 것 뿐이지만, 미들웨어에 따라 데이터 자료형 또는 함수의 구현이 다르므로 조금씩 차이가 있습니다.

 

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
   
-- BigQuery의 경우 text를 string, integer를 int64로 바꾸기
-- (CAST(replace(CAST(CURRENT_DATE AS string), '-', '') AS int64)
-- ) / 10000

FROM mst_users_with_dates
;

 

실행결과

user_id	register_date	birth_date	register_age	current_age
U001	2016-02-28	2000-02-29	15	16
U002	2016-02-29	2000-02-29	16	16
U003	2016-03-01	2000-02-29	16	16

 

정리

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

 

IP 주소 다루기

일반적인 웹서비스 로그 데이터에 사용자 IP 주소를 저장합니다. 보통 IP 주소를 로그로 저장할 때는 문자열로 저장합니다. 간단하게 IP 주소를 확인하거나 할 때는 문자열로 다루어도 충분하지만, IP 주소를 서로 비교하거나 동일한 네트워크의 IP 주소인지 판정할 때는 단순 문자열 비교만으로는 굉장히 코드가 복잡해집니다. 여기서는 IP 주소들을 비교하는 방법을 소개하겠습니다.

 

IP 주소 자료형 활용하기

postgresSQL 에는 IP 주소를 다루기 위한 inet 자료형이 구현되어 있습니다. inet 자료형을 사용하면 IP 주소를 쉽게 비교할 수 있습니다. inet 자료형의 대소를 비교할 때는 < 또는 > 를 사용합니다.

 

6-17 inet 자료형을 사용한 IP 주소 비교 쿼리

SELECT
  CAST('127.0.0.1' AS inet) < CAST ('127.0.0.2'   AS inet) AS lt
 ,CAST('127.0.0.1' AS inet) > CAST ('192.168.0.1'   AS inet) AS gt
 ;

실행결과

lt	gt
t	f

 

추가로 address/y 형식의 네트워크 범위에 IP 주소가 포함되는지도 판정할 수 있습니다. 판정에는 <<또는>> 연산자를 사용합니다.

 

6-18 inet 자료형을 사용해 IP 주소 범위를 다루는 쿼리

SELECT CAST('127.0.0.1' AS inet) << CAST('127.0.0.0/8' AS inet) AS is_contained;

 

실행결과

is_contained

T