서브쿼리·CTE·윈도우 함수 3

서브쿼리·CTE·윈도우 함수 2에서 서브쿼리(subquery) 결과에 이름을 붙이는 CTE(Common Table Expression, 공통 테이블 식)를 봤다. 이 문서는 윈도우 함수(window function)를 다룬다. 윈도우 함수는 행을 합치지 않고 각 행 옆에 순위·이전 행 값·누적합을 붙이므로, "부서 안에서 내 급여 순위"나 "전월 대비 매출"처럼 원래 행과 계산 결과가 함께 필요한 쿼리를 짧게 쓸 수 있다.

윈도우 함수

GROUP BY는 행을 묶어 그룹당 한 행으로 줄인다. 윈도우 함수는 행을 그대로 두고, 각 행과 관련된 행들의 범위(윈도우)로 계산한 값을 각 행 옆에 붙인다. "부서 안에서 내 급여 순위"처럼 원래 행과 집계 결과가 함께 필요할 때 쓴다.

SELECT dept_id, name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
       SUM(salary)  OVER (PARTITION BY dept_id)                       AS dept_total
FROM emp;

OVER (...)가 윈도우를 정한다. PARTITION BY dept_id는 부서별로 행을 나눠 부서마다 따로 계산하게 한다. 생략하면 전체 행이 한 묶음이다. ORDER BY는 그 묶음 안의 순서를 정한다.

급여	ROW_NUMBER()	RANK()	DENSE_RANK()
100	1	1	1
90	2	2	2
90	3	2	2
80	4	4	3

• ROW_NUMBER()는 같은 값이어도 서로 다른 번호를 준다. 같은 값끼리 누가 앞설지는 정해지지 않으므로 ORDER BY salary DESC, id처럼 유일한 칼럼을 붙여 고정한다.
• RANK()는 같은 값에 같은 순위를 주고, 동점인 행 수만큼 다음 순위를 건너뛴다(2위가 둘이면 다음은 4위). DENSE_RANK()는 건너뛰지 않고 3위로 이어 간다.
• LAG(col)·LEAD(col)은 순서상 바로 앞·뒤 행의 값을 가져온다. 기본은 한 행 차이이고 그런 행이 없으면 NULL이다. salary - LAG(salary) OVER (ORDER BY month)로 전월 대비 증감을 구하는 식이다.
• SUM()·COUNT() 같은 집계 함수도 OVER를 붙이면 윈도우 함수가 된다. OVER 안에 ORDER BY가 있으면, 윈도우 안에서 실제로 계산에 넣는 행의 범위인 프레임(frame)이 기본으로 "처음부터 현재 행까지"가 되어 누적합이 된다. 기본 프레임은 행 개수가 아니라 정렬 값을 기준으로 범위를 잡으므로, 정렬 값이 같은 행은 한꺼번에 포함된다. 한 행씩 누적하려면 범위를 행 단위로 바꾸는 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 명시한다.

윈도우 함수로 거르기. 윈도우 함수는 WHERE·GROUP BY·HAVING이 끝난 뒤 계산되고, SELECT 목록과 ORDER BY에만 쓸 수 있다. 그래서 "부서별 급여 상위 3명"은 한 번 감싸서 바깥에서 거른다.

WITH ranked AS (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
  FROM emp
)
SELECT * FROM ranked WHERE rn <= 3;

PARTITION BY·ORDER BY가 있는 윈도우 함수는 그 순서로 행을 정렬해야 계산할 수 있으므로, 행이 많으면 정렬 비용이 든다. 같은 정렬 조건을 쓰는 윈도우가 여럿이면 옵티마이저(optimizer)가 첫 번째 정렬만 하고 나머지는 건너뛴다. 정렬이 어떻게 처리되는지는 정렬·그룹·임시 테이블에서 본다.

면접 포인트

• "ROW_NUMBER·RANK·DENSE_RANK 차이?" → 동점 처리. 모두 다른 번호 / 같은 순위 후 건너뜀 / 같은 순위 후 이어짐.
• "그룹별 상위 N개" → ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)를 CTE로 감싸고 바깥에서 rn <= N. WHERE에 바로 못 쓰는 이유는 처리 순서.
• "누적합이 같은 날짜에서 한 번에 뛰어요" → OVER (ORDER BY ...)의 기본 범위는 같은 정렬 값을 한꺼번에 넣는다. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 명시한다.