윈도우 함수: 순위·누계·이동 범위

핵심 요약

행을 접지 않고 그룹별 순위·누계·이전 값을 계산하는 OVER와 Window Frame을 익힌다.

학습 목표

• GROUP BY 집계와 윈도우 함수의 행 수 차이를 설명한다.
• PARTITION BY·ORDER BY·ROWS/RANGE의 역할을 구분한다.

본문

개념 지도

입력 행 유지→PARTITION BY 그룹→ORDER BY 순서→Window Frame→분석값 계산

핵심 내용

윈도우 함수는 원래 행을 유지하면서 관련 행 집합을 대상으로 계산한다.

SELECT empno, deptno, sal,
       ROW_NUMBER() OVER (
         PARTITION BY deptno ORDER BY sal DESC, empno
       ) AS rn,
       SUM(sal) OVER (
         PARTITION BY deptno ORDER BY empno
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_sal
FROM emp;

PARTITION BY는 계산 그룹, ORDER BY는 그룹 안 순서, Window Frame은 현재 행을 기준으로 계산에 포함할 범위를 정한다. ROW_NUMBER는 동점에도 고유 순번, RANK는 동점 다음 순위가 건너뛰고, DENSE_RANK는 건너뛰지 않는다. LAG, LEAD는 이전·다음 행 값을 참조한다.

ROWS는 물리 행 수 기준, RANGE는 정렬값 기준의 논리 범위라 동점 처리 결과가 다를 수 있다. 기본 Frame에 의존하지 말고 누계·이동 계산에서는 명시하는 편이 안전하다.

GROUP BY와 Window Function의 차이

GROUP BY는 여러 원본 행을 그룹당 한 행으로 축약하지만, Window Function은 원본 행을 유지하면서 같은 Partition의 계산 결과를 덧붙입니다.

SELECT employee_id, deptno, sal,
       SUM(sal) OVER (PARTITION BY deptno) AS dept_sum,
       ROW_NUMBER() OVER (
         PARTITION BY deptno
         ORDER BY sal DESC, employee_id
       ) AS rn
FROM emp;

ROW_NUMBER는 동점에도 서로 다른 번호를 주므로 결과를 재현하려면 employee_id 같은 Tie-breaker를 포함합니다. RANK는 동점 다음 번호를 건너뛰고, DENSE_RANK는 건너뛰지 않습니다.

ROWS와 RANGE Frame

SUM(amount) OVER (
  ORDER BY trade_time, trade_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

ROWS는 물리적인 행 위치를 기준으로 하고, RANGE는 ORDER BY 값이 같은 Peer Group을 함께 포함할 수 있습니다. 동일한 거래시각이 여러 건이면 기본 Frame 또는 RANGE를 사용한 누계가 같은 시각의 행에 동일한 합계를 보여 줄 수 있습니다. 행 단위 누계가 필요하면 유일한 정렬과 ROWS를 명시합니다.

Window 범위 점검

Window Function은 PARTITION BY, ORDER BY, Window Frame의 조합에 따라 계산 대상 행이 달라집니다. 특히 기본 Frame과 명시한 ROWS·RANGE가 현재 행과 동점 행을 어떻게 포함하는지 확인합니다.

시험 포인트

순위 함수

상품별 순위를 구하려면 PARTITION BY 상품ID가 필요하다. “10등까지, 동점은 같은 등수”라면 RANK를 계산한 뒤 바깥 WHERE에서 rank_no <= 10을 적용한다.

SELECT *
FROM (
  SELECT a.*,
         RANK() OVER (
           PARTITION BY game_id
           ORDER BY score DESC
         ) rank_no
  FROM activity a
)
WHERE rank_no <= 10;

LAG와 LEAD

LAG(end_val)은 정렬된 현재 행의 이전 행 값을, LEAD(start_val)은 다음 행 값을 가져온다. 구간이 이어지는지 판정하려면 먼저 PARTITION BY와 ORDER BY로 실제 이웃 순서를 만든 뒤 행마다 비교한다.

ROWS와 RANGE

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING은 물리적으로 앞뒤 한 행을 포함한다. RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING은 ORDER BY 값이 현재 값의 ±10000 범위인 모든 동점·근접 행을 포함한다.

GROUP BY 결과에도 윈도우 함수를 적용할 수 있다. 이 경우 윈도우의 입력 행은 원본 상품 행이 아니라 그룹별 집계 행이다.

집계형 윈도우 함수

MAX(sal) OVER (PARTITION BY deptno)는 각 사원 행을 유지하면서 부서 최고 급여를 붙인다. 바깥에서 sal = dept_max를 필터하면 공동 최고 급여자도 모두 반환한다.

분포·비율 함수

SELECT empno, deptno, sal,
       NTILE(4) OVER (
         PARTITION BY deptno ORDER BY sal DESC
       ) AS quartile,
       PERCENT_RANK() OVER (
         PARTITION BY deptno ORDER BY sal
       ) AS percent_rank,
       CUME_DIST() OVER (
         PARTITION BY deptno ORDER BY sal
       ) AS cume_dist,
       RATIO_TO_REPORT(sal) OVER (
         PARTITION BY deptno
       ) AS salary_ratio
FROM emp;

동점이 있으면 PERCENT_RANK와 CUME_DIST가 같은 방식으로 움직이지 않습니다. 분모가 0이거나 NULL이 포함된 경우의 결과도 함수 정의와 실제 DBMS 동작을 확인합니다.

자주 틀리는 부분

• 분석 함수는 WHERE 단계보다 뒤에서 계산되므로 별도 단계 없이 분석 함수 별칭을 WHERE에서 바로 필터할 수 없다.
• RANK와 DENSE_RANK의 동점 뒤 순위를 구분한다.
• 정렬 기준이 유일하지 않으면 ROW_NUMBER 결과가 비결정적일 수 있다.