윈도우 함수

윈도우 함수(Window Function)는 행과 행 사이의 관계를 이용해 순위, 누적합, 이동평균, 비율 등을 계산한다. GROUP BY와 달리 결과 행 수를 줄이지 않고, 원래 행마다 계산 값을 덧붙인다.

기본 문법

SELECT 윈도우함수(인수) OVER ( [PARTITION BY 컬럼]
                              [ORDER BY 컬럼 [ASC|DESC]]
                              [ROWS | RANGE 윈도우절] )
FROM   테이블;

OVER 절 안의 세 요소는 "나누고 → 줄 세우고 → 범위를 정한다" 순서로 읽으면 쉽다.

예제 데이터

EMP 테이블 (전체 급여 합계 15000)

순위 함수 RANK, DENSE_RANK, ROW_NUMBER

SELECT ENAME, SAL,
       RANK()       OVER (ORDER BY SAL DESC) AS RNK,
       DENSE_RANK() OVER (ORDER BY SAL DESC) AS D_RNK,
       ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN
FROM   EMP;

ROW_NUMBER에서 동점인 김민과 정하 중 누가 2번을 받을지는 보장되지 않는다. 일정한 결과가 필요하면 ORDER BY SAL DESC, ENAME처럼 정렬 기준을 추가한다.

파티션별 순위

SELECT ENAME, DEPTNO, SAL,
       RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS DEPT_RNK
FROM   EMP;

부서가 바뀌면 순위가 1부터 다시 시작한다.

시험 포인트: 동점이 있을 때 RANK는 순위를 건너뛰고, DENSE_RANK는 건너뛰지 않으며, ROW_NUMBER는 동점에도 고유 번호를 준다. 결과표에서 세 함수를 구분하는 문제가 자주 나온다.

집계 윈도우 함수

SUM, MAX, MIN, AVG, COUNT 뒤에 OVER 절을 붙이면 윈도우 함수로 동작한다.

SELECT ENAME, DEPTNO, SAL,
       SUM(SAL)   OVER (PARTITION BY DEPTNO) AS DEPT_SUM,
       MAX(SAL)   OVER (PARTITION BY DEPTNO) AS DEPT_MAX,
       COUNT(*)   OVER (PARTITION BY DEPTNO) AS DEPT_CNT
FROM   EMP;

ORDER BY 없이 PARTITION BY만 쓰면 파티션 전체가 계산 범위가 된다. 부서 최고 급여자만 뽑으려면 인라인 뷰에서 SAL = DEPT_MAX 조건을 건다.

윈도우 절 ROWS와 RANGE

윈도우 절은 "현재 행을 계산할 때 어느 행부터 어느 행까지 볼 것인가"를 정한다. 아래 그림은 SAL 오름차순으로 정렬한 6행에서 현재 행이 김민(3000)일 때, UNBOUNDED PRECEDING ~ CURRENT ROW 범위를 ROWS와 RANGE로 각각 잡은 모습이다.

ROWS UNBOUNDED PRECEDING처럼 시작점만 쓰면 끝점은 CURRENT ROW로 간주한다.

기본 윈도우 범위

누적합에서 ROWS와 RANGE 차이

SELECT ENAME, SAL,
       SUM(SAL) OVER (ORDER BY SAL) AS RANGE_SUM,   -- 기본: RANGE
       SUM(SAL) OVER (ORDER BY SAL
                      ROWS UNBOUNDED PRECEDING) AS ROWS_SUM
FROM   EMP;

RANGE는 동점(2000, 3000) 행을 한꺼번에 범위에 넣으므로 같은 값을 가진 행의 누적합이 같다. ROWS는 한 행씩 누적한다.

시험 포인트: ORDER BY만 있는 SUM() OVER (ORDER BY ...)는 RANGE 기준이라 동점 행의 누적합이 같게 나온다. 결과표에서 같은 값이 반복되면 RANGE, 계속 증가하면 ROWS로 판단한다.

이동 범위 예제

SELECT ENAME, SAL,
       SUM(SAL) OVER (ORDER BY SAL
                      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ROWS_3,
       SUM(SAL) OVER (ORDER BY SAL
                      RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS RANGE_1000
FROM   EMP;

• ROWS_3: 앞 1행 + 현재 행 + 뒤 1행. 첫 행과 마지막 행은 2행만 합산한다.
• RANGE_1000: 현재 SAL ± 1000 범위의 값을 모두 합산한다. 예를 들어 SAL 3000이면 2000~4000 범위인 5개 행의 합 14000이다.

행 순서 함수 FIRST_VALUE, LAST_VALUE, LAG, LEAD

FIRST_VALUE와 LAST_VALUE

SELECT ENAME, SAL,
       FIRST_VALUE(ENAME) OVER (ORDER BY SAL DESC) AS FIRST_NM,
       LAST_VALUE(ENAME)  OVER (ORDER BY SAL DESC) AS LAST_DEFAULT,
       LAST_VALUE(ENAME)  OVER (ORDER BY SAL DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING) AS LAST_ALL
FROM   EMP
WHERE  DEPTNO = 20;

LAG와 LEAD

LAG(컬럼, n, 기본값)은 n행 앞, LEAD(컬럼, n, 기본값)은 n행 뒤의 값을 가져온다. n의 기본값은 1이고, 가져올 행이 없으면 기본값(생략 시 NULL)을 돌려준다.

월별 매출 SALES: 1월 100, 2월 150, 3월 120, 4월 200

SELECT MON, AMT,
       LAG(AMT)        OVER (ORDER BY MON) AS PREV_AMT,
       LEAD(AMT)       OVER (ORDER BY MON) AS NEXT_AMT,
       LAG(AMT, 2, 0)  OVER (ORDER BY MON) AS PREV2,
       AMT - LAG(AMT)  OVER (ORDER BY MON) AS DIFF
FROM   SALES;

비율 함수 RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE

SELECT ENAME, SAL,
       RATIO_TO_REPORT(SAL) OVER ()            AS RATIO,
       PERCENT_RANK() OVER (ORDER BY SAL DESC) AS P_RANK,
       CUME_DIST()    OVER (ORDER BY SAL DESC) AS C_DIST,
       NTILE(4)       OVER (ORDER BY SAL DESC) AS NT
FROM   EMP;

시험 포인트: CUME_DIST는 동점 행을 모두 포함해 계산한다. SAL 3000인 두 행은 4000, 3000, 3000까지 3행이 포함되어 3/6 = 0.5다. NTILE은 나머지 행을 앞 그룹부터 배정한다는 점을 기억한다.

DBMS별 참고

• SQL Server는 2012 버전부터 ROWS/RANGE 윈도우 절, FIRST_VALUE, LAST_VALUE, LAG, LEAD, PERCENT_RANK, CUME_DIST를 지원한다.
• RATIO_TO_REPORT는 Oracle 전용이며 SQL Server에서는 SAL * 1.0 / SUM(SAL) OVER ()처럼 계산한다.
• 순위 함수를 활용한 상위 N건 조회는 Top N 쿼리를 참고한다.

핵심 요약

• 윈도우 함수는 OVER 절을 사용하며 결과 행 수를 줄이지 않는다.
• RANK(1,2,2,4), DENSE_RANK(1,2,2,3), ROW_NUMBER(1,2,3,4)로 동점 처리가 다르다.
• ORDER BY가 있으면 기본 범위는 RANGE UNBOUNDED PRECEDING부터 CURRENT ROW까지다.
• ROWS는 행 위치, RANGE는 값 기준이며 RANGE는 동점을 같은 범위로 묶는다.
• LAST_VALUE로 파티션 마지막 값을 얻으려면 UNBOUNDED FOLLOWING까지 범위를 넓힌다.
• LAG는 이전 행, LEAD는 다음 행 값을 가져오며 세 번째 인수로 NULL 대체값을 준다.
• PERCENT_RANK는 (순위−1)/(행수−1), CUME_DIST는 동점 포함 누적 비율이다.
• 윈도우 함수는 WHERE 절에 쓸 수 없으므로 인라인 뷰로 감싸 필터링한다.