Funciones de ventana
Se aplica a:
Databricks SQL Databricks Runtime
Funciones que operan sobre un grupo de filas, a las que se hace referencia como ventana, y calculan un valor devuelto para cada fila en función del grupo de filas. Las funciones de ventana son útiles para procesar tareas, como calcular una media acumulada, calcular una estadística acumulativa o acceder al valor de las filas dada la posición relativa de la fila actual.
Sintaxis
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 ] )
Parámetros
function
Función que opera en la ventana. Las diferentes clases de funciones admiten diferentes configuraciones de especificaciones de ventana.
ranking_function
Cualquiera de las funciones de ventana de clasificación.
Si se especifica window_spec, se debe incluir una cláusula ORDER BY, pero no una cláusula window_frame.
analytic_function
Cualquiera de las funciones de ventana analíticas.
aggregate_function
Cualquiera de las funciones de agregado.
Si se especifica, la función no debe incluir una cláusula FILTER.
window_name
Identifica una especificación de ventana con nombre definida por la consulta.
window_spec
Esta cláusula define cómo se agruparán las filas, cómo se ordenarán dentro del grupo y sobre qué filas de una partición operará una función.
partición
Una o varias expresiones usadas para especificar un grupo de filas que definen el ámbito en el que opera la función. Si no se especifica ninguna cláusula PARTITION, la partición se compone de todas las filas.
order_by
La cláusula ORDER BY especifica el orden de las filas dentro de una partición.
window_frame
La cláusula window frame especifica un subconjunto deslizante de filas dentro de la partición en la que opera la función analítica o de agregado.
Puede especificar SORT BY como alias para ORDER BY.
También puede especificar DISTRIBUTE BY como alias para PARTITION BY. Puede usar CLUSTER BY como alias de PARTITION BY en ausencia de ORDER BY.
Ejemplos
> 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