Funciones de ventana

El problema que resuelven

GROUP BY colapsa muchas filas en una sola por grupo: pierdes el detalle. Las funciones de ventana calculan un valor agregado conservando todas las filas. Cada fila ve una "ventana" de filas relacionadas y obtiene su resultado.

La pieza clave es la cláusula OVER (...):

SELECT
  nombre,
  categoria,
  precio,
  AVG(precio) OVER (PARTITION BY categoria) AS media_categoria
FROM productos;

Aquí cada fila mantiene su nombre y precio, y además muestra la media de su categoría. Con GROUP BY categoria solo tendrías una fila por categoría.

PARTITION BY y ORDER BY dentro de OVER

Dentro del OVER puedes dividir y ordenar la ventana:

• PARTITION BY col → divide las filas en grupos independientes. La función se reinicia en cada grupo. Es como un "GROUP BY que no colapsa".
• ORDER BY col → ordena las filas dentro de la ventana. Es imprescindible para rankings y acumulados, porque definen "el orden" o "lo anterior".

Numerar y rankear

SELECT nombre, categoria, precio,
  ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY precio DESC) AS pos,
  RANK()       OVER (PARTITION BY categoria ORDER BY precio DESC) AS rk
FROM productos;

Acumulados (running totals)

Combinando un agregado con OVER (ORDER BY ...) obtienes un total acumulado: cada fila suma desde la primera hasta ella misma.

SELECT dia, importe,
  SUM(importe) OVER (ORDER BY id) AS acumulado
FROM ventas;

Las funciones de ventana se calculan después de WHERE/GROUP BY, por eso no puedes usarlas en un WHERE. Si necesitas filtrar por su resultado, envuelve la consulta en una subconsulta o en una CTE.

Ejemplos

Ranking de precios dentro de cada categoría

SELECT nombre, categoria, precio,
  RANK() OVER (PARTITION BY categoria ORDER BY precio DESC) AS rk
FROM productos
ORDER BY categoria, rk;

Total acumulado de ventas por orden de registro

SELECT id, dia, importe,
  SUM(importe) OVER (ORDER BY id) AS acumulado
FROM ventas
ORDER BY id;