El marco: ROWS contra RANGE

1. Qué es

El marco (frame) es la tercera pieza de OVER, y responde a la pregunta que las otras dos dejan abierta: dentro de la partición ya ordenada, ¿qué trozo entra en el cálculo?

funcion() OVER (
    PARTITION BY ...     -- qué grupos
    ORDER BY ...         -- en qué orden
    ROWS | RANGE ...     -- ← qué trozo
)

Es la parte que más gente se salta, y por eso es donde aparecen los resultados que "salen mal" sin dar error. El marco existe siempre: si no lo escribes, PostgreSQL pone uno por defecto, y el que pone depende de si hay ORDER BY.

La segunda fila de esa tabla es todo el tema. El marco por defecto no es "hasta esta fila": es "hasta este valor".

2. El problema, visto

SELECT p.product_name, p.unit_price,
       sum(p.unit_price) OVER (ORDER BY p.unit_price)                        AS range_def,
       sum(p.unit_price) OVER (ORDER BY p.unit_price ROWS UNBOUNDED PRECEDING) AS rows_acum
FROM products p WHERE p.category_id = 1 ORDER BY p.unit_price;

       product_name        | unit_price | range_def | rows_acum
---------------------------+------------+-----------+-----------
 Guaraná Fantástica        |       4.50 |      4.50 |      4.50
 Rhönbräu Klosterbier      |       7.75 |     12.25 |     12.25
 Laughing Lumberjack Lager |      14.00 |     40.25 |     26.25
 Sasquatch Ale             |      14.00 |     40.25 |     40.25
 Outback Lager             |      15.00 |     55.25 |     55.25
 Lakkalikööri              |      18.00 |    127.25 |     73.25
 Steeleye Stout            |      18.00 |    127.25 |     91.25
 Chartreuse verte          |      18.00 |    127.25 |    109.25
 Chai                      |      18.00 |    127.25 |    127.25
 Chang                     |      19.00 |    146.25 |    146.25
 Ipoh Coffee               |      46.00 |    192.25 |    192.25
 Côte de Blaye             |     263.50 |    455.75 |    455.75

Las dos columnas son correctas y responden a preguntas distintas:

• range_def — "suma de todos los productos que cuestan 18,00 o menos". Las cuatro filas de 18,00 dan el mismo número, porque comparten valor y por tanto comparten ventana.
• rows_acum — "suma de las filas que van hasta esta, incluida". Avanza fila a fila: 73,25 / 91,25 / 109,25 / 127,25.

Coinciden donde no hay empates, y se separan donde los hay. Por eso el error se esconde: en una tabla de fechas únicas o de order_id nunca se ve. Aparece el día que ordenas por precio, por país o por importe redondeado.

La regla práctica:

Si el ORDER BY de la ventana puede tener empates y quieres un acumulado fila a fila, escribe ROWS. Siempre.

Y si escribes ROWS, fija el desempate en el ORDER BY —ORDER BY unit_price, product_id— porque con ROWS el resultado de las filas empatadas depende del orden en que caigan, y sin desempate ese orden es arbitrario. (Con RANGE BETWEEN n PRECEDING ese desempate no es posible; ver la sección 5.)

3. Sintaxis del marco

ROWS  BETWEEN <inicio> AND <fin>
RANGE BETWEEN <inicio> AND <fin>

Los límites disponibles:

Y hay una forma corta: ROWS UNBOUNDED PRECEDING equivale a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Cuando solo se escribe un límite, el otro es CURRENT ROW.

ROWS cuenta filas. RANGE cuenta valores. Todo lo demás sale de ahí.

4. Ejemplos sobre Northwind

4.1 Media móvil de 3 meses

El uso más común del marco, y el que hace legible una serie con ruido:

SELECT date_trunc('month', o.order_date)::date AS mes,
       round(sum(od.unit_price*od.quantity*(1-od.discount)), 2) AS facturacion,
       round(avg(sum(od.unit_price*od.quantity*(1-od.discount)))
             OVER (ORDER BY date_trunc('month', o.order_date)
                   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_movil_3
FROM orders o JOIN order_details od ON od.order_id = o.order_id
GROUP BY date_trunc('month', o.order_date) ORDER BY 1;

    mes     | facturacion | media_movil_3
------------+-------------+---------------
 1996-07-01 |    27861.90 |      27861.90
 1996-08-01 |    25485.28 |      26673.59
 1996-09-01 |    26381.40 |      26576.19
 1996-10-01 |    37515.73 |      29794.13
 1996-11-01 |    45600.05 |      36499.06
 1996-12-01 |    45239.63 |      42785.13
 1997-01-01 |    61258.07 |      50699.25
 1997-02-01 |    38483.64 |      48327.11
 1997-03-01 |    38547.22 |      46096.31
 1997-04-01 |    53032.95 |      43354.60
 1997-05-01 |    53781.29 |      48453.82
 1997-06-01 |    36362.80 |      47725.68
 1997-07-01 |    51020.86 |      47054.98
 1997-08-01 |    47287.67 |      44890.44
 1997-09-01 |    55629.24 |      51312.59
 1997-10-01 |    66749.23 |      56555.38
 1997-11-01 |    43533.81 |      55304.09
 1997-12-01 |    71398.43 |      60560.49
 1998-01-01 |    94222.11 |      69718.12
 1998-02-01 |    99415.29 |      88345.28
 1998-03-01 |   104854.16 |      99497.18
 1998-04-01 |   123798.68 |     109356.04
 1998-05-01 |    18333.63 |      82328.82

2 PRECEDING AND CURRENT ROW son tres meses: los dos anteriores y este. La columna suavizada cuenta una historia que la cruda no deja ver:

• La facturación mensual salta constantemente — enero de 1997 hace 61 258 y febrero 38 484: un −37% que no significa nada. Diciembre de 1997 hace 71 398 y enero de 1998 hace 94 222.
• La media móvil sube casi sin interrupción de 26 673 a 109 356. Es una empresa que crece de forma sostenida durante 22 meses, y eso en la columna cruda no se ve.

Las dos primeras filas no son medias de tres meses. Julio de 1996 es su propio valor, agosto es la media de dos. El marco no inventa filas que no existen: se recorta. Es correcto, pero hay que decirlo al entregar el gráfico, porque el arranque de la curva está calculado con menos datos que el resto.

Y la última fila es una trampa: la media móvil cae de 109 356 a 82 329 porque mayo de 1998 solo tiene seis días de datos. Suavizar no protege de un periodo incompleto — lo reparte entre tres meses y lo disimula, que es peor.

4.2 Marco centrado

... OVER (ORDER BY mes ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

    mes     | facturacion | centrada_3
------------+-------------+------------
 1996-07-01 |    27861.90 |   26673.59
 1996-08-01 |    25485.28 |   26576.19
 1996-09-01 |    26381.40 |   29794.13
 1996-10-01 |    37515.73 |   36499.06
 1996-11-01 |    45600.05 |   42785.13
 1996-12-01 |    45239.63 |   50699.25

Mira el mes de agosto: la media centrada de agosto (26 576,19) es idéntica a la media móvil de septiembre del ejemplo anterior. Es el mismo cálculo desplazado una fila — julio, agosto, septiembre.

La diferencia es la honestidad temporal. La media móvil hacia atrás solo usa datos que ya existían en ese momento; la centrada usa el mes siguiente. Para un gráfico histórico, la centrada se ve mejor. Para cualquier cosa que alimente una predicción, la centrada es información del futuro y no vale.

4.3 Lo que queda por delante

FOLLOWING mira hacia el otro lado:

SELECT date_trunc('month', o.order_date)::date AS mes,
       round(sum(od.unit_price*od.quantity*(1-od.discount)), 2) AS facturacion,
       round(sum(sum(od.unit_price*od.quantity*(1-od.discount)))
             OVER (ORDER BY date_trunc('month', o.order_date)
                   ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), 2) AS resto
FROM orders o JOIN order_details od ON od.order_id = o.order_id
WHERE o.order_date < '1997-01-01'
GROUP BY date_trunc('month', o.order_date) ORDER BY 1;

    mes     | facturacion |   resto
------------+-------------+-----------
 1996-07-01 |    27861.90 | 180222.08
 1996-08-01 |    25485.28 | 154736.80
 1996-09-01 |    26381.40 | 128355.40
 1996-10-01 |    37515.73 |  90839.68
 1996-11-01 |    45600.05 |  45239.63
 1996-12-01 |    45239.63 |

La última fila es NULL y tiene que serlo: después de diciembre no queda nada. Un marco vacío devuelve NULL en sum y avg, y 0 en count. Es la misma distinción de null-y-la-logica-de-tres-valores: ausencia de dato, no cero.

4.4 RANGE usado a propósito

RANGE no es solo el defecto molesto: cuando lo que quieres es "todo lo que está cerca en valor", es la herramienta correcta.

SELECT p.product_name, p.unit_price,
       count(*) OVER (ORDER BY p.unit_price
                      RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) AS productos_cerca
FROM products p WHERE p.category_id = 1 ORDER BY p.unit_price;

       product_name        | unit_price | productos_cerca
---------------------------+------------+-----------------
 Guaraná Fantástica        |       4.50 |               2
 Rhönbräu Klosterbier      |       7.75 |               2
 Laughing Lumberjack Lager |      14.00 |               8
 Sasquatch Ale             |      14.00 |               8
 Outback Lager             |      15.00 |               8
 Lakkalikööri              |      18.00 |               8
 Steeleye Stout            |      18.00 |               8
 Chartreuse verte          |      18.00 |               8
 Chai                      |      18.00 |               8
 Chang                     |      19.00 |               8
 Ipoh Coffee               |      46.00 |               1
 Côte de Blaye             |     263.50 |               1

Aquí el 5 PRECEDING son cinco euros, no cinco filas. Cuenta cuántos productos tienen un precio a menos de 5 € de distancia — una medida de densidad de la banda de precio.

Y el resultado dibuja el catálogo de Beverages de una forma que ninguna consulta anterior había mostrado: ocho de los doce productos se apiñan entre 14 y 19 €, y Ipoh Coffee y Côte de Blaye están solos: no tienen ningún producto a menos de 5 € de su precio. No son solo caros, están desconectados del resto del catálogo.

4.5 RANGE con fechas

RANGE acepta intervalos, que es donde se vuelve imprescindible:

SELECT o.order_date::date AS dia, count(*) AS pedidos,
       sum(count(*)) OVER (ORDER BY o.order_date
                           RANGE BETWEEN INTERVAL '6 days' PRECEDING
                                     AND CURRENT ROW) AS ultimos_7_dias
FROM orders o WHERE o.order_date BETWEEN '1997-01-01' AND '1997-01-20'
GROUP BY o.order_date ORDER BY 1;

    dia     | pedidos | ultimos_7_dias
------------+---------+----------------
 1997-01-01 |       2 |              2
 1997-01-02 |       1 |              3
 1997-01-03 |       2 |              5
 1997-01-06 |       1 |              6
 1997-01-07 |       2 |              8
 1997-01-08 |       1 |              7
 1997-01-09 |       1 |              7
 1997-01-10 |       2 |              7
 1997-01-13 |       1 |              7
 ...

Esto con ROWS sería imposible de hacer bien. Los días 4 y 5 de enero no existen en la tabla — son sábado y domingo, y ya sabemos de cte-recursivas que Northwind no tiene ni un pedido en fin de semana. ROWS BETWEEN 6 PRECEDING contaría seis filas hacia atrás, que aquí son ocho o nueve días naturales.

RANGE con INTERVAL cuenta días de calendario aunque falten filas. Es la única forma correcta de una ventana móvil temporal sobre datos con huecos, y los datos reales siempre tienen huecos.

5. Errores comunes

1. Escribir un acumulado con ORDER BY y nada más, sobre una columna con empates. El caso de la sección 2. Es el error más frecuente del bloque entero y no da ningún aviso.

2. Usar ROWS sin desempate. ORDER BY unit_price ROWS ... con cuatro productos a 18,00 da un resultado que depende del orden físico. Añade siempre una columna única al final del ORDER BY.

3. Creer que el marco recorta la partición. No: el marco se recorta contra los bordes de la partición. 2 PRECEDING en la primera fila usa una fila, no falla ni devuelve NULL.

4. Poner un marco ROWS sin ORDER BY. PostgreSQL lo acepta, y ese es el problema.

sql SELECT product_name, unit_price, sum(unit_price) OVER (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS s FROM products WHERE category_id = 7; product_name | unit_price | s ---------------------------------+------------+------- Uncle Bob's Organic Dried Pears | 30.00 | 30.00 Tofu | 23.25 | 53.25 Rössle Sauerkraut | 45.60 | 68.85 Manjimup Dried Apples | 53.00 | 98.60 Longlife Tofu | 10.00 | 63.00

No da error: suma cada fila con la anterior en el orden físico de la tabla. Ese orden no está definido por nada — puede cambiar tras un UPDATE, un VACUUM o una lectura en paralelo. Un resultado reproducible hoy y distinto mañana, sin ningún aviso. Si escribes un marco, escribe el ORDER BY.

1. Intentar arreglar un empate en un marco RANGE con desplazamiento. El consejo de la sección 2 —añadir una columna de desempate— no se puede aplicar a RANGE BETWEEN n PRECEDING: ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column Tiene sentido: para restar 5 al valor hace falta un único valor al que restarle 5. RANGE con desplazamiento y desempate son incompatibles por definición — si necesitas las dos cosas, lo que quieres es ROWS.

Y por el mismo motivo, la columna tiene que ser restable: sql ... OVER (ORDER BY product_name RANGE BETWEEN 5 PRECEDING AND CURRENT ROW) ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type text

1. Mezclar en la misma tabla un acumulado particionado por año con una media móvil que no lo está. Es un error de diseño, no de sintaxis, y se ve en la pregunta de negocio.

6. Diferencias con SQL Server

Esta es la diferencia más grande de todo el bloque. SQL Server implementa RANGE solo con los límites no acotados: RANGE BETWEEN 5 PRECEDING no existe, y RANGE BETWEEN INTERVAL '6 days' PRECEDING tampoco.

Consecuencia práctica: las ventanas móviles por tiempo real —"los últimos 7 días naturales"— que en PostgreSQL son una línea, en SQL Server se resuelven con un JOIN contra una tabla de calendario o con APPLY. Si vas a portar código en esa dirección, es lo primero que hay que revisar.

7. Ejercicios

1. El acumulado de precios de Confections, fila a fila y con el desempate fijado.
2. La media móvil de 3 meses del número de pedidos (no de la facturación).
3. Para cada mes, cuál es el mejor mes registrado hasta esa fecha — un récord histórico que solo puede subir.
4. Para cada mes de 1996, cuánto queda por facturar desde el mes siguiente hasta el final del año.
5. ¿Por qué la media móvil de las dos primeras filas no es de tres meses? ¿Y cómo se dejaría en NULL en lugar de calcularla con menos datos?

8. Pregunta de negocio

Dirección quiere el informe mensual definitivo: facturación del mes, la tendencia sin el ruido, y cuánto llevamos acumulado en el año.

Las tres columnas usan tres ventanas distintas. Y dos de ellas no están de acuerdo sobre dónde empieza el año.