SQL 중급 — 집계, 윈도우 함수, CTE
80 SQL Interview Questions for Data Engineers (Real Asks, 2026) — DataVidhya blog Intermediate SQL & Aggregations, Q11-18 (pp.7-9) + FAQ
이 모듈을 다 읽으면
- 집계 함수와 윈도우 함수의 근본적인 차이(행을 접는지 여부)를 설명할 수 있다
- RANK, DENSE_RANK, ROW_NUMBER의 동작 차이를 예시로 구분할 수 있다
- CTE와 서브쿼리를 언제 각각 선택해야 하는지 판단할 수 있다
- LEAD/LAG로 시계열 비교를 구현하고 CASE 문으로 조건 로직을 표현할 수 있다
집계 함수와 윈도우 함수의 차이, 순위 계산 함수 3종의 미묘한 차이, CTE의 실전 활용, 그리고 LEAD/LAG·CASE 같은 데이터 엔지니어링 필수 도구를 다룬다.
집계 함수 vs 윈도우 함수
집계 함수(SUM, AVG, COUNT, MIN, MAX)는 여러 행에 걸친 값을 계산해 단일 요약값 하나로 '접어(collapse)' 반환한다. GROUP BY와 함께 쓰면 그룹당 한 행만 남는다.
반면 윈도우 함수는 `OVER()` 절을 사용해 원본 행을 그대로 유지하면서, 그 행과 관련된 행 집합(윈도우)에 대한 계산 결과를 나란히 붙여 보여준다. 즉 원본 데이터와 계산된 값을 동시에 볼 수 있다는 점이 집계 함수와의 핵심 차이다. 예를 들어 `SUM(sales) OVER (ORDER BY transaction_date)`는 각 거래 행마다 그날까지의 누적합(running total)을 계산해 원본 행 옆에 붙여준다.
핵심 포인트
- 집계 함수는 여러 행을 하나의 요약값으로 접지만, 윈도우 함수는 원본 행을 유지한 채 계산값을 덧붙인다.
- OVER (ORDER BY ...) 절로 누적합, 이동평균 같은 순서 기반 계산을 표현한다.
RANK, DENSE_RANK, ROW_NUMBER
ROW_NUMBER()는 동점이 있어도 무조건 1, 2, 3, 4처럼 유일한 순번을 매긴다. RANK()는 동점에 같은 순위를 주지만 다음 순위를 건너뛴다(1, 2, 2, 4). DENSE_RANK()도 동점에 같은 순위를 주지만 순위를 건너뛰지 않는다(1, 2, 2, 3).
'그룹별 상위 N개(top N per group)' 같은 쿼리를 짤 때는 특별히 동점 처리를 하고 싶은 의도가 없다면 ROW_NUMBER()를 쓰는 것이 안전하다 — RANK나 DENSE_RANK를 쓰면 동점 처리 때문에 의도한 것보다 더 많은 행이 반환될 수 있기 때문이다.
핵심 포인트
- ROW_NUMBER는 동점이어도 유일한 순번(1,2,3,4)을 부여한다.
- RANK는 동점에 같은 순위를 주고 다음 순위를 건너뛴다(1,2,2,4).
- DENSE_RANK는 동점에 같은 순위를 주되 순위를 건너뛰지 않는다(1,2,2,3).
- 그룹별 상위 N개를 뽑을 때는 동점 처리 의도가 없다면 ROW_NUMBER가 기본 선택이다.
CTE vs 서브쿼리, 그리고 CTE의 실행 방식
CTE(Common Table Expression)는 `WITH cte_name AS (SELECT ...)` 형태로 정의하는, 쿼리 실행 동안만 존재하는 임시 이름 붙은 결과 집합이다. 깊게 중첩된 서브쿼리보다 가독성과 유지보수성이 좋고, 같은 중간 결과를 여러 번 참조해야 할 때 특히 유용하며, 재귀 로직(조직도, 파일 트리 등 계층 구조)에도 쓸 수 있다. 서브쿼리는 WHERE나 FROM 절 안에서 한 번만 쓰이는 단순한 필터링에 적합하다.
— 정정: 원문은 CTE가 무조건 성능에 유리하다는 인상을 주지만, 엔진별로 CTE 실행 방식이 다르다는 점을 짚어야 한다. SQL Server, MySQL 8+, PostgreSQL 12 이상은 (재귀가 아닌) CTE를 기본적으로 쿼리 플래너가 메인 쿼리에 인라인(inline)하여 최적화한다. 다만 PostgreSQL은 버전 12 미만에서는 CTE를 '최적화 펜스(optimization fence)'로 취급해 항상 별도로 구체화(materialize)했고, 12 이상에서도 `AS MATERIALIZED`를 명시하면 여전히 구체화를 강제할 수 있다. 즉 'CTE는 다 인라인된다'가 아니라 '엔진과 버전, 힌트에 따라 다르다'가 정확한 설명이다.
핵심 포인트
- CTE는 WITH 절로 정의하는 임시 결과 집합으로, 중첩 서브쿼리보다 가독성이 좋고 재사용·재귀에 유리하다.
- 서브쿼리는 한 번만 쓰이는 단순 필터링에 적합하다.
- CTE가 항상 인라인되어 최적화된다는 통념은 부정확하다 — PostgreSQL 12 미만은 항상 구체화했고, 12 이상도 AS MATERIALIZED로 구체화를 강제할 수 있다 — 정정.
LEAD/LAG와 CASE 문
LAG()는 현재 행보다 '이전' 행의 값을, LEAD()는 '이후' 행의 값을 가져온다. 둘 다 윈도우 함수이므로 비용이 큰 셀프 조인 없이도 전월 대비 성장률이나 이벤트 간 시간차 계산을 할 수 있다.
CASE 문은 SQL에서 조건문(if-then-else)을 표현하는 방법이다. 조건을 평가해 그에 맞는 값을 반환하며, 데이터 정제, 값 분류, 파이프라인에서 커스텀 플래그를 만들 때 필수적으로 쓰인다.
핵심 포인트
- LAG는 이전 행, LEAD는 이후 행의 값을 가져와 전월 대비 성장률 등을 셀프 조인 없이 계산한다.
- CASE 문은 SQL의 if-then-else이며 데이터 정제·분류·플래그 생성에 쓰인다.