PARTITION BY: agregados sin colapsar filas
IntermedioUn agregado con OVER (PARTITION BY ...) calcula el valor del grupo y lo repite en cada fila, sin juntar las filas como hace GROUP BY. Así cada fila puede compararse con el promedio o el total de su grupo en la misma consulta. OVER () vacío usa todas las filas del resultado.
Sintaxis
SELECT
columna,
avg(valor) OVER (PARTITION BY grupo) AS promedio_del_grupo,
valor / sum(valor) OVER (PARTITION BY grupo) AS participacion
FROM tabla;Ejemplo
SELECT
a.genre AS genero,
a.name AS artista,
a.monthly_listeners AS oyentes,
round(avg(a.monthly_listeners) OVER (PARTITION BY a.genre)) AS promedio_genero,
round(100.0 * a.monthly_listeners / sum(a.monthly_listeners) OVER (PARTITION BY a.genre), 1) AS pct_del_genero
FROM artists AS a
WHERE a.genre IN ('forró', 'cueca urbana')
ORDER BY a.genre, a.monthly_listeners DESC, a.id;Resultado
| genero | artista | oyentes | promedio_genero | pct_del_genero |
|---|---|---|---|---|
| cueca urbana | Los Cardos de Cobre | 8057 | 4160 | 38.7 |
| cueca urbana | Los Colibríes de Barro | 3762 | 4160 | 18.1 |
| cueca urbana | Sierra Lejana | 3478 | 4160 | 16.7 |
| cueca urbana | Los Cardos del Alba | 3435 | 4160 | 16.5 |
| cueca urbana | Cielo Naranja | 2067 | 4160 | 9.9 |
| forró | Beira Dourado | 3694 | 2571 | 35.9 |
| forró | Estrada Perdida | 3190 | 2571 | 31.0 |
| forró | Blocoinho | 1706 | 2571 | 16.6 |
| forró | Janelas Dourado | 1693 | 2571 | 16.5 |
Cómo leerlo
Cada artista conserva su fila y al lado aparecen el promedio y su porcentaje dentro de su género. En cueca urbana un solo artista reúne el 38.7 % de los oyentes y sube el promedio a 4160: los otros cuatro quedan por debajo. Es una señal para mirar también la mediana antes de hablar del «artista promedio».
Error común
Así no
SELECT a.genre, a.name, a.monthly_listeners, avg(a.monthly_listeners)
FROM artists AS a
GROUP BY a.genre;Intentar comparar cada fila con su grupo usando GROUP BY. GROUP BY deja una fila por género, así que no puede mostrar el detalle de cada artista, y PostgreSQL rechaza a.name y a.monthly_listeners porque no están agrupados. La salida es un agregado de ventana, o bien agrupar en una CTE y unir el resultado con la tabla original.
En otros motores: PostgreSQL
PostgreSQL
No acepta
DISTINCTdentro de un agregado de ventana:count(DISTINCT user_id) OVER (...)da error. Hay que contar los distintos conGROUP BYen una CTE y unir ese resultado con la tabla.
Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.