SQL: 윈도우 함수 문법 구조

※ SQL: Window Function Syntax Structure.

 

안녕하세요. 듀스트림입니다.

 

또다시 월요일입니다.

 

오늘 포스팅은 SQL을 다룬다면, 필수적으로 이해하고 있어야 하는 윈도우 함수의 문법 구조에 대해 알아보겠습니다.

---

1. 윈도우 함수의 기본 문법 구조

Window-function ( [expression] ) OVER (
      [PARTITION BY ...]
      [ORDER BY ...]
      [ROWS | RANGE ...]
)

 

한 줄로 표현하면 아래와 같은 구조로 되어있습니다.

윈도우 함수 = 함수 + OVER(윈도우 정의)

• 윈도우 함수 자체: SUM(), AVG(), ROW_NUMBER(), LAG(), LEAD() 등
• OVER() 안의 윈도우 정의: 어떤 범위(window)에서 계산할지를 지정

 

ANSI 표준 윈도우 함수 목록

 

• 순위 함수: ROW_NUMBER(), RANK(), DENSE_RANK()
• 이동 함수: LAG(), LEAD()
• 누적/집계: SUM(), COUNT(), AVG(), MIN(), MAX() (윈도우 버전으로 사용 가능)

 

---

2. 구성 요소 설명

2.1 PARTITION BY

• 그룹을 나누는 역할

PARTITION BY user_id

---

2.2 ORDER BY

• 윈도우 내부에서 정렬 기준

ORDER BY created_at

 

---

2.3 ROWS / RANGE

• 정렬된 데이터에서 어디까지를 윈도우 범위로 볼 것인지 지정 (윈도우 프레임 지정 방식)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

---

+ expression

Window-function ( [expression] )

표현식 자리는 일반적인 스칼라 표현식이면 가능하다고 생각하시면 됩니다.

• 컬럼
• 연산식
• 함수 호출
• CASE WHEN
• 타입 변환
• 산술/문자/날짜 연산

 

❗주의점으로는 다른 윈도우 함수나 서브쿼리는 넣을 수 없습니다.

단, SELECT 절의 서브 쿼리 결과 ALIAS를 윈도우 함수가 참조하는 형태는 가능합니다.

 

예를 들면 아래 쿼리들처럼 사용 가능합니다:

-- 서브쿼리 예시 1: 상관 서브쿼리
SELECT
    t1.*,
    (
        SELECT SUM(amount)
        FROM t2
        WHERE t2.user_id = t1.user_id
    ) AS sub_amt,
    SUM(
        (
            SELECT SUM(amount)
            FROM t2
            WHERE t2.user_id = t1.user_id
        )
    ) OVER (PARTITION BY t1.dept_id ORDER BY t1.created_at) AS win_sum_sub_amt
FROM t1;

-- 서브쿼리 예시 2: 한 번만 계산해서 결과를 재사용하는 방식
SELECT
    t1.*,
    sub_amt,
    SUM(sub_amt) OVER (PARTITION BY t1.dept_id ORDER BY t1.created_at) AS win_sum_sub_amt
FROM (
    SELECT
        t1.*,
        (SELECT SUM(amount) FROM t2 WHERE t2.user_id = t1.user_id) AS sub_amt
    FROM t1
) t1;

-- CTE 형식 1: 한 번만 계산해서 결과를 재사용하는 방식
-- 서브쿼리 예시 2와 동일한 구조인데 CTE로 가독성만 향상시킨 버전입니다.
WITH base AS (
    SELECT
        t1.*,
        /** 1.스칼라 서브쿼리로 사용자별 합계를 미리 계산 **/
        (
            SELECT SUM(amount)
            FROM t2
            WHERE t2.user_id = t1.user_id
        ) AS sub_amt
    FROM t1
)
SELECT
    base.*,
    /** 2. CTE에서 만든 sub_amt를 가지고 윈도우 함수 수행 **/
    SUM(sub_amt) OVER (
        PARTITION BY dept_id
        ORDER BY created_at
    ) AS win_sum_sub_amt
FROM base;

-- CTE 형식 2: JOIN + GROUP BY
/**
* (SELECT SUM(amount) FROM t2 WHERE t2.user_id = t1.user_id)를
* 미리 사용자별로 한 번에 계산해서 테이블(agg_t2)로 만들고
* 그 테이블을 t1에 join하는 구조로 데이터량이 많을수록 일반적으로 성능에서 유리한 구조입니다.
**/
WITH agg_t2 AS (
    SELECT
        user_id,
        SUM(amount) AS sub_amt
    FROM t2
    GROUP BY user_id
),
base AS (
    SELECT
        t1.*,
        a.sub_amt
    FROM t1
    LEFT JOIN agg_t2 a
        ON a.user_id = t1.user_id
)
SELECT
    base.*,
    SUM(sub_amt) OVER (
        PARTITION BY dept_id
        ORDER BY created_at
    ) AS win_sum_sub_amt
FROM base;

---

3. 예제

▸ 예제1: 누적 합계

SELECT
    account_id,
    amount,
    SUM(amount) OVER(
        PARTITION BY account_id
        ORDER BY created_at
    ) AS running_total
FROM payments;

---

▸ 예제2: 최근 행 참조 (LEAD)

SELECT
    log_seq,
    created_at,
    LEAD(log_seq) OVER(ORDER BY created_at) AS next_log
FROM log_index;

---

▸ 예제3: 순위 매기기

SELECT
    user_id,
    score,
    RANK() OVER(ORDER BY score DESC) AS rank
FROM game_score;

---

요약하자면,

• 윈도우 함수는 일반 함수가 아니라 "행 집합 위에서 계산"하는 함수
• OVER() 는 필수
• OVER 안 옵션은 선택(PARTITION / ORDER BY / ROWS 등)
• GROUP BY 와 다르게 행을 줄이지 않는다.

---

예제를 이렇게 많이 작성할 생각은 없었는데..

다 작성하고 나면 너무 짧을 때도 있고 예상과 다르게 내용이 너무 길어질 때도 있는 거 같아요.

 

오늘은 여기까지~