PARTITION BY y ORDER BY

1. Qué es

Las dos cláusulas que dan forma a la ventana. Cada una responde a una pregunta:

• PARTITION BY — ¿en qué grupos se divide la tabla? Cada grupo es una ventana independiente.
• ORDER BY — ¿en qué orden se recorre cada ventana? Y con eso, qué filas ha visto ya la función cuando llega a la fila actual.

funcion() OVER (PARTITION BY columna ORDER BY otra)

La segunda es la que sorprende. ORDER BY dentro de OVER no ordena el resultado: cambia lo que la función calcula. Es la diferencia entre un total y un acumulado, y es el 90% de la confusión con este bloque.

PARTITION BY no es GROUP BY. Se parecen y hacen cosas opuestas:

Ese último punto es el que más se aprovecha: en la misma consulta puedes tener la media global, la media de la categoría y la media del país, cada una en su columna.

2. PARTITION BY: partir la tabla

SELECT c.category_name, p.product_name, p.unit_price,
       round(avg(p.unit_price) OVER (PARTITION BY p.category_id), 2)  AS media_categoria,
       round(p.unit_price - avg(p.unit_price) OVER (PARTITION BY p.category_id), 2) AS vs_categoria
FROM products p JOIN categories c USING (category_id)
WHERE c.category_name IN ('Beverages','Produce')
ORDER BY c.category_name, p.unit_price DESC;

 category_name |          product_name           | unit_price | media_categoria | vs_categoria
---------------+---------------------------------+------------+-----------------+--------------
 Beverages     | Côte de Blaye                   |     263.50 |           37.98 |       225.52
 Beverages     | Ipoh Coffee                     |      46.00 |           37.98 |         8.02
 Beverages     | Chang                           |      19.00 |           37.98 |       -18.98
 Beverages     | Chai                            |      18.00 |           37.98 |       -19.98
 Beverages     | Lakkalikööri                    |      18.00 |           37.98 |       -19.98
 ...
 Beverages     | Guaraná Fantástica              |       4.50 |           37.98 |       -33.48
 Produce       | Manjimup Dried Apples           |      53.00 |           32.37 |        20.63
 Produce       | Rössle Sauerkraut               |      45.60 |           32.37 |        13.23
 Produce       | Uncle Bob's Organic Dried Pears |      30.00 |           32.37 |        -2.37
 Produce       | Tofu                            |      23.25 |           32.37 |        -9.12
 Produce       | Longlife Tofu                   |      10.00 |           32.37 |       -22.37

Dos ventanas, dos medias. 37,98 se repite en las 12 filas de Beverages, 32,37 en las 5 de Produce. La función se reinicia en cada partición.

Y mira la columna vs_categoria de Beverages: diez de doce productos están por debajo de su propia media. La media de 37,98 la produce un solo producto, Côte de Blaye a 263,50. Sin él, la media de la categoría baja de 37,98 a 17,48. Una media con un atípico dentro describe al atípico, no al grupo.

2.1 Varias ventanas distintas en la misma consulta

SELECT c.category_name, p.product_name, p.unit_price,
       round(avg(p.unit_price) OVER (), 2)                           AS media_global,
       round(avg(p.unit_price) OVER (PARTITION BY p.category_id), 2) AS media_cat
FROM products p JOIN categories c USING (category_id)
WHERE p.product_name IN ('Côte de Blaye','Manjimup Dried Apples','Longlife Tofu',
                         'Chai','Konbu','Thüringer Rostbratwurst')
ORDER BY p.unit_price DESC;

 category_name |      product_name       | unit_price | media_global | media_cat
---------------+-------------------------+------------+--------------+-----------
 Beverages     | Côte de Blaye           |     263.50 |        79.05 |    140.75
 Meat/Poultry  | Thüringer Rostbratwurst |     123.79 |        79.05 |    123.79
 Produce       | Manjimup Dried Apples   |      53.00 |        79.05 |     31.50
 Beverages     | Chai                    |      18.00 |        79.05 |    140.75
 Produce       | Longlife Tofu           |      10.00 |        79.05 |     31.50
 Seafood       | Konbu                   |       6.00 |        79.05 |      6.00

Dos OVER distintos, dos ventanas independientes, una sola pasada. Con GROUP BY esto necesitaría dos consultas y un JOIN.

Y aquí hay una trampa que hay que ver ahora: las medias no son las de la tabla completa. media_global sale 79,05 y no 28,83, porque el WHERE filtró a 6 productos antes de que la ventana existiera. media_cat de Meat/Poultry sale 123,79 porque en la selección solo sobrevivió un producto de esa categoría — es su propia media.

La ventana opera sobre lo que sobrevive al WHERE, no sobre la tabla. Es la misma regla del tema anterior, y con PARTITION BY engaña más porque el número parece plausible.

2.2 Particionar por varias columnas

SELECT o.ship_country, extract(year FROM o.order_date)::int AS anio,
       count(*) AS pedidos,
       sum(count(*)) OVER (PARTITION BY o.ship_country) AS pedidos_pais
FROM orders o WHERE o.ship_country IN ('Brazil','UK')
GROUP BY o.ship_country, extract(year FROM o.order_date) ORDER BY 1, 2;

 ship_country | anio | pedidos | pedidos_pais
--------------+------+---------+--------------
 Brazil       | 1996 |      13 |           83
 Brazil       | 1997 |      42 |           83
 Brazil       | 1998 |      28 |           83
 UK           | 1996 |      10 |           56
 UK           | 1997 |      30 |           56
 UK           | 1998 |      16 |           56

El GROUP BY agrupa por país y año; la ventana particiona solo por país. Así conviven el detalle anual y el total del país en la misma fila. Es el patrón de "detalle y su subtotal al lado", y sin ventanas exige una subconsulta.

3. ORDER BY: recorrer la ventana

Aquí está el giro del tema. Añadir ORDER BY dentro de OVER cambia el resultado de la función.

SELECT p.product_name, p.unit_price,
       sum(p.unit_price) OVER ()                      AS sin_order,
       sum(p.unit_price) OVER (ORDER BY p.unit_price) AS con_order
FROM products p WHERE p.category_id = 7 ORDER BY p.unit_price;

          product_name           | unit_price | sin_order | con_order
---------------------------------+------------+-----------+-----------
 Longlife Tofu                   |      10.00 |    161.85 |     10.00
 Tofu                            |      23.25 |    161.85 |     33.25
 Uncle Bob's Organic Dried Pears |      30.00 |    161.85 |     63.25
 Rössle Sauerkraut               |      45.60 |    161.85 |    108.85
 Manjimup Dried Apples           |      53.00 |    161.85 |    161.85

Sin ORDER BY: el total, repetido. Con ORDER BY: el acumulado. La última fila coincide, porque al llegar al final ya ha sumado todo.

El motivo: sin ORDER BY, la ventana de cada fila es la partición entera. Con ORDER BY, la ventana por defecto pasa a ser "desde el principio hasta la fila actual". La función ya no ve el futuro.

Eso convierte cualquier agregación en su versión acumulada: sum en running total, count en cuántas van, max en el máximo hasta ahora.

3.1 El acumulado que se usa de verdad

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

    mes     | mes_facturacion | acumulado
------------+-----------------+-----------
 1997-01-01 |        61258.07 |  61258.07
 1997-02-01 |        38483.64 |  99741.71
 1997-03-01 |        38547.22 | 138288.93
 1997-04-01 |        53032.95 | 191321.88
 1997-05-01 |        53781.29 | 245103.17
 1997-06-01 |        36362.80 | 281465.97

Es el gráfico de "objetivo acumulado del año" de cualquier informe comercial, en tres líneas.

Comprobación: los 138 288,93 de marzo son exactamente la facturación de 1997-Q1 que salió en cte-with. El acumulado de un trimestre tiene que dar el trimestre.

4. El gotcha que hay que ver antes de seguir

Repite el acumulado sobre una categoría con precios repetidos:

SELECT p.product_name, p.unit_price,
       sum(p.unit_price) OVER (ORDER BY p.unit_price) AS acum
FROM products p WHERE p.category_id = 1 ORDER BY p.unit_price;

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

Los cuatro productos de 18,00 muestran el mismo acumulado: 127,25. No es 73,25 / 91,25 / 109,25 / 127,25 como esperarías de un acumulado fila a fila. Los dos de 14,00 hacen lo mismo: los dos marcan 40,25.

Es correcto, y es la causa número uno de "mi acumulado sale mal". Con ORDER BY y sin más, la ventana por defecto es RANGE, que trabaja por valor y no por fila: todas las filas empatadas comparten ventana, así que la función salta el bloque entero de golpe.

Si quieres el acumulado fila a fila hay que decirlo:

sum(p.unit_price) OVER (ORDER BY p.unit_price ROWS UNBOUNDED PRECEDING)

Ese ROWS es el tema siguiente, el-marco-rows-vs-range, y esta consulta es el motivo de que exista. Por ahora basta con la regla: si el ORDER BY de la ventana tiene empates, RANGE los agrupa. Con una fecha única o un order_id no se nota nunca; con un precio o una categoría, sí.

5. Errores comunes

1. Creer que el ORDER BY de dentro ordena la salida. No. Ordena el recorrido de la ventana. Para ordenar el resultado hace falta el ORDER BY final de la consulta, y son independientes: puedes acumular por fecha y presentar por importe.

2. Ponerlo dentro y esperar el total. Si querías el total y sale un acumulado, sobra el ORDER BY.

3. Usar GROUP BY <posición> con una ventana que ordena por la misma expresión. sql ... OVER (ORDER BY date_trunc('month', o.order_date)) ... GROUP BY 1 ERROR: column "o.order_date" must appear in the GROUP BY clause GROUP BY 1 funciona en el SELECT pero no vale como referencia para el ORDER BY de una ventana. PostgreSQL no reconoce que la expresión de la ventana es la misma que la agrupada. Se arregla escribiendo el GROUP BY con la expresión completa: GROUP BY date_trunc('month', o.order_date).

4. Suponer que PARTITION BY reduce filas. No reduce nada. Si el resultado tiene más filas de las esperadas, el problema está en los JOIN, no en la ventana.

5. Olvidar que el WHERE va antes. La media de la categoría se calcula sobre los productos que pasaron el filtro. Si el informe dice "media de la categoría", tiene que ser la de la categoría completa: entonces la ventana va en un CTE antes de filtrar.

6. Repetir la misma definición de ventana en cinco columnas. Funciona, pero se puede nombrar una vez: sql SELECT product_name, avg(unit_price) OVER w, max(unit_price) OVER w, min(unit_price) OVER w FROM products WINDOW w AS (PARTITION BY category_id); La cláusula WINDOW va después del HAVING y antes del ORDER BY. Con tres o más columnas compartiendo ventana, merece la pena.

6. Diferencias con SQL Server

Porta sin cambios, salvo la cláusula WINDOW si el servidor destino es anterior a SQL Server 2022: hay que repetir la definición en cada columna.

7. Ejercicios

1. Cada producto de Condiments con el precio máximo de su categoría y qué porcentaje representa de ese máximo.
2. Cada pedido con cuántos pedidos ha hecho ese cliente en total, sin subconsultas.
3. Facturación por país y año, con el total del país al lado y qué porcentaje aporta cada año dentro de su país. Para Germany y Brazil.
4. El acumulado de facturación mensual de 1998.
5. Explica por qué las cuatro cervezas de 18,00 comparten acumulado, y escribe la versión que acumula fila a fila.

8. Pregunta de negocio

Marketing quiere saber si el catálogo está equilibrado: dentro de cada categoría, qué porcentaje de la facturación aporta cada producto.

La pregunta real que hay detrás es cuánto depende cada categoría de un solo producto.