Con GROUP BY puedes saber cuánto ha gastado cada cliente, pero pierdes el detalle: cada grupo queda reducido a una fila. Las funciones de ventana hacen el mismo cálculo sin juntar las filas. Cada pedido sigue apareciendo, y a su lado puedes poner el total de su cliente, la media de su categoría o su posición en un ranking.
Una función se convierte en función de ventana cuando le añades OVER:
funcion(columna) OVER (
[PARTITION BY columnas]
[ORDER BY columnas]
)
OVER ()vacío: la ventana es todo el resultado.PARTITION BY: divide las filas en grupos y calcula la función en cada uno por separado.ORDER BY: establece un orden dentro de cada grupo. Lo necesitan las funciones de ranking,LAGyLEADy los totales acumulados.
OVER (): un total al lado de cada fila
El total de ventas de la tienda junto a cada pedido, y el porcentaje que representa cada uno:
| producto | totalPrecio | ventasTotales | porcentaje |
|---|---|---|---|
| Xiami Mi 11 | 4304,25 | 17251,60 | 24,9 |
| Echo DOT 4 | 2999,50 | 17251,60 | 17,4 |
| MacBook Pro M1 | 2449,50 | 17251,60 | 14,2 |
| Play Station 5 | 2199,80 | 17251,60 | 12,8 |
| Echo DOT 3 | 1999,50 | 17251,60 | 11,6 |
| Horno Balay | 1399,10 | 17251,60 | 8,1 |
| Xbox series X | 999,98 | 17251,60 | 5,8 |
| Nintendo Switch | 899,97 | 17251,60 | 5,2 |
Con GROUP BY esto necesitaría una subconsulta para calcular el total y otra consulta para cruzarlo con cada fila.
PARTITION BY: el cálculo por grupos
Ahora el total y el número de pedidos de cada cliente, sin dejar de ver cada pedido:
| nombre | producto | totalPrecio | pedidosCliente | gastoCliente |
|---|---|---|---|---|
| Alejandro | Nintendo Switch | 899,97 | 1 | 899,97 |
| Ana | Xiami Mi 11 | 4304,25 | 1 | 4304,25 |
| Fernando | MacBook Pro M1 | 2449,50 | 1 | 2449,50 |
| Luis | Echo DOT 4 | 2999,50 | 2 | 4999,00 |
| Luis | Echo DOT 3 | 1999,50 | 2 | 4999,00 |
| María | Play Station 5 | 2199,80 | 2 | 3199,78 |
| María | Xbox series X | 999,98 | 2 | 3199,78 |
María y Luis tienen dos filas cada uno, y en las dos aparece su gasto total. Compáralo con el equivalente agrupado, que solo devuelve una fila por cliente:
| nombre | pedidosCliente | gastoCliente |
|---|---|---|
| Alejandro | 1 | 899,97 |
| Ana | 1 | 4304,25 |
| Fernando | 1 | 2449,50 |
| Luis | 2 | 4999,00 |
| María | 2 | 3199,78 |
Comparar cada fila con su grupo
Lo interesante de tener el valor del grupo al lado es poder operar con él. Por ejemplo, cuánto se aleja el precio de cada producto de la media de su descripción:
| descripcionProducto | producto | precio | precioMedio | diferencia |
|---|---|---|---|---|
| Altavoz Alexa | Echo DOT 3 | 39,99 | 49,99 | -10,00 |
| Altavoz Alexa | Echo DOT 4 | 59,99 | 49,99 | 10,00 |
| Consola Nintendo | Nintendo Switch | 299,99 | 299,99 | 0,00 |
| Consola Sony | Play Station 5 | 549,95 | 549,95 | 0,00 |
| Consola Xbox | Xbox series X | 499,99 | 499,99 | 0,00 |
Los dos altavoces Alexa comparten descripción, así que su media es la de ambos (49,99 €) y cada uno queda 10 € por encima o por debajo. Cada consola tiene una descripción distinta, así que es su propio grupo y la diferencia es 0.
Qué funciones admiten OVER
| Tipo | Funciones |
|---|---|
| Agregadas | SUM, COUNT, AVG, MIN, MAX |
| Ranking | ROW_NUMBER, RANK, DENSE_RANK, NTILE |
| Desplazamiento | LAG, LEAD, FIRST_VALUE, LAST_VALUE |
Dónde se pueden usar
Las funciones de ventana se calculan después del WHERE, el GROUP BY y el HAVING, justo antes del ORDER BY. Por eso:
- Se pueden usar en el
SELECTy en elORDER BY. - No se pueden usar en el
WHERE. Para filtrar por su resultado, calcúlalas en una CTE y filtra fuera, como verás en la siguiente lección.
Practica
- Añade al primer ejemplo una columna con el pedido más caro de toda la tienda (
MAX ... OVER ()). - Calcula, para cada pedido, qué porcentaje supone sobre el gasto total de su cliente.
- ¿Qué pasa si en el segundo ejemplo quitas el
INNER JOINy usas soloPedidos? ¿Cómo aparece el pedido sin cliente?
