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.

Diagrama: tabla original (Zelda-Nintendo-20, Mario-Nintendo-10, GTA V-Rockstar-80, RDR 2-Rockstar-70, Dark Souls-From-60) → PARTITION BY developer → tabla con ROW_NUMBER() por partición.

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;

En nuestra tabla hay 3 juegos del año 2011. Como antes de ellos hay 4 juegos de años anteriores, los tres empatan en la posición 5:

• RANK() les asigna 5, 5, 5 y salta al 8 para el siguiente año (deja huecos por los empates).
• DENSE_RANK() les asigna 5, 5, 5 y continúa con 6 (sin huecos).

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

Nombre de la Tabla: videogames

id	title	developer	year	playtime_hours
1	The Legend of Zelda	Nintendo	1986	20
2	Super Mario Bros.	Nintendo	1985	10
3	Final Fantasy VII	Square Enix	1997	40
4	The Witcher 3	CD Projekt Red	2015	100
5	Minecraft	Mojang Studios	2011	9999
6	Grand Theft Auto V	Rockstar Games	2013	80
7	Dark Souls	FromSoftware	2011	60
8	Portal 2	Valve	2011	8
9	Red Dead Redemption 2	Rockstar Games	2018	70
10	The Last of Us	Naughty Dog	2013	15
11	Halo: Combat Evolved	Bungie	2001	10
12	God of War	Santa Monica Studio	2018	25