Vensterfuncties
Van toepassing op: 
Databricks SQL Databricks Runtime
Functies die worden uitgevoerd op een groep rijen, aangeduid als een venster, en een retourwaarde berekenen voor elke rij op basis van de groep rijen. Vensterfuncties zijn handig voor het verwerken van taken, zoals het berekenen van een zwevend gemiddelde, het berekenen van een cumulatieve statistiek of het openen van de waarde van rijen op basis van de relatieve positie van de huidige rij.
Syntaxis
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 ] )
Parameters
functie
De functie werkt in het venster. Verschillende klassen functies ondersteunen verschillende configuraties van vensterspecificaties.
ranking_function
Een van de classificatievensterfuncties.
Als de window_spec een ORDER BY-component moet bevatten, maar geen window_frame-component.
analytic_function
Een van de analysevensterfuncties.
aggregate_function
Een van de statistische functies.
Als de functie is opgegeven, mag geen FILTER-component bevatten.
window_name
Identificeert een benoemde vensterspecificatie die is gedefinieerd door de query.
window_spec
Met deze component wordt gedefinieerd hoe de rijen worden gegroepeerd, gesorteerd binnen de groep en op welke rijen binnen een partitie een functie werkt.
partitie
Een of meer expressies die worden gebruikt om een groep rijen op te geven waarmee het bereik wordt gedefinieerd waarop de functie werkt. Als er geen PARTITION-component is opgegeven, bestaat de partitie uit alle rijen.
order_by
De ORDER BY-component geeft de volgorde van rijen binnen een partitie aan.
window_frame
Met de component windowframe wordt een sliding subset van rijen binnen de partitie opgegeven waarop de statistische functie of analysefunctie werkt.
U kunt SORTEREN OP opgeven als alias voor ORDER BY.
U kunt OOK DISTRIBUTE BY opgeven als alias voor PARTITION BY. U kunt CLUSTER BY gebruiken als alias voor PARTITION BY als er geen ORDER BY is.
Voorbeelden
> 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