[데이터분석을 위한 SQL 레시피] 6강 : 여러 개의 값에 대한 조작

💡 새로운 지표 정의하기

• 데이터 집계를 통해 유의미한 지표를 정의하고 활용할 수 있음!
• 단순한 숫자 비교는 큰 데이터에만 주목하게 하지만, ‘개인별’, ‘비율’ 같은 지표를 사용하면 다양한 관점에서 데이터를 바라볼 수 있다
  ex) <페이지 뷰> / <방문자 수> : ‘사용자 한 명이 페이지를 몇번이나 방문했는가?’
  CTR (클릭비율:Click Through Rate)- 웹사이트에서는 방문한 사용자 수 중에서 특정한 행동을 시행한 사용자의 비율 CVR (전환율 : Conversion Rate ) - 전환율은 얼마나 많은 사람들이 광고를 본 후 행동을 '전환'했는지를 측정

1. 문자열 연결하기

: CONCAT 함수 or || 연산자를 이용

SELECT 
	user_id, CONCAT(pref_name, city_name) AS pref_city, pref_name || city_name AS pref_city2
FROM mst_user_location;

 

•  

2. 여러 개의 값 비교하기

: 하나의 레코드에 포함된 여러 개의 값을 비교하기

4분기 매출 테이블

분기별 매출 증감 판정하기 : 2개의 컬럼 비교

• 방법 1. CASE 문을 이용해 각 컬럼의 대소 비교
• 방법 2. 값의 차이 구하는 diff_q2_q1 변수 정의 후, SIGN 함수 이용 ‼️ SIGN 함수 → 매개변수 양수면 1, 0이면 0, 음수면 -1 리턴

SELECT 
	year, q1, q2,
CASE
	WHEN q1 < q2 THEN '+'
	WHEN q1 = q2 THEN ' '
	ELSE '-'
END AS judge_q1_q2
 , q2-q1 AS diff_q2_q1
, SIGN(q2 - q1) AS sign_q2_q1
FROM quarterly_sales ORDER BY year;

연간 최대/ 최소 4분기 매출 찾기 : 3개 이상의 컬럼 비교

→ greatest(컬럼1,컬럼2,..) 함수 or least(컬럼1,컬럼2,...)함수

# q1 ~ q4의 최대/최소 매출 구하기
SELECT 
	year, greatest(q1,q2,q3,q4) as greatest_sales,
	least(q1,q2,q3,q4) as least_sales
FROM quarterly_sales ORDER BY year;

연간 평균 4분기 매출 계산하기 : 여러개의 컬럼 사칙연산

# q1 ~ q4의 평균 매출 구하기
SELECT 
	year, (q1+q2+q3+q4) / 4 AS average
FROM quarterly_sales ORDER BY year;

2017년에 NULL 값이 있었음!

‼️ 이때, NULL값 사칙 연산 주의! → COALESCE 함수 이용해 변환하기!

# q1 ~ q4의 평균 매출 구하기 - NULL 있는 경우
SELECT 
	year, (COALESCE(q1,0) + COALESCE(q2,0) + COALESCE(q3,0) + COALESCE(q4,0)) / 4 AS average
FROM quarterly_sales ORDER BY year;

2017년엔 NULL값이 있는데 이걸 똑같이 4로 나누니까 평균값이 너무 작아져버림! → 4로 나누지 말고 NULL이 아닌 컬럼의 수를 계산해야 함!

‼️ NULL이 아닌 컬럼 수로 나누기! →SIGN 함수와 COALESCE 함수 조합

# q1 ~ q4의 평균 매출 구하기 - NULL 있는 경우
# SIGN(COALESCE(q1,0)) : q1이 NULL 아니면 +1, q1이 NULL이면 +0
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개의 값 비율 계산하기

‼️ 비율 구할 때는, 정수로 나누거나 0으로 나누는 거 조심할 것!

매일의 광고 노출 수 와 클릭 수 집계

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

• CTR 계산 : 클릭 수 / 노출 수
  • 정수 자료형을 나누면, 계산 결과 또한 정수형으로 나오므로 0을 반환
  → cast 함수를 이용해 clicks를 double 자료형으로 변환하고 계산 → 계산 결과도 double 형으로 나옴!
  • 결과를 퍼센트로 나타낼 때, ctr 컬럼 결과에 100을 곱하기 → 100.0 을 곱하면 자동으로 자료형변환 일어남!

  # cast 함수를 이용해 double 자료형으로 변환하고 계산
  # 실수를 상술 앞에 두고 계산하면 암묵적으로 자료형 변환이 일어남
  SELECT dt, ad_id, CAST(clicks AS double precision) / impressions AS ctr, 
  100.0 * clicks / impressions AS ctr_percent
  FROM advertising_stats
  WHERE dt = '2017-04-01'
  ORDER BY dt, ad_id;
  

0으로 나누는 것 피하기

2017-04-02의 impression 0이므로, 0으로 나눌 수 없어서 error 발생

→ 방법 1. CASE 식을 이용해 impressions가 0인지 확인

→ 방법 2. NULL 전파를 사용 : NULL을 포함한 데이터의 연산 결과가 모두 NULL이 되는 SQL 성질

NULLIF() 를 이용해 분모가 0이면 NULL로 변환해서 0으로 나누지 않게 하기!

# 1. CASE 식을 이용해 0인지 확인
# 2. NULLIF 이용
SELECT dt, ad_id, 
CASE
	WHEN impressions > 0 THEN 100.0 * clicks / impressions
END AS ctr_as_percent_by_case
, 100.0 * clicks / NULLIF(impressions,0) AS ctr_as_percent_by_null
FROM advertising_stats
ORDER BY dt, ad_id;

4. 두 값의 거리 계산하기

‘거리’ : 평균과 시험 점수가 얼마나 떨어져 있는지, 작년 매출과 올해 매출에 어느정도 차이가 있는지.., 특정 사용자와 특정 사용자의 구매 경향이 얼마나 비슷한지…

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

x1, x2를 기반으로 거리 구하기 → 1차원 데이터

방법1. 절댓값 거리 → ABS() 함수 사용

방법2. 제곱평균제곱근 이용 → 두 값의 차이 제곱(POWER() 함수) 한 후, 제곱근(SQRT()함수) 씌우기!

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

값이 1차원이면, 두개 모두 같은 값!

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

이차원 테이블

유클리드 거리 → 제곱 평균 제곱근 이용!

• PostgreSQL에는 POINT 자료형 이라는 좌표를 다루는 자료구조가 있어서, POINT 자료형으로 변환한 후 거리 연산자 ↔ 를 이용해서 유클리드 거리를 구현할 수 있음!

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

5. 날짜,시간 계산하기

• 두 날짜 데이터의 차이 구하거나, 1시간 후 시간 구하는 방법

등록시간과 생일 포함하는 데이터

미래 또는 과거의 날짜/시간을 계산

• 회원 등록 시간 1시간 후와 30분 전의 시간, 등록일의 다음날과 1달 전의 날짜를 계산
• 정수의 덧셈과 뺄셈 사용
• postgresql의 경우 interval 자료형 이용 ‼️ interval 타입은 year, month, day, hour, minute, second 등의 단위와 결합하여 날짜/시간 연산이 가능

SELECT user_id, 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
FROM mst_users_with_dates;

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

두 날짜 데이터 이용해 날짜의 차이 계산하기

• 회원 등록일과 현재 날짜의 차이

# PostgreSQL의 경우 날짜 자료형 끼리 뺄 수 있음
SELECT user_id, 
CURRENT_DATE AS today, register_stamp::Date AS register_date,
CURRENT_DATE - register_stamp::date AS diff_days
FROM mst_users_with_dates;

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

날짜 차이 구하는 것보다 나이 계산은 조금 복잡 → 단순하게 365일로 나누면 되는 게 아님! 윤년도 고려해야 함!

방법1. PostgreSQL에는 나이 계산 전용함수가 구현 되어 있음! AGE() 함수

# EXTRACT함수로 연도 부분만을 추출
SELECT user_id,
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;

cf) EXTRACT() 안 써주면, interval 자료형의 날짜 단위 출력됨 → EXTRACT로 연도만 추출!

방법2. 날짜를 정수로 표현해서 빼준 후, 10,000으로 나눠주기

• 날짜를 고정 자리 수의 정수로 표현!

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

방법3. 문자열로 계산하기

• 문자열에서 하이픈을 제거하고, 정수로 캐스트!

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;

6. IP 주소 자료형 활용하기

• IP 주소 다루기 위한 inet 자료형 구현되어 있음!
• 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('127.0.0.2' AS inet) AS gt;

→ 조건에 해당할경우 t, 아닐 경우 f 리턴

• address/y 형태의 네트워크 범위에 IP 주소 포함 여부 판정 가능<< 또는 >> 연산자 사용

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

정수 또는 문자열로 IP 다루기

• inet 자료형처럼 안될 경우! → 정수 or 문자열로 IP 다루기
• split_part() 함수를 이용해 문자열 분해한 후, 정수형으로 바꾸기!

SELECT
  ip
  , CAST(split_part(ip, '.', 1) AS integer) AS ip_part_1
  , CAST(split_part(ip, '.', 2) AS integer) AS ip_part_2
  , CAST(split_part(ip, '.', 3) AS integer) AS ip_part_3
  , CAST(split_part(ip, '.', 4) AS integer) AS ip_part_4
FROM
  (SELECT CAST('192.168.0.1' AS text) AS ip) AS t;

• 추출한 4개의 10진수 부분을 2^24, 2^16, 2^8, 2^0으로 곱하고 더하면 정수형 자료형 표기로 변환 → 대소 비교 및 범위 판정이 가능

SELECT
  ip
  , CAST(split_part(ip, '.', 1) AS integer) * 2^24
    + CAST(split_part(ip, '.', 2) AS integer) * 2^16
    + CAST(split_part(ip, '.', 3) AS integer) * 2^8
    + CAST(split_part(ip, '.', 4) AS integer) * 2^0
  AS ip_integer FROM (SELECT CAST('192.168.0.1' AS text) AS ip) AS t;

• IP 주소를 0으로 메우기→ lpad함수를 이용! : 지정한 문자 수가 되게 문자열의 왼쪽을 메움!
  • 데이터 집계를 통해 유의미한 지표를 정의하고 활용할 수 있음!
  • 단순한 숫자 비교는 큰 데이터에만 주목하게 하지만, ‘개인별’, ‘비율’ 같은 지표를 사용하면 다양한 관점에서 데이터를 바라볼 수 있다

SELECT
  ip
  , lpad(split_part(ip, '.', 1), 3, '0')
    || lpad(split_part(ip, '.', 2), 3, '0')
    || lpad(split_part(ip, '.', 3), 3, '0')
    || lpad(split_part(ip, '.', 4), 3, '0')
  AS ip_padding FROM (SELECT CAST('192.168.0.1' AS text) AS ip) AS t;