윈도우 함수

윈도우 함수(window function)는 행을 줄이지 않고, 행마다 '창'에 든 행들로 계산한 합계·순위·앞뒤 값을 붙입니다. 창은 OVER 절이 정합니다.

GROUP BY는 그룹을 한 줄로 접지만, 윈도우 함수는 줄을 그대로 두고 순위나 누적 합을 줄마다 붙입니다. Oracle에서는 분석 함수(analytic function)라고 부르며, 어느 이름으로 나와도 같은 것입니다. 그림의 GRADE는 SALGRADE의 급여 등급입니다. 최유나(450)와 강도윤(470)은 둘 다 4등급입니다.

SELECT ENAME,  SAL,  SUM(SAL) OVER () AS TOTALFROM EMPORDER BY SAL;

SELECT ENAME,  SAL,  SUM(SAL) OVER (    ORDER BY SAL  ) AS RUN_SUMFROM EMPORDER BY SAL;

SELECT ENAME,  DEPTNO,  SAL,  SUM(SAL) OVER (    PARTITION BY DEPTNO    ORDER BY SAL  ) AS DEPT_RUNFROM EMPORDER BY DEPTNO, SAL;

SELECT ENAME,  SAL,  SUM(SAL) OVER (    ORDER BY SAL    ROWS BETWEEN 1 PRECEDING      AND 1 FOLLOWING  ) AS MOVE_SUMFROM EMPORDER BY SAL;

SELECT E.ENAME,  E.SAL,  S.GRADE,  SUM(E.SAL) OVER (    ORDER BY S.GRADE  ) AS RANGE_SUM,…

…  RANK() OVER (    ORDER BY S.GRADE DESC  ) AS RNK,  DENSE_RANK() OVER (    ORDER BY S.GRADE DESC  ) AS DRNK,  ROW_NUMBER() OVER (    ORDER BY S.GRADE DESC  ) AS RN…

SELECT ENAME,  SAL,  LAG(SAL) OVER (    ORDER BY SAL  ) AS PREV_SAL,  LEAD(SAL) OVER (    ORDER BY SAL  ) AS NEXT_SALFROM EMPORDER BY SAL;

SELECT ENAME,  SAL,  SUM(SAL) OVER () AS TOTALFROM EMPORDER BY SAL;

1OVER와 파티션

집계 함수에 OVER를 붙이면 윈도우 함수가 됩니다. 꼴은 다음과 같고 괄호 안은 모두 빼도 됩니다. 순위를 매기는 함수와 LAG·LEAD는 ORDER BY가 있어야 합니다.

함수(인자) OVER (
  PARTITION BY …
  ORDER BY …
  ROWS 또는 RANGE …
)

PARTITION BY는 행을 칸, 곧 파티션(partition)으로 나눕니다. NULL끼리도 한 칸이 되고, 빼면 결과 전체가 한 칸입니다.

2창의 범위 — ROWS와 RANGE

범위는 ROWS나 RANGE 뒤에 BETWEEN 시작 AND 끝으로 적고, OVER에 ORDER BY가 있어야 합니다.

ROWS UNBOUNDED PRECEDING⁠처럼 시작만 적으면 끝은 현재 행입니다.

OVER에 ORDER BY만 쓰고 범위를 적지 않으면 기본 범위는 다음과 같습니다.

RANGE BETWEEN
  UNBOUNDED PRECEDING
  AND CURRENT ROW

RANGE는 정렬 값으로 창을 정해서, 정렬 값이 같은 행은 한꺼번에 창에 들어갑니다. GRADE로 정렬하면 4등급 동점인 최유나와 강도윤이 함께 들어가 두 줄 모두 2010입니다.

3순위 함수

순위 함수(ranking function)는 OVER의 ORDER BY 차례로 번호를 매깁니다.

동점에서 누가 ROW_NUMBER의 앞 번호를 받을지는 정해지지 않아서 OVER의 ORDER BY에 열을 더해 정합니다.

ROW_NUMBER() OVER (
  ORDER BY S.GRADE DESC,
    E.SAL DESC
)

윈도우 함수는 WHERE·GROUP BY·HAVING이 끝난 뒤에 계산되어 SELECT 절과 ORDER BY 절에만 씁니다.

4앞뒤 행과 비율 함수

LAG·LEAD·FIRST_VALUE·LAST_VALUE는 행 순서 함수, NTILE·RATIO_TO_REPORT·PERCENT_RANK·CUME_DIST는 비율 함수라고 부릅니다.

LAG(SAL, 2, 0)은 두 행 앞의 값이고, 앞 행이 없는 첫 두 행은 0입니다. 기본 범위의 창은 현재 행에서 끝나서 LAST_VALUE는 동점이 없으면 현재 행의 값입니다. 동점이 있으면 그 무리의 마지막 행 값이라 정해지지 않습니다. 칸의 끝값을 원하면 범위를 다음처럼 넓힙니다.

ROWS BETWEEN
  UNBOUNDED PRECEDING
  AND UNBOUNDED FOLLOWING

예제

RANGE와 ROWS를 견줍니다. ROWS_SUM은 동점끼리 차례를 정하려고 ORDER BY에 E.SAL을 더했습니다.

SELECT E.ENAME,
  E.SAL,
  S.GRADE,
  SUM(E.SAL) OVER (
    ORDER BY S.GRADE
  ) AS RANGE_SUM,
  SUM(E.SAL) OVER (
    ORDER BY S.GRADE, E.SAL
    ROWS UNBOUNDED PRECEDING
  ) AS ROWS_SUM
FROM EMP E
  JOIN SALGRADE S
  ON E.SAL BETWEEN
    S.LOSAL AND S.HISAL
ORDER BY E.SAL;

최유나 줄에서 RANGE는 같은 4등급인 강도윤까지 창에 넣어 2010이고, ROWS는 최유나까지만 더해 1540입니다.

시험 포인트

• GROUP BY는 행을 줄이고, 윈도우 함수는 행 수를 그대로 둡니다.
• OVER에 ORDER BY만 있으면 기본 범위가 RANGE라 동점 행은 같은 누적 합을 받습니다.
• 동점 다음 번호는 RANK만 건너뛰고, ROW_NUMBER는 동점에도 다른 번호를 줍니다.
• 기본 범위의 LAST_VALUE는 칸의 끝값이 아니라 현재 행까지의 마지막 값입니다.
• 윈도우 함수는 WHERE에 쓸 수 없습니다.

확인 문제

1. 다음 쿼리에서 박지호 줄의 CNT와 TOP_SAL은 각각 얼마일까요?

SELECT ENAME,
  SAL,
  COUNT(*) OVER (
    PARTITION BY DEPTNO
  ) AS CNT,
  MAX(SAL) OVER (
    PARTITION BY DEPTNO
    ORDER BY SAL
  ) AS TOP_SAL
FROM EMP
WHERE DEPTNO = 20
ORDER BY SAL;

2. MGR 순으로 순위를 매겼습니다. MGR이 NULL인 김민준 줄의 RNK와 DRNK는 얼마일까요?

SELECT ENAME,
  MGR,
  RANK() OVER (
    ORDER BY MGR
  ) AS RNK,
  DENSE_RANK() OVER (
    ORDER BY MGR
  ) AS DRNK
FROM EMP
ORDER BY RNK, EMPNO;

3. 월급 상위 3명을 거르다가 오류가 났습니다. 까닭과 고칠 방법은 무엇일까요?

SELECT ENAME
FROM EMP
WHERE RANK() OVER (
  ORDER BY SAL DESC
) <= 3;

SELECT ENAME,
  SAL,
  RNK
FROM (
  SELECT ENAME,
    SAL,
    RANK() OVER (
      ORDER BY SAL DESC
    ) AS RNK
  FROM EMP
)
WHERE RNK <= 3
ORDER BY RNK;