Fonctions Windows
S’applique à :
Databricks SQL Databricks Runtime
Fonctions qui opèrent sur un groupe de lignes désigné en tant que fenêtre, et calculent une valeur de retour pour chaque ligne en fonction du groupe de lignes. Des fonctions de fenêtre sont utiles pour traiter des tâches telles que le calcul d’une moyenne mobile, le calcul d’une statistique cumulative ou l’accès à la valeur de lignes en fonction de la position relative de la ligne active.
Syntaxe
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 ] )
Paramètres
function
Fonction opérant sur la fenêtre. Différentes classes de fonctions prennent en charge différentes configurations de spécifications de fenêtre.
ranking_function
N’importe laquelle des fonctions de fenêtre de classement.
En cas de spécification, window_spec doit inclure une clause ORDER BY, mais pas de clause window_frame.
analytic_function
N’importe laquelle des fonctions de fenêtre analytique.
aggregate_function
N’importe laquelle des fonctions d’agrégation.
Si elle est spécifiée, la fonction ne doit pas inclure de clause FILTER.
window_name
Identifie une spécification de fenêtre nommée définie par la requête.
window_spec
Cette clause définit la manière dont les lignes seront regroupées, triées dans le groupe, ainsi que les lignes d’une partition sur laquelle une fonction opère.
partition
Une ou plusieurs expressions utilisées pour spécifier un groupe de lignes définissant l’étendue sur laquelle la fonction opère. Si aucune clause PARTITION n’est spécifiée, la partition est composée de toutes les lignes.
order_by
La clause ORDER BY spécifie l’ordre des lignes dans une partition.
window_frame
La clause de cadre de fenêtre spécifie un sous-ensemble coulissant de lignes dans la partition sur lequel opère la fonction d’agrégation ou analytique.
Vous pouvez spécifier SORT BY en tant qu’alias pour la clause ORDER BY.
Vous pouvez également spécifier DISTRIBUTE BY en tant qu’alias pour la clause PARTITION BY. Vous pouvez utiliser CLUSTER BY comme alias pour PARTITION BY en l’absence de ORDER BY.
Exemples
> 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