윈도우 함수 개요 및 특징

MySQL 8.0 버전 이상에서 공식적으로 지원되는 윈도우(Window) 함수는 복잡한 데이터 통계 및 분석을 수행할 때 매우 유용한 도구입니다. 기존 그룹화 연산과 달리 테이블의 구조를 변경하거나 별도의 자바쿼리를 작성하지 않고도 행 단위로 데이터를 가공할 수 있습니다.

이 함수들의 핵심 특징은 '그룹화하여 집계하되, 결과 행이 줄어드는 것을 방지한다'는 점입니다. 일반 집계 함수 (SUM, COUNT 등) 는 그룹별로 하나의 행으로 병합하지만, 윈도우 함수는 각 행에 대해 해당 그룹의 계산 결과를 유지하며 반환합니다. 이를 통해 상세 데이터와 집계된 통계를 동시에 조회하는 리포트 작성이 용이해집니다.

기본 구문 구조

윈도우 함수는 OVER 절을 통해 적용 범위를 정의합니다. 이를 인라인 방식으로 직접 작성하거나, WINDOW 키워드를 사용하여 별도 정의할 수 있습니다.

-- 직접 정의 방식 (인라인)
SELECT 
    user_id, 
    category, 
    amount,
    SUM(amount) OVER (PARTITION BY category ORDER BY transaction_date) AS running_total
FROM transaction_log;

-- 이름 부여 방식 (명시적 정의)
SELECT 
    user_id, 
    category, 
    amount,
    SUM(amount) OVER win_scope AS total_by_category
FROM transaction_log
WINDOW win_scope AS (PARTITION BY category);

함수 호출 위치는 주로 SELECT 또는 ORDER BY 절에 배치됩니다. WHERE, GROUP BY, HAVING 절에서는 사용이 불가능한데, 이는 쿼리 실행 순상에서 윈도우 함수가 이들보다 후순위로 처리되기 때문입니다.

주요 함수 유형 및 활용

윈도우 함수는 대략 네 가지 카테고리로 나누어 볼 수 있으며, 각각 다른 분석 목적에 적합합니다. 아래 예시는 거래 내역 (transaction_log) 과 카테고리별 금액 (amount) 을 기준으로 작성되었습니다.

1. 집계 함수 (Aggregate Functions)

SUM, AVG, MAX, MIN, COUNT 와 같은 기존 집계 함수도 윈도우 컨텍스트에서 작동합니다. 행을 병합하지 않고 각 레코드마다 집합값을 채워 넣습니다.

SELECT 
    user_id,
    category,
    amount,
    -- 전체 합계
    SUM(amount) OVER() AS global_sum,
    -- 카테고리별 합계
    SUM(amount) OVER(PARTITION BY category) AS cat_sum,
    -- 카테고리 내에서 누적 합계
    SUM(amount) OVER(PARTITION BY category ORDER BY amount) AS cum_amount
FROM transaction_log;

여기서 ORDER BY 를 추가하면 누적 (Running Total) 계산이 가능해집니다. 동일한 값이 있을 경우 정렬 기준에 따라 누적 시점을 다르게 해석할 수 있으므로 주의해야 합니다.

2. 순위 지정 함수 (Ranking Functions)

데이터셋 내에서의 상대적인 순위를 매길 때 사용합니다. ROW_NUMBER, RANK, DENSE_RANK 은 모두 오름차순 또는 내림차순으로 번호를 매기지만, 중복 처리 방식이 다릅니다.

SELECT 
    category,
    amount,
    ROW_NUMBER() OVER(PARTITION BY category ORDER BY amount DESC) AS row_num,
    RANK() OVER(PARTITION BY category ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER(PARTITION BY category ORDER BY amount DESC) AS dense_rnk
FROM transaction_log
WHERE category = 'Electronics';

• ROW_NUMBER: 중복 여부와 상관없이 연속된 고유 번호를 부여 (1, 2, 3, 4).
• RANK: 동점 시 같은 순위를 주고 다음 번호를 건너뜀 (1, 2, 2, 4).
• DENSE_RANK: 동점 시 같은 순위를 주되 번호 연속성은 유지 (1, 2, 2, 3).

순위 조건에 따른 필터링을 원한다면 서브쿼리를 반드시 거쳐야 합니다. 윈도우 함수로 생성된 필드는 바로 외부 WHERE 절에서 참조할 수 없습니다.

SELECT * FROM (
    SELECT category, amount, 
           RANK() OVER(PARTITION BY category ORDER BY amount DESC) as rn
    FROM transaction_log
) derived_table
WHERE rn <= 3;

3. 오프셋 함수 (Offset Functions)

현재 행의 앞뒤에 있는 특정 거리만큼 떨어진 행의 값을 가져올 때 사용하는 함수입니다. 시계열 데이터나 트렌드 분석에 유용합니다.

SELECT 
    amount,
    -- 이전 행의 값 (기본값 1 행 전)
    LAG(amount, 1) OVER(ORDER BY id) AS prev_val,
    -- 다음 행의 값 (기본값 NULL)
    LEAD(amount, 1) OVER(ORDER BY id) AS next_val,
    -- 다음 2 행 전의 값 (데이터 없을 경우 0 으로 대체)
    LEAD(amount, 2, 0) OVER(ORDER BY id) AS next_two_val
FROM transaction_log
ORDER BY id;

4. 값 추출 함수 (Value Functions)

FIRST_VALUE, LAST_VALUE, NTH_VALUE 는 파티션 내의 특정 위치 값을 추출합니다.

SELECT 
    FIRST_VALUE(amount) OVER(win_def) AS top_val,
    LAST_VALUE(amount) OVER(win_def) AS bottom_val,
    NTH_VALUE(amount, 2) OVER(win_def) AS second_val
FROM transaction_log
WINDOW win_def AS (PARTITION BY category ORDER BY amount DESC);

주의할 점은 기본 프레임 설정이 현재 행까지만 포함한다는 것입니다. 따라서 LAST_VALUE 가 실제 마지막 행이 아닌, 현재까지 탐색한 범위 내의 마지막 값을 반환할 수 있습니다.

프레임 설정 (Frame Specification)

윈도우 함수의 거동을 제어하는 가장 중요한 요소는 프레임 정의입니다. ROWS 와 RANGE 두 가지 옵션을 통해 윈도우의 시작과 끝 지점을 명시할 수 있습니다.

<함수> OVER (
    PARTITION BY <구분 열> 
    ORDER BY <정렬 열>
    [ROWS | RANGE] BETWEEN <시점> AND <종료점>
);

특별히 지정을 하지 않으면 기본적으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 로 동작하여, 현재 행까지 포함하는 구간을 계산 대상으로 삼습니다.

ROWS 와 RANGE 의 차이

두 옵션의 차이는 데이터가 어떻게 선택되는지에 달려있습니다.

• ROWS: 물리적인 행 개수를 기준으로 계산합니다. 정렬된 순서상의 N 번째 행이라는 논리만 따집니다.
• RANGE: 논리적인 값의 범위를 기준으로 합니다. 정렬 열의 값이 같은 모든 행을 하나의 그룹으로 묶어 함께 처리합니다.

따라서 정렬 값이 중복될 경우, RANGE 모드는 중복된 값을 가진 모든 행을 포함하게 되어 누적 합계에 영향을 줄 수 있습니다.

실제로 LAST_VALUE 가 파티션 전체의 최종 값을 가져오게 하려면 프레임의 끝을 UNBOUNDED FOLLOWING 으로 명시해야 합니다.

SELECT 
    category,
    amount,
    -- 전체 범위 지정하여 진짜 마지막 값 확보
    LAST_VALUE(amount) OVER(
        PARTITION BY category 
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS real_last_amount
FROM transaction_log;

비슷하게 COUNT 나 SUM 연산에서도 물리적인 행 기반 계산을 원할 때는 RANGE 대신 ROWS 키워드를 사용하여 프레임 제한을 명확히 하는 것이 안전합니다.