[SQL] 윈도우 함수(ROW_NUMBER, RANK, LAG, CUME_DIST 등)

📌 윈도우 함수(WINDOW FUNCTION)란?

• 윈도우 함수는 SQL에서 데이터를 분석하고 집계할 때 사용되는 특별한 함수입니다.
• 일반적인 집계 함수(예: SUM, COUNT 등)는 결과를 그룹화하여 단일 값을 반환하는 반면, 윈도우 함수는 그룹화된 데이터의 각 행에 대해 계산을 수행하며, 결과를 새로운 열로 반환합니다.
• 윈도우 함수는 GROUP BY 절을 사용하지 않고도 데이터 집계를 수행할 수 있습니다. 즉, 데이터의 모든 행에 대해 연산을 수행하면서도 각 행의 상세 정보를 유지할 수 있습니다.
• 윈도우 함수를 사용하면 단순한 집계뿐만 아니라, 순위, 이동 평균, 백분율 등 다양한 분석을 수행할 수 있으며, 복잡한 서브쿼리나 자연 조인을 사용하지 않고도 동일한 결과를 얻을 수 있어 쿼리 성능을 향상시킬 수 있습니다.

 

📌 기본 문법

SELECT 
    WINDOW_FUNCTION(ARGUMENTS) -- 윈도우 함수 호출 (ROW_NUMBER(), RANK(), SUM() 등)
    OVER (                     -- OVER 절 시작✨ (윈도우 함수 동작 범위 정의)
        [PARTITION BY 컬럼]        -- 그룹을 나눌 컬럼 지정✨
        [ORDER BY 컬럼]            -- 그룹 내 정렬 기준 설정
        [WINDOWING 절]             -- 윈도우 범위 정의✨
    )                          -- OVER 절 종료
FROM 
    테이블명;                    -- 조회할 테이블 지정

 

📌 WINDOWING 절

개요

분석할 행의 범위를 설정하며, `ROWS`와 `RANGE` 두 가지 키워드를 통해 정의됩니다. 다음은 기본 구문입니다.

ROWS | RANGE BETWEEN <시작점> AND <끝점> 
-- ROWS: 실제 행 수 기준으로 범위 설정✨
-- RANGE: 값의 범위를 기준으로 범위 설정

• `BETWEEN ~ AND` 구문을 사용하여 윈도우 프레임의 시작과 끝을 정의합니다.
  • 시작점 : `UNBOUNDED PRECEDING`, `CURRENT ROW`, 또는 특정 행의 상대적 위치를 기준으로 지정합니다.
  • 끝점 : `UNBOUNDED FOLLOWING`, `CURRENT ROW`, 또는 특정 행의 상대적 위치를 기준으로 지정합니다.

사용 예시

-- BETWEEN ~ AND 사용✨
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 첫 번째 행부터 현재 행까지 지정 (현재 행 포함)
-- UNBOUNDED PRECEDING: 현재 행 기준 모든 이전 행 포함 (프레임 시작)
-- CURRENT ROW: 현재 행 포함 (프레임 끝)

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- 현재 행부터 마지막 행까지 지정 (현재 행 포함)
-- CURRENT ROW: 현재 행 포함 (프레임 시작)
-- UNBOUNDED FOLLOWING: 현재 행 기준 모든 이후 행 포함 (프레임 끝)

ROWS BETWEEN VALUE_EXPR PRECEDING AND CURRENT ROW  -- 현재 행에서 VALUE_EXPR 만큼 이전 행까지 지정 (현재 행 포함)
-- VALUE_EXPR PRECEDING: 현재 행에서 VALUE_EXPR 만큼 이전 행 포함 (프레임 시작)
-- CURRENT ROW: 현재 행 포함 (프레임 끝)

ROWS BETWEEN CURRENT ROW AND VALUE_EXPR FOLLOWING  -- 현재 행에서 VALUE_EXPR 만큼 이후 행까지 지정 (현재 행 포함)
-- CURRENT ROW: 현재 행 포함 (프레임 시작)
-- VALUE_EXPR FOLLOWING: 현재 행에서 VALUE_EXPR 만큼 이후 행 포함 (프레임 끝)

-- BETWEEN ~ AND 미사용✨
ROWS UNBOUNDED PRECEDING          -- 첫 행부터 현재 행까지
ROWS CURRENT ROW                  -- 현재 행만 포함
ROWS VALUE_EXPR PRECEDING         -- 현재 행에서 VALUE_EXPR 만큼 이전의 행까지

 

📌 함수 종류별 정리

1️⃣ 순위 관련

✔️값 : 100, 200, 200, 300 일 때,

• `ROW_NUMBER` : 1, 2, 3, 4 (모든 행에 대해 고유한 순위를 부여합니다.)
• `RANK` : 1, 2, 2, 4 (중복된 값에 대해 같은 순위를 부여하고, 다음 순위는 건너뜁니다.)
• `DENSE_RANK` : 1, 2, 2, 3 (중복된 값에 대해 같은 순위를 부여하지만, 다음 순위는 건너뛰지 않습니다.✨)

2️⃣ 집계 관련

✔️값 : 100, 200, 300 일 때,

• `SUM` : 600 (지정한 열의 합계를 계산합니다.)
• `MAX` : 300 (지정한 열의 최대값을 반환합니다.)
• `MIN` : 100 (지정한 열의 최소값을 반환합니다.)
• `AVG` : 200 (지정한 열의 평균값을 계산합니다.)
• `COUNT` : 3 (행의 개수를 계산합니다.)

3️⃣ 행 순서 관련

✔️값 : 100, 200, 300 일 때,

• `FIRST_VALUE` : 100 (지정한 열의 첫 번째 값을 반환합니다.)
• `LAST_VALUE` : 300 (지정한 열의 마지막 값을 반환합니다.)
• `LAG` : NULL, 100, 200 (지정한 행의 이전 값을 반환합니다.✨)
• `LEAD` : 200, 300, NULL (지정한 행의 다음 값을 반환합니다.✨)

4️⃣ 비율 관련

✔️값 : 100, 200, 300 일 때,

• `PERCENT_RANK` : 0.00(0/2), 0.50, 1.00
  • 비율 순위를 계산하여 각 값의 상대적 위치를 반환합니다.✨
  • 계산 방식 : (현재 순위 - 1) / (전체 행 수 - 1)로 계산되며, 이로 인해 가장 작은 값은 0.00, 가장 큰 값은 1.00을 갖습니다.
• `CUME_DIST` : 0.33(1/3), 0.67, 1.00
  • 누적 분포를 계산하여 각 값보다 작거나 같은 값의 비율을 나타냅니다.✨
  • 계산 방식 : (현재 값보다 작거나 같은 값의 개수) / (전체 행 수)로 계산되며, 이로 인해 가장 큰 값은 항상 1.00을 갖습니다.
• `RATIO_TO_REPORT` : 0.17(100/600), 0.33, 0.50
  • 값의 비율을 전체 합계에 대한 비율로 반환합니다.✨
  • 계산 방식 : (특정 값) / (전체 합계)로 계산되며, 이를 통해 특정 값이 전체에서 차지하는 비율을 알 수 있습니다.

     ※ Oracle에서만 지원됩니다.

• `NTILE` : (n=3일 때)1, 2, 3
  • 행을 n개의 그룹으로 나누어 그룹 번호를 반환합니다.
  • 계산 방식 : 전체 행 수를 n으로 나누어 각 그룹의 범위를 정하며, 각 행은 해당 그룹 번호를 반환받습니다. 예를 들어, n=3이면 전체 데이터가 3개의 그룹으로 나뉘어 각각 1, 2, 3의 그룹 번호가 부여됩니다.