SQL 윈도우 함수 실전 — ROW_NUMBER·RANK·LAG/LEAD로 top-N·전월대비·이동평균 뽑기

서브쿼리로 "카테고리별 최고가 상품"을 뽑느라 self-join을 세 번 겹쳤던 쿼리를, 윈도우 함수 한 줄로 갈아엎었던 날을 기억한다. 실행 계획도 깔끔해지고 무엇보다 읽을 수 있는 쿼리가 됐다. 윈도우 함수는 처음엔 문법이 낯설어서 겁먹기 쉬운데, OVER 절의 세 부품만 손에 익으면 그다음은 응용일 뿐이다.

윈도우 함수가 뭐고, GROUP BY와 뭐가 다른가?

한 문장으로: GROUP BY는 여러 행을 하나로 접고, 윈도우 함수는 행을 그대로 둔 채 옆에 집계값을 덧붙인다. SUM(x) OVER (...)를 쓰면 원본 행이 사라지지 않는다. 그래서 "각 주문 행에 그 고객의 누적 구매액을 같이 보여줘" 같은, 원행과 집계를 동시에 원하는 요구가 한 방에 해결된다.

차이를 눈으로 보자. 부서별 급여 합계가 필요할 때 GROUP BY는 부서 수만큼 행이 줄지만, 윈도우 함수는 직원 행을 유지한 채 각자 옆에 부서 합계를 붙인다.

-- GROUP BY: 행이 접힌다 (부서당 1행)
SELECT dept_id, SUM(salary) AS dept_total
FROM employees
GROUP BY dept_id;

-- 윈도우 함수: 행이 그대로, 옆에 집계가 붙는다
SELECT
  emp_id, dept_id, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
  ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id), 1) AS pct_of_dept
FROM employees;

두 번째 쿼리는 "내 급여가 우리 부서 총액의 몇 %인가"를 GROUP BY로는 서브쿼리 없이 못 한다. 이게 윈도우 함수의 존재 이유다.

OVER 절의 세 부품: PARTITION BY, ORDER BY, 프레임

OVER 절은 "이 함수가 볼 창을 어떻게 만들지"를 정의한다. 부품은 셋이다.

• PARTITION BY — 데이터를 그룹으로 쪼갠다. 생략하면 전체가 한 창. GROUP BY의 그룹 개념과 같지만 행은 유지된다.
• ORDER BY — 창 안에서의 정렬. 순위 함수·LAG/LEAD·누적합에 필수. 이게 있으면 프레임 기본값이 달라진다(뒤에서 설명).
• 프레임(ROWS/RANGE) — 현재 행 기준 "몇 행까지 계산에 넣을지". 이동평균·누적합의 핵심.

함수명(인자) OVER (
  PARTITION BY 그룹컬럼
  ORDER BY 정렬컬럼
  ROWS BETWEEN 시작 AND 끝    -- 프레임(선택)
)

같은 OVER 절을 여러 번 반복해서 쓰기 싫으면 WINDOW 절로 이름을 붙여 재사용할 수 있다. PostgreSQL·MySQL 8.0+에서 지원한다.

SELECT
  emp_id, dept_id, salary,
  RANK()       OVER w AS rnk,
  AVG(salary)  OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);

ROW_NUMBER vs RANK vs DENSE_RANK — 동점 처리가 전부다

셋 다 순위를 매기지만 동점(tie)을 어떻게 다루느냐가 유일한 차이다. 이걸 헷갈리면 "top 3"를 뽑았는데 4개가 나오거나 3개가 나오거나 한다.

• ROW_NUMBER() — 무조건 1,2,3,4… 동점도 구분해 유일값. "각 그룹에서 딱 하나"를 뽑을 때(중복 제거, 최신 행 1개).
• RANK() — 동점은 같은 순위, 다음은 건너뜀(1,2,2,4). 올림픽 메달식.
• DENSE_RANK() — 동점은 같은 순위, 다음은 안 건너뜀(1,2,2,3). "서로 다른 값의 등수"가 필요할 때, 예: 상위 3개 가격대.

실전 1: 그룹별 top-N (카테고리별 매출 상위 3개)

가장 많이 쓰는 패턴. "각 카테고리에서 매출 상위 3개 상품"은 ROW_NUMBER로 번호를 매긴 뒤 CTE 바깥에서 <= 3으로 거른다. 앞서 말했듯 순위 필터는 WHERE에서 직접 못 하니 감싸야 한다.

WITH ranked AS (
  SELECT
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC, product_id   -- id로 동점 tie-break
    ) AS rn
  FROM product_sales
)
SELECT category, product_name, revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

동점 상품까지 모두 포함하고 싶으면(예: 3위가 두 개면 둘 다) ROW_NUMBER 대신 RANK를 쓴다. 반대로 카테고리당 정확히 최신 주문 1건만 원하면 ROW_NUMBER에 ORDER BY order_date DESC를 걸고 rn = 1. 이 "그룹별 최신 1건" 패턴은 실무에서 정말 자주 나온다.

실전 2: LAG/LEAD로 전월 대비, 증감률

LAG는 "이전 행 값", LEAD는 "다음 행 값"을 현재 행으로 끌어온다. 시계열에서 전월 대비(MoM), 전일 대비를 구할 때 자체 조인 없이 끝난다. 세 번째 인자는 값이 없을 때의 기본값(첫 행은 이전 행이 없으니 NULL이 되는데, 0으로 채우고 싶을 때).

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)              AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month)    AS diff,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0)
  , 1) AS mom_pct
FROM monthly_revenue
ORDER BY month;

여기서 NULLIF(..., 0)은 전월 매출이 0일 때 0으로 나누는 사고를 막는 안전장치다. 이거 안 걸었다가 프로덕션에서 division by zero 터뜨린 적 있다. 습관처럼 감싸자.

제품별로 나눠서 전월 대비를 보려면 PARTITION BY를 추가한다. LAG(revenue, 2)처럼 두 번째 인자로 "몇 행 전"인지도 조절할 수 있다(전전월).

SELECT
  product_id, month, revenue,
  LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS prev
FROM product_monthly;

실전 3: 프레임(ROWS vs RANGE)과 누적합

프레임은 "현재 행을 기준으로 계산에 포함할 행의 범위"다. 누적합(running total)은 "맨 처음부터 현재 행까지"를 더하는 것이므로 프레임으로 표현된다.

SELECT
  order_date, amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY order_date;

여기서 가장 흔한 함정: ORDER BY만 쓰고 프레임을 생략하면 기본 프레임은 ROWS가 아니라 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW다. RANGE는 "값이 같은 행을 한 덩어리로" 취급한다. 그래서 같은 날짜 주문이 여러 건이면, 그 날짜의 모든 주문이 이미 다 더해진 값이 그 날 행마다 똑같이 찍힌다. 행 단위 누적을 원했다면 결과가 틀어진다.

결론: 누적합·이동평균처럼 "행 개수"로 생각하는 계산은 항상 ROWS를 명시하는 게 안전하다. RANGE는 RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW처럼 진짜 "값의 범위"(지난 7일치, 빠진 날짜 무시)가 필요할 때만 쓴다. 이 INTERVAL 프레임은 PostgreSQL이 잘 지원하고, MySQL은 제약이 있다.

실전 4: 이동평균(moving average)

7일 이동평균은 "현재 행 포함, 앞쪽 6행"의 평균이다. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW로 정확히 7개 행 창을 만든다.

SELECT
  metric_date,
  daily_users,
  ROUND(AVG(daily_users) OVER (
    ORDER BY metric_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 1) AS ma_7d,
  COUNT(*) OVER (
    ORDER BY metric_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS window_size   -- 앞부분은 7 미만
FROM daily_metrics
ORDER BY metric_date;

주의할 디테일 하나. 데이터 초반 6일은 창에 7개가 안 차서 3일치·4일치 평균이 나온다. window_size를 같이 뽑아 두면 "이 평균이 몇 개로 계산됐는지" 보이니 대시보드에서 초반 구간을 걸러내기 쉽다. 또, 날짜에 빠진 날이 있으면 ROWS는 "달력상 7일"이 아니라 "존재하는 7행"을 본다. 결측일을 0으로 채운 date spine과 조인해 두는 게 정석이다.

중심 이동평균이 필요하면 앞뒤로 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING처럼 대칭 창을 쓰면 된다.

실전 5: 세션화(sessionization)

이벤트 로그를 "사용자 세션"으로 묶는 건 윈도우 함수의 진가가 드러나는 작업이다. 규칙은 보통 "같은 유저의 이벤트 간격이 30분을 넘으면 새 세션". LAG로 이전 이벤트 시각을 끌어오고, 간격이 임계값을 넘으면 1을 찍은 뒤, 그 1들을 누적합하면 세션 번호가 된다. 이 "플래그 → 누적합 = 그룹ID" 패턴은 gaps-and-islands 문제의 표준 해법이다.

WITH ev AS (
  SELECT
    user_id, event_time,
    LAG(event_time) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS prev_time
  FROM events
),
flagged AS (
  SELECT
    user_id, event_time,
    CASE
      WHEN prev_time IS NULL
        OR event_time - prev_time > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS is_new_session
  FROM ev
)
SELECT
  user_id, event_time,
  SUM(is_new_session) OVER (
    PARTITION BY user_id ORDER BY event_time
    ROWS UNBOUNDED PRECEDING
  ) AS session_seq
FROM flagged
ORDER BY user_id, event_time;

결과의 session_seq는 유저별 1,1,1,2,2,3… 식으로 증가한다. 여기에 user_id || '-' || session_seq로 전역 세션ID를 만들면 세션 단위 집계(세션당 페이지뷰, 체류시간)로 바로 넘어갈 수 있다. 시간 타입 연산은 DB마다 문법이 다르다 — PostgreSQL은 위처럼 INTERVAL, MySQL은 TIMESTAMPDIFF(MINUTE, prev_time, event_time) > 30, BigQuery는 TIMESTAMP_DIFF(...)를 쓴다.

성능과 함정 — 실무에서 데인 것들

윈도우 함수가 마법은 아니다. 몇 가지 현실.

• 정렬 비용 — ORDER BY가 있는 윈도우는 내부적으로 정렬(또는 정렬된 인덱스 스캔)을 한다. PARTITION BY + ORDER BY와 같은 순서의 복합 인덱스가 있으면 정렬을 건너뛸 수 있다. 큰 테이블에서 이게 실행시간을 좌우한다.
• 같은 창은 재사용 — 옵티마이저는 동일한 OVER 절을 공유하니, RANK와 SUM이 같은 창이면 정렬은 한 번만 일어난다. 창 정의를 통일해 두면 이득.
• DISTINCT 불가 — COUNT(DISTINCT x) OVER (...)는 표준에서 지원 안 한다(대부분 DB에서 에러). 필요하면 서브쿼리로 우회.
• WHERE에서 못 거른다 — 반복하지만 순위·누적 결과 필터는 항상 CTE 바깥에서.
• QUALIFY — BigQuery·Snowflake·DuckDB엔 QUALIFY rn = 1이 있어 CTE 감싸기를 생략할 수 있다. 편하지만 표준은 아니라 이식성은 떨어진다.

자주 묻는 질문

WHERE 절에서 ROW_NUMBER를 바로 못 쓰는 이유가 뭔가요?

윈도우 함수는 WHERE·GROUP BY·HAVING가 끝난 다음에 계산되기 때문입니다. WHERE가 실행되는 시점엔 순위값이 아직 존재하지 않죠. 그래서 서브쿼리나 CTE로 한 번 감싸 순위를 물리화한 뒤, 바깥 쿼리의 WHERE에서 걸러야 합니다. BigQuery/Snowflake라면 QUALIFY로 이 단계를 줄일 수 있습니다.

ROW_NUMBER, RANK, DENSE_RANK 중 뭘 써야 하나요?

"각 그룹에서 딱 하나(중복 제거, 최신 1건)"면 ROW_NUMBER, "동점을 같은 등수로 두되 top-N에 동점까지 포함"하려면 RANK, "서로 다른 값의 등수(연속된 순위)"가 필요하면 DENSE_RANK입니다. 동점 처리 방식만 다르다고 기억하면 됩니다.

ROWS와 RANGE 중 이동평균엔 뭘 써야 하나요?

ROWS입니다. 이동평균·누적합은 "행 개수" 기준이라 ROWS로 명시해야 의도대로 동작합니다. ORDER BY만 쓰고 프레임을 생략하면 기본값이 RANGE라, 같은 정렬값 행이 한 덩어리로 묶여 결과가 틀어질 수 있습니다. 진짜 "지난 7일 같은 값 범위"가 필요할 때만 RANGE(INTERVAL)를 씁니다.

LAG로 전월 대비를 구할 때 첫 행이 NULL인데 어떻게 처리하나요?

첫 행은 이전 행이 없어 LAG가 NULL을 반환합니다. 0으로 채우고 싶으면 LAG(revenue, 1, 0)처럼 세 번째 인자에 기본값을 줍니다. 증감률 계산 시엔 분모가 0이 되지 않게 NULLIF(prev, 0)로 감싸 division by zero도 함께 막는 걸 권합니다.

PARTITION BY를 생략하면 어떻게 되나요?

전체 결과 집합이 하나의 창(파티션)이 됩니다. 즉 그룹 구분 없이 전체 기준으로 순위·누적·평균이 계산됩니다. "전체 매출 대비 비중"이나 "전 기간 누적합"처럼 그룹이 필요 없을 때 유용합니다.

윈도우 함수가 GROUP BY보다 항상 빠른가요?

아닙니다. 단순 집계 리포트(행을 접어도 되는 경우)는 GROUP BY가 더 가볍습니다. 윈도우 함수는 정렬 비용이 붙기 때문이죠. 다만 "원본 행 + 집계"를 동시에 원하거나 top-N, 이동평균처럼 서브쿼리/self-join으로 풀어야 하는 문제에선 윈도우 함수가 더 빠르고 가독성도 좋습니다. PARTITION BY·ORDER BY 순서와 맞는 복합 인덱스를 걸면 정렬을 건너뛰어 성능이 크게 개선됩니다.

세션화에서 "플래그 → 누적합" 패턴은 왜 세션 번호가 되나요?

새 세션이 시작되는 행에만 1을, 나머지엔 0을 찍은 뒤 시간순으로 누적합을 하면, 1이 나올 때마다 값이 하나씩 증가합니다. 결과적으로 같은 세션의 행들은 같은 누적값을 공유하게 되어 그것이 곧 세션 일련번호가 됩니다. 이 기법은 연속 구간을 묶는 gaps-and-islands 문제의 표준 해법입니다.