視窗函式
適用于：
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
這個子句會定義資料列的分組方式、排序在群組內，以及函式所操作之資料分割內的哪些資料列。
分區
一或多個運算式，用來指定定義函式運作範圍的資料列群組。 如果未指定 PARTITION 子句，則資料分割是由所有資料列所組成。
order_by
ORDER BY 子句會指定資料分割內資料列的順序。
window_frame
視窗框架子句會指定匯總或分析函式運作之資料分割內資料列的滑動子集。
您可以將 SORT BY 指定為 ORDER BY 的別名。
您也可以將 DISTRIBUTE BY 指定為 PARTITION 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