MySQL Window Functions

글쓴이 /

Window Function이 해결하는 문제: GROUP BY 없이 집계

일반 집계 함수(SUM, COUNT, AVG)는 GROUP BY로 행을 묶으면 개별 행 정보가 사라진다. “각 부서의 평균 급여와 함께 개별 직원 정보도 보고 싶다”는 요구를 GROUP BY만으로는 해결할 수 없다. Window Function은 행을 그룹으로 묶지 않고, 각 행에서 관련 행 집합(윈도우)에 대한 계산을 수행한다.

MySQL 공식 문서는 Window Function을 “현재 행과 관련된 일련의 행에 대해 집계와 유사한 연산을 수행하지만, 행을 단일 출력 행으로 묶지 않는 함수”로 정의한다. MySQL 8.0에서 도입되었으며, 이 글에서는 OVER 절의 구조, 주요 함수, 프레임 지정, 그리고 실무 쿼리 패턴을 공식 문서 기반으로 정리한다.

OVER 절의 구조: PARTITION BY + ORDER BY + Frame

-- Window Function 기본 구조
SELECT
  column,
  function_name() OVER (
    PARTITION BY partition_column    -- 윈도우를 나누는 기준 (선택)
    ORDER BY order_column            -- 윈도우 내 정렬 (선택, 일부 함수에서 필수)
    frame_clause                     -- 프레임 범위 (선택)
  ) AS result
FROM table_name;
구성요소 역할 생략 시 기본값
PARTITION BY 행을 그룹(파티션)으로 나눔 전체 결과 집합이 하나의 파티션
ORDER BY 파티션 내 행의 순서 결정 파티션 내 순서 미정의
Frame 절 현재 행 기준 계산 범위 지정 ORDER BY 있으면 RANGE UNBOUNDED PRECEDING ~ CURRENT ROW

순위 함수: ROW_NUMBER, RANK, DENSE_RANK

-- 부서별 급여 순위
SELECT
  department,
  employee_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- 결과 예시 (engineering 부서):
-- | employee | salary | row_num | rnk | dense_rnk |
-- |----------|--------|---------|-----|-----------|
-- | Alice    | 120000 |       1 |   1 |         1 |
-- | Bob      | 120000 |       2 |   1 |         1 |  ← 동점
-- | Carol    | 100000 |       3 |   3 |         2 |  ← RANK는 3, DENSE_RANK는 2
함수 동점 처리 번호 건너뜀 사용 시점
ROW_NUMBER() 없음 (항상 고유) 없음 페이지네이션, 중복 제거
RANK() 같은 순위 부여 ✅ (1,1,3) 공동 순위 + 건너뜀 필요
DENSE_RANK() 같은 순위 부여 ❌ (1,1,2) 연속 순위 필요

집계 Window Function: SUM, AVG, COUNT를 OVER와 함께

-- 개별 행 + 부서별 통계를 한 쿼리로
SELECT
  department,
  employee_name,
  salary,
  AVG(salary)   OVER (PARTITION BY department) AS dept_avg,
  SUM(salary)   OVER (PARTITION BY department) AS dept_total,
  COUNT(*)      OVER (PARTITION BY department) AS dept_count,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

-- 누적 합계 (Running Total)
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

프레임 절로 이동 평균 계산

-- 최근 7일 이동 평균
SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_stats;

-- 전후 1행 포함 3행 이동 평균
SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_avg
FROM daily_stats;

프레임 절 완전 정리: ROWS vs RANGE

프레임 타입 단위 동점(같은 ORDER BY 값) 처리
ROWS 물리적 행 수 동점이어도 정확히 N행만 포함
RANGE 값의 범위 같은 값의 행을 모두 포함 
-- 프레임 경계 옵션 (공식 문서)
ROWS BETWEEN
  { UNBOUNDED PRECEDING | N PRECEDING | CURRENT ROW }
  AND
  { CURRENT ROW | N FOLLOWING | UNBOUNDED FOLLOWING }

-- 예시: 전체 파티션 합계 (프레임을 파티션 전체로)
SUM(salary) OVER (
  PARTITION BY department
  ORDER BY hire_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS total_dept_salary

-- 기본 프레임 (ORDER BY가 있을 때):
-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- → 누적 합계 동작, 동점 값은 모두 같은 결과

오프셋 함수: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE

-- LAG/LEAD: 이전/다음 행 값 참조
SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue, 1)  OVER (ORDER BY order_date) AS prev_day,
  LEAD(daily_revenue, 1) OVER (ORDER BY order_date) AS next_day,
  daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS day_over_day
FROM daily_stats;

-- FIRST_VALUE / LAST_VALUE
SELECT
  department,
  employee_name,
  salary,
  FIRST_VALUE(employee_name) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS highest_paid,
  LAST_VALUE(employee_name) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_paid
FROM employees;
함수 역할 주의사항
LAG(col, n, default) n행 이전 값 (기본 n=1) 첫 행은 NULL (default로 지정 가능)
LEAD(col, n, default) n행 다음 값 (기본 n=1) 마지막 행은 NULL
FIRST_VALUE(col) 프레임 내 첫 번째 값 프레임 기본값 주의
LAST_VALUE(col) 프레임 내 마지막 값 기본 프레임이 CURRENT ROW까지라 UNBOUNDED FOLLOWING 필요
NTH_VALUE(col, n) 프레임 내 n번째 값 n번째 행이 없으면 NULL

NAMED WINDOW: 중복 OVER 절 제거 (MySQL 8.0)

-- 같은 OVER 절이 반복될 때 WINDOW 절로 이름 부여
SELECT
  department,
  employee_name,
  salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK()       OVER w AS rnk,
  AVG(salary)  OVER w AS running_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

공식 문서에 따르면 NAMED WINDOW는 WINDOW 절에서 정의하고, OVER window_name으로 참조한다. 복잡한 쿼리에서 가독성과 유지보수성을 크게 높여준다.

실전 쿼리 패턴

패턴 1: 그룹 내 Top-N 조회

-- 부서별 급여 상위 3명 (서브쿼리 + ROW_NUMBER)
SELECT *
FROM (
  SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

패턴 2: 연속 이벤트 그룹화 (Gap and Island)

-- 연속 로그인 일수 계산
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS consecutive_days
FROM (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) DAY) AS grp
  FROM user_logins
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;  -- 3일 이상 연속 로그인

패턴 3: 전월 대비 증감률

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 2
  ) AS growth_pct
FROM monthly_stats;

실전 체크리스트: Window Function 설계 6단계

  1. GROUP BY vs Window Function 선택 — 개별 행이 필요 없으면 GROUP BY, 개별 행 + 집계를 동시에 보려면 Window Function
  2. PARTITION BY 기준 확인 — 생략하면 전체 결과가 하나의 윈도우, 의도된 그룹핑인지 검증
  3. ORDER BY 존재 시 기본 프레임 인지 — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 기본, 전체 파티션이 필요하면 명시적 프레임 지정
  4. LAST_VALUE에는 UNBOUNDED FOLLOWING 필수 — 기본 프레임은 CURRENT ROW까지이므로 LAST_VALUE가 의도대로 동작하지 않음
  5. NAMED WINDOW로 중복 제거 — 같은 OVER 절이 3번 이상 반복되면 WINDOW 절 사용
  6. WHERE 절에서 직접 사용 불가 — Window Function 결과로 필터링하려면 서브쿼리/CTE로 감싸야 함

흔한 실수 4가지와 방지법

실수 1: LAST_VALUE가 기대와 다른 값을 반환

증상: 파티션의 마지막 행이 아닌 현재 행의 값이 반환된다.

원인: ORDER BY가 있으면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이므로, LAST_VALUE의 “마지막”이 현재 행이 된다.

방지: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 명시한다.

실수 2: WHERE 절에서 Window Function 직접 사용

증상: WHERE ROW_NUMBER() OVER (...) <= 3 작성 시 문법 에러.

원인: MySQL에서 Window Function은 SELECT와 ORDER BY에서만 사용할 수 있다. WHERE, HAVING, GROUP BY에서는 사용 불가.

방지: 서브쿼리 또는 CTE로 감싼 후 외부에서 필터링한다.

실수 3: ROWS와 RANGE를 혼동

증상: 같은 날짜(동점)의 행이 여러 개일 때 ROWS 3 PRECEDING과 RANGE 3 PRECEDING의 결과가 다르다.

방지: ROWS는 물리적 행 수, RANGE는 값의 범위다. 정확한 N행이 필요하면 ROWS, 같은 값을 모두 포함하려면 RANGE를 사용한다.

실수 4: ORDER BY 없이 순위/오프셋 함수 사용

증상: ROW_NUMBER() OVER (PARTITION BY dept)에서 ORDER BY를 생략하면 행 번호가 실행마다 달라진다.

방지: 순위 함수(ROW_NUMBER, RANK, DENSE_RANK)와 오프셋 함수(LAG, LEAD)는 반드시 ORDER BY를 지정한다. 결과의 결정성(determinism)이 보장되지 않는다.

마무리

MySQL 8.0 Window Function은 행을 묶지 않고 집계하는 강력한 도구다. PARTITION BY로 그룹을, ORDER BY로 순서를, 프레임 절로 계산 범위를 제어하며, ROW_NUMBER·LAG·SUM 등을 조합해 Top-N, 누적 합계, 이동 평균, 전월 대비 분석을 단일 쿼리로 해결할 수 있다. 이 글의 모든 내용은 MySQL 공식 문서(Window Functions, Window Function Concepts)를 근거로 한다.

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux