윈도우 함수·피벗·CTE로 설계하는 분석 SQL

행을 줄이지 않고 분석하는 SQL

분석 쿼리에서는 최신 상태를 골라내고, 상태가 바뀐 지점을 찾고, 열과 행의 형태를 바꾸거나 계층을 순회해야 할 때가 많다. 윈도우 함수, 피벗·언피벗, CTE는 이런 요구를 각각 분리하면서도 함께 조합할 수 있는 SQL 구성 요소다.

윈도우 함수는 파티션, 정렬, 프레임을 지정한 행 집합에 분석 값을 계산한다. SUM OVER, ROW_NUMBER, LAG 등이 여기에 속한다. GROUP BY와 달리 원본 행을 하나로 축소하지 않으므로, 분석용 파생 컬럼을 추가하는 데 적합하다.

피벗은 행 값을 열로 돌려 집계 리포트에 맞는 형태를 만들고, 언피벗은 넓은 열 구조를 행으로 풀어 폭이 좁은 정규화 형태로 되돌린다. CTE(Common Table Expression)는 WITH 절로 이름 붙인 일시 뷰를 선언하는 방식이며, 복잡한 쿼리를 단계로 나누고 재귀 CTE로 계층 구조를 탐색할 수 있게 한다. 다만 CTE의 물질화 동작은 DBMS마다 다르며, 최적화기가 인라인할 수 있다는 점을 고려해야 한다.

정렬 기준이 분석 결과를 결정한다

PARTITION BY는 분석 그룹의 경계를 만들고, ORDER BY는 그 안의 순서를 정한다. 누적값이나 이동 평균처럼 범위를 다루는 계산에는 ROWS 또는 RANGE 프레임을 사용한다.

특히 최신 행을 고르거나 이전 행을 참조하는 쿼리에서는 정렬이 결정적이어야 한다. 업무 키, 타임스탬프, 타이브레이커를 함께 사용해 같은 값이 있을 때도 순서가 고정되도록 구성한다.

ROW_NUMBER는 그룹 안에서 유일한 순번을 매긴다. 중복 제거, 고객별 최신 행 선택, 그룹별 Top-N에 적합하다. LAG는 이전 행의 값을 읽어 상태 변화 시점, 세션 경계, 시간 간격을 계산하는 데 쓴다.

리포트 형태와 정규화 형태를 오가는 방법

피벗 열이 고정되어 있다면 SQL Server의 네이티브 PIVOT 또는 CASE와 SUM을 조합한 조건부 집계를 사용할 수 있다. 반면 동적 피벗은 메타데이터를 바탕으로 동적 SQL을 구성해야 하며, NULL 처리와 데이터 타입 일치 여부를 확인해야 한다.

언피벗은 넓은 테이블을 분석이나 모델링에 맞는 좁은 행 구조로 변환한다. 변환 전에 데이터의 원자성과 타입 정규화를 점검하는 편이 안전하다.

CTE로 복잡한 쿼리를 단계화하기

CTE는 필터링, 전처리, 분석 계산, 집계를 독립된 단계로 나눌 수 있게 한다. 공통 서브쿼리를 재사용하고 각 단계의 결과를 검증하기도 쉬워진다.

재귀 CTE는 앵커 쿼리와 재귀 단계로 구성한다. 계층을 탐색할 때는 종료 조건과 깊이 제한을 명시해야 하며, MAXRECURSION 같은 제한도 함께 검토한다. 성능 임계 구간에서는 CTE를 임시 테이블로 명시적으로 물질화하고 인덱스를 부여하는 선택지가 있다.

고객 이벤트에서 최신 상태를 고르기

고객 이벤트에서 고객별 최신 상태만 필요하다면, 이벤트 시각을 기준으로 순번을 부여한 뒤 첫 행만 남긴다.

-- PostgreSQL 13+ / SQL Server 2019+
WITH ranked AS (
  SELECT
    customer_id, status, event_ts,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_ts DESC, customer_id) AS rn
  FROM customer_events
)
SELECT customer_id, status, event_ts
FROM ranked
WHERE rn = 1;

상태 변경과 체류 시간을 계산하기

티켓 상태 이력에서는 현재 상태와 이전 상태·시각을 함께 읽어 변경 행을 골라낼 수 있다. LAG(status)와 LAG(updated_at)이 이 역할을 맡는다.

-- PostgreSQL 13+
SELECT
  ticket_id,
  status,
  updated_at,
  LAG(status)     OVER (PARTITION BY ticket_id ORDER BY updated_at) AS prev_status,
  EXTRACT(EPOCH FROM (updated_at - LAG(updated_at) OVER (PARTITION BY ticket_id ORDER BY updated_at))) AS seconds_since_prev
FROM ticket_status_log
QUALIFY status IS DISTINCT FROM prev_status; -- BigQuery 문법
-- PostgreSQL은 외부 SELECT로 감싼 후 WHERE status IS DISTINCT FROM prev_status 적용

월별 매출을 열로 펼치기

SQL Server에서는 PIVOT으로 월별 매출을 열 단위 리포트로 바꿀 수 있다.

-- SQL Server 2019+
SELECT customer_id, [2025-08] AS m08, [2025-09] AS m09, [2025-10] AS m10
FROM (
  SELECT customer_id, FORMAT(order_date, 'yyyy-MM') AS ym, amount
  FROM sales
) s
PIVOT (SUM(amount) FOR ym IN ([2025-08], [2025-09], [2025-10])) p;

PostgreSQL에서는 조건부 집계로 같은 결과를 만들 수 있다.

-- PostgreSQL 13+
SELECT
  customer_id,
  SUM(CASE WHEN to_char(order_date, 'YYYY-MM') = '2025-08' THEN amount END) AS m08,
  SUM(CASE WHEN to_char(order_date, 'YYYY-MM') = '2025-09' THEN amount END) AS m09,
  SUM(CASE WHEN to_char(order_date, 'YYYY-MM') = '2025-10' THEN amount END) AS m10
FROM sales
GROUP BY customer_id;

넓은 지표 테이블을 행 구조로 바꾸기

여러 지표가 열로 저장된 테이블은 언피벗을 통해 지표 이름과 값으로 분리할 수 있다.

-- SQL Server 2019+
SELECT customer_id, metric_name, metric_value
FROM customer_metrics
UNPIVOT (metric_value FOR metric_name IN (score, risk, lifetime_value)) u;

PostgreSQL에서는 UNION ALL을 대안으로 사용할 수 있다.

-- PostgreSQL 13+
SELECT customer_id, 'score' AS metric_name, score::numeric AS metric_value FROM customer_metrics
UNION ALL
SELECT customer_id, 'risk', risk::numeric FROM customer_metrics
UNION ALL
SELECT customer_id, 'lifetime_value', lifetime_value::numeric FROM customer_metrics;

조직 계층을 재귀적으로 순회하기

재귀 CTE는 최상위 노드를 앵커로 선택하고, 자식 노드를 조인하면서 깊이를 증가시킨다. 자식이 없거나 깊이 제한에 도달하면 탐색을 끝낸다.

-- PostgreSQL 13+ / SQL Server 2019+
WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 1 AS depth, CAST(id AS text) AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, o.depth + 1, o.path || '>' || e.id
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org
-- SQL Server: OPTION (MAXRECURSION 1000)
;

분석 성능과 검증 가능성에 미치는 영향

정렬과 인덱스가 최적화된 경우 ROW_NUMBER 기반의 중복 제거·최신 행 추출은 서브쿼리 대비 실행 시간이 30~~70% 개선 가능하다. LAG 기반 상태 전이 집계는 셀프 조인 비용을 제거해 CPU·메모리 사용량을 20~~40% 절감할 수 있다. 사전 집계와 고정 열을 기준으로 한 피벗 리포트에서는 보고서 생성 쿼리 지연이 40% 이상 단축된 사례가 있다.

CTE로 단계를 분리하면 쿼리의 가독성과 검증성이 높아진다. 윈도우 함수는 행을 보존하는 방식으로 분석을 구현해 데이터 손실 위험을 낮추며, 피벗·언피벗은 분석과 모델링에 필요한 양방향 변환을 체계화한다.

⤢✕

운영 시 확인할 조건과 자원

입력 테이블은 파티션 키와 시간 키를 인덱스로 갖는 N행의 정규화 테이블을 전제로 한다. 처리 단계에서는 CTE로 필터와 전처리를 분리하고, 파티션·정렬 일관성을 확보한 뒤 윈도우 함수를 계산한다. 필요하면 집계 후 피벗 또는 언피벗을 수행하고, 결과에서는 행 수 보존 여부, NULL 대체, 스키마 일치를 검증한다.

LAG의 첫 행에는 LAG(value, 1, default_value) 형식으로 기본값을 지정할 수 있다. 정렬 동률은 ORDER BY에 ID 같은 결정적 컬럼을 더해 해결한다. 피벗에서 빠진 값은 COALESCE로 0 또는 기본값으로 대체하며, 재귀 CTE에는 path 검사로 사이클을 막고 MAXRECURSION 또는 WHERE depth<=N으로 깊이를 제한한다.

리포팅 쿼리는 REPEATABLE READ 또는 스냅샷 격리를 사용해 읽기 일관성을 확보할 수 있다. 대형 정렬과 윈도우 계산에서는 Work_mem 또는 TempDB 사용량을 모니터링하고, 필요하면 배치 시간대에 실행한다. 인덱스는 PARTITION BY와 ORDER BY의 선행 키를 커버하도록 구성하며, 피벗 전 사전 집계 인덱스도 활용한다.

기법	성능	확장성	일관성	안정성	운영 편의
윈도우 함수(ROW_NUMBER/LAG)	높음(정렬 최적 시)	높음(행 보존형)	높음(결정적 정렬 필요)	높음	높음(표준 SQL)
셀프 조인 대안	중간	중간	중간(조인 키 품질 의존)	중간	중간
PIVOT/조건부 집계	중간~높음(열 수 적을 때)	중간(열 증가 시 비용 증가)	높음	높음	중간(동적 피벗은 복잡)
UNPIVOT/UNION ALL	중간	높음	높음	높음	높음
CTE/재귀 CTE	중간(물질화 영향)	중간	높음(단계 검증 용이)	중간~높음	높음(가독성)

가독성과 실행 비용 사이의 선택

윈도우 함수의 정렬 키에는 단조 증가 타임스탬프와 PK 조합을 적용한다. 피벗은 고정 열 목록과 사전 집계로 단순하게 유지하고, 동적 피벗은 ETL 계층에서 처리하는 방식을 고려할 수 있다. CTE는 단계별 출력 검증용 스냅샷으로 쓰되, 성능이 임계점에 이르면 임시 테이블로 물질화한 뒤 인덱스를 부여한다.

CTE의 물질화는 가독성을 높이는 대신 불필요한 I/O를 늘릴 수 있고, DBMS 최적화기 동작도 다르다. PostgreSQL 12+는 대부분 인라인한다. 피벗은 열 폭이 넓어질수록 가독성은 높아지지만 스키마가 경직되고 쿼리 유지보수 비용이 커진다. 윈도우 함수의 정렬은 정확성을 높이는 대신 정렬 메모리와 디스크 사용량을 증가시킨다.

운영 전에는 다음 항목을 확인한다.

파티션과 정렬 키가 결정적으로 구성되었는지 확인한다.
LAG·LEAD의 기본값과 NULL 처리 방식을 정의한다.
피벗 대상 열 목록을 고정할지 동적으로 만들지 명시한다.
재귀 CTE의 종료 조건과 깊이 한도를 설정한다.
실행 계획에서 Sort/Spill, Hash Match, Temp I/O를 확인한다.