Qué es una función de ventana

1. Qué es

Una función de ventana calcula un agregado sin colapsar las filas.

Ese es el tema entero, y se entiende mejor con el error que lo motiva. Quieres el precio de cada producto y al lado la media de la empresa, para ver quién se sale de la norma:

SELECT product_name, unit_price, avg(unit_price) FROM products;

ERROR:  column "products.product_name" must appear in the GROUP BY clause
        or be used in an aggregate function

El error dice la verdad: no puedes tener las dos cosas. avg() es una función de agregación, y una agregación reduce muchas filas a una. En cuanto la pides, PostgreSQL exige que todo lo demás también se reduzca. Si añades GROUP BY product_name obtienes 77 medias de un solo producto cada una, que es un número inútil.

El problema real es que quieres dos granos a la vez: el detalle (un producto) y el resumen (la empresa entera). GROUP BY solo sabe darte uno.

-- GROUP BY: 8 filas. El detalle desaparece.
SELECT category_id, avg(unit_price) FROM products GROUP BY category_id;   -- 8 filas

-- OVER (): 77 filas. El detalle se conserva y el agregado viaja al lado.
SELECT product_name, unit_price, avg(unit_price) OVER () FROM products;   -- 77 filas

OVER es la palabra que convierte una función de agregación en una función de ventana. Misma avg(), comportamiento distinto:

2. Sintaxis

funcion(argumentos) OVER (
    PARTITION BY ...      -- opcional: en qué grupos se divide
    ORDER BY ...          -- opcional: en qué orden se recorre
    ROWS/RANGE ...        -- opcional: qué trozo de la partición entra
)

Los tres son opcionales. OVER () vacío significa "la ventana es toda la tabla", y con eso ya se resuelve la mitad de los casos reales.

Las otras tres piezas son los tres temas siguientes: partition-by-y-order-by para las dos primeras, el-marco-rows-vs-range para la tercera.

La palabra "ventana" es literal: para cada fila, la función mira por una ventana a un conjunto de filas y calcula algo sobre ellas. Lo que cambia de una función a otra es por dónde mira. Con OVER () mira a todas.

3. Ejemplos sobre Northwind

3.1 El precio contra la media de la empresa

SELECT product_name, unit_price,
       round(avg(unit_price) OVER (), 2)                AS media_global,
       round(unit_price - avg(unit_price) OVER (), 2)   AS diferencia
FROM products ORDER BY unit_price DESC LIMIT 5;

      product_name       | unit_price | media_global | diferencia
-------------------------+------------+--------------+------------
 Côte de Blaye           |     263.50 |        28.83 |     234.67
 Thüringer Rostbratwurst |     123.79 |        28.83 |      94.96
 Mishi Kobe Niku         |      97.00 |        28.83 |      68.17
 Sir Rodney's Marmalade  |      81.00 |        28.83 |      52.17
 Carnarvon Tigers        |      62.50 |        28.83 |      33.67

La columna media_global repite 28,83 en las 77 filas. Eso no es un defecto: es exactamente lo que se pedía. El valor es el mismo porque la ventana es la misma para todas — la tabla entera.

Y con la media al lado, la comparación es una resta. Côte de Blaye está 234,67 por encima de la media: más de nueve veces el precio medio de la casa. Ese producto lleva apareciendo desde el bloque 4 como el valor atípico de Northwind, y esta es la primera consulta que lo dice en una sola línea sin subconsultas.

3.2 El peso de cada parte sobre el total

Aquí está el uso que justifica el tema en cualquier informe: el porcentaje sobre el total.

SELECT c.category_name,
       round(sum(od.unit_price*od.quantity*(1-od.discount)), 2) AS facturacion,
       round(100.0 * sum(od.unit_price*od.quantity*(1-od.discount))
             / sum(sum(od.unit_price*od.quantity*(1-od.discount))) OVER (), 1) AS pct
FROM order_details od
JOIN products p   ON p.product_id = od.product_id
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.category_name ORDER BY facturacion DESC;

 category_name  | facturacion | pct
----------------+-------------+------
 Beverages      |   267868.18 | 21.2
 Dairy Products |   234507.29 | 18.5
 Confections    |   167357.23 | 13.2
 Meat/Poultry   |   163022.36 | 12.9
 Seafood        |   131261.74 | 10.4
 Condiments     |   106047.09 |  8.4
 Produce        |    99984.58 |  7.9
 Grains/Cereals |    95744.59 |  7.6

Fíjate en sum(sum(...)) OVER (). No es una errata. Es lo que más choca del tema y conviene entenderlo ahora:

1. El sum() de dentro es la agregación normal del GROUP BY: la facturación de esa categoría.
2. El sum(...) OVER () de fuera es la ventana, y opera sobre el resultado del GROUP BY, no sobre las filas originales. Suma las 8 facturaciones ya calculadas.

Las funciones de ventana se ejecutan después del GROUP BY. Por eso pueden agregar un agregado. Esto encaja con el orden lógico de orden-logico-de-ejecucion, que ahora se amplía:

FROM → WHERE → GROUP BY → HAVING → VENTANAS → SELECT → DISTINCT → ORDER BY → LIMIT

Sin ventanas, ese porcentaje necesitaría calcular el total en una subconsulta aparte y volver a recorrer las tablas. Con OVER () sale del mismo cálculo.

Lo que dice el resultado: ninguna categoría pasa del 21,2%. Las ocho reparten el negocio de forma bastante plana — no hay una categoría que sea el negocio. Es lo contrario de lo que pasaba con productos y con clientes, donde siempre había un dominante.

4. Dónde encaja en el orden de ejecución

Aquí la tabla de orden-logico-de-ejecucion crece. Las funciones de ventana no aparecían en ella porque tienen un paso propio, y va en un sitio muy concreto:

FROM → WHERE → GROUP BY → HAVING → [ventanas] → SELECT → ORDER BY

Se evalúan después de agrupar y filtrar grupos, y antes de que el SELECT termine de construir la salida. Todo el comportamiento del tema sale de ahí.

Consecuencia inmediata: solo se pueden usar en SELECT y en ORDER BY. En ningún otro sitio:

SELECT product_name, unit_price FROM products
WHERE unit_price > avg(unit_price) OVER ();

ERROR:  window functions are not allowed in WHERE

No es un capricho, se lee directamente del orden de arriba: el WHERE es el paso 2 y las ventanas van después del paso 4. Cuando el WHERE decide, la ventana todavía no existe. Lo mismo con GROUP BY y HAVING, que también corren antes.

El arreglo es siempre el mismo: calcular la ventana en un CTE y filtrar fuera.

WITH t AS (
    SELECT product_name, unit_price, avg(unit_price) OVER () AS media
    FROM products
)
SELECT count(*) FROM t WHERE unit_price > media;   -- 25

25 productos de 77 están por encima de la media de precio: el 32,5%. Que sea menos de la mitad es la firma de una distribución con cola: unos pocos productos caros tiran de la media hacia arriba y dejan a la mayoría por debajo. La media de 28,83 no describe al producto típico.

Este patrón —ventana en un CTE, filtro fuera— es el que resuelve el 90% de los casos donde uno querría poner una ventana en el WHERE. Vale la pena memorizarlo ahora.

4.1 La ventana ve lo que sobrevive al WHERE

SELECT round(avg(unit_price) OVER (), 2) FROM products LIMIT 1;
-- 28.83

SELECT round(avg(unit_price) OVER (), 2) FROM products WHERE NOT discontinued LIMIT 1;
-- 26.94

La ventana no es "toda la tabla": es "todas las filas que llegaron hasta aquí". El WHERE se aplica antes, así que la media cambia. Si el informe dice "media de la empresa", hay que tener claro de qué empresa: con productos descatalogados sale 28,83, sin ellos 26,94.

5. Errores comunes

1. Esperar que OVER () agrupe. No agrupa nada. Devuelve el mismo número repetido en todas las filas. Si querías un resumen por categoría, lo que falta es PARTITION BY, no OVER ().

2. Poner la ventana en el WHERE o el HAVING. El error es explícito. La solución es el CTE.

3. Olvidar OVER y no entender el mensaje. column ... must appear in the GROUP BY clause es el mismo error que sale al escribir mal un GROUP BY. Cuando aparezca, la pregunta es: ¿quiero colapsar las filas o quiero el agregado al lado? Si es lo segundo, falta OVER.

4. Creer que OVER () y GROUP BY dan lo mismo si envuelves. SELECT avg(unit_price) OVER () FROM products devuelve 77 filas idénticas, no una. Para una sola fila hace falta DISTINCT o un LIMIT 1.

5. Suponer que cuesta más que un GROUP BY. Suele ser al revés: OVER () recorre los datos una vez. La alternativa sin ventanas —subconsulta con el total y JOIN de vuelta— los recorre dos.

6. Diferencias con SQL Server

Es de lo que mejor porta entre los dos motores. El estándar SQL:2003 definió las funciones de ventana y ambos lo siguieron de cerca. Casi todo lo de este bloque se copia y pega sin tocar nada.

La excepción práctica es FILTER, que en PostgreSQL se combina con OVER —count(*) FILTER (WHERE x) OVER (...)— y en SQL Server hay que escribirlo como sum(CASE WHEN x THEN 1 ELSE 0 END) OVER (...).

7. Ejercicios

1. Cada producto con su precio, la media global y una columna que diga caro o barato según el lado en que caiga.
2. Cada pedido con su importe total y el importe medio de todos los pedidos, y la diferencia entre ambos.
3. Cuántos pedidos superan el importe medio, y qué porcentaje son del total.
4. Cada categoría con su número de productos y qué porcentaje representa sobre los 77.
5. ¿Por qué esto no devuelve una fila? SELECT avg(unit_price) OVER () FROM products. ¿Cómo se consigue una sola?

8. Pregunta de negocio

Dirección quiere la aportación de cada comercial: pedidos, facturación, qué porcentaje del negocio representa y cuánto se desvía del comercial medio. Todo en una tabla.

Las dos últimas columnas necesitan el total y la media de la propia tabla que estás construyendo.