Funciones de ventana

¿Cómo sacas "el top 3 de productos por categoría", "las ventas acumuladas día a día" o "cuánto cambió cada pedido respecto al anterior"? Con GROUP BY pierdes el detalle de cada fila. Las funciones de ventana calculan sobre un grupo de filas sin colapsarlas. Son una de las herramientas más poderosas de SQL moderno y aparecen en casi toda entrevista técnica de datos.

Concepto

Imagina una hoja con todas las ventas. GROUP BY es como resumir la hoja en una tabla pequeña de totales: el detalle desaparece. Una función de ventana es como agregar una columna nueva a la hoja original donde, en cada fila, escribes un cálculo que mira a sus "vecinas" (su ventana).

GROUP BY categoria_id                 Función de ventana (PARTITION BY categoria_id)
┌──────────┬──────────┐               ┌─────────────────────┬─────┬────────┬───────────────┐
│ categoria│ promedio │               │ producto            │ cat │ precio │ promedio_cat  │
├──────────┼──────────┤               ├─────────────────────┼─────┼────────┼───────────────┤
│ 1        │ 1882.67  │               │ Audífonos Bluetooth │ 1   │ 899    │ 1882.67       │
│ 2        │ 319.00   │               │ Teclado mecánico    │ 1   │ 1250   │ 1882.67       │
│ ...      │ ...      │               │ Monitor 24 pulgadas │ 1   │ 3499   │ 1882.67       │
└──────────┴──────────┘               │ Libro Aprende SQL   │ 2   │ 349    │ 319.00        │
   (3-4 filas)                        │ ...                 │     │        │               │
                                      └─────────────────────┴─────┴────────┴───────────────┘
                                         (las 8 filas se conservan)

Sintaxis:

funcion() OVER (
  PARTITION BY columna   -- opcional: divide en grupos ("ventanas")
  ORDER BY columna       -- opcional: orden dentro de cada ventana
  ROWS BETWEEN ...       -- opcional: marco de filas
)

Disponibles en PostgreSQL, MySQL 8+, MariaDB 10.2+, SQLite 3.25+ y SQL Server.

Agregados como ventana

Cualquier SUM, AVG, COUNT, MIN, MAX se convierte en función de ventana con OVER:

SELECT
  nombre,
  categoria_id,
  precio,
  ROUND(AVG(precio) OVER (PARTITION BY categoria_id), 2) AS promedio_categoria,
  ROUND(precio - AVG(precio) OVER (PARTITION BY categoria_id), 2) AS diferencia
FROM productos
WHERE categoria_id IS NOT NULL
ORDER BY categoria_id, precio DESC;

nombre	categoria_id	precio	promedio_categoria	diferencia
Monitor 24 pulgadas	1	3499.00	1882.67	1616.33
Teclado mecánico	1	1250.00	1882.67	-632.67
Audífonos Bluetooth	1	899.00	1882.67	-983.67
Libro Aprende SQL	2	349.00	319.00	30.00
Cien años de soledad	2	289.00	319.00	-30.00
Lámpara de escritorio	3	459.00	329.00	130.00
Taza térmica	3	199.00	329.00	-130.00

Con OVER () vacío, la ventana es toda la tabla. Ideal para porcentajes:

-- Participación de cada pedido en las ventas totales
WITH totales AS (
  SELECT pedido_id, SUM(cantidad * precio_unitario) AS total
  FROM detalle_pedido
  GROUP BY pedido_id
)
SELECT
  pedido_id,
  total,
  ROUND(100.0 * total / SUM(total) OVER (), 1) AS porcentaje
FROM totales
ORDER BY total DESC;

pedido_id	total	porcentaje
3	3499.00	38.9
2	1847.00	20.5
1	1597.00	17.8
4	1146.00	12.8
5	899.00	10.0

Funciones de ranking

Función	Empates	Ejemplo con valores 5, 2, 2, 1
ROW_NUMBER()	Numera sin repetir (desempate arbitrario)	1, 2, 3, 4
RANK()	Mismo número, luego salta	1, 2, 2, 4
DENSE_RANK()	Mismo número, sin saltos	1, 2, 2, 3
NTILE(n)	Divide en n grupos iguales	cuartiles, deciles

-- Productos más vendidos (piezas)
WITH vendidos AS (
  SELECT producto_id, SUM(cantidad) AS piezas
  FROM detalle_pedido
  GROUP BY producto_id
)
SELECT
  producto_id,
  piezas,
  ROW_NUMBER() OVER (ORDER BY piezas DESC, producto_id) AS fila,
  RANK()       OVER (ORDER BY piezas DESC)              AS rank,
  DENSE_RANK() OVER (ORDER BY piezas DESC)              AS dense_rank
FROM vendidos
ORDER BY fila;

producto_id	piezas	fila	rank	dense_rank
7	5	1	1	1
1	2	2	2	2
4	2	3	2	2
2	1	4	4	3
3	1	5	4	3
5	1	6	4	3
6	1	7	4	3

Ranking por grupo con PARTITION BY

SELECT
  nombre,
  categoria_id,
  precio,
  RANK() OVER (PARTITION BY categoria_id ORDER BY precio DESC) AS lugar_en_categoria
FROM productos
WHERE categoria_id IS NOT NULL
ORDER BY categoria_id, lugar_en_categoria;

nombre	categoria_id	precio	lugar_en_categoria
Monitor 24 pulgadas	1	3499.00	1
Teclado mecánico	1	1250.00	2
Audífonos Bluetooth	1	899.00	3
Libro Aprende SQL	2	349.00	1
Cien años de soledad	2	289.00	2
Lámpara de escritorio	3	459.00	1
Taza térmica	3	199.00	2

El patrón "top N por grupo"

Uno de los problemas más comunes en el trabajo real. No puedes usar una función de ventana en el WHERE (se calcula después), así que la envuelves en una CTE:

-- Los 2 productos más caros de cada categoría
WITH ranking AS (
  SELECT
    p.nombre,
    c.nombre AS categoria,
    p.precio,
    ROW_NUMBER() OVER (PARTITION BY p.categoria_id ORDER BY p.precio DESC) AS rn
  FROM productos p
  JOIN categorias c ON c.id = p.categoria_id
)
SELECT categoria, nombre, precio
FROM ranking
WHERE rn <= 2
ORDER BY categoria, precio DESC;

categoria	nombre	precio
Electrónica	Monitor 24 pulgadas	3499.00
Electrónica	Teclado mecánico	1250.00
Hogar	Lámpara de escritorio	459.00
Hogar	Taza térmica	199.00
Libros	Libro Aprende SQL	349.00
Libros	Cien años de soledad	289.00

Otro clásico: el último pedido de cada cliente con todos sus datos:

WITH ordenados AS (
  SELECT pe.*,
         ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY fecha DESC) AS rn
  FROM pedidos pe
)
SELECT id, cliente_id, fecha, estado
FROM ordenados
WHERE rn = 1
ORDER BY cliente_id;

id	cliente_id	fecha	estado
3	1	2024-06-01	enviado
2	2	2024-05-10	entregado
4	3	2024-06-15	pendiente
5	4	2024-06-20	cancelado

Acumulados: SUM con ORDER BY

Si agregas ORDER BY dentro de OVER, el SUM se vuelve acumulado (suma desde la primera fila hasta la actual):

WITH totales AS (
  SELECT pe.id, pe.fecha, SUM(d.cantidad * d.precio_unitario) AS total
  FROM pedidos pe
  JOIN detalle_pedido d ON d.pedido_id = pe.id
  GROUP BY pe.id, pe.fecha
)
SELECT
  id,
  fecha,
  total,
  SUM(total) OVER (ORDER BY fecha) AS acumulado
FROM totales
ORDER BY fecha;

id	fecha	total	acumulado
1	2024-05-02	1597.00	1597.00
2	2024-05-10	1847.00	3444.00
3	2024-06-01	3499.00	6943.00
4	2024-06-15	1146.00	8089.00
5	2024-06-20	899.00	8988.00

Perfecto para gráficas de "ventas acumuladas del mes" o el saldo de una cuenta.

LAG y LEAD: mirar la fila anterior o siguiente

Función	Devuelve
LAG(col, n, defecto)	El valor de n filas antes (por defecto 1)
LEAD(col, n, defecto)	El valor de n filas después
FIRST_VALUE(col)	El primer valor de la ventana
LAST_VALUE(col)	El último (¡cuidado con el marco!)

WITH totales AS (
  SELECT pe.id, pe.fecha, SUM(d.cantidad * d.precio_unitario) AS total
  FROM pedidos pe
  JOIN detalle_pedido d ON d.pedido_id = pe.id
  GROUP BY pe.id, pe.fecha
)
SELECT
  id,
  fecha,
  total,
  LAG(total) OVER (ORDER BY fecha)          AS total_anterior,
  total - LAG(total) OVER (ORDER BY fecha)  AS cambio
FROM totales
ORDER BY fecha;

id	fecha	total	total_anterior	cambio
1	2024-05-02	1597.00	NULL	NULL
2	2024-05-10	1847.00	1597.00	250.00
3	2024-06-01	3499.00	1847.00	1652.00
4	2024-06-15	1146.00	3499.00	-2353.00
5	2024-06-20	899.00	1146.00	-247.00

Caso real: días entre compras de un mismo cliente (indicador de recurrencia).

-- PostgreSQL: restar fechas da días. MySQL: DATEDIFF(fecha, LAG(...))
SELECT
  cliente_id,
  fecha,
  fecha - LAG(fecha) OVER (PARTITION BY cliente_id ORDER BY fecha) AS dias_desde_anterior
FROM pedidos
WHERE cliente_id = 1;

cliente_id	fecha	dias_desde_anterior
1	2024-05-02	NULL
1	2024-06-01	30

Marcos de ventana: ROWS BETWEEN

El marco define exactamente qué filas entran en el cálculo de cada fila:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   → desde el inicio hasta aquí (acumulado)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW           → esta fila y las 2 anteriores (media móvil de 3)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING           → anterior, actual y siguiente
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING → toda la partición

-- Media móvil de los últimos 3 pedidos
WITH totales AS (
  SELECT pe.id, pe.fecha, SUM(d.cantidad * d.precio_unitario) AS total
  FROM pedidos pe
  JOIN detalle_pedido d ON d.pedido_id = pe.id
  GROUP BY pe.id, pe.fecha
)
SELECT
  id, fecha, total,
  ROUND(AVG(total) OVER (ORDER BY fecha ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_movil_3
FROM totales
ORDER BY fecha;

id	fecha	total	media_movil_3
1	2024-05-02	1597.00	1597.00
2	2024-05-10	1847.00	1722.00
3	2024-06-01	3499.00	2314.33
4	2024-06-15	1146.00	2164.00
5	2024-06-20	899.00	1848.00

Error común: LAST_VALUE "no funciona"

Con ORDER BY en OVER, el marco por defecto es "desde el inicio hasta la fila actual". Por eso LAST_VALUE(x) OVER (ORDER BY fecha) devuelve el valor de la fila actual, no el último. Solución: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Ventanas con nombre (WINDOW)

Si repites la misma ventana, dale nombre (PostgreSQL, MySQL 8, SQLite):

SELECT nombre, precio,
       RANK()     OVER w AS lugar,
       AVG(precio) OVER w AS promedio_hasta_aqui
FROM productos
WINDOW w AS (ORDER BY precio DESC);

Dónde se pueden usar

Las funciones de ventana se calculan después de WHERE, GROUP BY y HAVING, justo con el SELECT. Por eso:

• Puedes usarlas en SELECT y ORDER BY.
• No puedes usarlas en WHERE, GROUP BY ni HAVING → envuélvelas en una CTE o subconsulta.
• Sí puedes aplicar una ventana sobre un resultado agrupado: SUM(SUM(x)) OVER ().

Buena práctica

Cuando te pidan "el primero/último/mejor de cada grupo", piensa de inmediato en ROW_NUMBER() OVER (PARTITION BY grupo ORDER BY criterio) + filtro rn = 1. Resuelve el 90 % de esos problemas.

Ejercicios

1. Numera los pedidos de cada cliente por fecha (columna num_pedido: 1 para su primer pedido, 2 para el segundo...).
2. Muestra cada cliente con su fecha de registro y el lugar en que se registró (1 = el primero) usando ROW_NUMBER.
3. Para cada producto, muestra el porcentaje que representa su precio dentro del total de precios de su categoría.
4. Obtén el producto más barato de cada categoría con el patrón top-N.
5. Calcula el acumulado de clientes registrados por fecha de registro.

Ver solución

-- 1
SELECT id, cliente_id, fecha,
       ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY fecha) AS num_pedido
FROM pedidos
ORDER BY cliente_id, fecha;

id	cliente_id	fecha	num_pedido
1	1	2024-05-02	1
3	1	2024-06-01	2
2	2	2024-05-10	1
4	3	2024-06-15	1
5	4	2024-06-20	1

-- 2
SELECT nombre, fecha_registro,
       ROW_NUMBER() OVER (ORDER BY fecha_registro) AS lugar
FROM clientes;

-- 3
SELECT nombre, categoria_id, precio,
       ROUND(100.0 * precio / SUM(precio) OVER (PARTITION BY categoria_id), 1) AS pct
FROM productos
WHERE categoria_id IS NOT NULL
ORDER BY categoria_id, pct DESC;

nombre	categoria_id	precio	pct
Monitor 24 pulgadas	1	3499.00	62.0
Teclado mecánico	1	1250.00	22.1
Audífonos Bluetooth	1	899.00	15.9
Libro Aprende SQL	2	349.00	54.7
Cien años de soledad	2	289.00	45.3
Lámpara de escritorio	3	459.00	69.8
Taza térmica	3	199.00	30.2

-- 4
WITH r AS (
  SELECT nombre, categoria_id, precio,
         ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY precio) AS rn
  FROM productos
  WHERE categoria_id IS NOT NULL
)
SELECT categoria_id, nombre, precio FROM r WHERE rn = 1;
-- 1: Audífonos Bluetooth 899 · 2: Cien años de soledad 289 · 3: Taza térmica 199

-- 5
SELECT nombre, fecha_registro,
       COUNT(*) OVER (ORDER BY fecha_registro) AS clientes_acumulados
FROM clientes;
-- 1, 2, 3, 4, 5

Resumen

• Las funciones de ventana calculan sobre grupos de filas sin colapsarlas: funcion() OVER (...).
• PARTITION BY define los grupos; ORDER BY el orden dentro de cada uno.
• ROW_NUMBER, RANK, DENSE_RANK para rankings; el patrón top-N usa una CTE y WHERE rn <= N.
• SUM(...) OVER (ORDER BY ...) da acumulados; LAG/LEAD comparan con filas vecinas.
• ROWS BETWEEN controla el marco (medias móviles). No se pueden usar en WHERE.