SQL 윈도우 함수

상위 문서: SQL

Window Function
행과 행 사이의 관계를 정의하여, 결과 행을 줄이지 않고 각 행마다 순위·누적·이동 값 등을 계산하는 함수. 분석 함수(Analytic Function)라고도 한다

문법

함수명(인수) OVER (
    [PARTITION BY 컬럼]        -- 계산 단위가 되는 그룹
    [ORDER BY 컬럼 [ASC|DESC]] -- 그룹 안의 정렬 순서
    [윈도우절]                  -- ROWS | RANGE BETWEEN ... AND ...
)

GROUP BY는 그룹마다 한 행으로 줄이지만, 윈도우 함수는 원래 행 수를 그대로 유지한다.
윈도우절 예: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(처음부터 현재 행까지)
ORDER BY가 있고 윈도우절이 없으면 기본 범위는 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이다.

분류

분류	함수	설명
순위 함수	RANK, DENSE_RANK, ROW_NUMBER	정렬 순서에 따른 순위
집계 함수	SUM, AVG, COUNT, MAX, MIN	파티션 또는 윈도우 범위의 집계값
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD	첫 행·마지막 행, 이전(LAG)·다음(LEAD) 행의 값
비율 함수	PERCENT_RANK, CUME_DIST, NTILE, RATIO_TO_REPORT	백분율 순위, 누적 분포, N등분, 합계 대비 비율

RATIO_TO_REPORT는 Oracle 전용이다. 나머지는 SQL 표준에 있으며 Oracle, PostgreSQL, MySQL 8.0 등이 지원한다.
LAST_VALUE는 기본 윈도우가 현재 행까지이므로, 파티션 전체의 마지막 값을 얻으려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 지정해야 한다.

순위 함수 비교

사원 테이블

이름	부서	급여
김	영업	500
이	영업	400
박	영업	400
최	영업	300
정	개발	600
강	개발	600
조	개발	450

SELECT 이름, 급여,
       RANK()       OVER (ORDER BY 급여 DESC)       AS 순위_RANK,
       DENSE_RANK() OVER (ORDER BY 급여 DESC)       AS 순위_DENSE,
       ROW_NUMBER() OVER (ORDER BY 급여 DESC, 이름) AS 순번
FROM 사원
ORDER BY 급여 DESC, 이름;

이름	급여	RANK	DENSE_RANK	ROW_NUMBER
강	600	1	1	1
정	600	1	1	2
김	500	3	2	3
조	450	4	3	4
박	400	5	4	5
이	400	5	4	6
최	300	7	5	7

RANK: 동순위를 같은 순위로 매기고, 다음 순위는 동순위 수만큼 건너뛴다(1, 1, 3).
DENSE_RANK: 동순위를 같은 순위로 매기되, 다음 순위를 건너뛰지 않는다(1, 1, 2).
ROW_NUMBER: 동순위가 있어도 고유한 순번을 매긴다. 동순위끼리의 순서는 ORDER BY에 컬럼을 더 적어야 정해진다.

PARTITION BY 예

SELECT 이름, 부서, 급여,
       RANK() OVER (PARTITION BY 부서 ORDER BY 급여 DESC) AS 부서내순위,
       SUM(급여) OVER (PARTITION BY 부서)                  AS 부서합계
FROM 사원
ORDER BY 부서, 급여 DESC, 이름;

이름	부서	급여	부서내순위	부서합계
강	개발	600	1	1650
정	개발	600	1	1650
조	개발	450	3	1650
김	영업	500	1	1600
박	영업	400	2	1600
이	영업	400	2	1600
최	영업	300	4	1600

PARTITION BY는 부서별로 따로 순위를 매기고 합계를 구한다. 결과 행의 출력 순서는 쿼리 끝의 ORDER BY로 정한다.

시험 포인트

실기 SQL: RANK, DENSE_RANK, ROW_NUMBER의 동순위 처리 차이와 결괏값 작성
필기: 윈도우 함수 분류(순위, 집계, 행 순서, 비율)와 OVER, PARTITION BY 문법
GROUP BY와 달리 행 수가 줄지 않는다는 점, LAG·LEAD의 의미

같이 보기

참고 문헌