row_number: numeración ordenada por grupo

Supongamos que necesitamos obtener los dos registros con mayor edad dentro de cada género en una tabla de personas. Una función de ventana asigna un número secuencial dentro de cada partición, lo que permite conservar más de un valor por grupo.

CREATE TABLE personas (
  id INT,
  edad INT,
  nombre STRING,
  genero STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INPATH '/ruta/datos/personas.dat' INTO TABLE personas;

SELECT id, edad, nombre, genero,
  row_number() OVER (PARTITION BY genero ORDER BY edad DESC) AS num_fila
FROM personas;

Para quedarse únicamente con las dos primeras filas de cada género, se envuelve la consulta anterior y se filtra por el número de fila:

SELECT id, edad, nombre, genero
FROM (
  SELECT id, edad, nombre, genero,
    row_number() OVER (PARTITION BY genero ORDER BY edad DESC) AS num_fila
  FROM personas
) sub
WHERE num_fila <= 2;

Diferencias entre row_number, rank y dense_rank

Las tres funciones ordenan las filas, pero manejan los empates de distinta forma:

• row_number(): asigna un número consecutivo único, aunque los valores ordenados sean iguales.
• rank(): asigna el mismo puesto a los empates y deja huecos en la numeración.
• dense_rank(): asigna el mismo puesto a los empates, pero no deja huecos.

SELECT puntuacion,
  row_number() OVER (ORDER BY puntuacion DESC) AS rn,
  rank()       OVER (ORDER BY puntuacion DESC) AS rk,
  dense_rank() OVER (ORDER BY puntuacion DESC) AS drk
FROM resultados;

Sumas con marcos de ventana

La cláusula de ventana define el rango de filas que participa en la agregación. Para calcular un total acumulado se indica un marco desde el inicio de la partición hasta la fila actual.

SELECT categoria, fecha, importe,
  sum(importe) OVER (
    PARTITION BY categoria
    ORDER BY fecha
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_acumulado
FROM ventas;

Promedio móvil entre filas adyacentes

El siguiente ejemplo calcula el salario medio del empleado actual junto con el anterior y el siguiente, siempre dentro del mismo departamento.

SELECT codigo, director, salario,
  row_number() OVER (PARTITION BY director ORDER BY codigo) AS rn,
  avg(salario) OVER (
    PARTITION BY director
    ORDER BY codigo
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS salario_medio
FROM empleados;

División en buckets con ntile

ntile(n) distribuye las filas de cada partición en n grupos de tamaño equilibrado. Es útil para crear cuartiles, quintiles u otros percentiles.

SELECT nombre, departamento, salario,
  ntile(4) OVER (PARTITION BY departamento ORDER BY salario DESC) AS cuartil
FROM empleados;

Acceso a filas adyacentes y extremas

lead y lag permiten leer la siguiente o la anterior fila del mismo grupo, mientras que first_value y last_value recuperan el primer y último valor del marco definido.

SELECT empleado, mes, ingreso,
  lag(ingreso, 1, 0) OVER (PARTITION BY empleado ORDER BY mes) AS ingreso_previo,
  lead(ingreso, 1, 0) OVER (PARTITION BY empleado ORDER BY mes) AS ingreso_siguiente,
  first_value(ingreso) OVER (PARTITION BY empleado ORDER BY mes) AS primer_ingreso,
  last_value(ingreso) OVER (
    PARTITION BY empleado
    ORDER BY mes
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS ultimo_ingreso
FROM nominas;

Si no se especifica un marco completo para last_value, la función devuelve el valor de la fila actual.

Distribución acumulada y ranking porcentual

cume_dist devuelve la posición relativa acumulada de cada fila dentro de su grupo, mientras que percent_rank indica el porcentaje de filas que quedan por debajo de la actual.

SELECT estudiante, nota,
  cume_dist()    OVER (PARTITION BY asignatura ORDER BY nota) AS dist_acum,
  percent_rank() OVER (PARTITION BY asignatura ORDER BY nota) AS pct_rango
FROM calificaciones;