Domine las funciones window de SQL: 40 herramientas esenciales para científicos de datos

Por qué las funciones window importan hoy

Aunque hay nuevas herramientas en el ecosistema de ciencia de datos, SQL sigue siendo el lenguaje principal para definir, transformar y analizar datos en la mayoría de organizaciones. Las funciones window (o funciones de ventana) permiten realizar cálculos complejos sobre conjuntos de filas sin reducir el número de registros devueltos. Eso las hace ideales para análisis de series temporales, segmentación, cálculo de indicadores acumulados y preparación de datos para modelos.

Para equipos en América Latina —donde muchas empresas trabajan con data warehouses centralizados, ERPs regionales o pipelines en la nube— dominar estas funciones reduce tiempo de ingeniería y facilita la comunicación entre analistas, cientistas de datos y decisores.

Ventana vs agregación clásica

Las funciones agregadas tradicionales (SUM(), AVG(), COUNT(), etc.) sin OVER() colapsan filas y devuelven un resumen por grupo. En cambio, esas mismas funciones usadas con OVER() realizan el cálculo sobre un conjunto definido de filas y retornan un resultado por cada fila original. Por ejemplo, puede ver la venta total del día al lado de cada transacción individual sin perder el detalle.

La cláusula mágica: OVER()

OVER() es el núcleo de las funciones window. Permite especificar qué filas forman la “ventana” para cada cálculo. Sus partes más comunes:

• PARTITION BY: define grupos independientes (por ejemplo, por cliente o tienda).
• ORDER BY: establece el orden dentro de cada partición —crucial para funciones que dependen de la secuencia.

Ejemplo básico:

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER(PARTITION BY customer_id ORDER BY order_date
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM orders;

Frames: ROWS, RANGE y GROUPS

El frame determina qué subconjunto relativo a la fila actual usa la función.

• ROWS: se basa en un número fijo de filas precedentes o siguientes. Útil para medias móviles por cantidad de observaciones.
• RANGE: se basa en valores lógicos del ORDER BY (por ejemplo, un rango de tiempo o valor). Incluye todas las filas cuyo valor de orden cae dentro del rango especificado.
• GROUPS: menos común, disponible en algunos dialectos avanzados; trabaja por grupos lógicos en lugar de por filas.

Elegir entre ROWS y RANGE cambia resultados en presencia de valores duplicados o saltos en la columna de orden.

Funciones de ranking y numeración

Estas funciones facilitan ordenar y asignar posiciones dentro de particiones:

• ROW_NUMBER(): asigna un número secuencial único a cada fila dentro de la partición.
• RANK(): asigna rangos dejando huecos cuando hay empates (ej.: dos primeros puestos -> siguiente es #3).
• DENSE_RANK(): similar a RANK() pero sin saltos en la numeración.
• NTILE(n): divide filas en n grupos lo más parejos posible (útil para deciles o cuartiles).
• PERCENT_RANK(): muestra la posición relativa entre 0 y 1 dentro de la partición.

Casos de uso: identificar el último registro por cliente, crear cohorts, seleccionar top-N por segmento.

Navegación y funciones posicionales

Permiten acceder a valores anteriores o siguientes relativos a la fila actual —muy útiles para cálculos de cambio y detección de eventos:

• LAG(columna, offset, default): devuelve el valor de una fila previa.
• LEAD(columna, offset, default): devuelve el valor de una fila posterior.
• FIRST_VALUE(): devuelve el valor de la primera fila en la ventana.
• LAST_VALUE(): devuelve el valor de la última fila en la ventana —atención: su comportamiento depende del frame; a menudo requiere definir explícitamente el frame para abarcar toda la partición si se desea el último valor completo.

Ejemplo de uso para variación diaria:

SELECT
  date,
  sales,
  sales - LAG(sales) OVER(ORDER BY date) AS sales_change
FROM daily_sales;

Agregados como ventanas

Funciones como SUM(), AVG(), COUNT() también se pueden usar con OVER() para obtener totales acumulados, promedios móviles, o proporciones relativas sin agrupar la consulta. Esto es habitual en reportes financieros o de ventas donde se necesita mantener el detalle transaccional y, al mismo tiempo, mostrar métricas agregadas.

Consideraciones prácticas y compatibilidad

• Dialectos: la sintaxis y disponibilidad de algunas opciones (por ejemplo, GROUPS) varían entre motores (Postgres, MySQL, SQL Server, Oracle, BigQuery, Snowflake). Verifiquen la documentación del proveedor.
• Rendimiento: las window functions pueden ser costosas en tablas muy grandes. Indices, particionamiento físico y ejecutar agregaciones previas en materialized views o tablas intermedias ayudan a mejorar tiempos.
• Orden y NULLs: el ORDER BY dentro de OVER() afectará resultados; definan explícitamente cómo tratar NULLs si es crítico.
• Casos regionales: en América Latina, donde a menudo se combinan datos de múltiples sistemas (ventas en tienda, ecommerce, marketplaces), las window functions facilitan alinear series temporales y calcular KPIs consistentes por canal.

Buenas prácticas para proyectos de datos

• Comience con una versión simple de la ventana y vaya ampliando el frame según necesidad.
• Documente la intención del PARTITION BY y ORDER BY en consultas compartidas para evitar malentendidos entre equipos.
• Use funciones de ranking para validar calidad de datos (por ejemplo, detectar múltiples registros marcados como “más reciente” por cliente).
• Pruebe consultas en una muestra antes de ejecutarlas sobre todo el dataset en producción.

¿Cuándo usar window functions vs agregaciones o joins?

• Use window functions cuando necesite mantener el detalle fila a fila y añadir métricas basadas en un contexto (p. ej., saldo acumulado al lado de cada movimiento).
• Use agregaciones con GROUP BY para resúmenes definitivos donde no importa conservar el nivel de registro.
• A veces combinar ambos (materializar un agregado y luego aplicar ventanas) ofrece mejor rendimiento y claridad.

Conclusión

Las funciones window de SQL son herramientas esenciales para cualquier científico de datos que trabaje con grandes volúmenes y necesite producir análisis reproducibles y detallados. Dominar OVER(), frames, funciones de ranking y de navegación reduce la dependencia de procesamientos fuera de la base de datos y acelera la entrega de insights. Para equipos latinoamericanos, esto representa una ventaja competitiva al unificar métricas entre canales y reducir tiempos de ingeniería.

Recursos rápidos

• Revise la documentación del dialecto SQL que usan (Postgres, BigQuery, Snowflake, etc.).
• Practique con datasets de ventas o logs para entender frames y funciones de navegación.