"이거 SQL로 되나요?" — Window 함수로 서브쿼리 지옥 탈출하기

서브쿼리 지옥에서 탈출하는 법

"전월 대비 성장률 뽑아주세요", "카테고리 내 TOP 3 제품", "코호트별 리텐션 분석"...

이런 요청을 받으면 서브쿼리 안에 서브쿼리를 넣고, 그 안에 또 서브쿼리를 넣다가 결국 본인도 이해할 수 없는 쿼리가 완성된 경험 있으신가요? Window 함수를 제대로 이해하면 복잡한 분석 쿼리도 깔끔하고 빠르게 작성할 수 있습니다.

1. Window 함수가 뭔가요? — 1분 설명

서브쿼리 방식 vs Window 함수 방식

"각 직원의 급여와 부서 평균 급여를 같이 보여주세요"

서브쿼리로 풀면:

SELECT
    e.name,
    e.department,
    e.salary,
    (SELECT AVG(salary) FROM employees e2
     WHERE e2.department = e.department) as dept_avg
FROM employees e;

행마다 서브쿼리가 실행됩니다. 1만 건이면 1만 번.

Window 함수로 풀면:

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

한 번에 계산됩니다. 같은 결과, 훨씬 빠름.

핵심 차이

2. OVER() 절 완전 분해

Window 함수의 핵심은 OVER() 절입니다. 세 부분으로 구성됩니다:

SUM(revenue) OVER (
    PARTITION BY category    -- 어떤 그룹 내에서?
    ORDER BY date            -- 어떤 순서로?
    ROWS BETWEEN             -- 어느 범위까지?
        UNBOUNDED PRECEDING
        AND CURRENT ROW
)

PARTITION BY — 그룹 나누기

-- 전체 매출 대비 각 제품의 비중
SELECT
    product_name,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER () as total_share,           -- 전체 대비
    revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category) as category_share  -- 카테고리 대비
FROM products;

결과:

ORDER BY — 순서 정하기

ORDER BY를 추가하면 누적 계산이 됩니다:

-- 일별 누적 매출
SELECT
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY date) as cumulative_revenue
FROM daily_sales;

결과:

3. LAG/LEAD — 전월 대비 계산의 핵심

기본 사용법

LAG(컬럼, N, 기본값)  -- N행 이전 값 (기본 1)
LEAD(컬럼, N, 기본값) -- N행 이후 값 (기본 1)

전월 대비 성장률 (MoM)

WITH monthly_revenue AS (
    SELECT
        strftime('%Y-%m', order_date) as month,
        SUM(total_amount) as revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
)
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 /
          LAG(revenue) OVER (ORDER BY month), 1) as growth_pct
FROM monthly_revenue
ORDER BY month;

결과:

주간 동일 요일 대비 (WoW)

SELECT
    date,
    revenue,
    LAG(revenue, 7) OVER (ORDER BY date) as same_day_last_week,
    ROUND((revenue - LAG(revenue, 7) OVER (ORDER BY date)) * 100.0 /
          LAG(revenue, 7) OVER (ORDER BY date), 1) as wow_growth
FROM daily_sales
WHERE date >= DATE('now', '-14 days');

4. 순위 함수 3총사 — 언제 뭘 써야 하나?

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    product_name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as row_num,
    RANK() OVER (ORDER BY revenue DESC) as rank,
    DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_rank
FROM products;

결과 (동점이 있는 경우):

차이점:

• ROW_NUMBER: 무조건 연속 번호 (동점 무시)
• RANK: 동점은 같은 순위, 다음 순위 건너뜀 (1,2,2,4)
• DENSE_RANK: 동점은 같은 순위, 다음 순위 안 건너뜀 (1,2,2,3)

언제 뭘 쓰나?

ROW_NUMBER — 정확히 N개만 필요할 때

-- 카테고리별 TOP 3 제품 (정확히 3개만)
WITH ranked AS (
    SELECT
        category,
        product_name,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

RANK — 동점자도 포함하고 싶을 때

-- TOP 10인데 10위가 동점이면 다 포함
WITH ranked AS (
    SELECT product_name, revenue,
           RANK() OVER (ORDER BY revenue DESC) as rank
    FROM products
)
SELECT * FROM ranked WHERE rank <= 10;
-- 동점 10위가 3명이면 12행 반환

DENSE_RANK — 순위 사이에 빈 번호 없이 매기고 싶을 때

-- 1등, 2등, 3등... 건너뛰기 없이
SELECT product_name, revenue,
       DENSE_RANK() OVER (ORDER BY revenue DESC) as rank
FROM products;

5. 이동 평균과 누적 합계

7일 이동 평균

SELECT
    date,
    daily_revenue,
    ROUND(AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) as ma7
FROM daily_sales;

결과:

월별 누적 매출 (Reset 매월)

SELECT
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY strftime('%Y-%m', date)  -- 월별로 리셋
        ORDER BY date
    ) as mtd_revenue  -- Month-to-Date
FROM daily_sales;

결과:

6. ROWS vs RANGE — 미묘하지만 중요한 차이

같은 날짜가 여러 개일 때 다르게 동작

-- 데이터: 같은 날짜에 여러 주문
-- | date       | amount |
-- |------------|--------|
-- | 2024-01-01 | 100    |
-- | 2024-01-01 | 150    |  ← 같은 날짜
-- | 2024-01-02 | 200    |

-- ROWS: 물리적 행 순서대로
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as rows_sum
FROM orders;
-- 결과: 100, 250, 450

-- RANGE: 같은 값끼리 묶어서
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date RANGE UNBOUNDED PRECEDING) as range_sum
FROM orders;
-- 결과: 250, 250, 450  ← 같은 날짜는 합계가 같음!

실무 팁: 대부분의 경우 ROWS가 더 직관적입니다. RANGE는 "같은 값은 같이 처리"가 필요할 때만 사용하세요.

7. 실전: 코호트 리텐션 분석

첫 구매 월별로 고객을 묶고, 그 후 몇 개월에 재구매했는지 분석합니다:

WITH customer_cohort AS (
    -- 고객별 첫 구매월
    SELECT
        customer_id,
        strftime('%Y-%m', MIN(order_date)) as cohort_month
    FROM orders WHERE status = 'completed'
    GROUP BY customer_id
),
monthly_activity AS (
    -- 고객별 월별 활동 여부
    SELECT DISTINCT
        o.customer_id,
        cc.cohort_month,
        strftime('%Y-%m', o.order_date) as activity_month
    FROM orders o
    JOIN customer_cohort cc ON o.customer_id = cc.customer_id
    WHERE o.status = 'completed'
)
SELECT
    cohort_month,
    COUNT(DISTINCT customer_id) as M0,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+1 month')) THEN customer_id END) as M1,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+2 month')) THEN customer_id END) as M2,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+3 month')) THEN customer_id END) as M3
FROM monthly_activity
GROUP BY cohort_month
ORDER BY cohort_month;

결과:

8. CTE로 복잡한 쿼리 정리하기

서브쿼리 지옥

SELECT * FROM (
    SELECT * FROM (
        SELECT customer_id, SUM(amount) as total
        FROM orders GROUP BY customer_id
    ) WHERE total > 100000
) WHERE customer_id IN (SELECT customer_id FROM vip_list);

읽기 어렵죠?

CTE로 정리

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) as total
    FROM orders
    GROUP BY customer_id
),
high_value AS (
    SELECT * FROM customer_totals
    WHERE total > 100000
)
SELECT hv.*
FROM high_value hv
WHERE hv.customer_id IN (SELECT customer_id FROM vip_list);

단계별로 이름을 붙이니 읽기도 쉽고 디버깅도 쉽습니다.

9. 실전 예제: 일간 KPI 대시보드

WITH daily_metrics AS (
    SELECT
        DATE(order_date) as date,
        SUM(total_amount) as revenue,
        COUNT(*) as orders,
        COUNT(DISTINCT customer_id) as customers
    FROM orders
    WHERE status = 'completed'
      AND order_date >= DATE('now', '-30 days')
    GROUP BY DATE(order_date)
)
SELECT
    date,
    revenue,
    orders,
    customers,

    -- 전일 대비
    LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY date)) * 100.0 /
          LAG(revenue) OVER (ORDER BY date), 1) as dod_growth,

    -- 7일 전 대비 (같은 요일)
    LAG(revenue, 7) OVER (ORDER BY date) as week_ago_revenue,
    ROUND((revenue - LAG(revenue, 7) OVER (ORDER BY date)) * 100.0 /
          LAG(revenue, 7) OVER (ORDER BY date), 1) as wow_growth,

    -- 7일 이동평균
    ROUND(AVG(revenue) OVER (
        ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) as ma7

FROM daily_metrics
ORDER BY date DESC
LIMIT 14;

결과:

10. 성능 팁: Window 함수가 느릴 때

문제: 다른 ORDER BY가 여러 개면 느림

-- ❌ 매번 다른 정렬 → 느림
SELECT
    SUM(revenue) OVER (ORDER BY date) as sum1,
    AVG(revenue) OVER (ORDER BY category) as avg1,  -- 다른 정렬
    LAG(revenue) OVER (ORDER BY product_id) as lag1 -- 또 다른 정렬
FROM sales;

해결: 같은 OVER 절 재사용

-- ✅ 같은 정렬을 공유 → 빠름
SELECT
    SUM(revenue) OVER w as running_sum,
    AVG(revenue) OVER w as running_avg,
    LAG(revenue) OVER w as prev_revenue
FROM sales
WINDOW w AS (ORDER BY date);  -- Named Window

인덱스 활용

-- PARTITION BY + ORDER BY 컬럼에 인덱스
CREATE INDEX idx_sales_cat_date ON sales(category, date);

-- 이제 이 쿼리가 빨라짐
SELECT
    category,
    date,
    SUM(revenue) OVER (PARTITION BY category ORDER BY date) as cumsum
FROM sales;

핵심 정리: Window 함수 3가지 원칙

1. 행을 유지하면서 집계: GROUP BY는 행을 줄이고, Window 함수는 행을 유지한다
2. OVER() 절이 핵심: PARTITION BY로 그룹, ORDER BY로 순서, ROWS/RANGE로 범위
3. LAG/LEAD로 비교: 전월 대비, 전주 대비 같은 시계열 비교는 LAG/LEAD가 정답

이 세 가지만 기억하면 "이거 SQL로 되나요?" 질문에 "네, Window 함수로요"라고 자신있게 대답할 수 있습니다.

부록: Window 함수 빠른 참조