ウィンドウ関数
適用対象:
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 ] )
パラメーター
function
ウィンドウに対して作用する関数。 関数のクラスによって、サポートするウィンドウ仕様の構成が異なります。
ranking_function
いずれかの順位付けウィンドウ関数。
指定した場合、window_spec には、window_frame 句ではなく ORDER BY 句を含める必要があります。
analytic_function
いずれかの分析ウィンドウ関数。
aggregate_function
いずれかの集計関数。
指定した場合、関数に FILTER 句を含めることはできません。
window_name
window_spec
どのように行がグループ化され、どのようにグループ内で並べ替えられるか、また、パーティション内のどの行に対して関数が作用するかが、この句によって定義されます。
partition
関数の作用する範囲を定義する行のグループを指定するための 1 つまたは複数の式。 PARTITION 句が指定されていない場合、パーティションは、すべての行で構成されます。
order_by
ORDER BY 句は、パーティション内の行の順序を指定します。
window_frame
window frame 句は、パーティション内で、集計または分析関数が作用する行のスライディング サブセットを指定します。
ORDER BY の別名として SORT BY を指定できます。
PARTITION BY の別名として DISTRIBUTE BY を指定することもできます。 ORDER BY がない場合は、PARTITION BY の別名として CLUSTER 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