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

핵심 요약

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

핵심 질문

1. 윈도우 함수: 순위·누계·이동 범위에서 반드시 구분해야 할 개념과 결과 규칙은 무엇인가?
2. 0건·1건·여러 건과 NULL·동점·중복 데이터에서 결과가 어떻게 달라지는가?
3. 비슷해 보이는 문법과 결과가 같아지는 조건, 달라지는 조건은 무엇인가?
4. 작은 샘플 데이터를 이용해 결과를 실수 없이 예측하는 순서는 무엇인가?

학습 목표

• 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에 의존하지 말고 누계·이동 계산에서는 명시하는 편이 안전하다.

흔한 오해와 주의점

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

문항 풀이 보강: 순위·이웃 행·Window Frame

순위 함수

상품별 순위를 구하려면 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 동작을 확인합니다.

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가 현재 행과 동점 행을 어떻게 포함하는지 확인합니다.

결과를 검증하는 순서

1. 각 Query Block이 만드는 한 행의 의미를 먼저 적습니다.
2. 조건을 적용하기 전 원본 행과 적용 후 남는 행을 작은 표로 그립니다.
3. NULL 비교가 TRUE, FALSE, UNKNOWN 중 무엇인지 구분합니다.
4. 중복 제거, 그룹화, 정렬과 행 제한이 적용되는 순서를 확인합니다.
5. 데이터가 0건·1건·여러 건일 때도 같은 규칙이 성립하는지 검증합니다.

실무와 시험에서 함께 확인할 항목

• ORDER BY가 없다면 결과 순서를 가정하지 않습니다.
• 문자열·숫자·날짜 비교에서는 데이터 타입과 명시적 형변환을 확인합니다.
• 같은 결과처럼 보이는 SQL도 NULL과 중복이 있을 때 달라질 수 있습니다.
• 문법을 외우기 전에 샘플 데이터 3~5행으로 결과를 직접 계산합니다.

마지막 점검

• 작성 순서가 아니라 SQL의 논리적 처리 순서로 결과를 계산합니다.
• NULL을 0이나 빈 값과 같은 것으로 취급하지 않습니다.
• ORDER BY가 없는 결과 순서와 DISTINCT 없는 중복 제거를 가정하지 않습니다.
• 비슷한 문법은 0건·다건·NULL 데이터를 넣어 결과가 정말 같은지 확인합니다.

복습 문제

1. 부서별 급여 1위만 고르려면 어떤 두 단계가 필요한가?
2. ROWS와 RANGE는 동점 행을 어떻게 다르게 볼 수 있는가?
3. 샘플 데이터 3행으로 결과를 직접 계산할 수 있는가?
4. NULL이 포함될 때 결과가 달라지는 지점은 어디인가?