SQL의 윈도우 함수 (with PARTITION BY)

윈도우 함수는 여러 행을 대상으로 연산을 수행하지만, 기존 행을 유지하면서 결과를 반환하는 함수입니다.

행과 행 간의 관계를 쉽게 정의하기 위해 만들어졌으며, 분석 함수나 순위 함수로도 알려져 있습니다.

 

윈도우 함수의 기본 구조는 다음과 같습니다.

<윈도우 함수> OVER (
    PARTITION BY <그룹 기준 컬럼>
    ORDER BY <정렬 기준 컬럼>
    ROWS BETWEEN <범위 지정>
)

 

윈도우 함수 종류

1. 집계 함수 : 그룹 내에서 집계 수행 - SUM(), AVG(), MAX(), MIN(), COUNT()

함수	설명
SUM()	그룹 내 합계를 계산
AVG()	그룹 내 평균을 계산
MAX()	그룹 내 최대값을 계산
MIN()	그룹 내 최소값을 계산
COUNT()	그룹 내 개수를 계산

 

사용 예시 : 상품 카테고리별 매출액 합계

SELECT 
    product_name, 
    category, 
    sales, 
    SUM(sales) OVER (PARTITION BY category) AS total_sales_per_category
FROM sales_data;

product_name	category	sales	total_category_sales
TV	전자제품	300000	500000
냉장고	전자제품	200000	500000
셔츠	의류	150000	300000
바지	의류	150000	300000
책상	가구	200000	200000

 

2. 순위 함수 : 그룹 내에서 순위를 부여 - RANK(), DENSE_RANK(), ROW_NUMBER()

함수	설명
RANK()	동일한 값이면 같은 순위를 부여하고, 다음 순위는 건너뜀.
DENSE_RANK()	동일한 값이면 같은 순위를 부여하지만, 건너뛰지 않음.
ROW_RANK()	동일한 값이 있어도 순위를 무조건 다르게 부여

 

사용 예시 : 상품 카테고리별 매출 순위 

SELECT 
    product_name, 
    category, 
    sales, 
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category
FROM sales_data;

product_name	category	sales	rank_in_category
TV	전자제품	300000	1
냉장고	전자제품	200000	2
셔츠	의류	150000	4
바지	의류	150000	4
책상	가구	200000	2

 

3. 이동 함수 : 이전 또는 다음 행의 값을 가져옴 - LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

함수	설명
LAG()	이전 행의 값을 가져옴
LEAD()	다음 행의 값을 가져옴
FIRST_VALUE()	그룹 내 첫 번째 값 반환
LAST_VALUE()	그룸 내 마지막 값 반환

 

사용 예시 : 상품 카테고리별, 날짜별 매출액과 이전 날 매출액 조회

SELECT 
    category,
    sales_date,
    SUM(sales) AS daily_sales,
    LAG(SUM(sales), 1, 0) OVER (PARTITION BY category ORDER BY sales_date) AS previous_day_sales
FROM sales_data
GROUP BY category, sales_date
ORDER BY category, sales_date;

category	sales_date	sales	previous_day_sales
전자제품	2025-02-17	300000	0
전자제품	2025-02-18	250000	300000
의류	2025-02-17	150000	0
의류	2025-02-18	180000	150000
가구	2025-02-19	220000	180000

 

PARTITION BY

 

PARTITION BY는 윈도우 함수에서 특정 컬럼을 기준으로 데이터를 나누고 그 범위 내에서 연산을 수행할 때 사용됩니다. 그룹화는 하되, 원본 데이터를 유지하면서 집계 값을 계산할 수 있습니다.

 

그렇다면 똑같이 그룹화를 해주는 GROUP BY랑은 어떤 차이가 있을까요?

둘 다 데이터를 그룹별로 나눈다는 공통점이 있지만, GROUP BY는 하나의 행으로 반환해주는 반면 PARTITION BY는 원본 데이터 유지한다는 차이점이 있습니다.

 

상품의 카테고리별 매출액을 계산하는 상황이라고 가정하겠습니다.

 

GROUP BY 사용한 경우 :

SELECT 
    category,
    SUM(sales) AS total_sales
FROM sales_data
GROUP BY category;

 

결과

category	total_sales
전자제품	500000
의류	400000
가구	200000

 

 

PARTITION BY 사용한 경우 : 

SELECT 
    product_name,
    category,
    sales,
    SUM(sales) OVER(PARTITION BY category) AS total_category_sales
FROM sales_data;

 

결과

product_name	category	sales	total_category_sales
TV	전자제품	300000	500000
냉장고	전자제품	200000	500000
셔츠	의류	150000	300000
바지	의류	150000	300000
책상	가구	200000	200000