ウィンドウ関数 — 畳まずに隣を見る方法

한 줄 요약

윈도우 함수는 행을 접지 않은 채로 그 행 주변의 다른 행을 참조하게 해 주며, 순위, 누적, 직전 값 비교처럼 집계만으로는 어려운 질문을 한 번의 스캔으로 푼다.

왜 이게 필요했나

"카테고리별로 가장 비싼 상품 3개"를 GROUP BY 만으로 구하려면 자기 조인이나 상관 서브쿼리가 필요하다. 코드가 길어지고 성능도 나쁘다. 각 행이 자기 그룹 안에서 몇 번째인지 알 수 있으면 문제가 단순해진다.

어떻게 동작하나

윈도우 함수는 함수() OVER (PARTITION BY ... ORDER BY ...) 형태다.

• PARTITION BY — 창을 나누는 기준. GROUP BY 와 비슷하지만 행을 접지 않는다.
• ORDER BY — 창 안에서의 순서. 순위와 누적의 기준이 된다.

자주 쓰는 함수는 이렇게 갈린다.

함수	하는 일	동점 처리
row_number()	1부터 순번	동점도 다른 번호
rank()	순위	동점은 같은 순위, 다음은 건너뜀 (1,1,3)
dense_rank()	순위	동점은 같은 순위, 다음은 연속 (1,1,2)
sum() OVER (ORDER BY ...)	누적 합	프레임 기본값이 시작부터 현재까지
lag() / lead()	이전 / 다음 행의 값	없으면 NULL
ntile(n)	n 등분 그룹 번호	크기가 균등하게 나뉨

중요한 제약이 하나 있다. 윈도우 함수 결과는 같은 SELECT 의 WHERE 에서 쓸 수 없다. 처리 순서상 WHERE 가 먼저이기 때문이다. "카테고리별 상위 3개"를 구하려면 서브쿼리나 CTE 로 한 겹 감싸고 바깥에서 걸러야 한다.

SELECT category, id, price
FROM (
  SELECT category, id, price,
         row_number() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
  FROM products
) t
WHERE rn <= 3;

sum(...) OVER () 처럼 괄호를 비우면 전체가 하나의 창이 된다. 전체 합계 대비 비중을 구할 때 쿼리를 두 번 돌릴 필요가 없어진다.

현장에서 만나는 모습

전월 대비 증감을 구할 때 lag() 가 정석이다. 자기 조인으로 하면 첫 달 처리와 결측 월 처리가 지저분해지지만, lag() 는 없는 값을 자연스럽게 NULL 로 돌려준다. 다만 월이 비어 있으면 그 달을 건너뛴 값과 비교하게 된다는 점은 주의해야 한다. 빈 달까지 포함하려면 날짜 시리즈를 먼저 만들어 조인해야 한다.

순위 함수를 고를 때도 기준이 필요하다. 페이지네이션이나 중복 제거처럼 행을 유일하게 식별해야 하면 row_number(), 사람에게 보여 주는 등수라면 동점을 같게 두는 rank() 나 dense_rank() 가 맞다. 그리고 순위의 정렬 기준에 동점이 있으면 결과가 실행마다 달라질 수 있으므로 tie-break 컬럼을 넣는 편이 안전하다.

다음 실습에서 할 것

순번, 순위, 누적 합, 전월 대비, 비중, 그룹별 상위 N, 사분위, 고객별 첫 주문을 차례로 구현한다.