Función de ventana (SQL)

En SQL , una función de ventana o función analítica ^{[ 1 ]} 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 de agregación , que devuelve un único 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 una función de agregación o de una sola fila (escalar). ^{[ 2 ]}

Como ejemplo, aquí hay una consulta que utiliza una función de ventana para comparar el salario de cada empleado con el salario promedio de su departamento (ejemplo de la documentación de PostgreSQL ): ^{[ 3 ]}

SELECCIONAR depname , empno , salary , avg ( salary ) SOBRE ( PARTICIÓN POR depname ) DESDE empsalary ;

Producción:

depname | empno | salary | prom ----------+-------+--------+---------------------- desarrollar | 11 | 5200 | 5020.0000000000000000 desarrollar | 7 | 4200 | 5020.0000000000000000 desarrollar | 9 | 4500 | 5020.0000000000000000 desarrollar | 8 | 6000 | 5020.0000000000000000 desarrollar | 10 | 5200 | 5020.0000000000000000 personal | 5 | 3500 | 3700.0000000000000000 personal | 2 | 3900 | 3700.0000000000000000 ventas | 3 | 4800 | 4866.6666666666666667 ventas | 1 | 5000 | 4866.6666666666666667 ventas | 4 | 4800 | 4866.6666666666666667 (10 filas)

La PARTITION BYcláusula agrupa las filas en particiones, y la función se aplica a cada partición por separado. Si PARTITION BYse omite la cláusula (por ejemplo, con 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 reportado sería el promedio tomado de todas las filas.

Las funciones de ventana se evalúan después de la agregación (después de la GROUP BYcláusula y las funciones de agregación que no son de ventana, por ejemplo). ^{[ 1 ]}

Según la documentación de PostgreSQL, una función de ventana tiene la sintaxis de una de las siguientes: ^{[ 4 ]}

function_name ([ expresión [, expresión ... ]]) SOBRE window_name function_name ([ expresión [, expresión ... ]]) SOBRE ( window_definition ) function_name ( * ) SOBRE window_name function_name ( * ) SOBRE ( window_definition )

donde window_definitiontiene sintaxis:

[ nombre_ventana_existente ] [ PARTICIÓN POR expresión [, ... ] ] [ ORDENAR POR expresión [ ASC | DESC | USING operador ] [ NULOS { PRIMERO | ÚLTIMO } ] [, ... ] ] [ cláusula_marco ]

frame_clausetiene la sintaxis de una de las siguientes:

{ RANGO | FILAS | GRUPOS } frame_start [ frame_exclusion ] { RANGO | FILAS | GRUPOS } ENTRE frame_start Y frame_end [ frame_exclusion ]

frame_starty frame_endpuede ser UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, o UNBOUNDED FOLLOWING. frame_exclusionpuede ser EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, o EXCLUDE NO OTHERS.

expressionSe 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, y cada opción está delimitada por una barra vertical |

Las funciones de ventana permiten acceder a los datos de los registros inmediatamente anteriores y posteriores al 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 sobre el conjunto de datos en la ventana. ^{[ 9 ] [ 10 ]}

 NOMBRE | ------------ Aaron| <-- Precedente (sin límites) Amelia| Andrés| James| Jill| Johnny | <-- Primera fila anterior Michael | <-- Fila actual Nick | <-- Primera fila siguiente Ofelia| Zach| <-- Siguiendo (sin límites)

En la tabla anterior, la siguiente consulta extrae para cada fila los valores de una ventana con una fila precedente y una fila siguiente:

SELECCIONAR RETRASO ( nombre , 1 ) SOBRE ( ORDENAR POR nombre ) "anterior" , nombre , ADELANTO ( nombre , 1 ) SOBRE ( ORDENAR POR nombre ) "siguiente" DE personas ORDENAR POR nombre

La consulta resultante contiene los siguientes valores:

| ANTERIOR | NOMBRE | SIGUIENTE | |----------|----------|----------| | (nulo)| Aaron| Amelia| | Aaron | Amelia | Andrew | | Amelia | Andrew | James | Andrew | James | Jill | James | Jill | Johnny | Jill | Johnny | Michael | Johnny | Michael | Nick | | Michael | Nick | Ofelia | Nick | Ofelia | Zach | | Ofelia| Zach| (nulo)|

Las funciones de ventana se incorporaron al estándar SQL:2003 y su funcionalidad se amplió en especificaciones posteriores. ^{[ 11 ]}

Se agregó compatibilidad con implementaciones de bases de datos específicas de la siguiente manera:

• Oracle - versión 8.1.6 en 2000. ^{[ 12 ] [ 13 ]}
• PostgreSQL - versión 8.4 en 2009. ^{[ 14 ]}
• MySQL - versión 8 en 2018. ^{[ 15 ] [ 16 ]}
• MariaDB - versión 10.2 en 2016. ^{[ 17 ]}
• SQLite - versión 3.25.0 en 2018. ^{[ 18 ]}

• Seleccionar (SQL) § Limitar las filas de resultados