Función de ventana (SQL) - Window function (SQL)
En SQL , una función de ventana o función analítica es una función que utiliza valores de una o varias filas para devolver un valor para cada fila. (Esto contrasta con una función agregada , que devuelve un solo valor para varias filas). Las funciones de ventana tienen una cláusula OVER; cualquier función sin una cláusula OVER no es una función de ventana, sino más bien una función agregada o de una sola fila (escalar).
Como ejemplo, aquí hay una consulta que usa una función de ventana para comparar a cada empleado con el salario promedio de su departamento (ejemplo de la documentación de PostgreSQL ):
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
Producción:
depname | empno | salary | avg ----------+-------+--------+---------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)
La PARTITION BY cláusula agrupa filas en particiones y la función se aplica a cada partición por separado. Si PARTITION BY se omite la cláusula (por ejemplo, si tenemos una OVER() cláusula vacía ), todo el conjunto de resultados se trata como una sola partición. Para esta consulta, el salario promedio informado sería el promedio tomado en todas las filas.
Las funciones de ventana se evalúan después de la agregación (después de la GROUP BY cláusula y las funciones de agregación que no son de ventana, por ejemplo).
Sintaxis
Según la documentación de PostgreSQL, una función de ventana tiene la sintaxis de uno de los siguientes:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
donde window_definition tiene sintaxis:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause tiene la sintaxis de uno de los siguientes:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start y frame_end puede ser UNBOUNDED PRECEDING , offset PRECEDING , CURRENT ROW , offset FOLLOWING , o UNBOUNDED FOLLOWING . frame_exclusion puede ser EXCLUDE CURRENT ROW , EXCLUDE GROUP , EXCLUDE TIES , o EXCLUDE NO OTHERS .
expression se refiere a cualquier expresión que no contenga una llamada a una función de ventana.
Notación:
- Los corchetes [] indican cláusulas opcionales
- Las llaves {} indican un conjunto de diferentes opciones posibles, con cada opción delimitada por una barra vertical |
Ejemplo
Las funciones de ventana permiten el acceso a los datos de los registros justo antes y después del registro actual. Una función de ventana define un marco o ventana de filas con una longitud determinada alrededor de la fila actual y realiza un cálculo en el conjunto de datos de la ventana.
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
En la tabla anterior, la siguiente consulta extrae para cada fila los valores de una ventana con una fila anterior y una siguiente:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
La consulta de resultados contiene los siguientes valores:
| PREV | NAME | NEXT | |----------|----------|----------| | (null)| Aaron| Andrew| | Aaron| Andrew| Amelia| | Andrew| Amelia| James| | Amelia| James| Jill| | James| Jill| Johnny| | Jill| Johnny| Michael| | Johnny| Michael| Nick| | Michael| Nick| Ophelia| | Nick| Ophelia| Zach| | Ophelia| Zach| (null)|
Historia
Las funciones de ventana se introdujeron en SQL: 2003 y su funcionalidad se expandió en especificaciones posteriores.