Funciones de ventana

Las funciones de ventana (window functions) realizan cálculos sobre un conjunto de filas relacionadas con la fila actual, sin colapsar el resultado como hacen GROUP BY y las funciones de agregación. Cada fila mantiene su identidad y además obtiene un valor calculado.

La cláusula OVER()

Toda función de ventana usa OVER() para definir la “ventana” de filas sobre la que opera:

SELECT title, playtime_hours,
  SUM(playtime_hours) OVER() AS total_horas
FROM videogames;

Con OVER() vacío, la ventana es toda la tabla. Cada fila muestra sus propias horas y además el total global. A diferencia de SUM con GROUP BY, aquí no se agrupan las filas: todas aparecen con la columna extra.

PARTITION BY: dividir en grupos

PARTITION BY divide las filas en particiones (grupos) y aplica la función dentro de cada grupo:

SELECT title, developer, playtime_hours,
  SUM(playtime_hours) OVER(PARTITION BY developer) AS horas_del_dev
FROM videogames;

Cada fila muestra el total de horas de su desarrollador, sin perder el detalle de cada juego.

ORDER BY dentro de OVER: funciones de ranking

Cuando añades ORDER BY dentro de OVER(), puedes usar funciones que asignan un número a cada fila según su posición:

ROW_NUMBER()

Asigna un número secuencial único a cada fila:

SELECT title, playtime_hours,
  ROW_NUMBER() OVER(ORDER BY playtime_hours DESC) AS posicion
FROM videogames;

RANK() y DENSE_RANK()

Cuando hay empates, RANK() deja huecos en la numeración y DENSE_RANK() no:

SELECT title, year,
  RANK() OVER(ORDER BY year) AS rank,
  DENSE_RANK() OVER(ORDER BY year) AS dense_rank
FROM videogames;

Si hay 3 juegos del año 2011:

• RANK() asigna 1, 1, 1 al empate y salta al 4 para el siguiente.
• DENSE_RANK() asigna 1, 1, 1 al empate y continúa con 2.

Ranking por grupos

Combinando PARTITION BY con ORDER BY puedes crear rankings dentro de cada grupo:

SELECT title, developer, playtime_hours,
  ROW_NUMBER() OVER(
    PARTITION BY developer
    ORDER BY playtime_hours DESC
  ) AS rank_en_dev
FROM videogames;

Esto numera los juegos de cada desarrollador por horas de juego: el juego más largo de Nintendo será 1, el siguiente 2, etc. Independiente del ranking de Rockstar.

LAG() y LEAD(): acceder a filas vecinas

LAG() accede a la fila anterior y LEAD() a la siguiente:

SELECT title, year,
  LAG(title) OVER(ORDER BY year) AS juego_anterior,
  LEAD(title) OVER(ORDER BY year) AS juego_siguiente
FROM videogames;

Muy útil para calcular diferencias entre filas consecutivas, como la diferencia de años entre juegos sucesivos:

SELECT title, year,
  year - LAG(year) OVER(ORDER BY year) AS anos_desde_anterior
FROM videogames;

Agregaciones como funciones de ventana

Todas las funciones de agregación que ya conoces (SUM, AVG, COUNT, MIN, MAX) pueden usarse como funciones de ventana:

SELECT title, playtime_hours,
  AVG(playtime_hours) OVER() AS promedio_global,
  playtime_hours - AVG(playtime_hours) OVER() AS diferencia
FROM videogames;

Cada fila muestra cuánto se desvía del promedio, algo imposible de hacer con un simple GROUP BY.

Nuestra tabla