PARTITION BY y media móvil

PARTITION BY reparte las filas en grupos y calcula la función de ventana dentro de cada grupo, sin juntar las filas. Un marco limita cuántas filas vecinas entran en el cálculo, por ejemplo para una media móvil.

Sirve para dos preguntas habituales: quién es el mejor de cada grupo y cuánto se alquiló de media los últimos tres días.

PARTITION BY class_group hace que la función empiece de nuevo en cada grupo. El primer puesto de la clase A y el de la clase B son dos puestos distintos.

A diferencia de GROUP BY, todas las filas siguen ahí. Sirve con RANK, con ROW_NUMBER y con agregados como AVG.

-- La media de cada grupo, junto a cada alumno.
select student, class_group, mark,
  round(avg(mark) over (partition by class_group), 1) as group_avg
from marks
order by student_id;
-- Cambia avg por max y pulsa Run.

Para quedarte con los dos mejores de cada grupo, numera con ROW_NUMBER y PARTITION BY. Después filtra n <= 2 en la consulta de fuera.

Añade un segundo criterio al ORDER BY, como student_id. Así el desempate es siempre el mismo.

DuckDB tiene también QUALIFY, que filtra por una ventana sin escribir la subconsulta.

-- Los dos mejores de cada grupo, con una subconsulta.
select student, class_group, mark
from (
  select *,
    row_number() over (partition by class_group order by mark desc, student_id) as n
  from marks
)
where n <= 2
order by class_group, n;
-- Cambia 2 por 1 y pulsa Run.

-- Lo mismo con QUALIFY, sin subconsulta.
select student, class_group, mark
from marks
qualify row_number() over (partition by class_group order by mark desc, student_id) <= 2
order by class_group, mark desc, student_id;
-- Cambia desc por asc para ver los dos peores.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW toma la fila actual y las dos anteriores. Con ese marco, AVG da una media móvil de tres días.

Al principio no hay tres filas todavía. El marco usa las que existen, y por eso el primer valor es la media de una sola fila.

-- La media de cada día y los dos anteriores.
select day, rides,
  round(avg(rides) over (order by day rows between 2 preceding and current row), 1) as moving_avg
from daily_rides
order by day;
-- Cambia 2 preceding por 1 preceding y pulsa Run.