Funciones de ventana, sin perder el detalle

GROUP BY tiene un precio que casi nadie te cuenta: te quedas sin el detalle. Preguntas el promedio por canal y te devuelve cuatro filas; los 900 pedidos desaparecieron.

Y hay preguntas que necesitan las dos cosas a la vez. "¿Cuánto se aleja este pedido del promedio de su canal?" pide el promedio del canal (un resumen) y el monto de este pedido (el detalle), en la misma fila.

Eso es lo que hacen las funciones de ventana: calculan un resumen y no aplastan las filas. Es lo que más separa a alguien que sabe SQL de alguien que lo usa, y una vez que las entiendes ya no hay vuelta 🌟

OVER (), o el resumen que se queda al lado

SELECT id, canal, monto,
       ROUND(AVG(monto) OVER (), 2) AS promedio_global,
       ROUND(monto - AVG(monto) OVER (), 2) AS diferencia
FROM pedidos
WHERE monto IS NOT NULL
ORDER BY id
LIMIT 5;

id  canal        monto   promedio_global  diferencia
--  -----------  ------  ---------------  ----------
1   Web          892.06  610.14           281.92
2   Marketplace  731.09  610.14           120.95
3   Tienda       407.39  610.14           -202.75
4   Tienda       358.23  610.14           -251.91
5   Web          844.94  610.14           234.8

Ese OVER () es toda la diferencia. Sin él, AVG(monto) te habría dado una sola fila; con él, el mismo 610,14 se repite al lado de cada pedido y puedes restarlo.

Piensa el OVER () como una ventana: es el conjunto de filas que la función mira para calcular su número. Vacío significa "míralas todas".

PARTITION BY, o una ventana por grupo

SELECT id, canal, monto,
       ROUND(AVG(monto) OVER (PARTITION BY canal), 2) AS promedio_del_canal
FROM pedidos
WHERE monto IS NOT NULL
ORDER BY id
LIMIT 5;

id  canal        monto   promedio_del_canal
--  -----------  ------  ------------------
1   Web          892.06  615.99
2   Marketplace  731.09  611.5
3   Tienda       407.39  598.41
4   Tienda       358.23  598.41
5   Web          844.94  615.99

Ahora cada fila ve solo las de su canal. El pedido 1 es Web y le sale 615,99; el 3 es Tienda y le sale 598,41. Los mismos números del capítulo 6, pero sin perder las 873 filas.

PARTITION BY es a las ventanas lo que GROUP BY es a los grupos, con una diferencia enorme: no reduce nada. Si lo tienes claro, ya entendiste la mitad del capítulo 💛

Rankings: las tres formas de numerar

SELECT ciudad, COUNT(*) AS clientes,
       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS fila,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS rango,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rango_denso
FROM clientes
GROUP BY ciudad;

ciudad    clientes  fila  rango  rango_denso
--------  --------  ----  -----  -----------
Chiclayo  25        1     1      1
Piura     24        2     2      2
Arequipa  22        3     3      3
Trujillo  17        4     4      4
Cusco     17        5     4      4
Lima      15        6     6      5

Aquí está toda la teoría en una tabla, y menos mal que Trujillo y Cusco empatan con 17, porque el empate es lo único que las diferencia:

• 🔢 ROW_NUMBER numera 1, 2, 3, 4, 5, 6. No sabe de empates: a uno le toca el 4 y al otro el 5, y cuál es cuál lo decide el motor.
• 🥇 RANK les da 4 a los dos y después salta al 6. Es el podio de una carrera: dos cuartos, no hay quinto.
• 🎗️ DENSE_RANK les da 4 a los dos y sigue en 5. No deja huecos.

Cuál usar depende de la pregunta. Para "el top 3 de verdad", RANK o DENSE_RANK, porque si hay empate en el tercer puesto quieres los dos. Para "dame exactamente una fila por grupo", ROW_NUMBER.

El top N por grupo, que es la reina de las consultas

"El pedido más grande de cada canal" es de las cosas que más te van a pedir y de las que peor se resuelven sin ventanas.

SELECT canal, id, monto,
       ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) AS puesto
FROM pedidos
WHERE monto IS NOT NULL
ORDER BY canal, puesto
LIMIT 8;

canal        id   monto    puesto
-----------  ---  -------  ------
Marketplace  874  1244.55  1
Marketplace  72   1244.2   2
Marketplace  65   1195.8   3
Marketplace  646  1189.28  4
Marketplace  798  1160.71  5
Marketplace  103  1145.49  6
Marketplace  644  1142.65  7
Marketplace  660  1055.15  8

La ventana numera de nuevo dentro de cada canal: el más caro de Marketplace es el puesto 1, el siguiente el 2, y cuando cambia el canal vuelve a empezar en 1. Ahora solo falta quedarse con los primeros. Y aquí viene el tropiezo clásico.

SELECT canal, id, monto
FROM pedidos
WHERE ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) = 1;

OperationalError: misuse of window function ROW_NUMBER()

misuse of window function. Acuérdate del orden de ejecución del capítulo 6: el WHERE pasa en el paso 2 y las ventanas se calculan en el paso 5, junto con el SELECT. Cuando el WHERE mira, el puesto todavía no existe.

Lo mismo pasa con HAVING y GROUP BY. La solución es siempre la misma: calcula la ventana en un CTE y filtra fuera, que es exactamente para lo que aprendimos WITH en el capítulo 8.

WITH ranking AS (
    SELECT canal, id, monto,
           ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) AS puesto
    FROM pedidos
    WHERE monto IS NOT NULL
)
SELECT canal, id, monto, puesto
FROM ranking
WHERE puesto <= 2
ORDER BY canal, puesto;

canal        id   monto    puesto
-----------  ---  -------  ------
Marketplace  874  1244.55  1
Marketplace  72   1244.2   2
Tienda       151  1236.97  1
Tienda       142  1170.3   2
Web          674  1372.34  1
Web          373  1341.15  2
WhatsApp     25   1399.98  1
WhatsApp     440  1322.73  2

Los dos pedidos más grandes de cada canal, en ocho filas. Ese patrón (WITH + ROW_NUMBER + WHERE puesto <= N) lo vas a escribir mil veces, y funciona igual en los cuatro motores.

El acumulado, o cómo va el año

Cuando el OVER lleva ORDER BY, la ventana deja de ser "todas las filas del grupo" y pasa a ser "todas las de aquí para atrás". Y eso es un acumulado.

WITH ventas_mes AS (
    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles
    FROM pedidos
    WHERE fecha >= '2026-01-01'
    GROUP BY mes
)
SELECT mes, soles,
       ROUND(SUM(soles) OVER (ORDER BY mes), 2) AS acumulado
FROM ventas_mes
ORDER BY mes;

mes      soles     acumulado
-------  --------  ---------
2026-01  29373.17  29373.17
2026-02  24033.91  53407.08
2026-03  28103.64  81510.72
2026-04  27651.42  109162.14
2026-05  33711.97  142874.11
2026-06  25293.12  168167.23

168.167 soles en el primer semestre de 2026, y la columna de al lado te dice cómo se llegó ahí. Es la típica que pide un gerente y que en Excel es una fórmula que se arrastra y se rompe 📈

Esa ventana se puede escribir a mano, y a veces hay que hacerlo.

WITH ventas_mes AS (
    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles
    FROM pedidos GROUP BY mes
)
SELECT mes, soles,
       ROUND(AVG(soles) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_movil_3
FROM ventas_mes
ORDER BY mes
LIMIT 6;

mes      soles     media_movil_3
-------  --------  -------------
2025-01  36743.94  36743.94
2025-02  20231.23  28487.58
2025-03  36274.87  31083.35
2025-04  35680.53  30728.88
2025-05  28200.36  33385.25
2025-06  25942.48  29941.12

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW es "esta fila y las dos de antes", o sea una media móvil de tres meses. Sirve para ver la tendencia sin que un mes raro te la tape: enero fue 36.743 y febrero 20.231, y a partir de la segunda fila la media de tres va suavecita entre 28 y 34 mil.

Fíjate en las dos primeras filas: enero no tiene dos meses antes, así que su media es él solo. Las medias móviles siempre arrancan cojas y eso hay que decirlo en el reporte 🌸

Mirar la fila de al lado: LAG y LEAD

La pregunta más frecuente del mundo de los reportes: ¿cuánto crecimos respecto al mes pasado?

WITH ventas_mes AS (
    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles
    FROM pedidos
    WHERE fecha >= '2026-01-01'
    GROUP BY mes
)
SELECT mes, soles,
       LAG(soles) OVER (ORDER BY mes) AS mes_anterior,
       ROUND(soles - LAG(soles) OVER (ORDER BY mes), 2) AS diferencia,
       ROUND(100.0 * (soles - LAG(soles) OVER (ORDER BY mes)) / LAG(soles) OVER (ORDER BY mes), 1) AS variacion
FROM ventas_mes
ORDER BY mes;

mes      soles     mes_anterior  diferencia  variacion
-------  --------  ------------  ----------  ---------
2026-01  29373.17
2026-02  24033.91  29373.17      -5339.26    -18.2
2026-03  28103.64  24033.91      4069.73     16.9
2026-04  27651.42  28103.64      -452.22     -1.6
2026-05  33711.97  27651.42      6060.55     21.9
2026-06  25293.12  33711.97      -8418.85    -25.0

LAG trae el valor de la fila anterior y LEAD el de la siguiente. Con eso, comparar meses deja de ser un rompecabezas de subconsultas y pasa a ser una columna más.

Enero está vacío en las tres columnas, y está bien: no hay mes anterior, así que es NULL. Del capítulo 4 ya sabemos que cualquier cuenta con NULL da NULL, por eso la diferencia y la variación también salen vacías.

Y mira lo que dice el negocio: febrero cae 18%, mayo sube 22%, junio cae 25%. Junio no está terminado en esta base, o sea que esa caída puede ser mitad realidad y mitad calendario. El último mes de un reporte casi siempre está incompleto y casi siempre alguien lo lee como una caída 🫠

Repartir en grupos: NTILE

WITH por_cliente AS (
    SELECT c.id, c.nombre, SUM(p.monto) AS soles
    FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
    GROUP BY c.id, c.nombre
),
en_cuartiles AS (
    SELECT nombre, soles, NTILE(4) OVER (ORDER BY soles DESC) AS cuartil
    FROM por_cliente
)
SELECT cuartil, COUNT(*) AS clientes,
       ROUND(MIN(soles), 2) AS el_mas_bajo,
       ROUND(MAX(soles), 2) AS el_mas_alto,
       ROUND(SUM(soles), 2) AS soles
FROM en_cuartiles
GROUP BY cuartil
ORDER BY cuartil;

cuartil  clientes  el_mas_bajo  el_mas_alto  soles
-------  --------  -----------  -----------  ---------
1        30        5230.87      10131.72     208380.05
2        30        4235.85      5173.36      142569.61
3        30        2820.29      4217.49      106697.68
4        29        436.26       2749.91      59795.19

NTILE(4) parte la lista ordenada en cuatro montones del mismo tamaño. Como son 119 clientes y no se divide exacto, el último se queda con 29 y los otros tres con 30.

Y ahí tienes una segmentación de clientes hecha en SQL, sin machine learning ni nada: el cuartil de arriba, 30 clientes, se lleva 208.380 soles de los 517.442 totales, o sea el 40%. Los 29 de abajo aportan 59.795, un 12%. Eso es una conversación de negocio completa 💡

Ventanas sobre grupos, que parece imposible y no lo es

SELECT canal, COUNT(*) AS pedidos,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS porcentaje
FROM pedidos
GROUP BY canal
ORDER BY pedidos DESC;

canal        pedidos  porcentaje
-----------  -------  ----------
Web          234      26.0
WhatsApp     231      25.7
Marketplace  229      25.4
Tienda       206      22.9

Ese SUM(COUNT(*)) OVER () es raro de ver la primera vez y es perfectamente legal: primero el GROUP BY arma los cuatro grupos con su cuenta, y después la ventana suma esas cuatro cuentas. Un COUNT de COUNT.

Así sacas porcentajes sobre el total sin ninguna subconsulta, y es de las cosas que más quedan bien en un reporte.

Con PARTITION BY el porcentaje se calcula sobre el grupo que quieras. Aquí, cuánto pesa cada cliente dentro de su propia ciudad.

SELECT c.ciudad, c.nombre, ROUND(SUM(p.monto), 2) AS soles,
       ROUND(100.0 * SUM(p.monto) / SUM(SUM(p.monto)) OVER (PARTITION BY c.ciudad), 1) AS peso_en_su_ciudad
FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
WHERE c.ciudad = 'Lima'
GROUP BY c.id, c.ciudad, c.nombre
ORDER BY soles DESC
LIMIT 5;

ciudad  nombre                 soles     peso_en_su_ciudad
------  ---------------------  --------  -----------------
Lima    Distribuidora Paz 058  10131.72  15.9
Lima    Almacenes Vega 072     6470.85   10.2
Lima    Comercial Rojas 021    5230.87   8.2
Lima    Almacenes Vega 039     5068.22   8.0
Lima    Minimarket Aurora 107  4870.84   7.6

Distribuidora Paz 058 es el 15,9% de toda Lima ella sola. Cuando un cliente pesa así, el riesgo tiene nombre y apellido 😬

Ejercicios

Siete sobre tienda.db. Intenta antes de abrir 💛

WITH por_ciudad AS (
    SELECT c.ciudad, c.id, c.nombre, SUM(p.monto) AS soles
    FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
    GROUP BY c.ciudad, c.id, c.nombre
),
ranking AS (
    SELECT ciudad, nombre, soles,
           ROW_NUMBER() OVER (PARTITION BY ciudad ORDER BY soles DESC) AS puesto
    FROM por_ciudad
)
SELECT ciudad, nombre, ROUND(soles, 2) AS soles
FROM ranking
WHERE puesto = 1
ORDER BY soles DESC;

ciudad    nombre                      soles
--------  --------------------------  --------
Lima      Distribuidora Paz 058       10131.72
Piura     Almacenes Vega 067          9516.06
Chiclayo  Market Central 087          8148.56
Trujillo  Restaurante Miraflores 093  8119.95
Arequipa  Cafe del Puerto 060         7948.71
Cusco     Mayorista Peru 027          7732.46

WITH ventas_dia AS (
    SELECT fecha, ROUND(SUM(monto), 2) AS soles
    FROM pedidos
    WHERE fecha BETWEEN '2026-06-01' AND '2026-06-10'
    GROUP BY fecha
)
SELECT fecha, soles,
       LAG(soles) OVER (ORDER BY fecha) AS dia_anterior,
       LEAD(soles) OVER (ORDER BY fecha) AS dia_siguiente
FROM ventas_dia
ORDER BY fecha
LIMIT 5;

fecha       soles    dia_anterior  dia_siguiente
----------  -------  ------------  -------------
2026-06-01  1854.01                1133.93
2026-06-02  1133.93  1854.01       413.38
2026-06-03  413.38   1133.93       890.15
2026-06-04  890.15   413.38        756.51
2026-06-05  756.51   890.15        1525.18

SELECT id, canal, monto,
       ROUND(AVG(monto) OVER (PARTITION BY canal), 2) AS promedio_del_canal,
       ROUND(monto - AVG(monto) OVER (PARTITION BY canal), 2) AS diferencia
FROM pedidos
WHERE monto IS NOT NULL
ORDER BY id
LIMIT 5;

id  canal        monto   promedio_del_canal  diferencia
--  -----------  ------  ------------------  ----------
1   Web          892.06  615.99              276.07
2   Marketplace  731.09  611.5               119.59
3   Tienda       407.39  598.41              -191.02
4   Tienda       358.23  598.41              -240.18
5   Web          844.94  615.99              228.95

SELECT segmento, COUNT(*) AS clientes,
       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS fila,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS rango,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rango_denso
FROM clientes
GROUP BY segmento;

segmento    clientes  fila  rango  rango_denso
----------  --------  ----  -----  -----------
Horeca      36        1     1      1
Bodega      35        2     2      2
Minimarket  25        3     3      3
Mayorista   24        4     4      4

SELECT canal, id, monto,
       FIRST_VALUE(monto) OVER (PARTITION BY canal ORDER BY monto DESC) AS el_mayor_del_canal,
       ROUND(100.0 * monto / FIRST_VALUE(monto) OVER (PARTITION BY canal ORDER BY monto DESC), 1) AS respecto_al_mayor
FROM pedidos
WHERE monto IS NOT NULL AND canal = 'Tienda'
ORDER BY monto DESC
LIMIT 4;

canal   id   monto    el_mayor_del_canal  respecto_al_mayor
------  ---  -------  ------------------  -----------------
Tienda  151  1236.97  1236.97             100.0
Tienda  142  1170.3   1236.97             94.6
Tienda  236  1170.08  1236.97             94.6
Tienda  361  1150.04  1236.97             93.0

WITH ventas_mes AS (
    SELECT STRFTIME('%Y-%m', fecha) AS mes, ROUND(SUM(monto), 2) AS soles
    FROM pedidos GROUP BY mes
)
SELECT mes, soles,
       ROUND(AVG(soles) OVER (), 2) AS media_de_todos,
       ROUND(SUM(soles) OVER (ORDER BY mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS acumulado
FROM ventas_mes
ORDER BY mes
LIMIT 4;

mes      soles     media_de_todos  acumulado
-------  --------  --------------  ---------
2025-01  36743.94  29591.88        36743.94
2025-02  20231.23  29591.88        56975.17
2025-03  36274.87  29591.88        93250.04
2025-04  35680.53  29591.88        128930.57

-- Se escribe IGUAL en PostgreSQL, MySQL 8, SQL Server y SQLite 3.25+.
WITH ranking AS (
    SELECT canal, id, monto,
           ROW_NUMBER() OVER (PARTITION BY canal ORDER BY monto DESC) AS puesto
    FROM pedidos
    WHERE monto IS NOT NULL
)
SELECT canal, id, monto FROM ranking WHERE puesto = 1;

Lo que te llevas

• 🪟 Una función de ventana calcula un resumen sin aplastar las filas. La marca es el OVER.
• 🧩 PARTITION BY es el GROUP BY de las ventanas, y la diferencia es que no reduce nada.
• 🥇 ROW_NUMBER nunca empata, RANK empata y salta, DENSE_RANK empata y no salta. Sin empates las tres son iguales.
• 🚫 No se puede filtrar por una ventana en el WHERE: se calcula después. Se mete en un CTE y se filtra fuera.
• 📈 Con ORDER BY dentro del OVER tienes acumulados; con ROWS BETWEEN tienes medias móviles.
• ↔️ LAG y LEAD traen la fila anterior y la siguiente. Es el crecimiento mes a mes en una columna.
• 📊 NTILE(4) parte en cuartiles: el 25% de arriba de esta tienda son 30 clientes y el 40% de la venta.
• 🗓️ Están en los cuatro motores con la misma sintaxis, pero MySQL las tiene desde la 8.0 y SQLite desde la 3.25, las dos de 2018.

En el capítulo 10 dejamos de leer y empezamos a escribir: crear tablas, tipos, claves y los cuatro autoincrementos, que es donde los motores se vuelven a separar del todo.

Que tengas lindo día! 🌸