[SQL] 윈도우 함수 (Windw Function)

GROUP BY로는 해결이 안 되는 상황이 있습니다. 그룹별 집계는 하고 싶은데 개별 행도 함께 보고 싶거나, 각 행의 순위를 매기거나, 이전 행과의 차이를 계산할 때입니다. 윈도우 함수를 알기 전까지는 서브쿼리를 중첩해서 해결했는데, 이걸 알고 나서 쿼리가 훨씬 간결해졌습니다.

 

오늘은 예제 데이터를 하나 두고 함수별로 어떤 결과가 나오는지 직접 보면서 정리해보겠습니다.

 

1. GROUP BY vs 윈도우 함수

윈도우 함수는 집계는 하되 행을 줄이지 않습니다. GROUP BY는 그룹으로 묶인 행들이 하나로 합쳐지지만, 윈도우 함수는 원래 행 수를 유지하면서 계산 결과를 새 컬럼으로 붙입니다.

 

기본 문법은 함수명() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) 형태입니다. PARTITION BY는 그룹을 나누는 기준, ORDER BY는 윈도우 안에서 행의 순서를 정합니다. 상황에 따라 둘 다 쓰기도 하고 하나만 쓰기도 합니다.

GROUP BY → 그룹으로 묶어 행 수가 줄어듦 윈도우 함수 → 그룹별 계산을 하되 행 수 유지, 결과를 각 행에 붙여서 반환

아래 예제 데이터를 기준으로 각 함수 결과를 확인해봅니다.

 

employee_id	department	salary	hire_date
1	개발	5000	2022-03-01
2	개발	4800	2022-07-15
3	개발	4800	2023-01-10
4	마케팅	4200	2021-11-01
5	마케팅	4200	2022-05-20
6	마케팅	3900	2023-03-15

 

 

2. ROW_NUMBER / RANK / DENSE_RANK

순위를 매기는 함수 세 가지입니다. 동점 처리 방식이 다릅니다.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

 

개발팀 기준으로 salary가 4800으로 동일한 employee_id 2, 3번이 어떻게 처리되는지 보면 차이가 명확합니다.

employee_id	department	salary	ROW_NUMBER	RANK	DENSE_RANK
1	개발	5000	1	1	1
2	개발	4800	2	2	2
3	개발	4800	3	2	2
4	마케팅	4200	1	1	1
5	마케팅	4200	2	1	1
6	마케팅	3900	3	3	2

 

노란색으로 표시된 동점 행을 보면 차이가 확실합니다. ROW_NUMBER는 동점 없이 무조건 순번을 매기고, RANK는 동점에 같은 순위를 주되 다음 순위를 건너뜁니다(마케팅 2번 → 1위가 두 명이라 다음은 3위). DENSE_RANK는 동점에 같은 순위를 주되 다음 순위를 건너뛰지 않습니다.

 

2.1 ROW_NUMBER로 최신 1건 추출

ROW_NUMBER의 가장 자주 쓰이는 패턴입니다. 부서별 가장 최근 입사자 1명만 뽑을 때 이렇게 씁니다.

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY hire_date DESC
        ) AS rn
    FROM employees
)
SELECT employee_id, department, salary, hire_date
FROM ranked
WHERE rn = 1;

employee_id	department	salary	hire_date
3	개발	4800	2023-01-10
6	마케팅	3900	2023-03-15

 

 

3. LAG / LEAD

현재 행의 이전 또는 다음 행 값을 참조합니다. LAG는 이전 행, LEAD는 다음 행입니다. 시계열 분석에서 이전 값과의 차이를 계산할 때 자주 씁니다.

SELECT
    employee_id,
    department,
    salary,
    hire_date,
    LAG(salary)  OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary,
    LEAD(salary) OVER (PARTITION BY department ORDER BY hire_date) AS next_salary
FROM employees;

employee_id	department	salary	hire_date	prev_salary (LAG)	next_salary (LEAD)
1	개발	5000	2022-03-01	NULL	4800
2	개발	4800	2022-07-15	5000	4800
3	개발	4800	2023-01-10	4800	NULL
4	마케팅	4200	2021-11-01	NULL	4200
5	마케팅	4200	2022-05-20	4200	3900
6	마케팅	3900	2023-03-15	4200	NULL

 

PARTITION BY department가 있어서 부서가 바뀌는 지점(employee_id 4번)에서 prev_salary가 NULL로 초기화됩니다. 같은 부서 내에서만 이전/다음 행을 참조합니다.

 

4. 집계 윈도우 함수

SUM, AVG, COUNT도 OVER 절을 붙이면 윈도우 함수로 쓸 수 있습니다. 부서별 평균 급여를 각 행에 붙여서 개인 급여와 부서 평균을 한 번에 비교할 수 있습니다.

SELECT
    employee_id,
    department,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS dept_avg_salary,
    salary - ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS diff_from_avg,
    SUM(salary) OVER (PARTITION BY department)           AS dept_total_salary,
    ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 1) AS salary_ratio
FROM employees;

employee_id	department	salary	dept_avg	diff_from_avg	salary_ratio
1	개발	5000	4867	+133	34.2%
2	개발	4800	4867	-67	32.9%
3	개발	4800	4867	-67	32.9%
4	마케팅	4200	4100	+100	34.2%
5	마케팅	4200	4100	+100	34.2%
6	마케팅	3900	4100	-200	31.7%

 

GROUP BY였다면 부서별 평균만 나오고 개인 행은 사라졌을 겁니다. 윈도우 함수 덕분에 개인 급여와 부서 평균을 같은 행에서 비교할 수 있습니다.

 

5. 누적 합계

입사일 순서로 누적 급여를 계산할 때 ROWS BETWEEN 절로 윈도우 범위를 지정합니다. UNBOUNDED PRECEDING AND CURRENT ROW는 첫 행부터 현재 행까지를 윈도우로 정의합니다.

SELECT
    employee_id,
    department,
    salary,
    hire_date,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_salary
FROM employees;

employee_id	department	salary	hire_date	cumulative_salary
1	개발	5000	2022-03-01	5000
2	개발	4800	2022-07-15	9800
3	개발	4800	2023-01-10	14600
4	마케팅	4200	2021-11-01	4200
5	마케팅	4200	2022-05-20	8400
6	마케팅	3900	2023-03-15	12300

 

부서가 바뀌는 마케팅 첫 행(employee_id 4)에서 누적이 다시 4200으로 초기화됩니다. PARTITION BY department 때문입니다.

 

정리

윈도우 함수를 처음 접할 때 OVER 절이 낯설어서 GROUP BY랑 혼동했습니다. 행이 줄지 않는다는 것만 확실히 이해하면 나머지는 PARTITION BY, ORDER BY 조합을 바꿔가면서 익히는 게 가장 빠릅니다.

 

ROW_NUMBER로 최신 1건 추출하는 패턴이랑 LAG로 이전 행 값 비교하는 패턴은 실무에서 정말 자주 쓰게 됩니다. 😊