poeun 🔅

Original Artwork © Henri Matisse (1869 ~ 1954), Le Cheval L’Ecuyere et le Clown, 1947, 64.6 x 42.5, Centre Georges-Pompidou

개인 저장공간입니다.
Since 2021.10

4 분 소요


윈도우 함수(Window Function)

  • 윈도우 함수는 SQL에서 특정 행 집합(윈도우)에 대해 계산을 수행하는 함수입니다.
  • 일반적인 집계 함수와 달리, 윈도우 함수는 각 행에 대해 계산된 결과를 반환하면서도 원본 행을 유지합니다. 즉, 데이터를 그룹화하지 않고도 그룹 내에서 누적 합계, 순위, 이동 평균 등을 계산할 수 있습니다.

윈도우 함수는 그룹화 없이도 특정 집합 내에서 계산을 수행할 수 있는 강력한 도구입니다.


윈도우 함수의 기본 문법

SELECT column_name,
       window_function() OVER (PARTITION BY column1 ORDER BY column2)
FROM table_name;
  • window_function(): 적용할 윈도우 함수 (예: ROW_NUMBER(), RANK(), SUM() 등)
  • OVER: 윈도우 함수를 적용할 집합을 정의하는 절
  • PARTITION BY: 데이터의 그룹을 정의. (이 부분은 선택 사항으로, 그룹화를 하지 않을 경우 생략할 수 있습니다.)
  • ORDER BY: 각 그룹 내에서 데이터를 정렬하는 기준

주의 사항 : 윈도우 함수는 SELECT 절에서 독립적으로 사용되거나 서브쿼리로 분리되어야 합니다.


주요 윈도우 함수

  • 사용 가능 윈도우 함수
    • 함수: SUM(), AVG(), COUNT(), MIN(), MAX()
    • 순위 함수: ROW_NUMBER(), RANK(), DENSE_RANK()
    • 기타 윈도우 함수: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
  1. ROW_NUMBER():
    • 각 행에 대해 고유한 순번을 부여합니다. 같은 순위가 없는 단독 순번입니다.
    • 예: 각 부서 내에서 직원의 순번을 부여.
  2. RANK():
    • 순위를 부여하되, 동점이 있을 경우 동일한 순위를 부여하고 다음 순위는 건너뜁니다.
    • 예: 매출액 기준으로 순위를 매기는데, 동일 매출액이 있으면 같은 순위를 부여.
  3. DENSE_RANK():
    • RANK()와 유사하지만, 다음 순위를 건너뛰지 않고 연속적인 순위를 부여합니다.
    • 예: 성적 순위에서 동일 점수는 같은 순위를 부여하고, 다음 순위는 건너뛰지 않음.
  4. SUM():
    • 각 행에 대해 누적 합계를 계산합니다.
    • 예: 판매 데이터를 월별로 누적 합계.
  5. AVG():
    • 각 행에 대해 이동 평균을 계산합니다.
    • 예: 최근 3개월 동안의 평균 판매량 계산.
  6. LAG():
    • 현재 행보다 이전 행의 값을 참조합니다.
    • 예: 전월 대비 이번 달의 판매량 차이 계산.
  7. LEAD():
    • 현재 행보다 다음 행의 값을 참조합니다.
    • 예: 다음 월의 예상 판매량과 현재 판매량 비교.


많이 활용되는 스킬 및 예제

1. 각 그룹의 첫 번째 행만 선택하기

# 각 부서에서 최고 연봉을 받는 직원만 선택하고 싶을 때
WITH RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM 
        Employees
)
SELECT * 
FROM RankedData 
WHERE rn = 1;


2. 누적 합계 계산

# 시간 순서대로 누적 판매 금액을 계산하고 싶을 때
SELECT 
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM 
    Orders;


3. 이전 행과의 차이 계산 (LAG() 활용)

# 매일의 판매량 변화를 계산하고 싶을 때
SELECT 
    order_id,
    order_date,
    amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS change_from_last
FROM 
    Orders;


4. 각 그룹 내 순위 계산 (RANK() 활용)

# 부서별로 직원의 연봉 순위를 계산하고 싶을 때
SELECT 
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM 
    Employees;


5. 윈도우 함수와 조건부 집계 (Conditional Aggregation with Window Functions)

  • 윈도우 함수는 조건부 집계와 결합할 수 있습니다. 이를 통해 특정 조건을 만족하는 행만을 집계하는 분석을 수행할 수 있습니다.
  • 예시: 특정 조건에 따른 누적 합계
SELECT 
    player_id,
    event_date,
    SUM(CASE WHEN event_type = 'win' THEN 1 ELSE 0 END) OVER (PARTITION BY player_id ORDER BY event_date) AS cumulative_wins
FROM 
    PlayerEvents;


6. 첫 번째 또는 마지막 값 가져오기 (FIRST_VALUE, LAST_VALUE)

  • 특정 그룹 내에서 첫 번째 또는 마지막 값을 가져올 때 사용합니다.
  • 예시: 부서 내 최초 입사자의 이름 가져오기
# FIRST_VALUE(): 각 부서 내에서 가장 먼저 입사한 직원의 이름을 가져옵니다.
SELECT 
    department,
    employee_name,
    hire_date,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired_employee
FROM 
    Employees;


7. 복잡한 그룹 내 비율 계산 (Ratio Calculation within a Group)

  • 특정 그룹 내에서의 비율이나 퍼센트를 계산할 때 윈도우 함수를 사용할 수 있습니다.
  • 예시: 부서 내 각 직원의 급여가 부서 전체 급여에서 차지하는 비율
SELECT 
    department,
    employee_name,
    salary,
    ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 2) AS salary_percentage
FROM 
    Employees;


LAG(), LEAD() 함수

  • MySQL 8.0부터 지원되는 LAG()와 LEAD() 함수는 데이터 분석에서 매우 유용한 윈도우 함수입니다. 이 두 함수는 현재 행을 기준으로 이전 또는 다음 행의 값을 참조할 수 있게 해줍니다.

1. LAG() 함수: 이전 행 값 가져오기

  • LAG() 함수는 현재 행에서 이전 행의 값을 가져옵니다. 주로 변화 추적이나, 연속된 데이터의 차이를 계산할 때 사용됩니다.
LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)
  • column_name: 참조하려는 열(column)의 이름입니다.
  • offset (옵션): 현재 행에서 몇 행 이전의 값을 참조할지 지정하는 숫자입니다. 기본값은 1입니다.
  • default_value (옵션): 이전 행이 없을 때 반환할 기본값을 지정합니다. 이 값은 주로 데이터 집합의 첫 번째 행처럼 참조할 이전 행이 없는 경우에 사용됩니다.


# 예시
SELECT
    id,
    value,
    LAG(value, 1, 0) OVER (ORDER BY id) AS previous_value
FROM
    your_table;
  • 이 쿼리는 각 행에 대해 이전 행의 value 값을 previous_value로 반환합니다. 첫 번째 행은 이전 행이 없으므로 기본값 0이 반환됩니다.

LAG() 함수에서 PARTITION BY와 ORDER BY를 함께 사용하면, 데이터셋을 특정 그룹으로 나누고 각 그룹 내에서 순서에 따라 이전 행의 값을 참조할 수 있습니다. 이 기능은 주로 그룹별로 데이터의 변화를 추적하거나 비교할 때 유용합니다.

2. LEAD() 함수: 다음 행 값 가져오기

  • LEAD() 함수는 현재 행에서 다음 행의 값을 참조합니다. 이는 미래 데이터를 미리 보여주거나, 현재 행과 다음 행 간의 차이를 계산할 때 유용합니다.
# 예시
SELECT
    id,
    value,
    LEAD(value, 1, 0) OVER (ORDER BY id) AS next_value
FROM
    your_table;
  • 이 쿼리는 각 행에 대해 다음 행의 value 값을 next_value로 반환합니다. 마지막 행은 다음 행이 없으므로 기본값 0이 반환됩니다.


3. 활용 사례: 현재 행에서 다음 행의 차이 계산

  • 두 함수를 활용해 현재 행과 다음 행의 값을 비교할 수 있습니다.
# 예시
SELECT
    id,
    value,
    value - LEAD(value) OVER (ORDER BY id) AS difference_with_next
FROM
    your_table;
  • 이 쿼리는 각 행의 value 값과 다음 행의 value 값의 차이를 계산하여 difference_with_next 열에 표시합니다.


Named Window 함수

  • Named Window 함수는 SQL에서 윈도우 함수의 OVER() 절에 사용하는 윈도우 프레임을 미리 정의하고 재사용할 수 있게 해주는 기능입니다.
  • 이를 통해 코드의 가독성을 높이고, 반복되는 윈도우 정의를 간소화할 수 있습니다.
  • Named Window 정의는 쿼리의 끝부분(order by 다음), WINDOW 절에서 선언합니다.


Named Window 기본 문법

WINDOW window_name AS (PARTITION BY column ORDER BY column)
  • window_name: 윈도우 정의에 붙일 이름.
  • PARTITION BY: 데이터를 특정 그룹으로 나누는 기준.
  • ORDER BY: 그룹 내에서 데이터를 정렬하는 기준.


예시

SELECT 
    employee_id,
    salary,
    ROW_NUMBER() OVER window_def AS row_num,
    RANK() OVER window_def AS rank_num
FROM Employees
WINDOW window_def AS (PARTITION BY department_id ORDER BY salary DESC);
  • window_def: 부서별(department_id)로 급여(salary)를 내림차순으로 정렬하는 윈도우 정의
  • ROW_NUMBER()와 RANK() 함수는 동일한 Named Window 정의를 재사용하여 각 부서별로 순위를 계산


사용 예시: 부서별 상위 3명 직원 조회

WITH RankedEmployees AS (
    SELECT 
        employee_id,
        department_id,
        salary,
        ROW_NUMBER() OVER window_def AS row_num
    FROM Employees
    WINDOW window_def AS (PARTITION BY department_id ORDER BY salary DESC)
)
SELECT * FROM RankedEmployees WHERE row_num <= 3;


댓글남기기

참고