Funciones de ventana: ROW_NUMBER, RANK, SUM OVER

Calcula rangos, totales acumulados y comparaciones dentro de un grupo sin colapsar las filas gracias a OVER y PARTITION BY.

10 min · 3 preguntas

GROUP BY colapsa las filas en grupos; una función de ventana calcula una agregación o un rango conservando todas las filas. Es la herramienta para el top N por grupo, los totales acumulados y las comparaciones con el promedio de tu propio equipo. Disponible en SQLite desde la versión 3.25 (2018). Carga el conjunto de datos.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

Tres funciones de ranking, tres comportamientos frente a los empates: ROW_NUMBER numera sin duplicados (desempate arbitrario), RANK da a los empates el mismo rango y luego salta (1, 2, 2, 4), DENSE_RANK no salta (1, 2, 2, 3). La cláusula WINDOW evita repetir la definición de la ventana (compatible con SQLite y PostgreSQL, no con SQL Server).

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

PARTITION BY reinicia el cálculo para cada subconjunto: una ventana por departamento, sin colapsar nada. Combinado con una CTE, esto da el patrón de entrevista: los N mejor pagados de cada departamento. La CTE es obligatoria porque una función de ventana se evalúa después del WHERE: no puedes filtrar sobre rn directamente.

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

Las agregaciones clásicas se convierten en ventanas con OVER: SUM(salary) OVER (ORDER BY ...) produce un total acumulado fila por fila. Especifica el marco con ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para un total acumulado estrictamente fila por fila.

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

Prueba de conocimientos

Comprueba que has retenido los puntos clave de esta lección.

1. Con los salarios 5200, 4800, 4800, 4100, ¿qué rango asigna RANK() a 4100?
   • 3
   • 4
   • 2
2. ¿Por qué necesitas una CTE para filtrar sobre ROW_NUMBER()?
   • OVER se evalúa después de WHERE, así que está prohibido dentro de él
   • Una CTE siempre es más eficiente
   • ROW_NUMBER solo funciona dentro de un WITH
3. ¿Qué hace PARTITION BY dept en una cláusula OVER?
   • Ordena el resultado final por departamento
   • Reinicia el cálculo de la ventana para cada departamento
   • Fusiona las filas en una sola por departamento