[데이터 분석을 위한 SQL 레시피] 7강 -하나의 테이블에 대한 조작

목차

1) 그룹의 특징 잡기

2) 그룹 내부의 순서

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

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

 

 

1) 그룹의 특징 잡기

집약 함수 = 여러 레코드를 기반으로 하나의 값을 리턴하는 함수

 

• 테이블 전체의 특징량 계산하기

COUNT 함수 = 지정한 컬럼의 레코드 수를 리턴하는 함수

DISTINCT 구문 = 중복을 제외하고 수를 세어줌

SUM 함수 = 합계

AVG 함수 = 평균

SUM과 AVG는 컬럼의 자료형이 정수 또는 실수 등의 숫자 자료형이어야함

MAX/MIN 함수 = 최댓값, 최솟값. → 대소 비교 가능한 자료형(숫자, 문자열, 타임스탬프 등)에 적용할 수 있음.

 

• 그루핑한 데이터의 특징량 계산하기

GROUP BY 구문을 사용해 데이터 분류할 키를 지정하고, 그 키를 기반으로 데이터 집약.

❗ GROUP BY 구문 사용한 쿼리에서는, GROUP BY 구문에 지정한 컬럼 또는 집약 함수만 SELECT 구문의 컬럼으로 지정할 수 있음.

EX) GROUP BY USER_ID 했으면 SELECT 구문 내부에 PRODUCT_ID 또는 SCORE를 지정할 수 없음

→ GROUP BY 구문을 사용한 쿼리에서는 GROUP BY 구문에 지정한 컬럼을 유니크 키(유일)로 새로운 테이블을 만들게됨.

이 과정에서 GROUP BY 구문에 지정하지 않은 컬럼은 사라져버림 → 집약 전과 후 값을 동시에 사용할 수 없음.

 

• 집약 함수를 적용한 값과 집약 전의 값을 동시에 다루기

집약함수로 윈도 함수 적용하기

집약함수 뒤에 OVER 구문을 붙이고 윈도함수 지정.

OVER 구문에 매개 변수 지정하지 않으면 테이블 전체에 집약함수 적용한 값이 리턴됨.

매개 변수에 PARTITION BY <컬럼 이름>을 지정하면 해당 컬럼 값을 기반으로 그룹화 하고 집약 함수 적용.

#코드 7-3 윈도 함수 사용해 집약 함수의 결과와 원래 값을 동시에 다루는 쿼리

SELECT
	user_id
	,product_id
--개별 리뷰 점수
	,score
-- 전체 평균 리뷰 점수
	,AVG(score) OVER() AS avg_score
-- 사용자의 평균 리뷰 점수
	,AVG(score) OVER(PARTITION BY user_id) AS user_avg_score
-- 개별 리뷰 점수와 사용자 평균 리뷰 점수의 차이
	,score - AVG(score) OVER(PARTITION BY user_id) AS user_avg_score_diff

FROM
	review
;

테이블 전체의 score 평균값과 user_id들의 score 평균값이 review 테이블의 원래 레코드를 건드리지 않고 추가됨.

집약 함수의 결과와 원래 값을 조합해서 계산하므로 각각 평균값 차이 계산도 가능

 

2) 그룹 내부의 순서

• ORDER BY 구문으로 순서 정의하기

윈도 내부에서 특정 값을 참조하려면 해당 값의 위치를 명확하게 지정해야함.

OVER 구문 내부에 ORDER BY 구문을 사용할 수 있음 . → 윈도 내부에 있는 데이터의 순서 정의

ex)

- ORDER BY score DESC → 테이블 내부의 상품을 스코어가 높은 순서로 정렬

- ROW_NUMBER = 유일한 순위 번호를 붙이는 함수

- RANK, DENSE_RANK = 같은 순위의 레코드 있을 때 순위 번호 같게 붙힘(같은 순위 허용)

RANK는 같은 순위의 레코드 뒤의 순위 번호 건너뛰고(1,2,3,4,5,5,7,8) , DENSE_RANK 는 순위 번호 건너뛰지 않음. (1,2,3,4,5,5,6,7)

- LAG, LEAD = 현재 행을 기준으로 앞의 행 또는 뒤의 행의 값을 추출하는 함수. 두 번째 매개변수에 숫자 지정하여 앞뒤 n번째 값 추출

 

• ORDER BY 구문과 집약 함수 조합하기
  • ROWS BETWEEN start AND end
  start와 end에는 ‘CURRENT ROW(현재의 행)’, ‘n PRECEDING(n행 앞)’, ‘n FOLLOWING(n행 뒤)’, ‘UNBOUNDED PRECEDING(이전 행 전부)’, UNBOUNDED FOLLOWING(이후 행 전부)’ 등 
  • FIRST_VALUE 윈도 함수와 LAST_VALUE 윈도 함수는 각각 윈도 내부의 가장 첫 번째 레코드와 가장 마지막 레코드를 추출해주는 함수임.

  # 코드 7-5 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
  
  -- 순위가 높은 상품 id 출력하기
  	, FIRST_VALUE(product_id)
  			OVER(ORDER BY score DESC
  				ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  		AS first_value
  
  FROM popular_products
  ORDER BY row
  ;

  • 윈도 프레임 지정에 대해서

# 코드 7-6 윈도 프레임 지정별 상품 id를 집약하는 쿼리

SELECT
	product_id

--점수 순서로 유일한 순서를 붙임
	, ROW_NUMBER() OVER(ORDER BY score DESC) AS row

-- 가장 앞 순위부터 가장 뒷 순위까지의 범위를 대상으로 상품 ID 집약하기
	, array_agg(product_id)
			OVER(ORDER BY score DESC
				ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
		AS whole_agg

FROM popular_products
WHERE category = 'action'
ORDER BY row
;

#실행 결과
product_id  | row  | whole_agg
-------------------------------
A001     |  1    |  {A001, A002, A003, A004}

 

• PARTITION BY와 ORDER BY 조합하기

#코드 7-7 윈도 함수를 사용해 카테고리들의 순위를 계산하는 쿼리

SELECT
	category
	,product_id
	,score

--카테고리별로 점수 순서로 정렬하고 유일한 순위를 붙임

	, ROW_NUMBER()
			OVER(PARTITION BY category ORDER BY score DESC)
		AS row

FROM popular_products
ORDER BY category, row
;

 

- 각 카테고리의 상위 n개 추출하기

윈도 함수를 WHERE 구문에 작성할 수 없으므로, SELECT 구문에서 윈도 함수를 사용한 결과를 서브 쿼리로 만들고,

외부에서 WHERE 구문을 적용해야함.

# 코드 7-8 카테고리들의 순위 상위 2개까지의 상품을 추출하는 쿼리

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
ORDERY BY category, rank
;

 

+상위 1개의 상품 ID를 추출할 경우, FIRST_VALUE 윈도 함수를 사용하고 SELECT DISTINCT 구문으로 결과 집약하는 방법도 있음. (서브쿼리 사용 X)

#코드 7-9 카테고리별 순위 최상위 상품을 추출하는 쿼리

SELECT DISTINCT
	category
	--카테고리별로 순위 최상품 상품 ID 추출
	,FIRST_VALUE(product_id)
		OVER(PARTITION BY category ORDER BY score DESC)
			ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
		AS product_id
FROM popular_products
;

 

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

최종 출력에서는 데이터를 열로 전개해야 가독성이 높은 경우가 많음.

 

• 행을 열로 변환하기

열로 전개할 데이터의 종류, 수를 명확하게 미리 알고 있어야함.

날짜를 1개의 레코드로 집약할 수 있게 GROUP BY dt 사용.

MAX(CASE~) → 각각 노출수, 세션수, 사용자수에 해당하는 레코드 만을 CASE식으로 추출하고, MAX함수로 해당 값 추출,

날짜 별로 지표들이 하나씩 존재하므로, CASE 표현식의 조건이 TRUE가 되는 기록이 하나뿐이라, 그 하나를 MAX 함수로 추출!!

#데이터 7-3 날짜별 KPI 데이터 테이블
dt  |  indicator  |  val
-------------------------
2017-01-01 | impressions | 1800
2017-01-01 | sessions  | 500

# 코드 7-10 행으로 지정된 지표 값을 열로 변환하는 쿼리

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

#실행결과
dt  | impressions | sessions | users
-----------------------------
2017-01-01 | 1800 | 500 | 200

 

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

미리 열의 수를 정할 수 없는 경우에는 데이터를 쉼표 등으로 구분한 문자열로 변환하는 방법 사용

#데이터 7-4 구매 상세 로그 테이블

purchase_id  | product_id  | price
-----------------------------------
10001  | A001  |  300
10001  | A002  |  400
..

# 코드 7-11 행을 집약해서 쉼표로 구분된 문자열로 변환하기

SELECT
	purchase_id

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

#실행 결과
purchase_id  |  product_ids  | amount 
---------------------------------------
10001  | A001,A002,A003  | 900
..

+string_agg 함수 : 문자열 목록을 연결하고 문자열 사이에 구분자를 배치하는 집계 함수

STRING_AGG ( expression, separator [order_by_clause] )

expression = 문자열로 확인할 수 있는 모든 유효한 표현식

separator = 연결된 문자열의 구분자

order_by_clause = 연결된 결과의 순서를 지정하는 선택적 절 ex) ORDER BY expression1 {ASC | DESC}, [...]

 

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

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

컬럼으로 표현된 가로 기반 데이터의 특징은 데이터의 수가 고정되었다는 것.

행으로 전개할 데이터 수가 고정되었다면, 그러한 데이터 수와 같은 수의 일련 번호를 가진 피벗 테이블을 만들고 cross join 하면 됨.

#데이터 7-5 4분기 매출 테이블(quarterly_sales)
year | q1 | q2 | q3 | q4
-------------------------
2015 | 82000 | 83000 | 78000 | 83000
2016 | 85000 | 85000 | 80000 | 81000
2017 | 92000 |81000

# 코드 7-12 일련 번호를 가진 피벗 테이블을 사용해 행으로 변환하는 쿼리

SELECT
	Q.year
--Q1에서 Q4까지의 레이블 이름 출력하기
	, 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 AD quarter
--Q1에서 Q4까지의 매출 출력하기
	, 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 ...

 

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

# 데이터 7-6 구매 로그(purchase_log 테이블)
purchase_id | product_ids
--------------------------
100001 | A001, A002, A003
..

# 코드 7-13 테이블 함수 사용해 배열을 행으로 전개하는 쿼리
SELECT unnest(ARRAY['A001', 'A002', 'A003']) AS product_id;

 

+unnest 함수 : 인수로 입력받은 annyarray를 행 집합으로 변환 = 배열을 레코드(행)로 전개할 수 있음.

 

PostgreSQL은 SELECT 구문 내부에 스칼라 값과 테이블 함수 동시 지정 가능.

+regexp_split_to_table 함수 : 문자열을 구분자로 분할해서 테이블화.

#코드 7-15 PostgreSQL에서 쉼표로 구분된 데이터를 행으로 전개하는 쿼리

SELECT
	purchase_id
-- 쉼표로 구분된 문자열을 한 번에 행으로 전개하기
	, regexp_split_to_table(product_id, ',') AS product_id
FROM purchase_log;

# 실행결과
purchase_id  |  product_id
---------------------------
100001 | A001
100001 | A002
...