En SQL , una función de ventana o función analítica [1] es una función que usa 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). [2]
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 ): [3]
SELECCIONE depname , empno , salary , avg ( salario ) OVER ( PARTITION BY depname ) FROM empsalary ;
Producción:
depname | empno | salario | promedio ---------- + ------- + -------- + ----------------------desarrollar | 11 | 5200 | 5020.0000000000000000desarrollar | 7 | 4200 | 5020.0000000000000000desarrollar | 9 | 4500 | 5020.0000000000000000desarrollar | 8 | 6000 | 5020.0000000000000000desarrollar | 10 | 5200 | 5020.0000000000000000personal | 5 | 3500 | 3700.0000000000000000personal | 2 | 3900 | 3700.0000000000000000ventas | 3 | 4800 | 4866.6666666666666667ventas | 1 | 5000 | 4866.6666666666666667ventas | 4 | 4800 | 4866.6666666666666667(10 filas)
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. [4] 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). [1]
Sintaxis
Según la documentación de PostgreSQL, una función de ventana tiene la sintaxis de uno de los siguientes: [4]
function_name ([ expresión [, expresión ... ]]) SOBRE window_name function_name ([ expresión [, expresión ... ]]) OVER ( window_definition ) function_name ( * ) SOBRE window_name function_name ( * ) OVER ( window_definition )
donde window_definition
tiene sintaxis:
[ nombre_ventana_existente ] [ PARTICIÓN POR expresión [, ... ] ] [ ORDEN POR expresión [ ASC | DESC | USANDO operador ] [ NULLS { FIRST | ÚLTIMO } ] [, ... ] ] [ frame_clause ]
frame_clause
tiene la sintaxis de uno de los siguientes:
{ RANGO | FILAS | GRUPOS } frame_start [ frame_exclusion ] { RANGE | FILAS | GRUPOS } ENTRE frame_start Y 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 la ventana permiten el acceso a los datos de los registros justo antes y después del registro actual. [5] [6] [7] [8] 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. [9] [10]
NOMBRE |------------ Aaron | <- Precedente (ilimitado) Andrew | Amelia | James | Jill | Johnny | <- 1ra fila anterior Michael | <- Fila actual Nick | <- Primera fila siguiente Ofelia | Zach | <- Siguiendo (ilimitado)
En la tabla anterior, la siguiente consulta extrae para cada fila los valores de una ventana con una fila anterior y una siguiente:
SELECCIONAR LAG ( nombre , 1 ) OVER ( ORDENAR POR nombre ) "anterior" , nombre , LEAD ( nombre , 1 ) OVER ( ORDENAR POR nombre ) "siguiente" DE personas ORDEN POR nombre
La consulta de resultados contiene los siguientes valores:
| ANTERIOR | NOMBRE | SIGUIENTE || ---------- | ---------- | ---------- || (nulo) | Aaron | Andrew || Aaron | Andrew | Amelia || Andrew | Amelia | James || Amelia | James | Jill || James | Jill | Johnny || Jill | Johnny | Michael || Johnny | Michael | Nick || Michael | Nick | Ofelia || Nick | Ofelia | Zach || Ofelia | Zach | (nulo) |
Historia
Las funciones de ventana se introdujeron en SQL: 2003 y su funcionalidad se expandió en especificaciones posteriores. [11]
Ver también
Referencias
- ^ a b "Conceptos de funciones analíticas en SQL estándar | BigQuery" . Google Cloud . Consultado el 23 de marzo de 2021 .
- ^ "Funciones de ventana" . sqlite.org . Consultado el 23 de marzo de 2021 .
- ^ "3.5. Funciones de la ventana" . Documentación de PostgreSQL . 2021-02-11 . Consultado el 23 de marzo de 2021 .
- ^ a b "4.2. Expresiones de valor" . Documentación de PostgreSQL . 2021-02-11 . Consultado el 23 de marzo de 2021 .
- ^ Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas (junio de 2015). "Procesamiento eficiente de funciones de ventana en consultas SQL analíticas". Proc. VLDB Endow . 8 (10): 1058–1069. doi : 10.14778 / 2794367.2794375 . ISSN 2150-8097 .
- ^ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (julio de 2012). "Optimización de funciones de la ventana analítica". Proc. VLDB Endow . 5 (11): 1244-1255. arXiv : 1208.0086 . doi : 10.14778 / 2350229.2350243 . ISSN 2150-8097 .
- ^ "Probablemente la característica más genial de SQL: funciones de ventana" . Java, SQL y jOOQ . 2013-11-03 . Consultado el 26 de septiembre de 2017 .
- ^ "Funciones de ventana en SQL - Simple Talk" . Charla simple . 2013-10-31 . Consultado el 26 de septiembre de 2017 .
- ^ "Introducción a las funciones de la ventana SQL" . Taladro Apache .
- ^ "PostgreSQL: Documentación: Funciones de ventana" . www.postgresql.org . Consultado el 4 de abril de 2020 .
- ^ "Resumen de funciones de ventana" . KnowledgeBase de MariaDB . Consultado el 23 de marzo de 2021 .