Una función de ventana (window function) realiza un cálculo a través de un conjunto de filas de tabla que están de alguna manera relacionadas con la fila actual. Esto es comparable al tipo de cálculo que se puede hacer con una función de agregación. 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 a funciones de agregación normales. En cambio, las filas conservan sus identidades separadas. Tras bastidores, la función de ventana puede acceder a más información además de la fila actual del resultado de la consulta.

Aquí tienes un ejemplo que muestra cómo comparar el salario de cada empleado con el salario promedio de su respectivo 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 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 calculado a través de todas las filas de la tabla que tienen el mismo valor de depname que la fila actual. (Esta es en realidad la misma función de agregación avg de siempre, pero la cláusula OVER hace que se trate como una función de ventana y se calcule a través del marco de ventana).

Una llamada a una función de ventana siempre contiene una cláusula OVER directamente después del nombre de la función de ventana y sus argumentos. Esto es lo que la distingue sintácticamente de una función normal o de una agregación normal. La cláusula OVER determina exactamente cómo se dividen las filas de la consulta para ser procesadas por la función de ventana. La cláusula PARTITION BY dentro de OVER divide las filas en grupos, o particiones, que comparten los mismos valores de las expresiones de PARTITION BY. Para cada fila, la función de ventana se calcula a través de las filas que caen en la misma partición que la fila actual.

También puedes controlar el orden en que se procesan las filas por las funciones de ventana utilizando ORDER BY dentro de OVER. (El ordenamiento ORDER BY de la ventana ni siquiera tiene que coincidir con el orden en que se muestran las filas en la salida de la consulta). Aquí tienes un ejemplo:

SELECT depname, empno, salary,
       row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

  depname  | empno | salary | row_number
-----------+-------+--------+------------
 develop   |     8 |   6000 |          1
 develop   |    10 |   5200 |          2
 develop   |    11 |   5200 |          3
 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 |          3
(10 rows)

Como se muestra aquí, la función de ventana row_number asigna números secuenciales a las filas dentro de cada partición, en el orden definido por la cláusula ORDER BY (con las filas empatadas numeradas en un orden no especificado). row_number no necesita ningún parámetro explícito, porque su comportamiento está completamente determinado por la cláusula OVER.

Las filas consideradas por una función de ventana son aquellas de la “tabla virtual” producida por la cláusula FROM de la consulta según lo filtrado por sus cláusulas WHERE, GROUP BY y HAVING, si las hubiera. Por ejemplo, una fila eliminada porque no cumple con la condición WHERE no es vista por ninguna función de ventana. Una consulta puede contener múltiples funciones de ventana que dividen los datos de diferentes maneras utilizando diferentes cláusulas OVER, pero todas actúan sobre la misma colección de filas definidas por esta tabla virtual.

Ya vimos que se puede omitir ORDER BY si el ordenamiento 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.

Existe 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 ventana (window frame). Algunas funciones de ventana actúan únicamente sobre las filas del marco de ventana, en lugar de toda la partición. Por defecto, si se proporciona ORDER BY, 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 según la cláusula ORDER BY. Cuando se omite ORDER BY, el marco por defecto consta de todas las filas de la partición. ^[5] Aquí tienes 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 un ORDER BY en la cláusula OVER, el marco de ventana es el mismo que la partición, que debido a la falta de PARTITION BY es toda la tabla; en otras palabras, cada suma se realiza sobre toda la tabla y por lo tanto obtenemos el mismo resultado para cada fila de salida. Pero si añadimos una cláusula ORDER BY, 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 calcula desde el primer (más bajo) salario hasta el actual, incluyendo cualquier duplicado del actual (observa los resultados para los salarios duplicados).

Las funciones de ventana están permitidas únicamente en la lista de SELECT y en la cláusula ORDER BY de la consulta. Están prohibidas en otros lugares, como en las cláusulas GROUP BY, HAVING y WHERE. Esto se debe a que lógicamente se ejecutan después del procesamiento de esas cláusulas. Además, las funciones de ventana se ejecutan después de las funciones de agregación que no son de ventana. Esto significa que es válido incluir una llamada a una función de agregación 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 ventana, puedes usar una subconsulta. Por ejemplo:

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
     row_number() OVER (PARTITION BY depname ORDER BY 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 row_number menor que 3 (es decir, las dos primeras filas de cada departamento).

Cuando una consulta involucra múltiples funciones de ventana, es posible escribir cada una con una cláusula OVER separada, pero esto es duplicar trabajo y es propenso a errores si se desea el mismo comportamiento de ventana para varias funciones. En su lugar, cada comportamiento de ventana puede ser nombrado en una cláusula WINDOW y luego ser referenciado 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 pueden encontrar más detalles sobre las funciones de ventana en Section 4.2.8, Section 9.22, Section 7.2.5 y en la página de referencia de SELECT.