3장 데이터 가공을 위한 SQL

7강 하나의 테이블에 대한 조작

2. 그룹 내부의 순서

ORDER BY 구문과 집약 함수 조합하기

SELECT product_id, score
     , ROW_NUMBER() OVER(ORDER BY score DESC) AS row
       -- 순위 상위부터의 누계 점수 계산하기
     , sum(score) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_score
       -- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
     , avg(score) OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS local_avg
     , FIRST_VALUE(product_id) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value
     , LAST_VALUE(product_id) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM popular_products
ORDER BY row

윈도 프레임 지정에 대해서
ROWS BETWEEN start AND end start와 end에는 CURRENT ROW, n PRECEDING, n FOLLOWING, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING 등 키워드 지정한다.

PARTITION BY 와 ORDER BY 조합하기

SELECT *
FROM (SELECT category, product_id, score
           , ROW_NUMBER() OVER(PARTITION BY category ORDER BY score DESC) AS rank
      FROM popular_products
	  ) AS popular_products_with_rank
WHERE RANK <= 2
ORDER BY category, rank

3. 세로 기반 데이터를 가로 기반으로 변환하기

sql string_agg함수

행을 열로 변환하기

• 미리 열의 종류와 수를 알고 있을 때만 사용 가능
  코드 7-10

SELECT dt
     , max(CASE WHEN indicator = 'impressions' THEN val END) AS impressions 
	 , max(CASE WHEN indicator = 'sessions' THEN val END) AS sessions
	 , max(CASE WHEN indicator = 'users' THEN val END) AS users
FROM daily_kpi
GROUP BY dt
ORDER BY dt

dt	impressions	sessions	users
2017-01-01	1800	500	200
2017-01-02	2000	700	250

CASE 표현식의 결과는 리스트 형식. 스칼라(값) 하나를 추출하기 위해 MIN/MAX 등의 함수를 사용한다.

행을 쉼표로 구분한 문자열로 집약하기

• 열의 종류와 수를 모를 때
  

코드 7-11

SELECT purchase_id
       -- 상품 ID를 배열에 집약하고 쉼표로 구분된 문자열로 변환하기
     , string_agg(product_id, ',') AS product_ids
     , sum(price) AS amount
FROM purchase_detail_log
GROUP BY purchase_id
ORDER BY purchase_id

purchase_id	product_ids	amount
100001	A001,A002,A003	9000
100002	D001,D002	8000
100003	A001	3000

string_agg( ) 함수는 문자열 목록을 연결하고 문자열 사이에 구분 기호를 배치하는 집계 함수(링크)

4. 가로 기반 데이터를 세로 기반으로 변환하기

sql unnest함수, explode함수, CROSS JOIN, LATERAL VIEW, regexp_split_to_table함수

열로 표현된 값을 행으로 변환하기

• 고정 길이의 데이터를 행으로 전개
  

코드 7-12

SELECT q.YEAR
     , CASE 
     	WHEN p.idx = 1 THEN 'q1'
     	WHEN p.idx = 2 THEN 'q2'
     	WHEN p.idx = 3 THEN 'q3'
     	WHEN p.idx = 4 THEN 'q4'
       END AS quarter
     , CASE 
     	WHEN p.idx = 1 THEN q.q1
     	WHEN p.idx = 2 THEN q.q2
     	WHEN p.idx = 3 THEN q.q3
     	WHEN p.idx = 4 THEN q.q4
       END AS sales
FROM quarterly_sales AS q
	CROSS JOIN (
	-- 행으로 전개하고 싶은 열의 수만큼 순번 테이블 만들기
		SELECT 1 AS idx
		UNION ALL SELECT 2 AS idx
		UNION ALL SELECT 3 AS idx
		UNION ALL SELECT 4 AS idx
		) AS p

year	quarter	sales
2015	q1	82000
2015	q2	83000
2015	q3	78000
2015	q4	83000
2016	q1	85000
2016	q2	85000
2016	q3	80000
2016	q4	81000
2017	q1	92000
2017	q2	81000
2017	q3
2017	q4

피벗 테이블 만드는 방법은 8강 5절에서 자세히 설명

임의의 길이를 가진 배열을 행으로 전개하기

• 데이터의 길이가 확정되지 않은 경우, 조금 복잡
  

코드 7-14

SELECT purchase_id, product_id
FROM purchase_log AS p
	-- string_to_array 함수로 문자열을 배열로 변환하고, unnest 함수로 테이블로 변환하기
	CROSS JOIN unnest(string_to_array(product_ids, ',')) AS product_id

purchase_id	product_id
100001	A001
100001	A002
100001	A003
100002	D001
100002	D002
100003	A001

테이블 함수는 함수의 리턴값이 테이블인 함수를 의미한다.
테이블 함수를 사용할 경우 주의할 점은
일반적인 SELECT 구문 내부에는 레코드에 포함된 스칼라 값을 리턴하는 함수와 컬럼 이름을 지정할 수 있지만,
테이블 함수는 테이블을 리턴한다.

Redshift에서 문자열을 행으로 전개하기

• 배열 자료형이 공식적으로 지원되지 않아, 쉼표로 구분된 문자열을 행으로 전개하는 새로운 방법을 소개