Postgresql 5. Funciones de ventana

A función de ventana realiza un cálculo en un conjunto de filas de la tabla que de alguna manera están relacionadas con la fila actual. Esto es comparable al tipo de cálculo que se puede hacer con una función agregada. Sin embargo, las funciones de ventana no hacen que las filas se agrupen en una sola fila de salida como lo harían las llamadas agregadas que no son de ventana. En cambio, las filas conservan sus identidades separadas. Detrás de escena, la función de ventana puede acceder a más que solo la fila actual del resultado de la consulta.

A continuación, se muestra un ejemplo que muestra cómo comparar el salario de cada empleado con el salario promedio en su departamento:

SELECT depname, empno, salary,avg(salary)OVER(PARTITIONBY depname)FROM empsalary;

  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)

Las primeras tres columnas de salida provienen directamente de la tabla. empsalary, y hay una fila de salida para cada fila de la tabla. La cuarta columna representa un promedio tomado en todas las filas de la tabla que tienen el mismo depname valor como la fila actual. (Esta es en realidad la misma función que la que no es de ventana avg agregado, pero el OVER La cláusula hace que se trate como una función de ventana y se calcule en todo el marco de la ventana).

Una llamada a la función de ventana siempre contiene un OVER cláusula que sigue directamente el nombre y los argumentos de la función de ventana. Esto es lo que lo distingue sintácticamente de una función normal o un agregado sin ventana. los OVER La cláusula determina exactamente cómo se dividen las filas de la consulta para que las procese la función de ventana. los PARTITION BY cláusula dentro OVER divide las filas en grupos, o particiones, que comparten los mismos valores de la PARTITION BY expresión (es). Para cada fila, la función de ventana se calcula en las filas que caen en la misma partición que la fila actual.

También puede controlar el orden en el que las funciones de ventana procesan las filas utilizando ORDER BY dentro de OVER. (La ventana ORDER BY ni siquiera tiene que coincidir con el orden de salida de las filas). Aquí hay un ejemplo:

SELECT depname, empno, salary,
       rank()OVER(PARTITIONBY depname ORDERBY salary DESC)FROM empsalary;

  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

Como se muestra aquí, el rank La función produce un rango numérico para cada ORDER BY valor en la partición de la fila actual, utilizando el orden definido por el ORDER BY cláusula. rank no necesita un parámetro explícito, porque su comportamiento está completamente determinado por el OVER cláusula.

Las filas consideradas por una función de ventana son las del “mesa virtual“ producido por la consulta FROM cláusula filtrada por su WHERE, GROUP BY, y HAVING cláusulas si las hubiera. Por ejemplo, una fila eliminada porque no cumple con el WHERE La condición no es vista por ninguna función de ventana. Una consulta puede contener varias funciones de ventana que dividen los datos de diferentes maneras usando diferentes OVER cláusulas, pero todas actúan sobre la misma colección de filas definidas por esta tabla virtual.

Ya vimos eso ORDER BY se puede omitir si el orden de las filas no es importante. También es posible omitir PARTITION BY, en cuyo caso hay una única partición que contiene todas las filas.

Hay otro concepto importante asociado con las funciones de ventana: para cada fila, hay un conjunto de filas dentro de su partición llamado su marco de la ventana. Algunas funciones de ventana actúan solo en las filas del marco de la ventana, en lugar de en toda la partición. Por defecto, si ORDER BY se suministra, entonces el marco consta de todas las filas desde el inicio de la partición hasta la fila actual, más cualquier fila siguiente que sea igual a la fila actual de acuerdo con el ORDER BY cláusula. Cuando ORDER BY se omite el marco predeterminado consta de todas las filas de la partición. ^[4] Aquí hay un ejemplo usando sum:

SELECT salary,sum(salary)OVER()FROM empsalary;

 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

Arriba, dado que no hay ORDER BY en el OVER cláusula, el marco de la ventana es el mismo que la partición, que por falta de PARTITION BY es toda la mesa; en otras palabras, cada suma se toma sobre toda la tabla y así obtenemos el mismo resultado para cada fila de salida. Pero si agregamos un ORDER BY cláusula, obtenemos resultados muy diferentes:

SELECT salary,sum(salary)OVER(ORDERBY salary)FROM empsalary;

 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

Aquí, la suma se toma desde el primer salario (más bajo) hasta el actual, incluidos los duplicados del actual (observe los resultados de los salarios duplicados).

Las funciones de ventana están permitidas solo en el SELECT lista y el ORDER BY cláusula de la consulta. Están prohibidos en otros lugares, como en GROUP BY, HAVING y WHERE cláusulas. Esto se debe a que se ejecutan lógicamente después del procesamiento de esas cláusulas. Además, las funciones de ventana se ejecutan después de las funciones agregadas que no son de ventana. Esto significa que es válido incluir una llamada de función agregada en los argumentos de una función de ventana, pero no al revés.

Si es necesario filtrar o agrupar filas después de realizar los cálculos de la ventana, puede utilizar una sub-selección. Por ejemplo:

SELECT depname, empno, salary, enroll_date
FROM(SELECT depname, empno, salary, enroll_date,
          rank()OVER(PARTITIONBY depname ORDERBY salary DESC, empno)AS pos
     FROM empsalary
  )AS ss
WHERE pos <3;

La consulta anterior solo muestra las filas de la consulta interna que tienen rank menos de 3.

Cuando una consulta implica varias funciones de ventana, es posible escribir cada una con un OVER cláusula, pero esto es duplicativo y propenso a errores si se desea el mismo comportamiento de ventana para varias funciones. En su lugar, cada comportamiento de ventana se puede nombrar en un WINDOW cláusula y luego referenciada en OVER. Por ejemplo:

SELECTsum(salary)OVER w,avg(salary)OVER w
  FROM empsalary
  WINDOW w AS(PARTITIONBY depname ORDERBY salary DESC);

Se pueden encontrar más detalles sobre las funciones de la ventana en la Sección 4.2.8, Sección 9.22, Sección 7.2.5 y la página de referencia SELECT.

^[4] Hay opciones para definir el marco de la ventana de otras formas, pero este tutorial no las cubre. Consulte la Sección 4.2.8 para obtener más detalles.