Las ventanas miran otras filas sin colapsarlas

La idea

GROUP BY responde una pregunta sobre un grupo y te da una fila por grupo. Una función de ventana responde el mismo tipo de pregunta pero pone la respuesta en cada fila del grupo. Mira un conjunto de filas relacionadas, calcula algo y deja las filas donde están.

La cláusula OVER (…) define ese conjunto de filas, la ventana. Tres partes hacen el trabajo: PARTITION BY divide las filas en grupos, ORDER BY pone cada grupo en secuencia, y el marco dice cuáles de esas filas están a la vista para la fila actual.

Por qué existe

Algunas preguntas necesitan el detalle y el grupo a la vez. "Cada producto junto al mejor precio de su categoría." "Cada línea de pedido como parte de su pedido." "Los pedidos de este mes junto a los del mes pasado." Solo con GROUP BY agregas en una consulta y unes el resultado de vuelta al detalle en otra. Funciona, pero es largo, y es fácil equivocarse con la clave del join.

Otras preguntas son sobre el orden, que GROUP BY ignora: totales acumulados, rankings, "el segundo pedido del cliente", la distancia entre un pedido y el anterior. Una función de ventana hace esto en una sola pasada sobre las filas, y por eso las entrevistas de analista insisten tanto en ellas.

Cómo funciona por dentro

Para cada fila, la base de datos hace las mismas cuatro cosas.

1. Particionar. Busca las filas del mismo grupo PARTITION BY que esta fila. Sin PARTITION BY, todo el resultado es una sola partición.
2. Ordenar. Ordena esa partición con el ORDER BY de la ventana.
3. Marco. Elige qué filas alrededor de la actual están a la vista.
4. Calcular. Ejecuta la función sobre las filas del marco y escribe la respuesta en la fila actual.

El marco es donde vive la mayor parte del poder, y de la confusión.

Si la ventana tiene ORDER BY y no tiene un marco explícito, el marco por defecto va desde el inicio de la partición hasta la fila actual (e incluye las filas empatadas con ella). Por eso SUM(x) OVER (ORDER BY month) da un total acumulado, mientras que SUM(x) OVER () da el total general en cada fila. Para un promedio móvil, escribe tú el marco: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW es una ventana de 7 filas. Ten en cuenta que cuenta filas, no días, así que un día que falte estira la ventana sin avisar.

Leer una ventana en una frase

Di la cláusula OVER en voz alta y se vuelve una frase. SUM(line_total) OVER (PARTITION BY order_id) se lee "suma los totales de línea, dentro de cada pedido". LAG(orders) OVER (PARTITION BY channel ORDER BY month) se lee "los pedidos del mes anterior, dentro de cada canal". Si no puedes decir quién es ese "dentro de cada", a la ventana le falta un PARTITION BY, y los números van a mezclar grupos que deberían estar separados.

Por qué el marco cuenta filas y no tiempo

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa "esta fila y las dos anteriores", sean cuales sean. En el diagrama son tres meses solo porque cada mes tiene una fila. Si un mes no tuviera pedidos y ninguna fila, el mismo marco llegaría cuatro meses atrás y promediaría en silencio el periodo equivocado. La solución habitual es construir primero un calendario completo, con una fila por cada mes o día, y unir tus datos a él antes de aplicar la ventana.

Los porcentajes salen solos. La parte que una línea representa de su pedido es line_total / SUM(line_total) OVER (PARTITION BY order_id): el detalle arriba, el total de la partición abajo, sin necesidad de volver a unir.

Tres familias de funciones

Agregados sobre una ventana: SUM, AVG, COUNT, MIN, MAX. Las mismas funciones que ya conoces, ahora con marco.

Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE. Dependen solo del orden, y los empates son lo que las distingue.

Navegación: LAG y LEAD leen un valor de la fila anterior o siguiente de la partición. LAG(revenue) OVER (ORDER BY month) es el ingreso del mes pasado junto al de este mes, y el primer mes recibe NULL porque no hay nada antes. FIRST_VALUE y LAST_VALUE leen los extremos del marco.

Dónde se ejecuta la ventana

Las funciones de ventana se ejecutan después de WHERE, GROUP BY y HAVING, y antes del ORDER BY final y del LIMIT del SELECT. Dos consecuencias. No puedes filtrar por el resultado de una ventana en el WHERE de la misma consulta, así que "top 3 por categoría" es un CTE o subconsulta que calcula ROW_NUMBER y una consulta externa que conserva rn <= 3. Y una ventana sobre una consulta agregada ve las filas ya agrupadas: SUM(SUM(revenue)) OVER (ORDER BY month) es un total acumulado de totales mensuales.

Errores comunes

"OVER (PARTITION BY x) es lo mismo que GROUP BY x"

El agrupamiento es el mismo, el resultado no. GROUP BY devuelve una fila por grupo. Una ventana devuelve todas las filas de entrada. Si tu resultado tiene menos filas de las que esperabas, algo las colapsó; si tiene el mismo número, tu ventana las dejó en paz.

"LAG toma la fila anterior, así que funciona sin PARTITION BY"

Toma la fila anterior en el orden de la ventana, entre todo lo que haya en la partición. Con varios clientes en un mismo resultado y sin PARTITION BY customer_id, el primer pedido de un cliente se compara con el último pedido del cliente anterior. Cuando compares con "antes", pregunta antes para quién.

"Los empates no importan si uso ROW_NUMBER"

Importan porque ROW_NUMBER los esconde: rompe el empate de forma arbitraria, y el resultado puede cambiar entre ejecuciones. Agrega una columna de desempate al ORDER BY para obtener una respuesta estable, o usa RANK o DENSE_RANK cuando las filas empatadas deban compartir lugar.