윈도우 함수 — 접지 않고 옆을 보는 법
한 줄 요약
윈도우 함수는 행을 접지 않은 채로 그 행 주변의 다른 행을 참조하게 해 준다. 순위, 누적, 직전 값 비교처럼 집계만으로는 어려운 질문을 한 번의 스캔으로 푼다. 대신 창(파티션)과 틀(프레임)이 무엇인지 를 정확히 알고 써야 한다. 기본값이 직관과 다른 곳이 있기 때문이다.
왜 이게 필요했나
"카테고리별로 가장 비싼 상품 3개" 를 GROUP BY 만으로 구하려면 자기 조인이나 상관 서브쿼리가 필요하다. 상품마다 "나보다 비싼 같은 카테고리 상품이 몇 개인가" 를 세는 식인데, 코드가 길어지고 표를 여러 번 읽는다. "전월 대비 매출 증감" 도 비슷하다. 월별 집계를 자기 자신과 한 달 어긋나게 조인해야 하고, 첫 달과 빈 달을 따로 처리해야 한다.
두 질문의 공통점은 각 행을 유지한 채로 옆 행을 봐야 한다는 것이다. 집계는 행을 접어 버리므로 이 일을 못 한다. 각 행이 자기 그룹 안에서 몇 번째인지, 바로 앞 행의 값이 무엇인지 알 수 있으면 문제가 단순해진다. 윈도우 함수가 그 자리를 채운다.
어떻게 동작하나
윈도우 함수는 func() OVER (PARTITION BY ... ORDER BY ... frame) 형태다.
PARTITION BY는 창을 나누는 기준이다. GROUP BY 와 비슷하지만 행을 접지 않는다. 생략하면 결과 전체가 하나의 창이다.ORDER BY는 창 안에서의 순서다. 순위와 누적의 기준이 된다.- 프레임은 현재 행을 계산할 때 창 안의 어느 행까지 볼지 정한다.
sum,avg,last_value처럼 여러 행을 보는 함수가 이것의 영향을 받는다.
평가 시점도 알아야 한다. 윈도우 함수는 WHERE, GROUP BY, HAVING 이 모두 끝난 뒤에 계산된다. 그래서 두 가지가 따라 나온다. 첫째, 윈도우 함수의 결과는 같은 쿼리의 WHERE 에서 쓸 수 없다. 순위로 거르려면 서브쿼리나 CTE 로 한 겹 감싸고 바깥에서 거른다. 둘째, 집계가 먼저 끝나므로 윈도우 함수의 인자에 집계를 넣을 수 있다. sum(sum(total_amount)) OVER () 는 그룹별 합계를 먼저 만들고 그 합계들의 전체 합을 각 행에 붙인다. 비중을 구할 때 쿼리를 두 번 돌릴 필요가 없는 이유다.
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;
자주 쓰는 함수는 이렇게 갈린다.
| 함수 | 하는 일 | 동점과 경계 |
|---|---|---|
row_number() |
1부터 순번 | 동점도 다른 번호, 누가 앞일지는 정렬 기준이 정한다 |
rank() |
순위 | 동점은 같은 순위, 다음은 건너뜀 (1,1,3) |
dense_rank() |
순위 | 동점은 같은 순위, 다음은 연속 (1,1,2) |
sum() OVER (ORDER BY ...) |
누적 합 | 기본 프레임이 동점 행까지 포함한다 |
lag() / lead() |
앞 / 뒤 행의 값 | 없으면 기본값, 생략하면 NULL |
ntile(n) |
1~n 의 묶음 번호 | 가능한 한 고르게 나누며 크기는 최대 1 차이 |
가장 조심할 것이 기본 프레임이다. ORDER BY 가 있으면 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 이고, 이것은 "파티션 처음부터 현재 행까지" 가 아니라 "처음부터 현재 행과 정렬 값이 같은 마지막 행까지" 를 뜻한다. ORDER BY 가 없으면 모든 행이 서로 동점이므로 프레임이 파티션 전체가 된다.
현장에서 무엇이 잘못되나
누적 합이 계단처럼 뛴다. 주문 시각으로 누적 매출을 구했는데 같은 시각의 주문 두 건이 똑같은 누적값을 갖는다. 기본 프레임이 동점 행까지 한꺼번에 포함하기 때문이다. 한 행씩 쌓으려면 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 를 명시하고, 정렬 기준에 id 같은 tie-break 컬럼을 더한다.
last_value 가 현재 행을 돌려준다. 파티션의 마지막 값을 원했는데 기본 프레임이 현재 행에서 끝나므로 현재 행(또는 그 동점)의 값이 나온다. PostgreSQL 문서도 이 조합이 쓸모없는 결과를 내기 쉽다고 경고한다. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 으로 프레임을 넓히거나, 정렬을 뒤집어 first_value 를 쓴다.
첫 주문이 실행마다 바뀐다. 고객별 첫 주문을 row_number() ... ORDER BY ordered_at 으로 골랐는데 같은 시각의 주문이 두 건이면 어느 쪽이 1번이 될지 정해져 있지 않다. 오늘은 맞고 내일은 다른 답이 나온다. 정렬 기준이 유일해질 때까지 컬럼을 더한다.
lag() 가 빈 달을 건너뛴다. 매출이 없는 달은 집계 결과에 행 자체가 없으므로, 3월의 lag() 는 1월 값을 돌려준다. 결과에는 "전월 대비" 라고 적혀 있다. 빈 달까지 비교해야 하면 generate_series 로 달력을 먼저 만들고 LEFT JOIN 한다.
WHERE 가 분모를 바꾼다. 윈도우 함수는 WHERE 뒤에 계산되므로, WHERE 로 특정 상태만 남기고 비중을 구하면 그것은 "남은 행 중의 비중" 이다. 전체 대비 비중이 필요하면 거르기 전에 계산하고 바깥에서 거른다.
반올림한 비중의 합이 100 이 아니다. 채널별 비중을 각각 소수점 둘째 자리로 반올림하면 합이 99.99 나 100.01 이 될 수 있다. 계산이 틀린 것이 아니라 반올림 오차다. 보고서에 합계를 함께 싣는다면 그 차이가 왜 생기는지 적어 둔다.
어떻게 확인하나
결과를 믿기 전에 불변 조건을 쿼리로 확인한다.
-- every partition has exactly one row numbered 1
SELECT count(*) FILTER (WHERE rn = 1) = count(DISTINCT category) FROM v_ranked_products;
-- the last running total equals the plain total
SELECT max(cum_revenue) = sum(revenue) FROM v_running_revenue;
-- the first month has no previous value, the others do
SELECT count(*) FILTER (WHERE prev_revenue IS NULL) FROM v_mom;
-- bucket sizes differ by at most one
SELECT quartile, count(*) FROM v_price_quartile GROUP BY quartile ORDER BY 1;
실행 계획으로도 확인할 수 있다. EXPLAIN 을 붙이면 윈도우 함수는 WindowAgg 노드로 나타나고, 대개 그 아래에 PARTITION BY 와 ORDER BY 기준의 Sort 가 있다. 정렬 기준이 서로 다른 OVER 절을 여러 개 쓰면 Sort 와 WindowAgg 가 그만큼 겹겹이 쌓이는 것도 보인다. 큰 표에서 윈도우 쿼리가 느리다면 이 정렬부터 본다.
다음 실습에서 할 것
먼저 앞 이론의 집계 실습에서 GROUP BY, HAVING, FILTER 와 월별 집계를 만들고, 그 뒤 윈도우 함수 실습에서 카테고리별 순번, 두 가지 순위 비교, 누적 매출, 전월 대비, 채널별 비중, 그룹별 상위 3개, 가격 사분위, 고객별 첫 주문을 차례로 구현한다. 위의 확인 쿼리를 실습에서 만든 뷰에 그대로 돌려 보면 채점 전에 스스로 답을 검증할 수 있다.