Window Function (윈도우 함수) 사용하기

윈도우 함수란?

윈도우 함수는 행의 집합을 대상으로 계산하는 함수이다. 행의 집합을 계산한다는 점에서 집계함수와 비슷하지만, 윈도우 함수는 각 행마다 처리 결과가 출력된다. 또한 윈도우 함수는 '윈도우 프레임'을 사용하여 처리하고자 하는 행의 범위를 지정 한다.

 

윈도우 함수 기본 구조

윈도우 함수는 OVER 구문이 필수로 정의되어야 하며, OVER 구문에서 윈도우 함수를 적용 할 데이터셋에 대한 구체적인 정보가 나열된다.

<window function>(argument) OVER (
    [PARTITION BY <partitioning expression>]
    [ORDER BY <sort expression>]
    [{ROWS | RANGE} <window frame specification>]
)

• window function: 윈도우 작업을 수행하는 함수. SUM(), RANK() 등
• argument: 함수에 필요한 인수. 인수가 포함된 함수도 있고, 그렇지 않은 함수도 있음
• PARTITION BY: 윈도우를 분할하는 기준이 되는 열. 윈도우 함수는 개별 파티션을 대상으로 독립적으로 처리됨
  • 여러개의 파티션 표현식이 허용됨
  • 파티션 절을 사용하지 않으면, 입력테이블의 모든 행이 단일 파티션으로 구성됨
• ORDER BY: 파티션 내에서 행이 정렬되는 방식을 정의 함.
  • 대부분의 경우에 선택사항이지만, FIRST_VALUE와 같은 탐색 함수 와 일부 경우에는 필수
• ROWS 또는 RANGE: 윈도우 프레임을 지정하는 키워드
  • ROWS는 행 수에 따라 윈도우 프레임을 설정
  • RANGE는 값의 범위에 따라 윈도우 프레임을 설정
• window frame specification: 윈도우 프레임을 지정하는 구문으로, ROWS 또는 RANGE를 사용하여 윈도우프레임의 시작과 끝을 설정

 

윈도우 함수 사용 예시

ROW_NUMBER()

파티션 별로 정렬 순서에 따라 각 행의 순서를 매기는 함수 (1부터 시작)

SELECT 
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
  employee_id, 
  last_name, 
  salary,
  department_id
FROM 
  employees

employees 테이블에서 부서별로(PARTITION BY) 급여가 높은 순(ORDER BY)으로 각 행에 번호를 부여한다.

FIRST_VALUE()

각 파티션 별로 정렬한 그룹에서 첫번째 값을 반환하는 함수

SELECT 
  department_id, 
  employee_id, 
  name,
  salary,
  FIRST_VALUE(name)
  	OVER (PARTITION BY department_id ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_name
FROM 
  employees;

employees 테이블에서 부서별로 급여로 정렬하여 각 부서에서 가장 급여가 높은 사원의 이름을 반환하도록 한다.

FIRST_VALUE에서는 윈도우 프레임을 지정하지 않으면 오류가 난다. 특정 범위를 제한하지 않고 전체 행을 대상으로 하고 싶을때는 윈도우 프레임을  'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' 로 작성한다.

SUM()

파티션 별 값의 합계를 구하는 함수

SELECT 
  employee_id,
  last_name,
  salary,
  SUM(salary) OVER (
    ORDER BY salary DESC
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_sum
FROM employees;

employees 테이블에서 salary를 기준으로 내림차순으로 정렬한 후, 현재 행을 포함하여 직전 2개의 행까지의 salary 합을 구한다.

'CURRENT ROW' 대신 '2 FOLLOWING'을 사용하면 직전 2개 ~ 직후 2개의 행 범위의 salary 합을 구한다.