🚨INCIDENT ALERT

HIGH

랭킹, 누적합, 전월 대비 같은 분석 쿼리는 GROUP BY만으로는 원본 행을 유지하기 어렵습니다. 윈도우 함수는 행을 접지 않고 주변 행과 비교하는 강력한 도구입니다. 리포트와 운영 대시보드 쿼리를 작성할 때 큰 차이를 만듭니다.

이번 챕터에서 배울 것

Window 함수는 OVER() 절로 정의된 '윈도우(창)'를 통해 관련 행들을 바라보며 집계나 분석을 수행합니다. GROUP BY와 달리 각 행이 원본 그대로 결과에 남아있으면서 집계값도 함께 표시됩니다. PARTITION BY로 그룹 경계를, ORDER BY로 행의 순서를, ROWS/RANGE로 프레임 범위를 정의합니다.

1Window 함수의 구조 — OVER() 절의 PARTITION BY, ORDER BY, FRAME
2순위 함수 — ROW_NUMBER, RANK, DENSE_RANK, NTILE 비교
3GROUP BY와의 핵심 차이 — 왜 행이 줄어들지 않는가
4LAG, LEAD — 이전/다음 행 값으로 증감률 계산
5누적합과 이동평균 — SUM, AVG OVER 프레임 활용
6FIRST_VALUE, LAST_VALUE로 윈도우 경계 값 추출

Window 함수 — RANK, ROW_NUMBER, LAG, LEAD

영업팀 요청이 왔다. 각 직원의 월별 매출과 전체 순위를 함께 보고 싶다고. GROUP BY로 집계하면 개별 매출 행이 사라지고, 서브쿼리로 순위를 붙이려니 자기 JOIN이 두 겹 세 겹 쌓였다. 쿼리가 20줄이 넘었고 실행 계획을 보면 테이블을 세 번 스캔하고 있었다. 그때 누군가 RANK() OVER (ORDER BY sales DESC) 한 줄을 보여줬다. 원본 행은 그대로 두면서 각 행에 순위 컬럼만 추가됐다 — 테이블 스캔은 한 번. Window 함수를 모르면 서브쿼리 지옥이나 애플리케이션 레이어 정렬로 우회하게 되고, 쿼리는 점점 읽기 어려워진다. RANK, ROW_NUMBER, LAG — 이 세 가지만 제대로 알아도 분석 쿼리의 절반은 깔끔하게 해결된다.

💡개념

Window 함수 기초 — OVER()가 GROUP BY와 다른 이유

부서별 직원 급여를 조회하면서 부서 평균도 같이 보고 싶습니다. GROUP BY를 쓰면 개별 행이 사라지고, 서브쿼리를 쓰면 복잡해집니다. Window 함수는 행을 유지하면서 그룹 집계를 동시에 할 수 있습니다. GROUP BY와 무엇이 다른지 이해하지 못하면 이런 요구사항마다 복잡한 서브쿼리를 짜게 됩니다.

Window 함수란?

Window 함수는 현재 행과 관련된 행들의 집합(윈도우)에 대해 계산을 수행하는 함수입니다. 핵심은 행을 줄이지 않는다는 점입니다. GROUP BY는 그룹 수만큼 행을 압축하지만, Window 함수는 원본 행을 그대로 유지하면서 집계값을 각 행에 추가합니다.

SQL

SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

SELECT
    product_name,
    category_id,
    price,
    AVG(price) OVER (PARTITION BY category_id) AS category_avg
FROM products;

OUTPUT

실행 완료 또는 조회 결과가 표시됩니다.

🔍실행 후 확인할 것

파티션 범위—PARTITION BY 기준별로 순위나 누적합이 따로 계산되는지 확인합니다.
정렬 기준—ORDER BY가 바뀌면 window 결과도 달라지는지 봅니다.
원본 행 유지—GROUP BY와 달리 개별 행이 사라지지 않는지 점검합니다.

첫 번째 쿼리는 카테고리가 5개라면 5행을 반환합니다. 두 번째 쿼리는 상품 수만큼 행을 반환하며, 각 행에 해당 카테고리의 평균 가격이 함께 표시됩니다.

OVER() 절 구조

OVER() 절은 세 가지 구성 요소로 이루어집니다. PARTITION BY는 윈도우를 나누는 기준으로, 생략하면 전체가 하나의 파티션입니다. ORDER BY는 파티션 내에서 행의 순서를 정의하며, 순위 함수와 누적합에 필수입니다. ROWS/RANGE BETWEEN은 현재 행 기준으로 프레임(계산 범위)을 정의합니다.

SQL

함수명() OVER (
    PARTITION BY 그룹기준컬럼
    ORDER BY 정렬기준컬럼 [ASC|DESC]
    ROWS BETWEEN 시작 AND 끝
)

순위 함수 비교 — ROW_NUMBER, RANK, DENSE_RANK, NTILE

네 가지 순위 함수는 동점 처리 방식에서 차이가 납니다. 어떤 함수를 선택하느냐에 따라 비즈니스 로직이 달라지므로 구분을 명확히 해야 합니다.

함수	동점 처리	다음 순위	주요 용도
ROW_NUMBER()	동점도 고유 번호 부여	연속	페이지네이션, 중복 제거
RANK()	동점은 동일 순위	건너뜀(1,2,2,4)	스포츠 순위표
DENSE_RANK()	동점은 동일 순위	연속(1,2,2,3)	등급/티어 계산
NTILE(n)	n등분한 분위수 번호	-	사분위수, 상위 25% 추출

동점(같은 price) 상품이 있을 때 각 함수의 결과:

product_name	price	ROW_NUMBER	RANK	DENSE_RANK	NTILE(4)
상품A	50000	1	1	1	1
상품B	40000	2	2	2	1
상품C	40000	3	2	2	2
상품D	30000	4	4	3	2
상품E	20000	5	5	4	3

SQL

SELECT
    product_name,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num,
    RANK()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dense_rank,
    NTILE(4)     OVER (PARTITION BY category_id ORDER BY price DESC) AS quartile
FROM products;

순위 함수는 OVER() 안에 ORDER BY가 없으면 어떤 행이 어떤 순위를 받을지 실행마다 달라집니다. 이 문제는 로컬에서는 항상 같아 보여도 운영 환경이나 병렬 실행 시 달라질 수 있습니다.

SQL

SELECT product_name, RANK() OVER () AS rnk FROM products;

이 쿼리에서 RANK()는 정렬 기준이 없으므로 모든 행에 1을 반환하거나, 실행 환경에 따라 임의 순서로 순위를 매깁니다. 의미 있는 순위 결과를 얻으려면 반드시 ORDER BY를 명시해야 합니다.

SQL

SELECT product_name, RANK() OVER (ORDER BY price DESC) AS rnk FROM products;

또한 ROW_NUMBER()에서 동점 행이 있을 때 ORDER BY 기준이 유니크하지 않으면 동점 행끼리의 번호 배정이 비결정적입니다. 결정적 결과가 필요하면 타이브레이커 컬럼(예: id)을 ORDER BY에 추가하세요.

카테고리별 상위 N개 추출 — ROW_NUMBER 활용

Window 함수 결과는 WHERE 절에서 직접 참조할 수 없으므로 CTE나 서브쿼리로 감싸야 합니다. 아래는 카테고리별 가장 비싼 상위 3개 상품을 추출하는 표준 패턴입니다.

SQL

WITH ranked_products AS (
    SELECT
        product_name,
        category_id,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY price DESC
        ) AS rn
    FROM products
)
SELECT product_name, category_id, price
FROM ranked_products
WHERE rn <= 3;

"정확히 3개"가 필요하면 ROW_NUMBER, "공동 순위 포함"이 필요하면 RANK나 DENSE_RANK를 선택하세요.

집계 Window 함수

SQL

SELECT
    order_id,
    customer_id,
    total_amount,
    AVG(total_amount) OVER ()                              AS overall_avg,
    SUM(total_amount) OVER (PARTITION BY customer_id)      AS customer_total,
    total_amount / SUM(total_amount) OVER () * 100         AS pct_of_total
FROM orders;

💡개념

LAG/LEAD와 누적합 — 실무 분석 쿼리 패턴

전월 대비 매출 증감을 계산해야 합니다. 이걸 SQL로 어떻게 하는지 모르겠습니다. 또 일별 주문 건수의 누적합을 보여주는 그래프가 필요합니다. LAG/LEAD와 누적합 패턴을 알면 전월비교, 이동평균, 누계 같은 분석 쿼리를 애플리케이션 코드 없이 SQL에서 바로 계산할 수 있습니다.

LAG와 LEAD — 이전/다음 행 값 가져오기

LAG(컬럼, offset, 기본값)은 현재 행보다 offset만큼 앞선 행의 값을 반환하고, LEAD는 뒤따르는 행의 값을 반환합니다. 기본값을 생략하면 파티션 경계에서 NULL이 반환됩니다. 월별 매출 증감률 계산이 대표적인 사용 패턴입니다.

SQL

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month)          AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS mom_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100,
        2
    ) AS mom_growth_pct
FROM monthly_sales
ORDER BY month;

누적합(Running Total) — SUM OVER ORDER BY

ORDER BY를 지정하면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 설정되어, 파티션 시작부터 현재 행까지의 누적합이 계산됩니다.

SQL

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('year', order_date)
        ORDER BY order_date
    ) AS ytd_revenue
FROM daily_sales;

이동평균 — ROWS BETWEEN으로 프레임 지정

ROWS BETWEEN은 물리적 행 수 기준으로 프레임을 지정합니다. RANGE BETWEEN은 논리적 값 범위 기준으로, 동일한 ORDER BY 값을 가진 행을 같은 그룹으로 묶습니다. 이동평균에는 예측 가능한 ROWS BETWEEN이 일반적으로 더 적합합니다.

SQL

SELECT
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS moving_avg_30d
FROM daily_sales
ORDER BY sale_date;

FIRST_VALUE와 LAST_VALUE

LAST_VALUE는 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이므로, 파티션의 마지막 값을 올바르게 가져오려면 프레임을 UNBOUNDED FOLLOWING까지 명시적으로 확장해야 합니다.

SQL

SELECT
    product_name,
    category_id,
    price,
    FIRST_VALUE(product_name) OVER (
        PARTITION BY category_id
        ORDER BY price DESC
    ) AS most_expensive_in_category,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category_id
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS least_expensive_in_category
FROM products;

실무 대시보드 쿼리 패턴

SQL

SELECT
    rep_name,
    region,
    monthly_sales,
    RANK() OVER (ORDER BY monthly_sales DESC)                           AS national_rank,
    RANK() OVER (PARTITION BY region ORDER BY monthly_sales DESC)       AS regional_rank,
    ROUND(PERCENT_RANK() OVER (ORDER BY monthly_sales) * 100, 1)       AS percentile,
    ROUND(
        monthly_sales / AVG(monthly_sales) OVER (PARTITION BY region) * 100,
        1
    )                                                                   AS vs_region_avg_pct,
    monthly_sales - LAG(monthly_sales) OVER (
        PARTITION BY rep_name ORDER BY month
    )                                                                   AS mom_change
FROM sales_summary
WHERE month = '2024-03-01';

Window 함수 성능 팁

PARTITION BY와 ORDER BY에 사용되는 컬럼에 복합 인덱스를 만들면 정렬 비용을 줄일 수 있습니다. Window 함수 결과를 WHERE로 필터링할 때는 반드시 서브쿼리나 CTE를 사용해야 합니다. Window 함수는 WHERE 절에서 직접 사용할 수 없으며, EXPLAIN ANALYZE 결과에서 WindowAgg 또는 Sort 노드로 나타납니다.

SQL

WITH ranked AS (
    SELECT *, RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk
    FROM products
)
SELECT * FROM ranked WHERE rnk = 1;

💼

실무 맥락월별 누적 매출 및 부서별 영업 순위 대시보드

현업 패턴

데이터 분석팀이 매월 경영진에게 보고하는 실적 대시보드를 자동화하려 합니다. 요구사항은 다음과 같습니다: (1) 연간 누적 매출 추이, (2) 부서 내 담당자 순위, (3) 전월 대비 증감액.

이 세 가지를 과거에는 별도 쿼리 3개와 애플리케이션 조합으로 처리했지만, Window 함수를 사용하면 단일 쿼리로 해결됩니다.

SQL

WITH monthly_by_rep AS (
    SELECT
        rep_id,
        rep_name,
        department,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount)                    AS monthly_sales
    FROM sales
    WHERE sale_date >= DATE_TRUNC('year', CURRENT_DATE)
    GROUP BY rep_id, rep_name, department, DATE_TRUNC('month', sale_date)
)
SELECT
    month,
    rep_name,
    department,
    monthly_sales,
    SUM(monthly_sales) OVER (
        PARTITION BY rep_id
        ORDER BY month
    )                                                   AS ytd_cumulative,
    RANK() OVER (
        PARTITION BY department, month
        ORDER BY monthly_sales DESC
    )                                                   AS dept_rank,
    monthly_sales - LAG(monthly_sales) OVER (
        PARTITION BY rep_id ORDER BY month
    )                                                   AS mom_delta
FROM monthly_by_rep
ORDER BY month, department, dept_rank;

추가 JOIN이나 애플리케이션 코드 없이 단일 SQL 쿼리가 대시보드에 필요한 모든 수치를 계산합니다.

다음 모듈에서는 대량 데이터를 고성능으로 처리하는 Bulk Insert·Update·Delete 패턴과 데드락 방지 전략을 다룹니다.

[Database] RANK, ROW_NUMBER, LAG, LEAD 윈도우 함수 실무