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

 

여러 값을 집약해서 하나의 값으로 만들거나, 다양한 값을 비교하거나, 여러 값들을 조합해서 새로운 값을 집계함으로써 새로운 지표를 정의할 수 있다.

---

1. 문자열 연결하기

mst_user_location

select
	user_id,
	concat(pref_name,city_name) as pref_city1,
	concat(pref_name,' ',city_name) as pref_city2,
	pref_name || city_name as pref_city3,
	concat(pref_name, concat(pref_name,city_name)) as pref_city4
	
from mst_user_location;

concat 함수로 문자열을 연결할 수 있음

공백을 넣고 싶을 땐, ' '(공백)을 추가로 인수로 넣어주면 됨

|| 연산자도 같은 기능을 함

+ concat 함수 안에 concat함수를 넣어서 활용 가능

---

2. 여러 개의 값 비교하기

quarterly_sales

select
	year,
	q1,
	q2,
	case
	when q1<q2 then '+'
	when q1>q2 then '-'
	else ' '
	end as judge_q1_q2,
	q2 - q1 as diff,
	sign(q2-q1) as sign_diff
from
	quarterly_sales
order by 
	year;

 

CASE 에 익숙해지자 : 테이블 내의 칼럼들의 값들을 통해 새로운 값 도출

sign 함수 : 1, 0, -1 로 표현 -> 증감 표현 가능!

 

select
	year,
	greatest(q1,q2,q3,q4) as greatest_sales1,
	greatest(q1) as greatest_sales2,
	least(q1,q2,q3,q4) as least_sales,
	(q1+q2+q3+q4)/4 as average1,
	(coalesce(q1,0) + coalesce(q2,0) + coalesce(q3,0) + coalesce(q4,0))/4 as average2,
	(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 average3
from
	quarterly_sales
order by
	year;

 

greatest , least

- 컬럼 값에서 최댓값 또는 최솟값을 찾는 함수

- greatest_sale2를 보면, 컬럼 값들중에서 각 행별로 최댓값을 출력하기 때문에 컬럼들이 q1 하나밖에 없다면 그냥 q1컬럼이 출력됨

 

단순히 평균을 낼 때, 컬럼 값을 더해서 컬럼 수로 나눠도 되지만 null 값이 있다면 그 수 만큼 나눠야할 컬럼수가 줄어들게 됨. average3 처럼 coalesce 함수를 사용하여 연산 결과가 null이 나오지 않게 해주고, sign 함수를 통해 나눠야할 수를 정해줘야 타당한 평균이라고 할 수 있음

---

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

advertising_stats

select 
	dt,
	ad_id,
	case
	when impressions > 0 then round(100.0 * clicks/impressions,2)
	end as ctr1,
	100.0 * clicks/nullif(impressions, 0) as ctr2
from
	advertising_stats
order by
	dt, ad_id;

 

비율은 데이터 분석에서 자주 사용되는 계산

select 구문 내부에서 / 를 사용하여 비율을 알 수 있음

 

100을 곱하면 결과값이 정수형(integer)임. 100.0 을 곱하면 결과값이 수치형(numeric)

round 함수로 소숫점 자리수 조절

 

나눠야할 수(분모)가 0 일 때 해야할 처리 -> 그대로 0으로 나누면 오류 발생, null이 나오게 해줘야함

- case 문을 사용하여 0보다 큰 경우(0이 아닌 경우)만 연산이 되도록 하기 -> case 외의 연산은 null 반환

- nullif를 사용하여 0인 경우에 null 값이 되게함 -> null 전파(null 연산은 결과가 null)

 

ifnull vs nullif

ifnull(1,2) : 1이 null 이면 2 반환 -> coalesce 함수와 같은 기능. coalesce는 1,2 뿐만 아니라 여러 값을 넣을 수 있음

nullif(1,2) : 1과 2가 같으면 null 반환 -> nullif(impression, 0) : impression이 0이면 null 반환

---

4. 두 값의 거리 계산하기

구매 성향이 비슷한 사용자를 뽑거나 할 때도 거리는 유용하다

 

select
	abs(x1-x2) as abs,
	sqrt(power(x1-x2, 2)) as rms,
	sqrt(power(x1-x2,2)+power(y1-y2,2)) as dist1,
	point(x1,y1) <-> point(x2,y2) as dist2
from location_2d;

 

abs : 절댓값

power : 제곱

sqrt : 제곱근

 

위 함수들로 유클리드 거리를 구할 수 있음

psql에서는 point 자료형 존재 -> 좌표를 다루는 자료 구조

point 자료형으로 변환하고 <->(거리연산자)를 사용해서 거리 구할 수 있음

---

5. 날짜/시간 계산하기