Bigquery

Window Functions en BigQuery

Qué son y para qué sirven

En BigQuery, una window function —también llamada analytic function— calcula un valor para cada fila usando un conjunto relacionado de filas definido por la cláusula OVER. A diferencia de GROUP BY, no colapsa el detalle del resultado: mantiene una fila por registro y le agrega contexto analítico. Las window functions pueden usarse en SELECT, ORDER BY y QUALIFY; además, no pueden referenciar otra window function en sus argumentos o en su OVER, y se evalúan después de la agregación. (Google Cloud Documentation)

La intuición práctica es esta:
GROUP BY cambia la granularidad del resultado;
OVER(...) conserva la granularidad y añade contexto fila por fila. (Google Cloud Documentation)

WITH ventas AS (
  SELECT 'A' AS cliente, DATE '2026-01-01' AS fecha, 100 AS monto UNION ALL
  SELECT 'A', DATE '2026-01-03', 80 UNION ALL
  SELECT 'A', DATE '2026-01-05', 120 UNION ALL
  SELECT 'B', DATE '2026-01-02', 50 UNION ALL
  SELECT 'B', DATE '2026-01-04', 70
)
SELECT
  cliente,
  fecha,
  monto,
  SUM(monto) OVER (PARTITION BY cliente) AS total_cliente,
  SUM(monto) OVER (
    PARTITION BY cliente
    ORDER BY fecha
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS acumulado_cliente
FROM ventas
ORDER BY cliente, fecha;

Aquí, total_cliente repite el total del cliente en cada fila, mientras que acumulado_cliente va creciendo a medida que avanza la fecha. Ese patrón resume muy bien el valor de las window functions. (Google Cloud Documentation)

Anatomía de OVER(...)

La forma general es esta:

funcion(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS | RANGE BETWEEN ...
)

PARTITION BY divide los datos en subconjuntos independientes; ORDER BY define el orden analítico dentro de cada partición; y el frame (ROWS o RANGE) delimita qué filas participan en el cálculo alrededor de la fila actual. En BigQuery, si no incluyes ni named window ni una especificación de ventana, todas las filas de entrada forman la ventana para cada fila. (Google Cloud Documentation)

Hay un detalle importante: el ORDER BY de la ventana no ordena el resultado final de la consulta; para eso sirve el ORDER BY externo. BigQuery documenta que el ORDER BY de la consulta es el que ordena el result set, y que sin ORDER BY el orden del resultado no está definido; además, LIMIT también tiene orden indefinido si no aparece después de un ORDER BY. (Google Cloud Documentation)

Otro detalle útil: para aggregate analytic functions, si incluyes ORDER BY dentro de OVER pero no declaras un frame explícito, BigQuery usa por defecto RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Por eso, cuando el comportamiento del frame importa, conviene declararlo de forma explícita. (Google Cloud Documentation)

1) Aggregate analytic functions

Las funciones agregadas como SUM, AVG, COUNT, MIN y MAX resumen grupos de filas. Cuando las usas con OVER, pasan a calcular un valor por fila sobre una ventana de filas relacionadas. (Google Cloud Documentation)

Ejemplo: promedio móvil sobre filas

WITH ventas AS (
  SELECT DATE '2026-01-01' AS fecha, 100 AS monto UNION ALL
  SELECT DATE '2026-01-03', 80 UNION ALL
  SELECT DATE '2026-01-05', 120 UNION ALL
  SELECT DATE '2026-01-10', 60
)
SELECT
  fecha,
  monto,
  AVG(monto) OVER (
    ORDER BY fecha
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS promedio_ultimas_3_filas
FROM ventas
ORDER BY fecha;

Aquí ROWS cuenta desplazamientos físicos: la fila actual más las 2 anteriores. Si faltan filas al principio, BigQuery usa solo las disponibles dentro de la partición. (Google Cloud Documentation)

ROWS vs RANGE

ROWS trabaja con posiciones físicas. RANGE trabaja con una distancia lógica respecto del valor usado en ORDER BY. En BigQuery, un frame con RANGE exige exactamente una expresión numérica en el ORDER BY; para fechas se recomienda UNIX_DATE() y para timestamps UNIX_SECONDS(), UNIX_MILLIS() o UNIX_MICROS(). Además, el desplazamiento numérico debe ser una constante entera no negativa o un parámetro. (Google Cloud Documentation)

Ejemplo: promedio móvil de los últimos 7 días

WITH ventas AS (
  SELECT DATE '2026-01-01' AS fecha, 100 AS monto UNION ALL
  SELECT DATE '2026-01-03', 80 UNION ALL
  SELECT DATE '2026-01-05', 120 UNION ALL
  SELECT DATE '2026-01-10', 60
)
SELECT
  fecha,
  monto,
  AVG(monto) OVER (
    ORDER BY UNIX_DATE(fecha)
    RANGE BETWEEN 7 PRECEDING AND CURRENT ROW
  ) AS promedio_ultimos_7_dias
FROM ventas
ORDER BY fecha;

En este caso no estás diciendo “mira 3 filas”, sino “mira todas las filas cuya fecha esté dentro de un rango lógico de 7 días respecto de la fila actual”. Esa diferencia entre ROWS y RANGE es una de las más importantes de aprender bien. (Google Cloud Documentation)

2) Navigation functions

Las navigation functions leen un valor de otra fila del mismo contexto analítico. BigQuery documenta entre ellas LAG, LEAD, FIRST_VALUE, LAST_VALUE y NTH_VALUE. De forma general, calculan un value_expression sobre otra fila del window frame. (Google Cloud Documentation)

Ejemplo: fila anterior y fila siguiente

WITH eventos AS (
  SELECT 'A' AS pedido_id, TIMESTAMP '2026-01-01 10:00:00+00' AS ts, 'creado'  AS estado UNION ALL
  SELECT 'A', TIMESTAMP '2026-01-01 10:05:00+00', 'pagado' UNION ALL
  SELECT 'A', TIMESTAMP '2026-01-01 10:20:00+00', 'enviado' UNION ALL
  SELECT 'B', TIMESTAMP '2026-01-01 11:00:00+00', 'creado'
)
SELECT
  pedido_id,
  ts,
  estado,
  LAG(estado)  OVER (PARTITION BY pedido_id ORDER BY ts) AS estado_anterior,
  LEAD(estado) OVER (PARTITION BY pedido_id ORDER BY ts) AS estado_siguiente
FROM eventos
ORDER BY pedido_id, ts;

LAG devuelve el valor de una fila anterior; LEAD, el de una fila posterior. En ambos casos, el offset por defecto es 1. En BigQuery, si especificas offset, debe ser un entero no negativo; y si usas default_expression, debe ser compatible con el tipo de la expresión original. (Google Cloud Documentation)

La trampa clásica de LAST_VALUE

LAST_VALUE no significa automáticamente “último valor de toda la partición”. BigQuery lo define como el valor de la última fila del window frame actual. Si no amplías el frame, con frecuencia termina devolviendo el último valor “hasta la fila actual”, no el último de toda la partición. (Google Cloud Documentation)

WITH eventos AS (
  SELECT 'A' AS pedido_id, TIMESTAMP '2026-01-01 10:00:00+00' AS ts, 'creado'  AS estado UNION ALL
  SELECT 'A', TIMESTAMP '2026-01-01 10:05:00+00', 'pagado' UNION ALL
  SELECT 'A', TIMESTAMP '2026-01-01 10:20:00+00', 'enviado'
)
SELECT
  pedido_id,
  ts,
  estado,
  LAST_VALUE(estado) OVER (
    PARTITION BY pedido_id
    ORDER BY ts
  ) AS ultimo_en_el_frame_actual,
  LAST_VALUE(estado) OVER (
    PARTITION BY pedido_id
    ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS ultimo_estado_real
FROM eventos
ORDER BY pedido_id, ts;

La segunda columna es la que normalmente espera quien quiere “el estado final del pedido”, porque extiende el frame hasta UNBOUNDED FOLLOWING. BigQuery muestra este patrón también en sus ejemplos de FIRST_VALUE y LAST_VALUE. (Google Cloud Documentation)

3) Numbering functions

Las numbering functions asignan una posición o ranking dentro de la ventana. BigQuery documenta entre ellas ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, NTILE y PERCENT_RANK. (Google Cloud Documentation)

Ejemplo: ranking por sucursal

WITH ventas AS (
  SELECT 'Norte' AS sucursal, 'Ana' AS vendedor, 900 AS monto UNION ALL
  SELECT 'Norte', 'Luis', 900 UNION ALL
  SELECT 'Norte', 'Marta', 850 UNION ALL
  SELECT 'Norte', 'Pablo', 700 UNION ALL
  SELECT 'Sur', 'Eva', 920 UNION ALL
  SELECT 'Sur', 'Leo', 910 UNION ALL
  SELECT 'Sur', 'Noa', 910
)
SELECT
  sucursal,
  vendedor,
  monto,
  RANK()       OVER (PARTITION BY sucursal ORDER BY monto DESC) AS rank_monto,
  DENSE_RANK() OVER (PARTITION BY sucursal ORDER BY monto DESC) AS dense_rank_monto,
  ROW_NUMBER() OVER (PARTITION BY sucursal ORDER BY monto DESC, vendedor) AS row_number_monto
FROM ventas
ORDER BY sucursal, monto DESC, vendedor;

RANK() deja huecos cuando hay empates; DENSE_RANK() no deja huecos; ROW_NUMBER() asigna una secuencia única. BigQuery además aclara que el orden dentro del grupo de empates de ROW_NUMBER() es no determinista si no agregas un criterio de desempate en ORDER BY. (Google Cloud Documentation)

Top N por grupo con QUALIFY

WITH ventas AS (
  SELECT 'Norte' AS sucursal, 'Ana' AS vendedor, 900 AS monto UNION ALL
  SELECT 'Norte', 'Luis', 900 UNION ALL
  SELECT 'Norte', 'Marta', 850 UNION ALL
  SELECT 'Norte', 'Pablo', 700 UNION ALL
  SELECT 'Sur', 'Eva', 920 UNION ALL
  SELECT 'Sur', 'Leo', 910 UNION ALL
  SELECT 'Sur', 'Noa', 910
)
SELECT
  sucursal,
  vendedor,
  monto
FROM ventas
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY sucursal
  ORDER BY monto DESC, vendedor
) <= 3
ORDER BY sucursal, monto DESC, vendedor;

La cláusula QUALIFY filtra el resultado de las window functions. En BigQuery, una window function debe aparecer en QUALIFY o en el SELECT list para que QUALIFY sea válido, y su evaluación ocurre después de WINDOW y antes de DISTINCT, ORDER BY y LIMIT. (Google Cloud Documentation)

Cómo quedarían corregidos tus ejemplos originales

Promedio de los 100 viajes anteriores

SELECT
  start_date,
  AVG(duration) OVER (
    ORDER BY start_date ASC
    ROWS BETWEEN 100 PRECEDING AND 1 PRECEDING
  ) AS average_duration
FROM `bigquery-public-data.london_bicycles.cycle_hire`
ORDER BY start_date ASC
LIMIT 5;

La corrección importante aquí es el ORDER BY externo, para que LIMIT 5 sí devuelva las primeras 5 filas del resultado ordenado cronológicamente. Sin ese orden externo, BigQuery no garantiza el orden de salida. (Google Cloud Documentation)

Próximo alquiler de la bicicleta

SELECT
  start_date,
  end_date,
  LEAD(start_date) OVER (
    PARTITION BY bike_id
    ORDER BY start_date ASC
  ) AS next_rental_start
FROM `bigquery-public-data.london_bicycles.cycle_hire`
ORDER BY bike_id, start_date
LIMIT 5;

Esta versión es más clara que la variante con LAST_VALUE(... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) y además evita la explicación incorrecta del material original sobre LEAD. (Google Cloud Documentation)

Top 5 viajes más largos por estación

SELECT
  start_station_id,
  duration,
  RANK() OVER (
    PARTITION BY start_station_id
    ORDER BY duration DESC
  ) AS nth_longest
FROM `bigquery-public-data.london_bicycles.cycle_hire`
QUALIFY nth_longest <= 5
ORDER BY start_station_id, nth_longest, duration DESC;

Esta sí devuelve el top 5 por partición. Si necesitas exactamente 5 filas por estación, usa ROW_NUMBER() en lugar de RANK(), porque RANK() puede devolver más de 5 filas cuando hay empates. (Google Cloud Documentation)

Buenas prácticas para enseñar y usar window functions

• Sé explícito con el frame cuando el resultado dependa de “hasta dónde mira” la función; esto evita errores sutiles, sobre todo con LAST_VALUE. (Google Cloud Documentation)
• Usa ROWS cuando pienses en “cantidad de registros” y RANGE cuando pienses en “distancia lógica” respecto del valor ordenado. (Google Cloud Documentation)
• Usa QUALIFY para top N por grupo; suele ser más legible que envolver la consulta en subqueries. (Google Cloud Documentation)
• Añade criterios de desempate cuando uses ROW_NUMBER() y quieras resultados estables. (Google Cloud Documentation)
• Si varias funciones comparten la misma ventana, BigQuery permite reutilizarla con la cláusula WINDOW y un named window. (Google Cloud Documentation)

Cierre

La idea central es simple: usa GROUP BY cuando quieras reducir filas, y usa OVER(...) cuando quieras mantener las filas y añadirles contexto. En BigQuery, dominar PARTITION BY, ORDER BY, ROWS/RANGE, QUALIFY y las diferencias entre LAG/LEAD, RANK/DENSE_RANK/ROW_NUMBER te da casi todo lo que necesitas para análisis temporal, rankings, acumulados y comparaciones fila contra fila. (Google Cloud Documentation)

También te lo puedo reordenar en formato documentación interna, tutorial de curso o Markdown listo para pegar en Confluence/Notion.