SQL 고급 : 집계 함수 vs. 윈도우 함수 비교

SQL을 공부하면서 처음 윈도우 함수(Window Fuctions)를 접했을 때 기존의 집계함수(Aggregate Functions)와 비슷해서 헷갈렸던 적이 있습니다. 그럼 서로 비슷해 보이는 집계함수와 윈도우 함수, 언제 어떻게 사용하는걸까요?

 

이 글에서는 집계함수와 윈도우 함수에 관련된 실무 예제를 활용해 살펴보면서 데이터 분석을 할 때 두 함수가 어떻게 사용되는지 알아보도록 하겠습니다.

 

먼저, 집계함수와 윈도우 함수에 대해 알아보기에 앞서 여러분이 가상의 이커머스 회사 '로켓마트'의 데이터 지표를 관리하고 분석하는 담당자가 되었다고 가정해보겠습니다.

 

유관부서에서 다음과 같은 데이터를 추출해달라고 요청이 들어왔을 때, 여러분은 어떤 함수를 이용해 데이터를 추출하면 될까요?

1. 연도별, 카테고리별 매출액 합계와 고객 만족도 평점 데이터를 각각 추출해주세요. 
2. 연도별 평균 매출액을 구해 해당 연도의 카테고리별 매출액과 비교한 데이터를 추출해주세요.

 

우선 정답을 먼저 말씀 드리면 여기서는 1번 질문은 집계함수, 2번 질문은 윈도우 함수를 이용해 데이터를 추출할 것입니다. 그럼 이제 본격적으로 집계함수와 윈도우 함수에 대해 차근차근 알아보도록 하겠습니다!

 

1. 집계함수

- 여러 행의 데이터를 집계하여 1개의 값으로 반환할 때 사용한다.
- 대표적인 집계함수로는 SUM, COUNT, AVG, MAX, MIN 등이 있다.
- 집계함수와 GROUP BY 함께 사용하면 특정 그룹별로 집계할 수 있다.

 

위에서 첫번째로 언급했던 연도별, 카테고리별 매출액 합계와 고객 만족도 평점 데이터를 집계함수로 추출하기 위해, 아래 Sales 라는 테이블에 2개 년도의 카테고리별 매출, 고객 만족도 데이터가 있다고 가정해보겠습니다.

 

year	category	amount	grade
2020	가전	1000000	4.8
2020	가구	800000	4.3
2020	패션	500000	4.7
2020	식품	900000	4.6
2020	뷰티	300000	4.4
2021	가전	1200000	4.7
2021	가구	1000000	4.9
2021	패션	600000	4.5
2021	식품	800000	4.6
2021	뷰티	400000	4.4

 

여기서는 집계함수와 GROUP BY를 사용해 연도별 매출액 합계와 고객 만족도 평균, 카테고리별 매출액 합계와 고객 만족도 평균을 각각 계산할 수 있습니다.

 

1) 연도별 매출액 합계와 고객 만족도 평균

 

SELECT date, SUM(amount) AS total_amount, ROUND(AVG(grade), 1) AS avg_grade
FROM sales
GROUP BY date
ORDER BY date;

 

✏️ 실행결과

date	total_amount	avg_grade
2020	3500000	4.6
2021	4000000	4.6

date 컬럼을 기준으로 GROUP BY 를 통해 연도별로 그룹핑을 해주고 집계함수 SUM을 사용해 전체 매출액을, AVG를 사용해 평균 고객 만족도를 구하였습니다. 아래 카테고리별 매출액 합계와 고객 만족도도 동일한 방식으로 쿼리를 작성하면 됩니다.

 

2) 카테고리별 매출액 합계와 고객 만족도 평균

 

SELECT category, SUM(amount) AS total_amount, ROUND(AVG(grade), 1) AS avg_grade
FROM sales
GROUP BY category
ORDER BY amount DESC;

 

✏️ 실행결과

 

category	total_amount	avg_grade
가전	2200000	4.8
가구	1800000	4.6
식품	1700000	4.6
패션	1100000	4.6
뷰티	700000	4.4

이번에는 category 컬럼을 기준으로 GROUP BY 를 통해 카테고리별로 그룹핑을 해주었고, 위와 마찬가지로 집계함수 SUM을 사용해 전체 매출액을, AVG를 사용해 평균 고객 만족도를 구하였습니다. 앞에서 말한 것처럼 집계함수는 여러 행의 데이터를 집계하여 1개의 값으로 반환하는 것을 보실 수 있습니다.

 

2. 윈도우 함수

- 여러 행의 데이터를 집계하여 각 행마다 1개의 값을 반환한다.
- 집계함수(SUM, AVG 등) 뒤에 OVER 구문을 붙여주어 사용한다.
- 그룹핑을 위해 GROUP BY가 아닌 PARTITIION BY를 사용해야 한다.

 

이번에는 위에서 두 번째로 언급했던 연도별 평균 매출액을 구해 해당 연도의 카테고리별 매출액과 비교하기 위해 집계함수가 아닌 윈도우 함수를 사용해보겠습니다. 사용할 테이블은 위의 집계함수에서 사용했던 테이블과 동일 합니다. 

 

year	category	amount	grade
2020	가전	1000000	4.8
2020	가구	800000	4.3
2020	패션	50000	4.7
2020	식품	900000	4.6
2020	뷰티	300000	4.4
2021	가전	1200000	4.7
2021	가구	1000000	4.9
2021	패션	600000	4.5
2021	식품	800000	4.6
2021	뷰티	400000	4.4

 

데이터를 추출할 수 있는 쿼리를 작성하기 전에 이번에는 우리과 만들어낼 결과물을 먼저 보여드리도록 하겠습니다. 아래를 보시면 date, category, amount 컬럼이 위의 테이블과 동일하게 모두 남아 있습니다. 여기서 우리가 해야할 일은 윈도우 함수를 사용해 연도별 평균 매출(year_avg_amount), 매출액 차이(amount_diff) 컬럼을 새로 만들어 붙여주어 각 카테고리의 매출액과 비교 하는 것입니다.

 

✏️ 실행결과

 

date	category	amount	year_avg_amount	amount_diff
2020	가전	1000000	700000	300000
2020	가구	800000	700000	100000
2020	패션	500000	700000	-200000
2020	식품	900000	700000	200000
2020	뷰티	300000	700000	-400000
2021	가전	1200000	800000	400000
2021	가구	1000000	800000	200000
2021	패션	600000	800000	-200000
2021	식품	800000	800000	0
2021	뷰티	400000	800000	-400000

해당 결과물을 추출하기 위한 쿼리는 다음과 같습니다.

 

SELECT date
     , category
     , amount
     , ROUND(AVG(amount) OVER(PARTITION BY date), 0) AS year_avg_amount
     , amount - ROUND(AVG(amount) OVER(PARTITION BY date), 0) AS amount_diff
FROM sales;

 

앞에서 설명했던 것처럼 윈도우 함수 적용을 위해 집계함수에 OVER 구문을 붙여 주었고, 여기에 연도별로 그룹핑을 위해 PARTITION BY 를 사용한 것을 보실 수 있습니다. 집계함수와 달리 윈도우 함수는 각 행마다 1개의 값이 모두 반환된 것을 확인할 수 있네요. 이를 통해 어떤 카테고리가 해당 연도에 평균 대비 매출이 높았고, 낮았는지 분석할 수 있을 것 입니다.

 

해당 쿼리에서는 매출액에 대한 다루고 있지만 고객 만족도의 평균과 차이를 비교하는 것도 동일한 방식으로 추출할 수 있습니다.

 

이와 같이 윈도우 함수는 현재 행의 값과 집계를 통해 계산된 값을 비교할 때 용이하게 사용할 수 있습니다. 오늘 설명했던 내용을 다시 정리해보면 집계함수는 단순히 내가 보고자 하는 데이터를 집계해줄 때 사용한다면, 윈도우 함수는 여기서 한발 더 나아가 진짜 데이터 분석을 위해 비교를 할 때 사용할 수 있을 것 입니다. 

 

오늘은 집계함수와 비교해서 윈도우 함수를 어떻게 사용하는지 포커스를 맞추고 설명을 드렸는데요. 지금까지 설명한 윈도우 함수에 대한 개념과 예제는 사실 윈도우 함수 내용 중 일부에 불과 합니다. 다음번에는 좀 더 다양한 윈도우 함수의 실무 활용 예제를 가져와서 설명 드릴 수 있도록 하겠습니다.

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.