Un total acumulado suma, en cada fila, todo lo anterior más la fila actual: las ventas acumuladas del año, el saldo de una cuenta tras cada movimiento, las unidades vendidas hasta la fecha… Antes de las funciones de ventana había que resolverlo con subconsultas correlacionadas, lentas y difíciles de leer. Hoy basta con SUM(...) OVER (ORDER BY ...).
Total acumulado
El importe acumulado de la tienda pedido a pedido, por orden de fecha:
| fechaPedido | producto | totalPrecio | acumulado |
|---|---|---|---|
| 2022-01-21 | Xbox series X | 999,98 | 999,98 |
| 2022-06-20 | MacBook Pro M1 | 2449,50 | 3449,48 |
| 2022-07-09 | Xiami Mi 11 | 4304,25 | 7753,73 |
| 2022-07-18 | Play Station 5 | 2199,80 | 9953,53 |
| 2022-09-01 | Echo DOT 3 | 1999,50 | 11953,03 |
| 2022-09-01 | Echo DOT 4 | 2999,50 | 14952,53 |
| 2022-09-05 | Nintendo Switch | 899,97 | 15852,50 |
| 2022-09-10 | Horno Balay | 1399,10 | 17251,60 |
La última fila coincide con el total de ventas, 17.251,60 €. La línea ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW es el marco de la ventana: le dice al motor qué filas sumar en cada momento. Aquí, desde la primera (UNBOUNDED PRECEDING) hasta la actual (CURRENT ROW).
El marco de la ventana
El marco se define con ROWS BETWEEN inicio AND fin, donde cada extremo puede ser:
| Extremo | Significado |
|---|---|
UNBOUNDED PRECEDING |
La primera fila de la partición |
n PRECEDING |
n filas antes de la actual |
CURRENT ROW |
La fila actual |
n FOLLOWING |
n filas después de la actual |
UNBOUNDED FOLLOWING |
La última fila de la partición |
ROWS frente a RANGE: cuidado con los empates
Si escribes solo ORDER BY sin marco, el motor usa por defecto RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. La diferencia con ROWS aparece con los empates: RANGE trata todas las filas con el mismo valor de ordenación como una sola, y les da a todas el mismo acumulado.
| fechaPedido | producto | totalPrecio | conRows | conRange |
|---|---|---|---|---|
| 2022-07-09 | Xiami Mi 11 | 4304,25 | 4304,25 | 4304,25 |
| 2022-07-18 | Play Station 5 | 2199,80 | 6504,05 | 6504,05 |
| 2022-09-01 | Echo DOT 3 | 1999,50 | 8503,55 | 11503,05 |
| 2022-09-01 | Echo DOT 4 | 2999,50 | 11503,05 | 11503,05 |
| 2022-09-05 | Nintendo Switch | 899,97 | 12403,02 | 12403,02 |
| 2022-09-10 | Horno Balay | 1399,10 | 13802,12 | 13802,12 |
Los dos Echo DOT son del 1 de septiembre. Con ROWS el acumulado sube en dos pasos; con RANGE los dos reciben ya la suma de ambos. Ninguno es incorrecto, pero casi siempre lo que se espera es ROWS, así que conviene escribir el marco explícitamente.
ROWS además es más rápido que RANGE, que necesita guardar el resultado intermedio en disco. Es otra razón para escribir siempre el marco.Acumulado por cliente
Con PARTITION BY el acumulado vuelve a cero en cada grupo:
| nombre | fechaPedido | producto | totalPrecio | acumuladoCliente |
|---|---|---|---|---|
| Alejandro | 2022-09-05 | Nintendo Switch | 899,97 | 899,97 |
| Ana | 2022-07-09 | Xiami Mi 11 | 4304,25 | 4304,25 |
| Fernando | 2022-06-20 | MacBook Pro M1 | 2449,50 | 2449,50 |
| Luis | 2022-09-01 | Echo DOT 3 | 1999,50 | 1999,50 |
| Luis | 2022-09-01 | Echo DOT 4 | 2999,50 | 4999,00 |
| María | 2022-01-21 | Xbox series X | 999,98 | 999,98 |
| María | 2022-07-18 | Play Station 5 | 2199,80 | 3199,78 |
Media móvil
Cambiando el marco a “las dos filas anteriores y la actual” se obtiene una media móvil de tres periodos, que suaviza los picos y se usa mucho para ver tendencias:
| fechaPedido | producto | totalPrecio | mediaMovil3 |
|---|---|---|---|
| 2022-01-21 | Xbox series X | 999,98 | 999,98 |
| 2022-06-20 | MacBook Pro M1 | 2449,50 | 1724,74 |
| 2022-07-09 | Xiami Mi 11 | 4304,25 | 2584,58 |
| 2022-07-18 | Play Station 5 | 2199,80 | 2984,52 |
| 2022-09-01 | Echo DOT 3 | 1999,50 | 2834,52 |
| 2022-09-01 | Echo DOT 4 | 2999,50 | 2399,60 |
| 2022-09-05 | Nintendo Switch | 899,97 | 1966,32 |
| 2022-09-10 | Horno Balay | 1399,10 | 1766,19 |
En las dos primeras filas no hay todavía tres pedidos, así que la media se calcula con los que hay: uno y dos.
Porcentaje acumulado
Juntando un acumulado con un total (OVER ()) sale el porcentaje acumulado, la base de un análisis de Pareto (¿qué pedidos suman el 80 % de las ventas?):
| producto | totalPrecio | porcentajeAcumulado |
|---|---|---|
| Xiami Mi 11 | 4304,25 | 24,9 |
| Echo DOT 4 | 2999,50 | 42,3 |
| MacBook Pro M1 | 2449,50 | 56,5 |
| Play Station 5 | 2199,80 | 69,3 |
| Echo DOT 3 | 1999,50 | 80,9 |
| Horno Balay | 1399,10 | 89,0 |
| Xbox series X | 999,98 | 94,8 |
| Nintendo Switch | 899,97 | 100,0 |
Los cinco pedidos más grandes ya suman más del 80 % de lo vendido.
Practica
- Calcula las unidades acumuladas (
numeroProductos) por orden de fecha. - Cambia la media móvil para que use la fila anterior, la actual y la siguiente.
- Usa
LAST_VALUEcon el marcoROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGpara mostrar en cada fila el último producto pedido.
