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 agregada , 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 agregada o de una sola fila (escalar). ^[2]

A modo de 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 , salario , avg ( salario ) SOBRE ( PARTICIÓN POR depname ) DE empsalary ;          

Producción:

nombredep | nombreemplo | salario | promedio          
-+-------+--------+----------------------
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 única partición. ^[4] Para esta consulta, el salario promedio informado sería el promedio tomado sobre 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]

nombre_función ([ expresión [, expresión ... ]]) SOBRE nombre_ventana nombre_función ([ expresión [, expresión ... ]]) SOBRE ( definición_ventana ) nombre_función ( * ) SOBRE nombre_ventana nombre_función ( * ) SOBRE ( definición_ventana )       
         
     
       

donde window_definitiontiene sintaxis:

[ nombre_ventana_existente ] [ expresión PARTITION BY [, ... ] ] [ expresión ORDER BY [ operador ASC | DESC | USING ] [ NULLS { FIRST | LAST } ] [, ... ] ] [ cláusula_marco ]  
      
                      
  

frame_clausetiene la sintaxis de una de las siguientes:

{ RANGO | FILAS | GRUPOS } inicio_marco [ exclusión_marco ] { RANGO | FILAS | GRUPOS } ENTRE inicio_marco Y fin_marco [ exclusión_marco ]          
             

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, cada opción 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 en el conjunto de datos de la ventana. ^{[9] [10]}

      NOMBRE |
------------
      Aaron| <-- Precedente (sin límites)
     Andrés|
     Amelia|
      Jaime|
       niña|
     Johnny| <-- 1.ª fila anterior
    Michael| <-- Fila actual
       Nick| <-- 1.ª 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 anterior y una siguiente:

 SELECCIONAR 
LAG ( nombre , 1 ) SOBRE ( ORDENAR POR nombre ) "prev" , nombre , LEAD ( nombre , 1 ) SOBRE ( ORDENAR POR nombre ) "next" DE personas ORDENAR POR nombre    
       
   
    
       
  
   

La consulta de resultado contiene los siguientes valores:

| ANTERIOR | NOMBRE | SIGUIENTE |
|----------|----------|----------|
| (nulo)| Aarón| Andrés|
| Aarón| Andrés| Amelia|
| Andrés| Amelia| Jaime|
| Amelia| 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 introdujeron en el estándar SQL:2003 y su funcionalidad se amplió en especificaciones posteriores. ^[11]

Se agregó soporte para implementaciones de bases de datos particulares de la siguiente manera:

• PostgreSQL - versión 8.4 en 2009. ^[12]
• MySQL - versión 8 en 2018. ^{[13] [14]}
• MariaDB - versión 10.2 en 2016. ^[15]

• Seleccionar (SQL) § Limitar filas de resultados