창 함수
적용 대상:
Databricks SQL 검사 예 Databricks Runtime
창이라고 하는 행 그룹에서 작동하고 행 그룹에 따라 각 행의 반환 값을 계산하는 함수입니다. 창 함수는 이동 평균 계산, 누적 통계 계산 또는 현재 행의 상대 위치가 지정된 행 값에 액세스하는 등의 작업을 처리하는 데 유용합니다.
구문
function OVER { window_name | ( window_name ) | window_spec }
function
{ ranking_function | analytic_function | aggregate_function }
over_clause
OVER { window_name | ( window_name ) | window_spec }
window_spec
( [ PARTITION BY partition [ , ... ] ] [ order_by ] [ window_frame ] )
매개 변수
함수
창에서 작동하는 함수입니다. 다양한 함수 클래스는 창 사양의 다양한 구성을 지원합니다.
ranking_function
지정된 경우 window_spec ORDER BY 절을 포함해야 하지만 window_frame 절은 포함하지 않아야 합니다.
analytic_function
aggregate_function
지정된 경우 함수에 FILTER 절이 포함되어서는 안됩니다.
window_name
window_spec
이 절은 행을 그룹화하고 그룹 내에서 정렬하는 방법과 함수가 작동하는 파티션 내의 행을 정의합니다.
파티션
함수가 작동하는 scope 정의하는 행 그룹을 지정하는 데 사용되는 하나 이상의 식입니다. PARTITION 절이 지정되지 않은 경우 파티션은 모든 행으로 구성됩니다.
order_by
ORDER BY 절은 파티션 내의 행 순서를 지정합니다.
window_frame
창 프레임 절은 집계 또는 분석 함수가 작동하는 파티션 내 행의 슬라이딩 하위 집합을 지정합니다.
SORT BY를 ORDER BY의 별칭으로 지정할 수 있습니다.
PARTITION BY의 별칭으로 DISTRIBUTE BY를 지정할 수도 있습니다. ORDER BY가 없는 경우 CLUSTER BY를 PARTITION BY의 별칭으로 사용할 수 있습니다.
예
> CREATE TABLE employees
(name STRING, dept STRING, salary INT, age INT);
> INSERT INTO employees
VALUES ('Lisa', 'Sales', 10000, 35),
('Evan', 'Sales', 32000, 38),
('Fred', 'Engineering', 21000, 28),
('Alex', 'Sales', 30000, 33),
('Tom', 'Engineering', 23000, 33),
('Jane', 'Marketing', 29000, 28),
('Jeff', 'Marketing', 35000, 38),
('Paul', 'Engineering', 29000, 23),
('Chloe', 'Engineering', 23000, 25);
> SELECT name, dept, salary, age FROM employees;
Chloe Engineering 23000 25
Fred Engineering 21000 28
Paul Engineering 29000 23
Helen Marketing 29000 40
Tom Engineering 23000 33
Jane Marketing 29000 28
Jeff Marketing 35000 38
Evan Sales 32000 38
Lisa Sales 10000 35
Alex Sales 30000 33
> SELECT name,
dept,
RANK() OVER (PARTITION BY dept ORDER BY salary) AS rank
FROM employees;
Lisa Sales 10000 1
Alex Sales 30000 2
Evan Sales 32000 3
Fred Engineering 21000 1
Tom Engineering 23000 2
Chloe Engineering 23000 2
Paul Engineering 29000 4
Helen Marketing 29000 1
Jane Marketing 29000 1
Jeff Marketing 35000 3
> SELECT name,
dept,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dense_rank
FROM employees;
Lisa Sales 10000 1
Alex Sales 30000 2
Evan Sales 32000 3
Fred Engineering 21000 1
Tom Engineering 23000 2
Chloe Engineering 23000 2
Paul Engineering 29000 3
Helen Marketing 29000 1
Jane Marketing 29000 1
Jeff Marketing 35000 2
> SELECT name,
dept,
age,
CUME_DIST() OVER (PARTITION BY dept ORDER BY age
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume_dist
FROM employees;
Alex Sales 33 0.3333333333333333
Lisa Sales 35 0.6666666666666666
Evan Sales 38 1.0
Paul Engineering 23 0.25
Chloe Engineering 25 0.50
Fred Engineering 28 0.75
Tom Engineering 33 1.0
Jane Marketing 28 0.3333333333333333
Jeff Marketing 38 0.6666666666666666
Helen Marketing 40 1.0
> SELECT name,
dept,
salary,
MIN(salary) OVER (PARTITION BY dept ORDER BY salary) AS min
FROM employees;
Lisa Sales 10000 10000
Alex Sales 30000 10000
Evan Sales 32000 10000
Helen Marketing 29000 29000
Jane Marketing 29000 29000
Jeff Marketing 35000 29000
Fred Engineering 21000 21000
Tom Engineering 23000 21000
Chloe Engineering 23000 21000
Paul Engineering 29000 21000
> SELECT name,
salary,
LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag,
LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
FROM employees;
Lisa Sales 10000 NULL 30000
Alex Sales 30000 10000 32000
Evan Sales 32000 30000 0
Fred Engineering 21000 NULL 23000
Chloe Engineering 23000 21000 23000
Tom Engineering 23000 23000 29000
Paul Engineering 29000 23000 0
Helen Marketing 29000 NULL 29000
Jane Marketing 29000 29000 35000
Jeff Marketing 35000 29000 0