9. Window Function

9.1. 윈도우 함수

9.1.1. 윈도우 함수 개요

9.1.2. 윈도우 함수 특징

1. 계산의 다양성 : 평균(AVG), 합계(SUM), 카운트(COUNT) 등 계산을 각 행에 수행할 수 있습니다.

2. 쿼리 성능 향상 : 윈도우 함수는 중간 결과를 캐싱하므로 여러 번의 계산이 필요 없습니다.

3. 코드의 간결성 : 복잡한 쿼리를 더 간결하게 작성할 수 있어 코드의 유지보수가 용이합니다.

4. 분석의 편의성 : 특정 범위의 데이터 분석이 용이하여 시계열, 순위/비율 계산 등을 수행합니다.

9.1.3. 윈도우 함수 구성요소

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER(PARTITION BY column [1|2|,,]
																			 ORDER BY [1|2|,,] WINDOW_frame)
FROM Table1;

• WINDOW_FUNCTION

• OVER

• PARTITION BY

• ORDER BY

• WINDOW_frame

9.2. 구성요소

9.2.1. 윈도우 함수

WINDOW_FUNCTION() OVER( [PARTITION BY 절] [ORDER BY 절] [WINDOW_frame 절] )

• WINDOW_FUNCTION() : 연산을 수행하는 함수로 OVER절 앞에 선언합니다. 예를 들어, 여기서 RANK()라는 순위 함수를 사용할 수 있습니다.

• OVER : 반환할 결과에 대한 행의 속성을 지정합니다.

• PARTITION BY: 함수 적용 범위를 지정합니다.

• ORDER BY: 함수의 데이터 정렬을 지정합니다. DISTINCT가 있는 경우에는 허용되지 않습니다.

• WINDOW_frame : 함수 결과의 행(윈도우 프레임)을 지정합니다. DISTINCT가 있는 경우에는 허용되지 않습니다.

SELECT 
			employee_id
    , salary
    , SUM(salary) OVER() AS total_salary
FROM `HR.employees`;

SELECT
	    employee_id
	  , salary
	  , department_id
	  , ROUND(AVG(salary) OVER(PARTITION BY department_id 
														 ORDER BY department_id DESC)) AS avg_salary_per_department
FROM `HR.employees`
ORDER BY salary DESC;

• 행 분할 : PARTITION BY
PARTITION BY는 데이터를 어떻게 그룹화할지 정의하는데 사용됩니다. PARTITION BY를 통해 데이터를 여러 그룹으로 나누어 각 그룹에 독립적으로 윈도우 함수를 계산할 수 있습니다. 예를 들어, PARTITION BY를 사용하여 고객별로 판매량을 계산하거나 날짜별로 주문을 분석할 수 있습니다.
 
예시
PARTITION BY를 사용하여 부서로 평균 급여를 계산합니다.

SELECT
	    DISTINCT department_id
	  , ROUND(AVG(salary) OVER(PARTITION BY department_id)) AS avg_salary_by_depart
FROM `HR.employees`
ORDER BY department_id;

• 행 정렬 : ORDER BY
ORDER BY는 윈도우 함수가 데이터를 어떤 순서로 처리할지 지정하는 역할을 합니다. ORDER BY를 사용하여 데이터를 정렬하고 원하는 순서대로 윈도우 함수가 작동하게 할 수 있습니다.
 
예시
각 부서 별 급여를 내림차순으로 정렬하고 RANK()함수를 이용해 순위를 매겨 출력합니다.

SELECT
	    department_id
	  , employee_id
	  , salary
	  , RANK() OVER(PARTITION BY department_id 
									ORDER BY salary DESC) AS rank_by_salary_in_department
FROM `HR.employees`
ORDER BY department_id;

• 행 지정 : ROWS, RANGE (frame절)
ROWS 절은 윈도우 함수에서 행의 수를 기준으로 윈도우의 범위를 정의하는 데 사용됩니다. 이 범위는 윈도우 함수가 현재 행 주변의 행을 어떻게 포함할지를 지정하여 윈도우 프레임의 범위가 결정됩니다.
RANGE는 논리적인 값에 의한 범위로 논리적 주소에 의해 행 집합을 지정합니다.
💡
ROW대신 RANGE도 사용될 수 있지만, 동작 방식에 차이가 있습니다. ROW는 특정 행 수를 기준으로 하며, RANGE는 값의 범위를 기준으로 합니다.
 
예시
부서별로 최대 급여를 반환하고, RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 구문을 사용하여 프레임을 전체 파티션에 대해 설정합니다. 결과는 각 부서별 최대 급여입니다.

SELECT
		  DISTINCT department_id
		, MAX(salary) OVER(PARTITION BY department_id 
												RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_salary
FROM `HR.employees`
ORDER BY department_id;

9.2.2. WINDOW_frame

{ [ROWS| RANGE] BETWEEN frame_start AND frame_end }

• ROWS : 행(row) 수준에서 범위를 지정합니다. 물리적인 행의 순서를 기준으로 프레임 범위를 정의합니다.

• RANGE : 값(value)의 범위를 지정합니다. 값의 순서에 따라 범위를 정의해 동일한 값이 여러 행에 걸쳐 있을 때 동일한 값을 가진 모든 행이 같은 범위에 속합니다.

• BETWEEN : 윈도우 프레임의 시작과 끝을 지정하는 데 사용됩니다. 윈도우 프레임의 시작과 끝을 연결해 주는 연산자인 AND와 함께 사용됩니다.

• frame_start : 윈도우 함수가 적용되는 시작 범위를 정의합니다.
• unbounded_preceding : 윈도우함수 적용 범위가 가장 앞 행 부터 시작합니다.
• n preceding : 현재 행 기준 윈도우의 시작 위치를 n만큼 이전 행으로 설정합니다.
• current_row : 윈도우의 시작을 현재 위치하고 있는 행으로 설정합니다.
• n following : 현재 행을 기준으로 현재 행에서 n만큼 이후 행을 시작 위치로 설정합니다.

• frame_end : 윈도우함수가 적용되는 마지막 범위를 정의합니다.
• n preceding : 현재 행 기준 윈도우의 종료 위치를 n만큼 이전 행으로 설정합니다.
• current_row: 윈도우 프레임의 종료 위치를 현재 행으로 설정합니다.
• n following : 윈도우 프레임의 종료 위치를 현재 행에서 n만큼 이후 행으로 설정합니다.
• unbounded_following : 윈도우 프레임 종료 위치를 현재 행 이후 모든 행으로 설정합니다.

SELECT 
			employee_id 
	  , department_id 
		, salary
		,	LAST_VALUE(salary) OVER(employee_window) AS top_salary
FROM `HR.employees`
WINDOW employee_window AS (PARTITION BY department_id 
													 ORDER BY salary 
													 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);

SELECT 
      employee_id
    , department_id
    , salary
    , SUM(salary) OVER(PARTITION BY department_id 
											 ORDER BY department_id 
											 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM `HR.employees`;

SELECT 
			employee_id
		,	first_name 
		,	salary
		,	LAST_VALUE(first_name) OVER(ORDER BY salary)
FROM `HR.employees`;

SELECT 
			employee_id
		,	first_name 
		,	salary
		,	LAST_VALUE(first_name) OVER(ORDER BY salary 
																	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LAST_VALUE_SALARY
FROM `HR.employees`;

9.2.3. 사용자 정의 윈도우 함수

WINDOW [ named_window ][ PARTITION BY 절 ][ ORDER BY 절 ][ window_frame 절 ]

1. 윈도우의 스펙 확장 : 기존 윈도우 함수를 선언한 윈도우 함수에서 정의한 다른 스펙과 함께 사용할 수 있습니다. 즉, 기존 윈도우 함수의 스펙을 새로운 스펙으로 확장할 수 있습니다.

2. 중복 정의 방지 : 기존 윈도우 함수와 선언한 윈도우 함수 내에서 중복된 정의를 가질 수 없습니다. 예를 들어, 기존 윈도우 함수에 ORDER BY 절을 정의하고 새로 정의한 윈도우에 ORDER BY 절을 정의하면 오류가 발생합니다.

3. 절의 순서 : PARTITION BY는 항상 가장 먼저 나와야 하며, 그 뒤에 ORDER BY와 window_frame절이 따라와야 합니다. 기존 윈도우 함수를 추가할 경우, 가장 먼저 처리됩니다.

4. PARTITION BY : 신규 선언된 윈도우 함수 내에서 PARTITION BY와 기존 윈도우 함수를 동시에 사용할 수 없습니다. PARTITION BY가 필요한 경우, 해당 부분을 기존 윈도우 함수에 추가해야 합니다.

5. ORDER BY : ORDER BY절, 하위쿼리, 외부쿼리에서는 기존 윈도우 함수를 사용할 수 없습니다.

SELECT 
			employee_id 
		,	salary
		,	LAST_VALUE(employee_id) OVER(employee_window ORDER BY salary) AS top_salary
FROM `HR.employees`
WINDOW employee_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY salary;

SELECT 
			employee_id 
		,	salary 
		,	LAST_VALUE(employee_id) OVER(employee_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS top_salary
FROM `HR.employees`
WINDOW employee_window AS (ORDER BY salary)
ORDER BY salary;

SELECT
	    employee_id
	  , department_id
	  , salary
	  , SUM(salary) OVER employee_window AS cumulative_salary
FROM `HR.employees`
WINDOW employee_window AS (PARTITION BY department_id 
													 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY department_id;

9.3. WINDOW FUNCTION 분류

9.3.1. 집계함수

• SUM

SELECT 
      DISTINCT SUM(salary) OVER() AS total_salary
FROM `HR.employees`;

SELECT 
      DISTINCT department_id
    , SUM(salary) OVER(PARTITION BY department_id) AS subtotal_salary
FROM `HR.employees`
ORDER BY department_id;

SELECT 
      employee_id
    , department_id
    , salary
    , SUM(salary) OVER(PARTITION BY department_id 
											 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS cumulative_salary
FROM `HR.employees`
ORDER BY department_id;

• AVG

SELECT 
		  DISTINCT department_id
		, AVG(salary) OVER(PARTITION BY department_id 
											 ORDER BY department_id) AS avg_department_salary
FROM `HR.employees`

• MIN

SELECT 
      DISTINCT department_id
    , MIN(salary) OVER(PARTITION BY department_id 
											 ORDER BY department_id) AS min_salary
FROM `HR.employees`;

• MAX

SELECT 
      DISTINCT department_id
    , MAX(salary) OVER(PARTITION BY department_id) AS max_salary
FROM `HR.employees`
ORDER BY department_id;

• COUNT

SELECT 
      DISTINCT department_id
    , COUNT(employee_id) OVER(PARTITION BY department_id) AS count_employees
FROM `HR.employees`
ORDER BY department_id;

9.3.2. 순위함수

• RANK

SELECT 
			column1
		, column2
		, RANK() OVER (PARTITION BY column [1|2]
									 ORDER BY column [1|2] [ASC|DESC],...
) AS alias
FROM Table1 as t
WHERE condition1;

SELECT
      employee_id
    , department_id
    , salary
    , RANK() OVER(PARTITION BY department_id 
								  ORDER BY salary DESC) AS department_salary_rank
FROM `HR.employees`
ORDER BY department_id, department_salary_rank;

• DENSE_RANK

SELECT 
			column1
		, column2
		, DENSE_RANK() OVER(ORDER BY column [1|2] [ASC|DESC],...
) AS alias
FROM Table1
WHERE condition1;

SELECT 
			column1
		, column2
	  , DENSE_RANK() OVER(PARTITION BY column [1|2]
												ORDER BY column1 [ASC|DESC], column2 [ASC|DESC] ...
) AS alias
FROM Table1
WHERE condition1;

SELECT 
      department_id
    , employee_id
    , salary
    , DENSE_RANK() OVER(PARTITION BY department_id 
											  ORDER BY salary DESC) AS dense_rank
FROM `HR.employees`
ORDER BY department_id, dense_rank;

• ROW_NUMBER

ROW_NUMBER() OVER ([PARTITION BY column [1|2|,,], .. ]
							      ORDER BY sort_expression [ASC | DESC],...
)

SELECT 
      department_id
    , employee_id
    , salary
    , ROW_NUMBER() OVER(PARTITION BY department_id 
												ORDER BY salary DESC) AS row_number
FROM `HR.employees`
ORDER BY department_id DESC, row_number ASC;

SELECT 
      department_id
    , employee_id
    , salary
    , RANK() OVER(PARTITION BY department_id 
									ORDER BY salary DESC) AS rank
    , DENSE_RANK() OVER(PARTITION BY department_id 
												ORDER BY salary DESC) AS dense_rank
    , ROW_NUMBER() OVER(PARTITION BY department_id 
												ORDER BY salary DESC) AS row_number
FROM `HR.employees`
ORDER BY department_id, row_number;

9.3.3. 행 순서 함수

• FIRST_VALUE

FIRST_VALUE(column1) OVER(PARTITION BY column [1|2|,,,], ... ]
											    ORDER BY column [1|2|,,,] [ASC | DESC], ...
												  WINDOW_frame
)

SELECT
	    employee_id
    , department_id
    , salary
    , FIRST_VALUE(salary) OVER(PARTITION BY department_id 
															 ORDER BY salary DESC) AS first_value
FROM `HR.employees`
ORDER BY department_id ASC, salary DESC;

• LAST_VALUE

LAST_VALUE(column1) OVER (
		[PARTITION BY partition_expression, ... ]
     ORDER BY sort_expression [ASC | DESC], ...
    [WINDOW_frame]
)

SELECT
      department_id
    , salary
    , LAST_VALUE(salary) OVER(PARTITION BY department_id
                              ORDER BY salary DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                              ) AS last_value
FROM `HR.employees`
ORDER BY department_id DESC, salary DESC;

• LAG

LAG(column [1|2|,,,], offset, default_value) OVER(
    [PARTITION BY column [1|2|,,], ... ]
     ORDER BY column [1|2|,,] [ASC | DESC], ...
)

SELECT
		  first_name
    , department_id
    , salary
    , LAG(salary, 4) OVER(PARTITION BY department_id 
													ORDER BY salary DESC) AS prev_salary
FROM `HR.employees`
ORDER BY department_id, salary DESC;

• LEAD

LEAD(column1, offset, default_num) OVER([PARTITION BY column [1|2], ... ]
																				 ORDER BY column [1|2] [ASC | DESC], ...
)

SELECT
			CONCAT(first_name, ' ', last_name) AS full_name
    , department_id
    , salary
    , LEAD(salary, 2) OVER(PARTITION BY department_id
													 ORDER BY salary DESC) AS next_salary 
FROM `HR.employees`
ORDER BY department_id, salary DESC;

9.3.4. 비율 함수

• PERCENT_RANK

SELECT
		  employee_id
		, first_name || ' ' || last_name AS full_name
	  , salary
	  , PERCENT_RANK() OVER(ORDER BY salary) AS percent_rank
FROM `HR.employees`
ORDER BY salary;

• CUME_DIST

SELECT
      department_id
    , employee_id
    , salary
    , CUME_DIST() OVER(PARTITION BY department_id 
											 ORDER BY salary) AS salary_percent_rank
FROM `HR.employees`;

SELECT
	    employee_id
	  , first_name
	  , last_name
    , hire_date
	  , CUME_DIST() OVER(ORDER BY hire_date) AS cumulative_distribution
FROM `HR.employees`;

• PERCENTILE_DISC

SELECT
	    PERCENTILE_DISC(commission_pct,0) OVER() AS min_com
	  , PERCENTILE_DISC(commission_pct,0.5) OVER() AS median_com
	  , PERCENTILE_DISC(commission_pct,1) OVER() AS max_com
FROM `HR.employees`
LIMIT 1;

SELECT
	    PERCENTILE_DISC(commission_pct,0 RESPECT NULLS) OVER() AS min_com
	  , PERCENTILE_DISC(commission_pct,0.5 RESPECT NULLS) OVER() AS median_com
	  , PERCENTILE_DISC(commission_pct,1 RESPECT NULLS) OVER() AS max_com
FROM `HR.employees` 
LIMIT 1;

• NTILE

SELECT
      department_id
    , employee_id
    , salary
    , NTILE(4) OVER(ORDER BY salary) AS salary_quartile
FROM `HR.employees`;

SELECT
	    employee_id
	  , first_name
	  , last_name
	  , hire_date
    , NTILE(5) OVER(ORDER BY hire_date) AS experience_group
FROM  `HR.employees`;