[SQLD] 10. 윈도우 함수 (분석 함수)

윈도우 함수 (분석 함수)

GROUP BY처럼 행을 하나로 압축하지 않고, 원본 데이터의 각 행은 그대로 유지하면서 특정 범위(Window)의 데이터를 참조하여 분석 값을 계산하고 새로운 컬럼으로 추가하는 함수.

• 문법: 분석_함수() OVER ( [PARTITION BY 절] [ORDER BY 절] [WINDOWING 절] )
  • PARTITION BY: 분석할 데이터의 그룹(창문)을 나눔. GROUP BY와 유사하나 행을 압축하지 않음.
  • ORDER BY: 파티션 내에서 정렬 순서를 지정함. 순위, 순서 관련 함수에서는 필수적임.
  • WINDOWING: 파티션 내에서 계산할 범위를 더 상세히 지정함 (예: ROWS BETWEEN ...).

---

1. 순위 함수 (Ranking Functions)

파티션 내에서 각 행의 순위를 계산함. 동점자 처리 방식에 따라 종류가 나뉘며, 이는 SQLD 시험의 핵심 포인트임.

함수	동점자 처리 방식	예시 (점수: 100, 90, 90, 80)
RANK()	공동 순위만큼 다음 등수를 건너뜀.	1, 2, 2, 4
DENSE_RANK()	공동 순위여도 다음 등수를 이어서 매김.	1, 2, 2, 3
ROW_NUMBER()	동점자와 관계없이 고유한 순번을 부여함.	1, 2, 3, 4

/*
 * -- 순위 함수 비교 --
 * 사용자별(PARTITION BY)로 게시물을 조회수가 높은 순서(ORDER BY)대로 순위를 매김.
 */
SELECT
    user_id,
    view_count,
    RANK() OVER (PARTITION BY user_id ORDER BY view_count DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY view_count DESC) AS dense_rnk,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY view_count DESC) AS row_num
FROM
    Post;

---

2. 순서 함수 (Sequence Functions)

파티션 내에서 현재 행을 기준으로 이전 또는 다음 행의 값을 가져옴. 시계열 분석 등에 유용함.

• LAG(컬럼, [offset], [default]): 파티션 내에서 현재 행보다 이전(Previous) 행의 값을 가져옴.
  • offset: 몇 칸 이전 행을 가져올지 지정 (기본값 1).
  • default: 가져올 이전 행이 없을 때(예: 첫 번째 행) 반환할 기본값.
• LEAD(컬럼, [offset], [default]): 파티션 내에서 현재 행보다 다음(Next) 행의 값을 가져옴.

/*
 * -- LAG 함수 예시 --
 * 사용자별로 게시물을 작성일 순으로 정렬하고,
 * 현재 게시물의 조회수와 바로 이전 게시물의 조회수를 함께 조회함.
 */
SELECT
    user_id,
    creation_date,
    view_count,
    LAG(view_count, 1, 0) OVER (PARTITION BY user_id ORDER BY creation_date) AS prev_view_count
FROM
    Post;

---

3. 집계 윈도우 함수 (Aggregate Window Functions)

SUM, AVG, COUNT 등 일반 집계 함수를 OVER() 절과 함께 사용하여, 그룹별 요약 값이 아닌 각 행에 대한 분석 값을 계산함.

• WINDOWING 절: 계산할 범위를 상세히 지정하는 역할. (SQLD 고득점 포인트)
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 파티션의 첫 행부터 현재 행까지. 누적 합계(Running Total)를 구할 때 주로 사용됨.
  • ORDER BY와 함께 사용될 때 이 구문을 생략하면, DBMS에 따라 기본값으로 적용됨.

/*
 * -- 누적 합계 계산 --
 * 사용자별로 게시물을 작성일 순으로 정렬하며,
 * 각 시점까지의 누적 조회수를 계산함.
 */
SELECT
    user_id,
    creation_date,
    view_count,
    SUM(view_count) OVER (PARTITION BY user_id ORDER BY creation_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_views
FROM
    Post;

---

4. 시험 문제 유형 및 함정 포인트

• GROUP BY와의 차이점: 윈도우 함수는 결과 행의 수가 줄어들지 않고, 원본 데이터에 분석 컬럼이 추가되는 형태라는 점을 명확히 이해해야 함.
• 순위 함수 3종 비교: RANK, DENSE_RANK, ROW_NUMBER의 동점자 처리 방식 차이는 매 시험마다 출제될 가능성이 높은 핵심 중의 핵심임. 주어진 결과 테이블을 보고 어떤 순위 함수가 사용되었는지 유추할 수 있어야 함.
• PARTITION BY의 역할: PARTITION BY가 없을 경우 전체 데이터를 하나의 파티션으로 간주하여 분석함. PARTITION BY 유무에 따른 결과 차이를 이해해야 함.
• LAG/LEAD 함수: 파라미터(offset, default)의 의미와 ORDER BY에 따른 결과 값의 변화를 정확히 파악해야 함.
• 누적 집계: SUM() OVER (PARTITION BY ... ORDER BY ...) 구문이 누적 값을 계산한다는 것을 이해하고, WINDOWING 절의 기본 개념(ROWS BETWEEN ...)을 알아두어야 함.