SQL Avanzado en BigQuery: CTEs y Window Functions (OVER, LAG, RANK)

1. El modelo mental correcto: GROUP BY vs Window Functions

El error conceptual más frecuente consiste en pensar que una Window Function es una alternativa sintáctica a GROUP BY. No lo es.

GROUP BY reduce cardinalidad: varias filas se convierten en una fila por grupo. Una Window Function, en cambio, calcula un valor utilizando un conjunto de filas relacionadas pero mantiene la granularidad original.

2. OVER, PARTITION BY y ORDER BY en BigQuery

La cláusula OVER() define el contexto en el que una función analítica debe evaluar cada fila. Sus dos componentes conceptualmente más importantes son PARTITION BY y ORDER BY.

PARTITION BY determina los grupos lógicos independientes. ORDER BY determina la secuencia dentro de cada grupo.

SELECT
  customer_id,
  order_id,
  order_date,
  amount,

  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date, order_id
  ) AS cumulative_amount

FROM `project.analytics.orders`;

El resultado conserva cada pedido, pero añade el importe acumulado de cada cliente siguiendo el orden temporal definido.

Desde una perspectiva arquitectónica, esto es importante: la ventana define el contexto de cálculo, no una nueva tabla física. Por tanto, una consulta con muchas ventanas puede introducir operaciones de ordenación y redistribución que deben vigilarse cuando trabajamos con grandes volúmenes.

3. LAG y LEAD: análisis temporal sin self-joins

LAG() permite acceder a una fila anterior dentro de una ventana, mientras que LEAD() permite acceder a una fila posterior. Este patrón es especialmente útil para series temporales, evolución de métricas y detección de cambios.

WITH daily_sales AS (
  SELECT
    customer_id,
    DATE(order_timestamp) AS order_date,
    SUM(amount) AS daily_amount
  FROM `project.analytics.orders`
  GROUP BY
    customer_id,
    order_date
)

SELECT
  customer_id,
  order_date,
  daily_amount,

  LAG(daily_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS previous_day_amount,

  LEAD(daily_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS next_day_amount

FROM daily_sales;

El CTE primero establece la granularidad correcta: una fila por customer_id + order_date. Solo después se ejecutan las ventanas. Esta separación evita comparar eventos individuales cuando realmente queremos comparar métricas diarias.

El patrón puede extenderse para calcular variaciones:

daily_amount
  - LAG(daily_amount) OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS absolute_change

4. RANK, DENSE_RANK y ROW_NUMBER: no son intercambiables

Los rankings son uno de los usos más habituales de Window Functions, pero elegir la función incorrecta puede introducir errores silenciosos.

SELECT
  category,
  product_id,
  revenue,

  RANK() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS revenue_rank,

  DENSE_RANK() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS dense_revenue_rank,

  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY revenue DESC, product_id
  ) AS deterministic_row_number

FROM `project.analytics.product_revenue`;

Observa un detalle de arquitectura: cuando utilizamos ROW_NUMBER() para seleccionar un único registro por grupo, conviene incluir un desempate explícito. Por ejemplo, ORDER BY revenue DESC, product_id hace que la selección sea mucho más determinista que ordenar únicamente por revenue.

7. Implementación Práctica de Producción

El siguiente patrón combina CTEs, agregación, LAG y ranking para producir una métrica analítica lista para consumo downstream. La clave es separar explícitamente las etapas: primero normalizamos la granularidad, después calculamos contexto temporal y finalmente clasificamos.

WITH daily_customer_revenue AS (
  -- 1. Reducimos los eventos a la granularidad analítica necesaria.
  SELECT
    customer_id,
    DATE(order_timestamp) AS order_date,
    SUM(amount) AS revenue
  FROM `project.analytics.orders`
  WHERE order_timestamp >= TIMESTAMP_SUB(
    CURRENT_TIMESTAMP(),
    INTERVAL 180 DAY
  )
    AND status = 'COMPLETED'
  GROUP BY
    customer_id,
    order_date
),

customer_evolution AS (
  -- 2. Añadimos contexto temporal sin perder cada día.
  SELECT
    customer_id,
    order_date,
    revenue,

    LAG(revenue) OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS previous_revenue

  FROM daily_customer_revenue
),

customer_metrics AS (
  -- 3. Calculamos el delta y el ranking por cliente.
  SELECT
    customer_id,
    order_date,
    revenue,
    previous_revenue,

    revenue - COALESCE(previous_revenue, 0) AS revenue_delta,

    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY revenue DESC, order_date DESC
    ) AS revenue_position

  FROM customer_evolution
)

SELECT
  customer_id,
  order_date,
  revenue,
  previous_revenue,
  revenue_delta,
  revenue_position,

  SAFE_DIVIDE(
    revenue - previous_revenue,
    previous_revenue
  ) AS revenue_growth_rate

FROM customer_metrics
ORDER BY
  customer_id,
  order_date;

Hay dos decisiones importantes aquí. La primera es utilizar SAFE_DIVIDE para evitar que un denominador cero convierta una transformación completa en un error. La segunda es utilizar COALESCE únicamente donde el significado de negocio lo permite: una primera observación no necesariamente significa que su revenue anterior sea realmente cero.

8. Patrones de Diseño y Mejores Prácticas

9. CTEs: legibilidad frente a materialización

Un CTE mejora enormemente la estructura de una consulta compleja porque permite expresar una transformación como una secuencia lógica.

Pero existe una distinción fundamental para un arquitecto de datos: un CTE es una construcción lógica de la consulta, no una garantía de materialización física.

Por tanto, no debe asumirse automáticamente que cada CTE equivale a una tabla temporal persistida o que reutilizar el mismo CTE implica necesariamente que su resultado se calcule una única vez de una determinada manera.

Cuando una etapa intermedia es extremadamente costosa y se reutiliza en múltiples procesos, puede ser más apropiado evaluar una estrategia de materialización, tabla intermedia, tabla incremental o modelo de datos derivado. La decisión debe basarse en frecuencia, volumen, SLA, concurrencia y coste operativo.

10. Framework de Implementación Paso a Paso

11. Errores Críticos que Debes Evitar

Preguntas Frecuentes sobre Window Functions en BigQuery

Conclusión: SQL Analítico con Mentalidad de Arquitecto

Dominar window functions BigQuery SQL no consiste en memorizar funciones como LAG, LEAD o RANK. El verdadero salto profesional está en entender qué conjunto de filas debe observar cada cálculo, cuál es la granularidad correcta y qué impacto tiene la consulta sobre rendimiento, coste y mantenibilidad.

El patrón CTE → granularidad correcta → ventana → validación → optimización proporciona una base sólida para construir SQL analítico mantenible.

Cuando combinas OVER PARTITION BY BigQuery con una correcta modelización de datos, filtros eficientes y decisiones conscientes de FinOps, puedes resolver problemas analíticos complejos sin convertir cada consulta en un pipeline inmanejable.