SQL 내 집계 함수 vs. 윈도우 함수: 유사점과 차이점

만약 여러분이 SQL 윈도우 함수와 아직 친숙하지 않다면, 윈도우 함수가 기존의 집계 함수와 어떻게 다른지 궁금할 수 있습니다. 윈도우 함수는 언제 쓰는 걸까요? 이 글에서 윈도우 함수와 집계 함수를 살펴볼 건데, 이 둘의 유사점과 차이점을 중심으로 살펴보려고 합니다. 그러고 난 후 어떤 분석을 하고 싶은지에 따라 무엇을 사용하는 것이 좋을지 보려고 합니다.

만약 SQL의 기초 문법을 공부하셨다면 그다음 단계의 함수들에 관해 알아보고 싶을 겁니다. 이런 함수들은 분석을 더 용이하게 하죠. SQL 공부를 하다 보면 곧 2개 유형의 그룹핑과 관련된 함수를 마주하게 됩니다. 바로 윈도우 함수(window functions)와 집계 함수(aggregate functions)이죠. 이 두 함수가 하는 역할은 무엇일까요? 그리고 이 둘은 어떻게 다를까요?

이제부터 함께 살펴봅시다.

 

집계 함수는 무엇인가요?

집계 함수는 여러 행의 수치를 단 1개의 수치로 반환할 때 사용합니다. 대표적인 SQL 집계 함수는 아래와 같아요.

AVG() : 여러 행의 수치의 평균 값을 반환합니다.
SUM() : 여러 행의 수치의 총 합을 반환합니다.
MAX()와 MIN() : 여러 행의 수치 내에서 각각 최댓값과 최솟값을 반환합니다.
COUNT() : 여러 행의 수치의 총개수를 반환합니다. 

집계 함수와 더불어 GROUP BY 구문을 사용하면 특정 그룹에 따른 집계 값을 구할 수 있습니다.

예를 들어, 아래 transactions 라는 테이블에 총 2개의 도시(샌프란시스코, 뉴욕)에 관한 거래 내역 데이터가 쌓이고 있다고 가정해봅시다.

id	date	city	amount
1	2020-11-01	San Francisco	420.65
2	2020-11-01	New York	1129.85
3	2020-11-02	San Francisco	2213.25
4	2020-11-02	New York	499.00
5	2020-11-02	New York	980.30
6	2020-11-03	San Francisco	872.60
7	2020-11-03	San Francisco	3452.25
8	2020-11-03	New York	563.35
9	2020-11-04	New York	1843.10
10	2020-11-04	San Francisco	1705.00

여기서 집계 함수를 사용해 각 도시별 일별 평균 거래액을 계산할 수 있습니다. 날짜와 도시를 기준으로 GROUP BY를 실행하면 됩니다.

SELECT
 date,
 city,
 AVG(amount) AS avg_transaction_amount_for_cities
FROM transactions
GROUP BY date, city;

위 쿼리의 결과는 아래와 같이 나옵니다.

date	city	avg_transaction_amount_for_city
2020-11-01	New York	1129.85
2020-11-02	New York	739.65
2020-11-03	New York	563.35
2020-11-04	New York	1843.1
2020-11-01	San Francisco	420.65
2020-11-02	San Francisco	2213.25
2020-11-03	San Francisco	2162.425
2020-11-04	San Francisco	1705

집계 함수 AVG()와 GROUP BY를 사용해서 날짜별 및 도시별 평균 거래액을 계산할 수 있었습니다. 로우 데이터가 있는 첫 번째 표로 다시 가보면 11월 2일 뉴욕에서는 총 2개의 거래액이 발생했고(499와 980), 11월 3일 샌프란시스코에서는 총 2개의 거래액이(872와 3452) 발생했습니다. 하지만 집계 함수와 GROUP BY를 활용한 쿼리 결과에서는 건건이 거래액을 나열하지 않았죠(로우 데이터의 행: 10개 > 집계 함수+GROUP BY 실행한 데이터의 행: 8개, 변화 있음). 즉, 집계 함수는 여러 개의 행들을 합쳐 1개의 값을 반환했습니다. 그 1개의 값은 어떤 집계 함수를 썼는지에 따라 값이 달라지겠죠. 예시에서는 AVG 함수를 사용했지만 이외에 SUM, MIN, MAX, COUNT 등을 사용해 다른 값을 도출해 낼 수도 있을 겁니다.

 

윈도우 함수는 무엇인가요?

SQL에서 윈도우 함수는 윈도우 창(window frame)을 기준으로 실행됩니다. 여기서 말하는 윈도우 창은 여러 행들의 모음이라고 생각하면 됩니다. 윈도우 함수는 각 행마다 1개의 값을 반환해요. 윈도우 함수를 실행하려면 OVER() 구문을 사용하면 됩니다. OVER() 구문의 괄호 안에 무엇으로 구분 짓느냐(i.e. PARTITION BY)에 따라 하나의 열 안에 있는 여러 행들을 여러 창으로 나눕니다. 마치 GROUP BY처럼 말이죠.

윈도우 함수는 기존의 데이터에는 아무런 변화를 주지 않은 상태에서, 새로운 열에 반환할 값을 계산하고자 집계 함수를 "함께" 사용할 수도 있어요. 하지만 같이 쓰든 아니든 간에 무조건 OVER() 구문은 써야 합니다.

아까 예시로 살펴봤던 transactions 테이블을 다시 살펴봅시다.

id	date	city	amount
1	2020-11-01	San Francisco	420.65
2	2020-11-01	New York	1129.85
3	2020-11-02	San Francisco	2213.25
4	2020-11-02	New York	499.00
5	2020-11-02	New York	980.30
6	2020-11-03	San Francisco	872.60
7	2020-11-03	San Francisco	3452.25
8	2020-11-03	New York	563.35
9	2020-11-04	New York	1843.10
10	2020-11-04	San Francisco	1705.00

우리는 이 테이블에 새로운 열을 하나 추가하고 싶습니다. 바로 amount 열 옆에다가 말입니다. 그 열은 도시별 일별 평균 거래액을 보여주려고 해요. 아래 쿼리문은 윈도우 함수를 사용해 우리가 원하는 결과를 도출해 줍니다.

SELECT
 id,
 date,
 city,
 amount,
 AVG(amount) OVER(PARTITION BY date, city) AS avg_daily
FROM transactions
ORDER BY id; 

결과는 다음과 같아요.

id	date	city	amount	avg_daily
1	2020-11-01	San Francisco	420.65	420.65
2	2020-11-01	New York	1129.85	1129.85
3	2020-11-02	San Francisco	2213.25	2213.25
4	2020-11-02	New York	499.00	739.65
5	2020-11-02	New York	980.30	739.65
6	2020-11-03	San Francisco	872.60	2162.425
7	2020-11-03	San Francisco	3452.25	2162.425
8	2020-11-03	New York	563.35	563.35
9	2020-11-04	New York	1843.10	1843.1
10	2020-11-04	San Francisco	1705.00	1705

처음 집계 함수만 사용했을 때와 다른 점을 발견하셨나요? 바로 여러 행들이 1개의 행으로 반환되지 않았다는 점이 달라요(로두 데이터의 행: 10개 > 윈도우 함수를 실행한 데이터의 행: 10개, 변화 없음). 거래액이 담긴 amount열의 행 내의 데이터는 변함 없이 그대로 있고, 윈도우 함수를 통해 계산하고자 했던 도시별 일별 평균 거래액은 'avg_daily'라는 새로운 열에 담겨 있고요. 

 

윈도우 함수와 집계 함수의 유사점과 차이점 살펴보기

지금까지 각 함수가 어떤 식으로 작동하는지 살펴보았으니, 둘 사이의 유사점과 차이점을 알아봅시다.

윈도우 함수와 집계 함수 모두

1. 여러 행의 수치를 가지고 계산합니다.
2. 여러 행 내의 값을 집계된 값으로 계산할 수 있습니다 (평균값, 합, 최댓값, 최솟값, 개수 등)
3. 1개 또는 1개 이상의 열을 기준으로 데이터를 그룹핑할 수 있습니다.

GROUP BY와 함께 쓰인 집계 함수는 윈도우 함수와 비교했을 때 이런 면이 달라요

1. 그룹핑을 할 행의 범위를 정할 때 GROUP BY를 사용합니다.
2. 특정 열 내의 값을 가지고 행을 합칩니다.
3. GROUP BY에서 명시된 열로만 행을 합칠 수 있습니다.

윈도우 함수는 GROUP BY와 함께 쓰인 집계 함수와 비교했을 때 이런 면이 달라요

1. 그룹핑을 할 행의 범위를 정할 때 OVER()을 사용합니다.
2. 집계 함수 이외에도 다른 함수와 함께 사용할 수 있습니다 (e.g. RANK(), LAG(), LEAD() 등)
3. 특정 열 내의 값 이외에도 행의 순위, 퍼센타일 등을 가지고도 행을 합칠 수 있습니다.
4. 기존의 행에 변화를 주지 않습니다.
5. 현재 행과 연관 있는 구간만 따로 설정해 계산할 수 있습니다 (영어로는 sliding window frame라는 용어를 사용함)

마지막 차이점을 예시를 통해 좀더 자세히 알아봅시다. 이번에는 날짜별로 당일과 그 전날의 평균 거래액을 계산하고 싶습니다(i.e. 11월 2일이라면 당일인 11월 2일과 전날인 11월 1일의 평균 거래액). 우선 날짜별 거래액을 구하기 위해서 Common Table Expression (CTE)를 사용해 daily_sales란 테이블을 임시로 생성하고자 합니다. 그런 후, 윈도우 함수와 더불어 sliding window frame을 활용해 당일과 연관된 전날이라는 구간을 설정한 후 두 날짜의 거래액의 평균을 계산할 겁니다. 쿼리는 아래와 같습니다.

WITH daily_sales AS (
 SELECT
  date,
  SUM(amount) AS sales_per_day,
  FROM transactions
  GROUP BY date)

SELECT
 date,
 AVG(sales_per_day) OVER(ORDER BY date ROWS 1 PRECEDING) AS avg_2days_sales
FROM daily_sales
ORDER BY date;

결과는 다음과 같습니다.

date	avg_2days_sales
2020-11-01	1550.5
2020-11-02	2621.525
2020-11-03	4290.375
2020-11-04	4218.15

첫 번째 행인 11월 1일은 테이블 내에 전날인 10월 31일 데이터가 존재하지 않기 때문에 11월 1일의 거래액이 반환됐네요. 두 번째 행인 11월 2일부터는 전날이 존재하므로 11월 1일과 11월 2일의 평균 거래액이 계산되어 반환됐고, 3번째 행은 11월 2일과 11월 3일의 평균 거래액이 계산되어 반환됐습니다.

이와 같이 윈도우 함수는 현재 행을 기준으로 이와 연관된 구간을 설정하여 집계 값을 구할 때 상당히 용이합니다. 이건 단순히 집계 함수와 GROUP BY로는 구할 수 없거든요.

 

원문: SQL Window Functions vs. SQL Aggregate Functions: Similarities and Differences