[SQL] 윈도우 함수 Window Function

Window 함수 (Window Function)

Window 함수

• 각 행을 기준으로 그 행과 연관된 행의 집합을 가지고 결과를 계산하는 함수
• 즉, 특정 행과 연관된 이웃 행들을 비교하거나, 누적하거나, 순서를 매기는 것

Window

• 여기서 Window 란 특정 행과 연관된 행의 집합 을 지칭한다.
• 마치 데이터 부분 집합을 창문(window) 처럼 본다는 의미를 가지며
• 이 창문은 고정되지 않고 이동성(Sliding) 을 가지고 있다.
• 계산 범위 라고 지칭하면 가장 알맞을 것이다.

Window 함수의 기본 구조

<Window Function>(COLUMN) OVER (
	[PARTITION BY ...]
	[ORDER BY ...]
	[ROWS | RANGE ...]
)

• Window Function : 윈도우 함수
• COLUMN : 함수를 적용할 컬럼
• OVER() : 괄호 안에 나오는 범위(window) 위에서 이 함수를 적용한다. (SELECT의 FROM 같은)
• PARTITION BY : 윈도우(계산 범위)를 나눌 기준 컬럼
• ORDER BY : 파티션 내에서 계산 순서를 정하는 기준
• ROWS / RANGE : 정확히 몇 개의 행까지 계산에 포함할지 제어하는 부분

Window 함수의 종류

예제 데이터

emp_id	emp_name	dept_id	salary	hire_date
1	Alice	10	6000	2021-01-10
2	Bob	10	5500	2020-03-15
3	Carol	20	7000	2019-08-01
4	Dave	20	7200	2022-06-05
5	Erin	10	6200	2023-02-20

• emp_id : 사원 번호
• emp_name : 사원 이름
• dept_id : 부서 아이디
• salary : 급여
• hire_date : 입사일

이전 행 혹은 다음 행 참조

함수	설명
LAG(expr, offset)	이전 행의 값을 참조
LEAD(expr, offset)	다음 행의 값을 참조

-- 입사일 기준으로, 바로 전에 입사한 사원의 이름을 가져오세요.
-- (1) LAG 이용
SELECT
  emp_id
  , emp_name
  , hire_date
  , LAG(emp_name, 1) OVER(ORDER BY hire_date ASC) pre_hired
FROM TEST_EMPLOYEE te

-- (2) LEAD 이용
SELECT
  emp_id
  , emp_name
  , hire_date
  , LEAD(emp_name, 1) OVER(ORDER BY hire_date DESC) pre_hired
FROM TEST_EMPLOYEE te

순위 및 번호 매기기

함수	설명
ROW_NUMBER()	정렬 기준으로 순차 번호를 매김
RANK()	순위 매기기. 동순위 허용하며, 동순위인 경우 순위 건너뜀
DENSE_RANK()	순위 매기기. 동순위 허용하며, 동순위인 경우 건너뛰지 않고 바로 다음 순위로
NTILE(n)	데이터를 n개의 동일 구간으로 분할

-- 부서별로 급여 순위를 매겨 `rank_in_dept` 컬럼으로 출력하세요.
SELECT
  emp_id
  , emp_name
  , dept_id
  , salary
  , RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC)
FROM TEST_EMPLOYEE te

누적 집계

함수	설명
SUM(expr)	누적 합
AVG(expr)	누적 평균
COUNT(expr)	누적 개수
MIN(expr)	누적 최소값
MAX(expr)	누적 최대값

-- 입사일 기준으로 정렬했을 때, 각 사원의 누적 급여 합계를 구하세요.
SELECT
  emp_id
  , emp_name
  , salary
  , hire_date
  , SUM(salary) OVER(ORDER BY hire_date ASC)
FROM TEST_EMPLOYEE te

-- 각 사원의 급여가 부서 평균보다 얼마나 높은지(차이)를 구하세요.
SELECT
  emp_id
  , emp_name
  , dept_id
  , salary
  , AVG(salary) OVER(PARTITION BY dept_id) dept_avg_salary
  , salary - (AVG(salary) OVER(PARTITION BY dept_id)) salary_gap_with_dept_avg
FROM TEST_EMPLOYEE te

비율 계산

함수	설명
PERCENT_RANK()	백분위 순위 (0-1 사이)
CUME_DIST()	누적 분포 비율

-- 부서 내 급여의 상대적 위치를 백분율로 표현하라
SELECT
  emp_id
  , emp_name
  , dept_id
  , salary
  , PERCENT_RANK() OVER(PARTITION BY dept_id ORDER BY salary)
FROM TEST_EMPLOYEE te

OVER() 의 구성 요소

PARTITION BY

• 파티션(데이터 그룹)을 나누는 역할
• 생략하면 전체 데이터가 하나의 파티션으로 간주된다.
• GROUP BY 와 비슷하게 지정한 컬럼을 기준으로 그루핑한다.

-- 사용법
OVER(PARTITION BY USER_ID)

-- 부서별로 급여 순위를 매겨 `rank_in_dept` 컬럼으로 출력하세요.
SELECT
  emp_id
  , emp_name
  , dept_id
  , salary
  , RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC)
FROM TEST_EMPLOYEE te

ORDER BY

• 파티션(혹은 파티션이 없다면 윈도우) 내에서 계산 순서를 정의
• 예) 시간순, 금액순 등 정렬 기준

-- 사용법
OVER(ORDER BY CREATED_AT)

-- 입사일 기준으로, 바로 전에 입사한 사원의 이름을 가져오세요.
SELECT
  emp_id
  , emp_name
  , hire_date
  , LAG(emp_name, 1) OVER(ORDER BY hire_date ASC) pre_hired
FROM TEST_EMPLOYEE te

ROWS / RANGE

• 윈도우 프레임(Window Frame) 지정
• 프레임 : 정확히 몇 개의 행까지 계산에 포함할지
• ORDER BY와 함께 사용되어 계산의 대상 범위를 몇 개의 행으로 할지 정의하는 것

-- 사용법
OVER (
	PARTITION BY USER_ID
	ORDER BY CREATED_AT
	ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
)

-- 입사일 기준 최근 2명 + 현재 사원의 급여 합 구하기
SELECT
  emp_id
  , emp_name
  , hire_date
  , salary
  , SUM(salary) OVER(
                      ORDER BY hire_date
                      ROWS BETWEEN 2 PRECEDING
                      AND CURRENT ROW
                    )
FROM TEST_EMPLOYEE te

-- 사용법
RANGE BETWEEN UNBOUNDED PRECEDING -- 가장 처음 값부터 현재까지
RANGE BETWEEN UNBOUNDED FOLLOWING -- 현재부터 가장 마지막 값까지
RANGE BETWEEN CURRENT ROW         -- 현재 행과 동일한 정렬값을 가진 모든 행
RANGE BETWEEN <value> PRECEDING   -- 현재 값보다 N만큼 작은 값 이상까지 포함
RANGE BETWEEN <value> FOLLOWING   -- 현재 값보다 N만큼 큰 값 이하까지 포함

-- 현재 사원의 급여 이하의 사원 급여 총합 구하기
SELECT
  emp_id
  , emp_name
  , hire_date
  , salary
  , SUM(salary) OVER(
                      ORDER BY salary
                      RANGE BETWEEN UNBOUNDED PRECEDING
                      AND CURRENT ROW
                    )
FROM TEST_EMPLOYEE te

Reference

https://learnsql.com/blog/sql-window-functions-cheat-sheet/