Jongya

Jonghyuk Park

Window 함수 (Window Function)

Window 함수

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

Window

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

Window 함수의 기본 구조

1
2
3
4
5
6

윈도우함수(표현식) --------- 1
  OVER ( ------------------ 2
    [PARTITION BY ...] ---- 3
    [ORDER BY ...] -------- 4
    [ROWS | RANGE ...] ---- 5
)
  • 1 윈도우함수 : 윈도우 함수. 윈도우함수는 집계 함수로 구성된다.
  • 1 표현식 : 함수(집계 함수)를 적용할 컬럼 또는 값
  • 2 OVER() : 괄호 안에 나오는 범위(window) 위에서 이 함수를 적용한다. (SELECT의 FROM 같은)
  • 3 PARTITION BY : 윈도우(계산 범위)를 나누는 기준 컬럼
  • 4 ORDER BY : 윈도우(파티션)에서 집계함수를 수행하기 전에 정렬하는 기준
  • 5 ROWS / RANGE : 계산의 범위 (정확히 몇 개의 행까지 계산에 포함할지)

PARTITION BY 계산 범위

1
2
3

OVER(
  PARTITION BY user ...    -- PARTITION BY
)

ORDER BY 계산 전 정렬

1
2
3
4
5

OVER(
  PARTITION BY user
  ORDER BY date    -- ORDER BY
)

ROWS/RANGE 계산의 범위

1
2
3
4
5

OVER(
  PARTITION BY user
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING -- ROWS
)
  • 가장 첫 행에서 현재 행의 다음 행까지
1
2
3
4
5
6

-- ROWS
OVER(
  PARTITION BY user
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- ROWS
)
  • 가장 첫 행에서 현재 행까지
1
2
3
4
5
6

-- ROWS
OVER(
  PARTITION BY user
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING -- ROWS
)
  • 현재 행보다 2줄 전에서 1줄 뒤까지
1
2
3
4
5
6

-- RRANGE
OVER(
  PARTITION BY user
  ORDER BY date
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- RANGE
)
  • ORDER BY 기준값이 같다면, 그 값을 가진 모든 row를 모두 포함해 계산

Window 함수

  • 지정된 범위에 대해 특정한 집계 작업을 한다.
  • 윈도우 함수의 종류는 뒤에서 알아본다.

1
2
3
4
5
6

SUM(PRICE)  -- Window Function  
OVER(
  PARTITION BY user
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
)

Window 함수의 종류

전체 종류

함수명 설명
순위 함수 (Ranking Functions)  
ROW_NUMBER() 각 파티션 내에서 고유한 순차적 정수를 1부터 부여합니다.
RANK() 동일한 값에 대해 같은 순위를 부여하며, 다음 순위는 건너뜁니다.
DENSE_RANK() 동일한 값에 대해 같은 순위를 부여하며, 다음 순위를 건너뛰지 않고 연속적으로 부여합니다.
NTILE(n) 파티션을 지정된 n개의 그룹(버킷)으로 나눕니다.
PERCENT_RANK() 윈도우 내에서 백분율 순위(0과 1 사이)를 계산합니다.
CUME_DIST() 윈도우 내에서 현재 행 값보다 작거나 같은 값들의 누적 분포를 계산합니다.
분석 함수 (Analytic Functions)  
LAG(expr, offset, default) 현재 행을 기준으로 지정된 오프셋만큼 이전 행의 표현식(expr) 값을 반환합니다.
LEAD(expr, offset, default) 현재 행을 기준으로 지정된 오프셋만큼 이후 행의 표현식(expr) 값을 반환합니다.
FIRST_VALUE(expr) 윈도우 프레임 내의 첫 번째 행의 표현식(expr) 값을 반환합니다.
LAST_VALUE(expr) 윈도우 프레임 내의 마지막 행의 표현식(expr) 값을 반환합니다.
NTH_VALUE(expr, n) 윈도우 프레임 내의 n번째 행의 표현식(expr) 값을 반환합니다.
집계 함수 (Aggregate Functions)  
SUM(expr) 윈도우 프레임 내 표현식(expr) 값의 합계를 계산합니다.
AVG(expr) 윈도우 프레임 내 표현식(expr) 값의 평균을 계산합니다.
COUNT(expr) 윈도우 프레임 내 행 또는 널이 아닌 값의 개수를 셉니다.
MAX(expr) 윈도우 프레임 내 표현식(expr) 값 중 최댓값을 반환합니다.
MIN(expr) 윈도우 프레임 내 표현식(expr) 값 중 최솟값을 반환합니다.
VAR_POP(expr) 윈도우 프레임 내 표현식(expr) 값의 모집단 분산을 계산합니다.
STDDEV_POP(expr) 윈도우 프레임 내 표현식(expr) 값의 모집단 표준 편차를 계산합니다.

예제 데이터

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 입사일 기준으로, 바로 전에 입사한 사원의 이름을 가져오세요.
-- (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개의 동일 구간으로 분할 
1
2
3
4
5
6
7
8

-- 부서별로 급여 순위를 매겨 `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) 누적 최대값 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 입사일 기준으로 정렬했을 때, 각 사원의 누적 급여 합계를 구하세요.
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() 누적 분포 비율 
1
2
3
4
5
6
7
8

-- 부서 내 급여의 상대적 위치를 백분율로 표현하라
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 와 비슷하게 지정한 컬럼을 기준으로 그루핑한다.
1
2
3
4
5
6
7
8
9
10
11

-- 사용법
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

  • 파티션(혹은 파티션이 없다면 윈도우) 내에서 계산 순서를 정의
  • 예) 시간순, 금액순 등 정렬 기준
1
2
3
4
5
6
7
8
9
10

-- 사용법
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와 함께 사용되어 계산의 대상 범위를 몇 개의 행으로 할지 정의하는 것
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 사용법
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 사용법
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/

Comments

You May Also Enjoy

Latex 문법

1 minute read

마크다운에서 수학 공식 표현하기