05.-Funciones de ventanas

Una función de ventana realiza un cálculo a través de un conjunto de filas de tablas 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 las ventanas no hacen que las filas se agrupen en una sola fila de salida como las llamadas agregadas no de ventana. En cambio, las filas conservan sus identidades separadas. Detrás de las escenas, la función de la ventana es capaz de acceder a algo más que la fila actual del resultado de la consulta.

Aquí hay 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 (PARTITION BY 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 tres primeras columnas de salida provienen directamente de la tabla empsalary, y hay una fila de salida para cada fila en la tabla. La cuarta columna representa un promedio tomado en todas las filas de tabla que tienen lo mismo depnamevalor como la fila actual. (En realidad es la misma función que la no-ventada avgagregado, pero el OVERcláusula hace que sea tratada como una función de la ventana y calculada a través del marco de la ventana.)

Una llamada de función de ventana siempre contiene un OVERcláusula directamente siguiendo el nombre y los argumentos de la función de la ventana. Esto es lo que lo distingue sintácticamente de una función normal o agregado no de ventana. El OVERla cláusula determina exactamente cómo se dividen las filas de la consulta para su procesamiento por la función de la ventana. El PARTITION BYcláusula dentro OVERdivide las filas en grupos, o particiones, que comparten los mismos valores de la PARTITION BYla expresión (s). Para cada fila, la función de la ventana se calcula a través de las filas que caen en la misma partición que la fila actual.

También puede controlar el orden en el que las filas se procesan por funciones de ventana usando ORDER BYdentro OVER. (La ventana ORDER BYni siquiera tiene que coincidir con el orden en el que las filas son de salida.) He aquí un ejemplo:

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY 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 rankfunción produce un rango numérico para cada distinto ORDER BYvalor en la partición de la fila actual, utilizando el orden definido por el ORDER BYcláusula. rankno necesita un parámetro explícito, porque su comportamiento está totalmente determinado por la OVERcláusula.

Las filas consideradas por una función de ventana son las de la tabla virtual . producida por la consulta's FROMcláusula filtrada por su WHERE, GROUP BY, y HAVINGcláusulas, si las hay. Por ejemplo, una fila eliminada porque no cumple con la WHEREla condición no se ve por ninguna función de ventana. Una consulta puede contener múltiples funciones de ventana que cortan los datos de diferentes maneras usando diferentes OVERcláusulas, pero todas actúan sobre la misma colección de filas definidas por esta tabla virtual.

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

Hay otro concepto importante asociado a las funciones de la ventana: para cada fila, hay un conjunto de filas dentro de su partición llamada marco de la ventana. Algunas funciones de ventana actúan sólo en las filas del marco de la ventana, en lugar de toda la partición. Por defecto, si ORDER BYse suministra entonces el marco consiste en todas las filas desde el inicio de la partición hasta la fila actual, además de cualquier fila siguiente que sean iguales a la fila actual de acuerdo con el ORDER BYcláusula. Cuando ORDER BYse omite el marco predeterminado consiste en todas las filas en la partición. ¹ Aquí hay un ejemplo de uso 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, ya que no hay ORDER BYen el OVERcláusula, el marco de la ventana es el mismo que la partición, que por falta de PARTITION BYes toda la tabla; en otras palabras, cada suma se toma sobre toda la tabla y por lo tanto obtenemos el mismo resultado para cada fila de salida. Pero si añadimos un ORDER BYcláusula, obtenemos resultados muy diferentes:

SELECT salary, sum(salary) OVER (ORDER BY 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 del primer (menor) sueldo hasta el actual, incluyendo cualquier duplicado de la actual (adunice los resultados para los salarios duplicados).

Las funciones de la ventana sólo se permiten en la SELECTlista y la ORDER BYcláusula de la consulta. Están prohibidos en otros lugares, como en GROUP BY, HAVINGy WHEREcláusulas. Esto se debe a que lógicamente se ejecutan después de la tramitación de esas cláusulas. Además, las funciones de la ventana se ejecutan después de funciones de agregados no 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 viceversa.

Si hay una necesidad de filtrar o agrupar filas después de que se realizan los cálculos de la ventana, puede utilizar un subseleccionado. Por ejemplo:

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

La consulta anterior sólo muestra las filas de la consulta interior teniendo rankmenos de 3.

Cuando una consulta implica múltiples funciones de ventana, es posible escribir cada una con una separada OVERcláusula, pero esto es duplicado y propenso a errores si se quiere el mismo comportamiento de escaparate para varias funciones. En su lugar, cada comportamiento de ventanas puede ser nombrado en un WINDOWcláusula y luego referenciada en OVER. Por ejemplo:

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Se verán más detalles sobre las funciones de la ventana más adelante