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

하나의 테이블에 대한 조작

앞서 5강과 6강에서는 하나의 값 또는 여러개의 값을 기반으로 데이터를 가공하는 방법에 대해 알아보았습니다.

이번 7강에서는 테이블 기반으로 데이터를 처리하는 방법을 알아보는데, 만약 테이블의 형식이 집계에 적합하지 않은 경우, 어떻게 테이블을 가공해야 하는지도 알아보겠습니다.

 

그룹의 특징 잡기

그럼 집약 함수를 사용하는 방법부터 알아봅시다. 집약 함수란 여러 레코드를 기반으로 하나의 값을 리턴하는 함수입니다. 예를 들어 모든 레코드의 수를 리턴해주는 COUNT 함수, 값의 합계를 리턴해주는 SUM 함수 등이 있습니다.

 

다음 데이터 예는 상품 평가(review) 테이블인데요. 상품(product_id)에 대한 사용자 평가(score)가 저장됩니다. 여기서는 이 테이블을 사용해서 SUM 함수와 AVG 함수 등의 집약 함수를 사용하는 방법에 대해 알아보겠습니다.

 

7-1 상품 평가(review) 테이블

user_id	product_id	score
U001	A001	4.0
U001	A002	5.0
U001	A003	5.0
U002	A001	3.0
U002	A002	3.0
U002	A003	4.0
U003	A001	5.0
U003	A002	4.0
U003	A003	4.0

 

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

다음 코드 예는 자주 사용되는 집약 함수를 review 테이블에 적용하는 쿼리입니다. COUNT 함수는 지정한 컬럼의 레코드 수를 리턴하는 함수입니다. 컬럼 이름 앞에 DISTINCT 구문을 지정하면, 중복을 제외하고 수를 세어 줍니다. 추가로 SUM 함수는 합계, AVG 함수는 평균을 구하는 함수입니다. 따라서 SUM 함수와 AVG 함수는 컬럼의 자료형이 정수 또는 실수 등의 숫자 자료형이어야 합니다. MAX 함수와 MIN 함수는 각각 최댓값과 최솟값을 구하는 함수입니다. 따라서 대소 비교가 가능한 자료형(숫자,문자열,타임스탬프 등)에 적용할 수 있습니다.

 

7-1 집약 함수를 사용해서 테이블 전체의 특징량을 계산하는 쿼리

SELECT
	COUNT(*) AS total_count
   ,COUNT(DISTINCT user_id) AS user_count
   ,COUNT(DISTINCT product_id) AS product_count
   ,SUM(score) AS sum
   ,AVG(score) AS avg
   ,MAX(score) AS max
   ,MIN(score) AS min
FROM
 review
;

 

실행결과

total_count	user_count	product_count	sum	avg	max	min
9	3	3	37.0	4.11	5.0	3.0

 

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

데이터를 조금 더 작게 분할하고 싶다면 GROUP BY 구문을 사용해 데이터를 분류할 키를 지정하고, 그러한 키를 기반으로 데이터를 집약합니다. 다음 코드 예는 GROUP BY 구문을 사용해서 3명의 user_id를 기반으로 그룹을 묶고, 각각의 데이터 집합에 집약 함수를 적용합니다.

 

7-2 사용자 기반으로 데이터를 분할하고 집약 함수를 적용하는 쿼리

SELECT
   user_id
  ,COUNT(*) AS total_count
  ,COUNT(DISTINCT product_id) AS product_count
  , SUM(score) AS sum
  , AVG(score) AS avg
  , MAX(score) AS max
  , MIN(score) AS min
FROM
 review
GROUP BY
 user_id
;

 

실행결과

user_id	total_count	product_count	sum	avg	max	min
U001	3	3	14.0	4.67	5.0	4.0
U002	3	3	10.0	3.33	4.0	3.0
U003	3	3	13.0	4.33	5.0	4.0

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

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

SELECT
  user_id
 ,product_id
 -- 개별 리뷰 점수
 , 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
;

 

실행결과

user_id	product_id	score	avg_score	user_avg_score	user_avg_score_diff
U001	A001	4.0	4.11	4.67	-0.67
U001	A002	5.0	4.11	4.67	0.33
U001	A003	5.0	4.11	4.67	0.33
U002	A001	3.0	4.11	3.33	-0.33
U002	A002	3.0	4.11	3.33	-0.33
U002	A003	4.0	4.11	3.33	0.67
U003	A001	5.0	4.11	4.33	0.67
U003	A002	4.0	4.11	4.33	-0.33
U003	A003	4.0	4.11	4.33	-0.33

 

집약 함수로 윈도 함수를 사용하려면, 집약 함수 뒤에 OVER 구문을 붙이고 여기에 윈도 함수를 지정합니다. OVER 구문에 매개변수를 지정하지 않으면 테이블 전체에 집약 함수를 적용한 값이 리턴됩니다. 매개 변수에 PARTITION BY <컬럼이름>을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약함수를 적용합니다.

 

앞의 코드 예를 보면 테이블 전체의 score 평균값과 user_id들의 score 평균값이 review 테이블의 원래 레코드을 건드리지 않고 추가된 것을 알 수 있습니다. 참고로 집약 함수의 결과와 원래 값을 조합해서 계산하므로 원래 score와 user_id들의 score 평균값 차이도 계산할 수 있습니다.

 

계속해서 윈도 함수를 활용해 데이터를 가공하는 방법을 소개하겠습니다.

 

SQL의 테이블은 기본적으로 순서라는 개념이 없습니다. 따라서 SQL로 순위를 작성하거나 시간 순서로 데이터를 다루려면 복잡한 방법을 사용해야 했습니다. 하지만 윈도 함수가 등장하면서 SQL로 순서를 다루는 것이 굉장히 쉬워졌습니다.

 

여기서는 인기 상품의 상품 ID, 카테고리, 스코어 정보를 가진 인기 상품 테이블을 샘플로 SQL를 사용한 순위 작성, 순서를 고려한 계산 실행 방법 등을 소개하겠습니다.

product_id	category	score
A001	action
A002	action
A003	action
A004	action
D001	dream
D002	dream
D003	dream
D004	dream

 

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

7-4 윈도 함수의 ORDER BY 구문을 사용해 테이블 내부의 순서를 다루는 쿼리

SELECT
	product_id
   ,score
   
   -- 점수 순서로 유일한 순위를 붙임
   , ROW_NUMBER() OVER(ORDER BY score DESC) AS row
   -- 같은 순위를 허용해서 순위를 붙임
   , RANK() OVER(ORDER BY score DESC) AS rank
   -- 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
   , DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank
   
   
   -- 현재 행보다 앞에 있는 행의 값 추출하기
   , LAG(product_id) OVER(ORDER BY score DESC) AS lag1
   , LAG(product_id, 2) OVER(ORDER BY score DESC) AS lag2
   
   -- 현재 행보다 뒤에 있는 행의 값 추출하기
   , LEAD(product_id) OVER(ORDER BY score DESC) AS lead1
   , LEAD(product_id, 2) OVER(ORDER BY score DESC) AS lead2
FROM popular_products
ORDER BY row
;

 

실행결과

 

product_id	score	row	rank	dense_rank	lag1	lag2	lead1	lead2
A001	94	1	1	1			D001	D002
D001	90	2	2	2	A001		D002	A002
D002	82	3	3	3	D001	A001	A002	A003
A002	81	4	4	4	D002	D001	A003	D003
A003	78	5	5	5	A002	D002	D003	A004
D003	78	6	6	5	A003	A002	A004	D004
A004	64	7	7	6	D003	A003	D004
D004	58	8	8	7	A004	D003