D
Adquisición y arquitectura de datos M7 avanzado 38 min

Capitulo 07

SQL analítico avanzado e ingeniería de features

Ventanas, granularidad y datos relacionales para construir señales de negocio confiables

Construye análisis por fila y por segmento con funciones de ventana, CTE, JOIN y JSON; aprende a preservar granularidad y a convertir eventos transaccionales en features RFM reproducibles.

Antes de escribir una ventana, declara el grano

El grano responde: «¿qué representa una fila de esta tabla o etapa?». Si no lo declaras, un JOIN, una agregación o una ventana puede duplicar ventas y producir features incorrectas sin dar ningún error.

Agrega en el nivel correcto; usa ventanas cuando necesites comparar cada fila con su grupo sin perder el detalle.

— Regla de SQL analítico

7.1 Qué aprenderás y cómo conecta

Este capítulo completa el Módulo 8 del PDF: SQL Analítico Avanzado. Al terminar podrás:

  • Distinguir una agregación que colapsa filas de una función de ventana que conserva el detalle.
  • Elegir PARTITION BY, ORDER BY y el marco de ventana adecuados.
  • Usar ROW_NUMBER, RANK, LAG, sumas acumuladas y agregaciones por segmento.
  • Unir tablas sin multiplicar registros accidentalmente.
  • Leer JSON con funciones propias del motor SQL.
  • Diseñar features con una definición de negocio, una fecha de corte y una granularidad explícitas.

En M6: SQL y DuckDB aprendiste a filtrar, agrupar y consultar archivos. Aquí añadimos contexto entre filas y relaciones entre tablas. El siguiente capítulo, M8, lleva las tablas validadas a persistencia y automatización.

7.2 GROUP BY versus funciones de ventana

GROUP BY reduce cada grupo a una fila. Una función de ventana calcula sobre filas relacionadas, pero deja una salida por cada fila de entrada.

NecesidadHerramientaFilas de salida
Total vendido por clienteGROUP BY cliente_idUna por cliente
Cada compra y el total histórico del clienteSUM(...) OVER (PARTITION BY cliente_id)Una por compra
Posición de cada compra dentro del clienteROW_NUMBER() OVER (...)Una por compra
Tres compras se reducen a dos filas con GROUP BY; SUM OVER conserva las tres compras y añade a cada una el total de su cliente.
Elige según el grano que necesites: GROUP BY responde una fila por cliente; la ventana conserva el detalle de cada orden. Datos esquemáticos.

Supongamos orders(order_id, customer_id, order_date, amount). Esta consulta conserva cada compra y agrega su contexto:

SELECT
    order_id,
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_lifetime_value,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
    ) AS purchase_number
FROM orders;

La cláusula OVER define la ventana:

  • PARTITION BY reinicia el cálculo para cada grupo; no ordena la salida final.
  • ORDER BY establece el orden lógico dentro de cada grupo. Agrega una segunda clave estable para resolver empates.
  • El marco, por ejemplo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, define qué filas ordenadas entran en el cálculo.

Ranking y acumulados

ROW_NUMBER() asigna una posición única; RANK() comparte la posición en empates y deja saltos; DENSE_RANK() comparte la posición sin saltos.

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

El marco explícito evita ambigüedad entre filas que comparten la misma fecha. Para una serie temporal, LAG y LEAD comparan con la observación anterior o siguiente:

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
    ) AS previous_order_amount
FROM orders;

La primera compra de cada cliente no tiene una compra anterior: previous_order_amount será NULL. Ese NULL significa «no existe antecedente», no cero.

7.3 CTE y granularidad: formar features sin inflar el total

Un CTE (Common Table Expression) nombra una etapa de una consulta con WITH. Sirve para que cada transformación exprese una sola idea y deje claro el grano que recibe y entrega.

Ejemplo: construir RFM por cliente a partir de órdenes y catálogo. La tabla orders tiene una fila por línea de pedido; products debe tener una fila por producto.

WITH order_lines AS (
    SELECT
        o.order_id,
        o.customer_id,
        CAST(o.order_date AS DATE) AS order_date,
        o.quantity * p.unit_price AS line_amount
    FROM orders AS o
    JOIN products AS p
      ON p.product_id = o.product_id
    WHERE o.order_date >= DATE '2024-01-01'
      AND o.order_date <  DATE '2025-01-01'
),
rfm AS (
    SELECT
        customer_id,
        MAX(order_date) AS last_order_date,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(line_amount) AS monetary
    FROM order_lines
    GROUP BY customer_id
),
scored AS (
    SELECT
        customer_id,
        DATE_DIFF('day', last_order_date, DATE '2025-01-01') AS recency_days,
        frequency,
        monetary
    FROM rfm
)
SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    DENSE_RANK() OVER (ORDER BY monetary DESC) AS spend_rank,
    SUM(monetary) OVER (
        ORDER BY monetary DESC, customer_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
FROM scored
ORDER BY spend_rank, customer_id;

Este ejemplo usa sintaxis de DuckDB para DATE_DIFF. En otros motores cambia la función de fechas, no la definición de RFM:

  • Recency: días desde la última actividad hasta una fecha de corte conocida.
  • Frequency: número de compras en una ventana temporal declarada.
  • Monetary: valor monetario en esa misma ventana y moneda.

La fecha de corte no debe ser «hoy» si quieres reproducir un análisis histórico. Guárdala como parámetro y documenta si frequency cuenta órdenes, líneas o días activos.

El error silencioso: un JOIN uno-a-muchos

Si una orden contiene tres líneas, al unir orders con order_lines la fila de orden aparece tres veces. Sumar después un total a nivel orden lo triplica. Antes de combinar tablas, escribe sus granos:

orders:       una fila por order_id
order_lines:  una fila por order_id + product_id
customers:    una fila por customer_id

Agrega primero al grano del consumidor o conserva los campos de detalle; no asumas que un JOIN preserva el número de filas. Usa COUNT(DISTINCT ...) solo cuando la unidad de negocio realmente sea esa entidad: no como parche para una relación mal definida.

Una tabla más grande no siempre tiene más información

Comprueba cardinalidades antes y después de cada unión. Una dimensión que debería ser única por product_id pero tiene duplicados multiplica importes y altera cualquier feature posterior.

7.4 JSON en SQL

Una API puede devolver datos semi-estructurados, por ejemplo:

{"order_id": 81, "customer": {"id": "C-17"}, "items": [{"sku": "A2", "qty": 2}]}

JSON es útil en la zona de ingesta porque conserva campos variables; para análisis repetible conviene extraer los atributos que tienen significado y contrato claros. Las funciones exactas varían por motor:

-- DuckDB
SELECT
    json_extract_string(payload, '$.customer.id') AS customer_id,
    CAST(json_extract(payload, '$.order_id') AS INTEGER) AS order_id
FROM raw_events;
-- SQLite con su extensión JSON1
SELECT
    json_extract(payload, '$.customer.id') AS customer_id,
    json_extract(payload, '$.order_id') AS order_id
FROM raw_events;

Antes de castear, valida que el atributo exista y tenga el formato esperado. Para listas como items, decide si necesitas conservar el documento original o normalizar cada elemento a una fila relacionada. No mezcles en la misma columna el dato raw y una interpretación transformada.

7.5 De la consulta a una feature utilizable

Una buena feature no es solo una columna calculada. Debe incluir:

  1. Unidad de observación: por ejemplo, un cliente al cierre de cada mes.
  2. Ventana temporal: compras de los 90 días anteriores a ese cierre.
  3. Fecha de corte: la información disponible antes de predecir.
  4. Regla para ausencias: cliente sin compras tiene frecuencia 0; recency puede ser desconocida o codificarse por una regla explícita.
  5. Prueba: comprobar una muestra a mano y que no existan duplicados en la llave esperada.
-- Una fila de salida por cliente y mes de corte.
SELECT
    customer_id,
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT order_id) AS orders_in_month,
    SUM(line_amount) AS spend_in_month
FROM order_lines
WHERE order_date >= DATE '2024-01-01'
  AND order_date <  DATE '2025-01-01'
GROUP BY customer_id, DATE_TRUNC('month', order_date);

Para ML, evita incorporar eventos posteriores al momento de predicción. Si el target pregunta si habrá churn en los próximos 30 días, las features deben construirse solo con información disponible antes de ese horizonte. La fuga temporal puede ocurrir incluso en una consulta SQL que «corre bien».

Función de ventana

M7

Cálculo sobre filas relacionadas que conserva una fila de salida por cada fila de entrada, a diferencia de GROUP BY.

Ej: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) calcula el acumulado por cliente sin colapsar las compras.

#sql #window-functions #analitica

7.6 Errores comunes y lista de revisión

Ventana sin orden estable
✗ ROW_NUMBER() OVER (PARTITION BY customer_id)
✓ Agregar ORDER BY order_date, order_id para que el orden sea defendible.
Sumar después de duplicar filas
✗ Unir dos tablas uno-a-muchos y sumar el total de la entidad repetida.
✓ Declarar el grano, agregar al nivel correcto y luego unir.
RFM sin fecha de referencia
✗ Calcular recency contra la fecha actual en cada ejecución histórica.
✓ Fijar una fecha de corte reproducible y excluir información futura.
Usar funciones JSON como si fueran universales
✗ Copiar una función específica de SQLite en DuckDB o PostgreSQL.
✓ Identificar el motor y verificar sus funciones, casts y manejo de tipos.
Feature store ligero con RFM avanzado

Usa las tablas de usuarios, órdenes y productos de una tienda. Construye una fila por usuario que incluya recency, frequency, monetary, ranking por gasto y tendencia entre períodos. Incluye usuarios sin compras, prueba las cardinalidades, fija un corte temporal y compara tres resultados con un cálculo manual. Explica qué decisión podría apoyar cada feature y qué no permite concluir.

💡 Cuenta cuántas filas corresponden al grano de entrada.

Q: ¿Cómo detectarías que un JOIN infló los ingresos?

Declararía la granularidad de ambas fuentes, verificaría unicidad de las claves y compararía conteos y sumas antes y después. Revisaría específicamente relaciones uno-a-muchos y agregaría al grano correcto antes de unir si la métrica lo requiere.

🪤 Aplicar DISTINCT al total sin entender qué relación produjo los duplicados.

Para continuar

Las features analíticas solo son útiles si sobreviven a la ejecución y se pueden regenerar. En M8 conectarás contratos, validaciones, transacciones e idempotencia para llevar ese resultado a una capa persistente.

Tu espacio de estudio

Notas privadas

Se guardan solo en este navegador y no se publican.

Sin cambios