Fensterfunktionen
Gilt für:
Databricks SQL Databricks Runtime
Funktionen, die mit einer Gruppe von Zeilen arbeiten, die als Fenster bezeichnet werden, und basierend auf der Zeilengruppe einen Rückgabewert für jede Zeile berechnen. Fensterfunktionen sind nützlich für die Verarbeitung von Aufgaben, wie z. B. das Berechnen eines gleitenden Durchschnitts, das Berechnen einer kumulativen Statistik oder das Zugreifen auf den Wert von Zeilen bei Angabe der relativen Position der aktuellen Zeile.
Syntax
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 ] )
Parameter
Funktion
Die Funktion, die im Fenster ausgeführt wird. Verschiedene Klassen von Funktionen unterstützen verschiedene Konfigurationen von Fensterspezifikationen.
ranking_function
Eine beliebige Rangfolgefensterfunktion.
Wenn angegeben, muss „window_spec“ eine ORDER BY-Klausel enthalten, darf aber keine Fensterrahmenklausel enthalten.
analytic_function
Eine beliebige Analysefensterfunktion.
aggregate_function
Eine beliebige Aggregatfunktion.
Wenn angegeben, darf die Funktion keine FILTER-Klausel enthalten.
window_name
Identifiziert eine Spezifikation für ein benanntes Fenster, die von der Abfrage definiert wird.
window_spec
Diese Klausel definiert, wie die Zeilen gruppiert sowie in der Gruppe sortiert und für welche Zeilen in einer Partition eine Funktion verwendet wird.
partition
Mindestens ein Ausdruck, mit dem eine Gruppe von Zeilen angegeben wird, die den Bereich definieren, auf den die Funktion angewendet wird. Wenn keine PARTITION-Klausel angegeben wird, besteht die Partition aus allen Zeilen.
order_by
Die ORDER BY-Klausel gibt die Reihenfolge der Zeilen innerhalb einer Partition an.
window_frame
Die Fensterrahmenklausel gibt eine gleitende Teilmenge von Zeilen innerhalb der Partition an, auf die die Aggregat- oder Analysefensterfunktion angewendet wird.
Sie können SORT BY als Alias für ORDER BY angeben.
Sie können auch DISTRIBUTE BY als Alias für PARTITION BY angeben. Sie können CLUSTER BY als Alias für PARTITION BY verwenden, wenn ORDER BY nicht angegeben wurde.
Beispiele
> 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