[SQLD] 윈도우 함수(Window Function) - 자주 사용하는 함수 모음

| Window Function

윈도우 함수는 SQL에서 데이터 집합의 특정 '윈도우' 또는 하위 집합에 대해 계산을 수행하는 함수이다. 윈도우 함수는 그룹화와 유사하게 보일 수 있지만, 그룹화 함수가 결과 집합의 행 수를 줄이는 반면 윈도우 함수는 원래의 행 수를 유지한다.

윈도우 함수는 주로 다음과 같은 상황에서 주로 쓰인다.

• 누적 합계 : 각 행에 대한 이전 행들의 누적합계를 계산할 때
• 이동 평균 : 주어진 기간에 대한 평균값을 계산할 때
• 순위 부여 : 결과의 각 행에 순위를 부여할 때

 

 

-  윈도우 함수의 형식

<윈도우 함수>(<열>) OVER (
    PARTITION BY <열>  -- (선택적) 같은 값의 행들을 나누기 위해 사용
    ORDER BY <열>      -- (선택적) 행들을 어떤 순서로 정렬할지 지정
    <윈도우 범위>      -- (선택적) 특정 범위 내에서만 함수를 적용하려면 사용
)

SELECT
    id,
    value,
    SUM(value) OVER (PARTITION BY id ORDER BY date) AS running_total
FROM
    table_name;

 

 

| Window Function의 대표 함수

1. ROW_NUMBER()

• 각 행에 고유한 순서 번호를 부여한다.
• 동일한 값을 가진 행에는 동일한 순위가 부여되지 않는다. -> 값이 같더라도 각 행은 고유하기 때문에 달라야한다.
• ROW_NUMBER() OVER (ORDER BY column_name)

2. RANK()

• 각 행에 순서번호를 부여한다.  D0FFB5
• 동일한 값을 가진 행에는 동일한 순위가 부여되며, 그 다음 순위는 중복된 행의 수를 고려하여 건너뛴다.
• (1) -> (2), (2) -> (4)
• RANK() OVER (ORDER BY column_name)

3. DENSE_RANK()

• RANK()와 유사하지만 순위에 건너뛰기가 없다. DENSE 는 빽빽하다는 뜻이다. 따라서 순서의 건너뛰기 없이 순차적으로 순위를 매긴다 (1) -> (2), (2) -> (3).
• DENSE_RANK() OVER (ORDER BY column_name)

4. NTILE()

• 전체 행을 N개의 그룹으로 나눈다.
• 각 행에 그룹번호를 부여한다.
• NTILE(4) OVER (ORDER BY column_name) – 결과를 4개 그룹으로 나눈다.

5. LEAD()

• 현재행을 기준으로 지정된 수만큼 뒤에 있는 행의 값을 반환한다.(이후 몇 번째 행의 값)
• LEAD(컬럼명) :  바로 다음 값 (= LEAD(컬럼명, 1))
• LEAD(column_name, 1) OVER (ORDER BY column_name)

6. LAG()

• 현재행을 기준으로 지정된 수만큼 앞에 있는 행의 값을 반환한다.(이전 몇 번째 행의 값)
• LEAD(컬럼명) :  바로 다음값 (= LEAD(컬럼명, 1))
• LAG(column_name, 1) OVER (ORDER BY column_name)

7. FIRST_VALUE()

• 지정된 윈도우의 첫번째 행의 값을 반환한다.
• FIRST_VALUE(column_name) OVER (ORDER BY column_name)

8. LAST_VALUE()

• 지정된 윈도우의 마지막 행의 값을 반환한다. 하지만, 기본적인 윈도우 프레임 설정 때문에 종종 오해되는 결과를 반환할 수 있다. 기본적으로 윈도우 프레임 설정은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이다. 이는 현재 행에서 시작하여 그 이전의 모든 행들을 포함하는 윈도우를 의미한다. 따라서  LAST_VALUE()함수를 사용할 때 기본 설정으로 사용하면, 현재 행이 윈도우의 마지막 행이 아니라면 원하는 결과를 얻지 못할 수도 있다. 즉, 기대했던 전체 윈도우의 마지막 행의 값이 아니라 현재 행의 값이 봔환되는 것이다.
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING을 사용하면 현재 행부터 윈도우의 끝까지를 대상으로 한다.
• LAST_VALUE(column_name) OVER (ORDER BY column_name)

SELECT
    column_name,
    LAST_VALUE(column_name) OVER (
        ORDER BY another_column 
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) as last_value
FROM
    table_name;

9. SUM()

• 지정된 윈도우 내의 합계를 계산한다.
• SUM(column_name) OVER (ORDER BY column_name)

10. AVG()

• 지정된 위도우 내의 평균을 계산한다.
• AVG(column_name) OVER (ORDER BY column_name)

 

 

 

-  PARTITION BY

 

윈도우 함수는 각 함수에 대한 목적에 따라 복잡한 분석 잡업을 수행할 수 있게 해준다. 특히 PARTITION BY 절과 함께 사용하면 각 파티션 내에서만 연산을 수행하게 된다.