Clasificar filas y calcular totales acumulados con

Visión General

Las funciones de ventana son una de las capacidades más potentes de SQL. Permiten calcular valores a través de un conjunto de filas relacionadas con la fila actual sin colapsar el resultado como GROUP BY. El ranking, totales acumulados y promedios móviles se vuelven directos, y a menudo reemplazan self-joins lentos o loops en la capa de aplicación.

Cuándo Usar

• For alternatives, see SQL Window Functions — Complete Guide.

Usa este recurso cuando:

• Necesites clasificar filas dentro de grupos (top-N por categoría).
• Quieras totales acumulados o promedios móviles sin subconsultas.
• Estés construyendo tablas de clasificación, reportes de ventas o paginación con empates.
• Necesites comparar cada fila con la anterior o siguiente.

Solución

Ranking y totales acumulados

SELECT
  department,
  employee,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
  SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running_total,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;

Top-N filas por categoría

-- Top 3 empleados mejor pagados por departamento
WITH ranked AS (
  SELECT
    department,
    employee,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, employee, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, salary DESC;

Promedio móvil con marco de ventana explícito

-- Promedio móvil de 7 días de ingresos diarios
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_revenue
ORDER BY date;

Crecimiento interanual con LAG

-- Comparar ingresos de cada mes con el mismo mes del año anterior
SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
  revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_delta,
  ROUND(
    (revenue - LAG(revenue, 12) OVER (ORDER BY month)) /
     NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 2
  ) AS yoy_pct_change
FROM monthly_revenue
ORDER BY month;

Ranking de percentiles con NTILE y PERCENT_RANK

-- Dividir clientes en 4 cuartiles por gasto total
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile,
  PERCENT_RANK() OVER (ORDER BY total_spend DESC) AS pct_rank,
  CUME_DIST() OVER (ORDER BY total_spend DESC) AS cumulative_dist
FROM customer_totals;

Primer y último valor por partición

-- Salario más alto y más bajo por departamento en cada fila
SELECT
  department,
  employee,
  salary,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS highest_in_dept,
  LAST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_in_dept
FROM employees;

Desduplicar filas con ROW_NUMBER

-- Mantener solo el registro más reciente por usuario
WITH deduped AS (
  SELECT
    user_id,
    email,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_records
)
SELECT user_id, email, updated_at
FROM deduped
WHERE rn = 1;

Explicación

ROW_NUMBER() asigna un número único a cada fila de la partición. RANK() da el mismo ranking a empates, dejando huecos. SUM() OVER calcula un total acumulado porque el marco de ventana por defecto abarca filas desde el inicio de la partición hasta la fila actual. LAG() devuelve el valor de la fila anterior, útil para deltas. La cláusula PARTITION BY reinicia cálculos por departamento, y ORDER BY controla la secuencia dentro de la partición.

Sintaxis de marco de ventana

La sintaxis completa de marco de ventana da control preciso sobre qué filas se incluyen:

-- Sintaxis: ROWS BETWEEN <inicio> AND <fin>
-- Inicio: UNBOUNDED PRECEDING | N PRECEDING | CURRENT ROW
-- Fin: CURRENT ROW | N FOLLOWING | UNBOUNDED FOLLOWING

-- Total acumulado: inicio hasta fila actual
SUM(amount) OVER (ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Promedio de 3 filas anteriores
AVG(amount) OVER (ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Promedio centrado de 5 filas
AVG(amount) OVER (ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

-- Total de toda la partición
SUM(amount) OVER (PARTITION BY category
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Variantes

Función	Caso de uso	Comportamiento
ROW_NUMBER	Ranking único	Sin huecos, sin empates
RANK	Ranking con empates	Huecos tras empates
DENSE_RANK	Ranking con empates	Sin huecos
SUM OVER	Totales acumulados	Acumulativo dentro de partición
AVG OVER	Promedios móviles	Marco configurable
LAG/LEAD	Comparar filas adyacentes	Offset de N filas
FIRST_VALUE/LAST_VALUE	Extremos por partición	Primero o último del marco
NTILE	Bucketing	Divide filas en N buckets
PERCENT_RANK	Ranking de percentil	Rank relativo 0.0 a 1.0
CUME_DIST	Distribución acumulada	Fracción de filas en o por debajo

Lo que funciona

1. Indexa columnas de partición y orden. La base de datos aún necesita ordenar; los índices ayudan.
2. Usa ROW_NUMBER para top-N cuando los empates no importan. Usa RANK o DENSE_RANK cuando los empates importan.
3. Los marcos de ventana importan. Agrega ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explícitamente para claridad.
4. Evita anidar funciones de ventana. Algunas bases de datos no permiten SUM() OVER (ORDER BY ROW_NUMBER() OVER...).
5. Materializa reportes complejos. Para dashboards, pre-agrega resultados de ventana en una tabla resumen.
6. Usa CTEs para filtrar resultados de ventana. Envuelve la consulta y filtra en una consulta externa ya que las ventanas no pueden aparecer en WHERE.
7. Especifica NULLS FIRST o NULLS LAST. Diferentes bases de datos ordenan NULLs de forma distinta; sé explícito.

Errores Comunes

1. Olvidar PARTITION BY. Sin ella, la ventana cubre toda la tabla, mezclando departamentos.
2. Confundir RANK y ROW_NUMBER. Los empates pueden producir resultados inesperados si eliges la función incorrecta.
3. Usar funciones de ventana en WHERE. La mayoría de bases de datos requieren una subconsulta porque las ventanas se ejecutan después del filtrado.
4. Dirección de ORDER BY incorrecta. El orden descendente es común para rankings; ascendente para totales acumulados.
5. Ignorar NULLs en el ordenamiento. Los NULLs se ordenan primero o último según la base de datos; sé explícito con NULLS FIRST/NULLS LAST.
6. LAST_VALUE sin marco completo. LAST_VALUE por defecto usa la fila actual, no la última de la partición. Siempre especifica ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
7. Usar RANGE en vez de ROWS sin intención. RANGE incluye pares (filas con el mismo valor de orden), lo que puede producir resultados inesperados en totales acumulados.

Notas de Producción

• Despliega gradualmente usando canary o blue-green para detectar regresiones temprano.
• Configura alertas para errores, latencia p99 y tasa de fallos antes de habilitar en producción.
• Documenta el rollback en el runbook; prueba el procedimiento en staging al menos una vez por trimestre.
• Revisa logs estructurados con correlation IDs para trazar requests end-to-end en incidentes.

Puntos Clave

• Aplica clasificar filas y calcular totales acumulados con cuando necesites una solución práctica para tu caso de uso.
• Monitorea el rendimiento después de implementar; mide latencia, errores y uso de recursos antes y después.
• Revisa la sección de Troubleshooting ante errores comunes; la mayoría tienen causa raíz documentada con solución.
• Mantén dependencias actualizadas y ejecuta tests en CI para prevenir regresiones en producción.

Comparación de Funciones de Ranking

Escenario	ROW_NUMBER	RANK	DENSE_RANK
Salarios: 100, 90, 90, 80	1, 2, 3, 4	1, 2, 2, 4	1, 2, 2, 3
Empates mismo número	No	Sí	Sí
Huecos tras empates	Sin huecos	Huecos	Sin huecos
Mejor para	Desduplicación, top-N	Ranking competitivo	Ranking denso

-- Ver las tres funciones lado a lado
SELECT
  employee,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC;

Tips de Rendimiento

1. Agrega índices en columnas PARTITION BY + ORDER BY. Un índice compuesto en (department, salary DESC) permite a PostgreSQL evitar el sort.

2. Usa EXPLAIN para verificar avoidance de sort. Busca WindowAgg sin un nodo Sort precedente cuando hay índices disponibles.

-- Verificar si se evita el sort con un índice
EXPLAIN ANALYZE
SELECT
  department,
  employee,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

3. Limita particiones para tablas grandes. Si solo necesitas top-N para unos pocos departamentos, filtra primero en una subconsulta para reducir el volumen de sort.

4. Considera vistas materializadas para dashboards. Los resultados de funciones de ventana son costosos de recomputar en cada page load. Almacénalos en una vista materializada y refresca periódicamente.

CREATE MATERIALIZED VIEW dept_ranking AS
SELECT
  department,
  employee,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

-- Refrescar semanalmente
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_ranking;

Troubleshooting

• Query is slow after an index change: check execution plans and cardinality estimates. Rebuild statistics and verify the index is being used.
• Replication lag grows: Split large writes and consider parallel replication.
• Connections exhausted: review connection pool size, idle timeouts, and leaked connections.
• Backup takes too long: enable compression, incremental backups, and off-peak scheduling.
• Deadlocks in high concurrency: access tables and rows in a consistent order.

Errores Comunes en Producción

• Copiar el ejemplo sin adaptarlo a volúmenes y modos de fallo reales.
• Saltar tests de carga e inyección de errores antes del primer despliegue productivo.
• Codificar valores fijos que deberían ser configurables por entorno.
• Olvidar agregar logging y monitoreo en cada paso.
• Desplegar sin plan de rollback ni estrategia de backup probada.
• Asumir que el ejemplo mínimo escalará sin agregar caché o procesamiento por lotes.
• No documentar la versión y configuración usadas en producción.
• Dejar la receta sin cambios cuando evolucionan las dependencias o la escala.