SQL 윈도우 함수와 CTE - ROW_NUMBER RANK SUM OVER, WITH 절 (SQL 중급 10단원)

이 단원에서 배우는 것

8단원에서 "자기 부서 평균보다 많이 받는 사원"을 상관 서브쿼리로 풀었다. 답은 맞지만 바깥 행마다 서브쿼리를 다시 도는 구조였고, 9단원에서 그런 형태가 왜 비싼지도 확인했다. 윈도우 함수는 같은 문제를 테이블을 한 번만 읽고 푼다. 게다가 GROUP BY 와 달리 원래 행을 그대로 남긴 채 집계값을 옆에 붙일 수 있다. 이번 단원은 윈도우 함수와, 복잡한 쿼리를 읽히게 만드는 CTE(WITH 절)를 다룬다. 테이블은 계속 dept · emp 다.

ROW_NUMBER · RANK · DENSE_RANK 의 차이와 TOP-N 추출에 미치는 영향을 안다
PARTITION BY 와 프레임(ROWS/RANGE)을 지정해 누적합과 이동평균을 만든다
WITH 절로 쿼리를 단계로 쪼개고, 재귀 CTE 로 조직도를 펼친다

개념

GROUP BY 는 여러 행을 한 행으로 접는다. 부서별 평균을 구하면 사원 10행이 부서 5행이 되고, 개인의 급여는 사라진다. 그런데 실무 요구는 "각 사원의 급여와, 그 사원이 속한 부서의 평균을 나란히 보여 달라"인 경우가 훨씬 많다. 지금까지는 이걸 서브쿼리나 자기 조인으로 풀었다.

윈도우 함수는 행을 접지 않고, 행마다 그 행에서 내다볼 창(window) 을 정의한다. 창의 범위는 OVER 괄호 안에서 정한다.

집계함수(...) OVER (
  PARTITION BY 창을 나눌 기준     -- 생략하면 전체가 하나의 창
  ORDER BY 창 안에서의 정렬       -- 순위 함수와 누적 계산에 필요
  ROWS|RANGE 프레임               -- 창 안에서 몇 행까지 볼지
)

CTE 는 성격이 다르다. WITH 이름 AS (...) 로 중간 결과에 이름을 붙이는 문법이고, 기능만 보면 8단원 인라인 뷰와 같다. 다만 위에서 아래로 읽히고, 같은 이름을 여러 번 참조할 수 있으며, 자기 자신을 참조하는 재귀가 가능하다는 점이 다르다.

표준 SQL 문법과 예제

순위 함수

SELECT dept_id, emp_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,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drnk
FROM emp
ORDER BY dept_id, rn;

개발팀 안에서 유관순 1, 김유신 2, 이순신 3 이 붙고, 영업팀은 안중근 1, 을지문덕 2, 강감찬 3 으로 다시 1부터 시작한다. PARTITION BY 가 창을 부서 단위로 끊기 때문이다. 부서가 NULL 인 홍범도도 NULL 이라는 하나의 창을 이루어 1번을 받는다. 세 함수의 차이는 동점이 있을 때 드러난다.

함수	동점 처리	급여가 7200, 5200, 5200, 4800 인 경우
`ROW_NUMBER()`	동점이어도 무조건 다른 번호	1, 2, 3, 4
`RANK()`	동점은 같은 순위, 다음은 건너뜀	1, 2, 2, 4
`DENSE_RANK()`	동점은 같은 순위, 다음은 연속	1, 2, 2, 3

"상위 3명"을 뽑을 때 rnk <= 3 은 동점 때문에 4명이 나올 수 있고, rn <= 3 은 항상 3명이지만 동점자 중 누가 잘릴지는 정렬 기준에 달렸다. 어느 쪽이 맞는지는 기획에 물어봐야 하는 문제다. 결과를 재현 가능하게 하려면 ORDER BY salary DESC, emp_id 처럼 동점을 깨는 컬럼을 반드시 추가한다.

집계 함수의 OVER

SELECT emp_name, dept_id, salary,
       SUM(salary) OVER (PARTITION BY dept_id) AS 부서합계,
       ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id), 1) AS 부서내비중,
       AVG(salary) OVER () AS 전체평균
FROM emp
ORDER BY dept_id, salary DESC;

개발팀 세 명 모두 부서합계 17200 을 달고 나오고, 유관순의 부서내비중은 41.9 다. OVER () 처럼 괄호를 비우면 창이 전체 결과 집합이 되어 전체 평균 4830.00 이 모든 행에 붙는다. 8단원의 스칼라 서브쿼리를 대체하는 형태다.

누적합과 프레임

SELECT emp_name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적인건비
FROM emp
ORDER BY hire_date;

입사 순서는 유관순 · 안중근 · 을지문덕 · 김유신 · 이순신 · 김구 · 강감찬 · 윤봉길 · 신채호 · 홍범도이므로 7200 → 14000 → 18100 → 23300 → 28100 → 33100 → 37000 → 40600 → 43900 → 48300 이 쌓이고, 마지막 값이 전체 인건비와 같아진다. 프레임은 창 안에서 어디부터 어디까지 볼지를 정한다.

프레임	범위	쓰임
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	첫 행 ~ 현재 행	누적합
`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	직전 2행 ~ 현재 행	3일 이동평균
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	창 전체	ORDER BY 와 함께 쓰면서도 전체 합계가 필요할 때

LAG / LEAD — 앞뒤 행 값 가져오기

SELECT emp_name, hire_date, salary,
       LAG(salary) OVER (ORDER BY hire_date) AS 직전입사자급여,
       salary - LAG(salary) OVER (ORDER BY hire_date) AS 차이
FROM emp
ORDER BY hire_date;

첫 행인 유관순의 LAG 는 NULL 이다. 기본값을 주려면 LAG(salary, 1, 0) 로 세 번째 인자를 쓴다. 매출의 전월 대비 증감, 로그의 이벤트 간 간격 계산이 전부 이 패턴이다.

WITH 절 (CTE)

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM emp
  GROUP BY dept_id
),
high AS (
  SELECT e.emp_id, e.emp_name, e.dept_id, e.salary, a.avg_sal
  FROM emp e
  JOIN dept_avg a ON a.dept_id = e.dept_id
  WHERE e.salary > a.avg_sal
)
SELECT h.emp_name, h.salary, ROUND(h.avg_sal, 1) AS 부서평균, d.dept_name
FROM high h
JOIN dept d ON d.dept_id = h.dept_id
ORDER BY h.salary DESC;

8단원 상관 서브쿼리와 같은 결과(유관순 · 안중근 · 윤봉길)를 낸다. 중첩된 괄호를 위에서 아래로 읽는 단계로 펼친 것이 전부지만, 이 차이가 3개월 뒤 유지보수 난이도를 가른다.

재귀 CTE — 조직도 펼치기

WITH RECURSIVE org (emp_id, emp_name, mgr_id, lvl, path) AS (
  SELECT emp_id, emp_name, mgr_id, 1, CAST(emp_name AS CHAR(200))
  FROM emp
  WHERE mgr_id IS NULL                          -- 시작점(앵커)
  UNION ALL
  SELECT e.emp_id, e.emp_name, e.mgr_id, o.lvl + 1,
         CONCAT(o.path, ' > ', e.emp_name)
  FROM emp e
  JOIN org o ON e.mgr_id = o.emp_id             -- 자기 자신을 참조
)
SELECT lvl, path FROM org ORDER BY path;

mgr_id 가 NULL 인 유관순 · 안중근 · 윤봉길 · 김구가 1레벨, 그 아래 여섯 명이 2레벨로 총 10행이 나온다. 재귀 CTE 는 항상 같은 뼈대다. 앵커 쿼리 + UNION ALL + 직전 결과를 참조하는 쿼리. 카테고리 트리, 부품 구성표, 댓글 스레드가 전부 이 구조로 풀린다.

DB별 차이

항목	MySQL · MariaDB	PostgreSQL	Oracle	SQL Server
윈도우 함수 도입	MySQL 8.0 / MariaDB 10.2	8.4 부터	8i 부터 (분석 함수)	2005 부터, 프레임은 2012 부터
재귀 CTE 키워드	`WITH RECURSIVE` 필수	`WITH RECURSIVE` 필수	`WITH` 만 (`CONNECT BY` 도 가능)	`WITH` 만
재귀 깊이 제한	`cte_max_recursion_depth` 기본 1000	제한 없음 (무한루프 주의)	제한 없음	`OPTION (MAXRECURSION 100)` 기본 100
NULLS FIRST/LAST	미지원	지원	지원	미지원
FILTER 절	미지원 (CASE 로 대체)	지원	미지원	미지원

MySQL 5.7 이나 MariaDB 10.1 이하에는 윈도우 함수가 아예 없다. 그 환경에서는 사용자 변수로 순위를 매기는 코드를 보게 되는데, 실행 순서 보장이 없어 버전이 올라가면 결과가 바뀔 수 있다. 가능하면 서버를 올리는 쪽이 정답이다.

실무에서 자주 틀리는 것

1. WHERE 절에서 윈도우 함수를 쓴다

-- 에러 (MySQL 8.0)  You cannot use the window function 'row_number' in this context.
-- 에러 (PostgreSQL) ERROR:  window functions are not allowed in WHERE
SELECT emp_name, salary
FROM emp
WHERE ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) <= 2;

윈도우 함수는 WHERE · GROUP BY · HAVING 이 끝난 뒤에 평가된다. 1단원에서 본 처리 순서 그대로다. 아직 존재하지 않는 값을 WHERE 에서 참조할 수 없으니 한 겹 감싸야 한다.

WITH ranked AS (
  SELECT emp_name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn
  FROM emp
)
SELECT * FROM ranked WHERE rn <= 2;

2. ORDER BY 를 넣었더니 합계가 달라진다

SUM(salary) OVER (PARTITION BY dept_id)                  -- 부서 전체 합
SUM(salary) OVER (PARTITION BY dept_id ORDER BY salary)  -- 누적합 (다른 값!)

프레임을 생략하고 ORDER BY 만 쓰면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 가 되어 누적합이 된다. 게다가 기본값이 ROWS 가 아니라 RANGE 라서 정렬 키가 같은 행들은 한 덩어리로 취급된다. 개발팀에 4800 짜리 사원이 한 명 더 들어오면 그 두 행은 같은 누적값을 갖는다. 행 단위로 자르고 싶으면 ROWS 를 명시한다.

3. 재귀 CTE 가 멈추지 않는다

데이터에 순환 참조가 있으면(A의 관리자가 B, B의 관리자가 A) 재귀가 끝나지 않는다. MySQL 은 1000 depth 에서, SQL Server 는 100 에서 에러로 끊지만 PostgreSQL 과 Oracle 은 그대로 돈다. 운영 데이터를 다룰 때는 안전장치를 넣는다.

  ...
  FROM emp e
  JOIN org o ON e.mgr_id = o.emp_id
  WHERE o.lvl < 10                                        -- 깊이 제한
    AND o.path NOT LIKE CONCAT('%', e.emp_name, '%')      -- 이미 지나온 노드 배제

4. 윈도우 함수를 쓰면 무조건 빠를 거라 믿는다

윈도우 함수는 테이블을 한 번만 읽지만, PARTITION BY · ORDER BY 조합에 맞는 인덱스가 없으면 그 정렬을 정렬 버퍼에서 한다. MySQL EXPLAIN 에 Using filesort 가 뜨고 데이터가 크면 디스크 임시 파일까지 쓴다. (dept_id, salary) 인덱스가 있으면 정렬이 통째로 생략된다. 9단원에서 배운 인덱스 설계가 여기서 그대로 이어진다.

스스로 확인하기

부서별 급여 상위 2명을, 동점자가 있어도 모두 포함해서 출력하라.
입사일 순으로 각 사원의 급여가 직전 입사자보다 얼마나 늘었는지 출력하라. 첫 행은 0 으로 표시한다.
재귀 CTE 로 각 사원의 조직 레벨과 최상위 관리자 이름을 출력하라.

-- 1  (동점 포함이므로 ROW_NUMBER 가 아니라 RANK)
WITH r AS (
  SELECT dept_id, emp_name, salary,
         RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM emp
)
SELECT dept_id, emp_name, salary FROM r WHERE rnk <= 2 ORDER BY dept_id, rnk;

-- 2
SELECT emp_name, hire_date, salary,
       salary - LAG(salary, 1, salary) OVER (ORDER BY hire_date) AS 증가액
FROM emp
ORDER BY hire_date;

-- 3
WITH RECURSIVE org (emp_id, emp_name, lvl, root_name) AS (
  SELECT emp_id, emp_name, 1, emp_name FROM emp WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, o.lvl + 1, o.root_name
  FROM emp e JOIN org o ON e.mgr_id = o.emp_id
)
SELECT emp_name, lvl, root_name FROM org ORDER BY lvl, emp_id;

1번은 개발팀 유관순 · 김유신, 영업팀 안중근 · 을지문덕, 인사팀 윤봉길 · 신채호, 재무팀 김구, 그리고 부서가 NULL 인 홍범도가 나온다. 2번에서 LAG(salary, 1, salary) 로 기본값에 자기 급여를 준 이유는 첫 행의 증가액을 0 으로 만들기 위해서다. 3번의 root_name 은 재귀 단계마다 그대로 물려주므로 김유신 · 이순신 · 홍범도는 유관순, 강감찬 · 을지문덕은 안중근이 된다.

다음 11단원에서는 조회를 떠나 데이터를 바꾸는 쪽으로 넘어간다. 여러 사람이 같은 행을 동시에 고칠 때 무슨 일이 생기는지, 트랜잭션과 격리수준을 다룬다.

참고: PostgreSQL 윈도우 함수 문서, MySQL 윈도우 함수 문서