Módulo 6: Accumulating And Cumulative Patterns

Ventanas móviles con columnas tipo arreglo

Siguiente →

Descripción

Esta lección escala el mecanismo de la lección 5 —list_prepend(valor_de_hoy, arreglo_de_ayer)— a las tres tiendas de Kiosko y los siete días completos de la semana (2026-08-03 a 2026-08-09), agregando la pieza que el ejemplo mínimo todavía no necesitaba: recortar el arreglo a un tamaño fijo con slicing, para que revenue_array_7d nunca tenga más de 7 elementos y revenue_array_30d nunca tenga más de 30. Al final, construyes fact_store_activity completo —21 filas— y lo verificas dos veces: contra el revenue semanal ya conocido de cada tienda, y contra una segunda versión, calculada desde cero con una función de ventana SQL en vez del bucle incremental.

Conexión con el módulo. Esta lección es la construcción central del segundo patrón del módulo, tal como la lección 3 lo fue para el primero. La lección 7 usa esta misma tabla para responder la pregunta de negocio real: activos de 7 y 30 días por tienda.

Ejemplo trabajado: fact_store_activity, día por día, tienda por tienda

El revenue diario de cada tienda se calcula agregando fact_orders —la misma tabla de 40 filas y 106.15 de revenue total desde el módulo 1— por store_id y por día. El bucle que sigue recorre los siete días de la semana, y dentro de cada día, las tres tiendas, aplicando en cada paso el mismo mecanismo list_prepend de la lección 5, con dos diferencias: agrega también el arreglo de 30 días en paralelo, y recorta ambos arreglos a su tamaño máximo con slicing de DuckDB (arr[1:7] para conservar solo los primeros 7 elementos, arr[1:30] para los primeros 30).

# fact_store_activity_build.py -- continua sobre fact_orders ya cargada (modulo 1)
import duckdb

con.execute("""
    CREATE TABLE fact_store_activity (
        store_id VARCHAR,
        activity_date DATE,
        daily_revenue DOUBLE,
        revenue_array_7d DOUBLE[],
        active_days_7d INTEGER,
        revenue_array_30d DOUBLE[],
        active_days_30d INTEGER
    )
""")

STORES = ["S01", "S02", "S03"]
DAYS = ["2026-08-03", "2026-08-04", "2026-08-05", "2026-08-06", "2026-08-07", "2026-08-08", "2026-08-09"]

for day in DAYS:
    for store_id in STORES:
        daily_revenue = con.sql(f"""
            SELECT COALESCE(ROUND(SUM(revenue), 2), 0.0)
            FROM fact_orders
            WHERE store_id = '{store_id}' AND CAST(order_ts AS DATE) = DATE '{day}'
        """).fetchone()[0]

        prev = con.sql(f"""
            SELECT revenue_array_7d, revenue_array_30d
            FROM fact_store_activity
            WHERE store_id = '{store_id}'
            ORDER BY activity_date DESC
            LIMIT 1
        """).fetchone()

        if prev is None:
            new_array_7d = [daily_revenue]
            new_array_30d = [daily_revenue]
        else:
            prev_7d, prev_30d = prev
            new_array_7d = ([daily_revenue] + list(prev_7d))[:7]    # equivalente a arr[1:7]
            new_array_30d = ([daily_revenue] + list(prev_30d))[:30]  # equivalente a arr[1:30]

        active_7d = sum(1 for v in new_array_7d if v > 0)
        active_30d = sum(1 for v in new_array_30d if v > 0)

        con.execute(
            "INSERT INTO fact_store_activity VALUES (?, ?, ?, ?, ?, ?, ?)",
            (store_id, day, daily_revenue, new_array_7d, active_7d, new_array_30d, active_30d),
        )

print(f"fact_store_activity rows: {con.sql('SELECT COUNT(*) FROM fact_store_activity').fetchone()[0]}\n")
con.sql("""
    SELECT store_id, activity_date, daily_revenue, revenue_array_7d, active_days_7d
    FROM fact_store_activity ORDER BY store_id, activity_date
""").show(max_width=250)

Nota sobre el recorte: ([daily_revenue] + list(prev_7d))[:7] en Python es el equivalente exacto del slicing revenue_array_7d[1:7] de DuckDB —quedarse con, como máximo, los primeros 7 elementos del arreglo—; esta lección lo hace en Python porque el bucle construye cada fila con INSERT individual, pero la lección incluye más abajo la versión 100% SQL con el mismo recorte, para que veas ambas sintaxis. La documentación de DuckDB confirma que el slicing de arreglos usa índices desde 1 (no desde 0), con la forma list[begin:end] — exactamente la sintaxis que vas a usar en la sección siguiente de esta misma lección.

Qué esperar.

fact_store_activity rows: 21

┌──────────┬───────────────┬───────────────┬──────────────────────────────────────────┬────────────────┐
│ store_id │ activity_date │ daily_revenue │             revenue_array_7d             │ active_days_7d │
│ varchar  │     date      │    double     │                 double[]                 │     int32      │
├──────────┼───────────────┼───────────────┼──────────────────────────────────────────┼────────────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]                                   │              1 │
│ S01      │ 2026-08-04    │          3.45 │ [3.45, 8.45]                             │              2 │
│ S01      │ 2026-08-05    │          0.55 │ [0.55, 3.45, 8.45]                       │              3 │
│ S01      │ 2026-08-06    │           6.7 │ [6.7, 0.55, 3.45, 8.45]                  │              4 │
│ S01      │ 2026-08-07    │           5.0 │ [5.0, 6.7, 0.55, 3.45, 8.45]             │              5 │
│ S01      │ 2026-08-08    │         13.05 │ [13.05, 5.0, 6.7, 0.55, 3.45, 8.45]      │              6 │
│ S01      │ 2026-08-09    │           1.1 │ [1.1, 13.05, 5.0, 6.7, 0.55, 3.45, 8.45] │              7 │
│ S02      │ 2026-08-03    │           3.9 │ [3.9]                                    │              1 │
│ S02      │ 2026-08-04    │           4.6 │ [4.6, 3.9]                               │              2 │
│ S02      │ 2026-08-05    │           9.0 │ [9.0, 4.6, 3.9]                          │              3 │
│ S02      │ 2026-08-06    │          3.15 │ [3.15, 9.0, 4.6, 3.9]                    │              4 │
│ S02      │ 2026-08-07    │          7.25 │ [7.25, 3.15, 9.0, 4.6, 3.9]              │              5 │
│ S02      │ 2026-08-08    │           9.7 │ [9.7, 7.25, 3.15, 9.0, 4.6, 3.9]         │              6 │
│ S02      │ 2026-08-09    │           1.2 │ [1.2, 9.7, 7.25, 3.15, 9.0, 4.6, 3.9]    │              7 │
│ S03      │ 2026-08-03    │           3.5 │ [3.5]                                    │              1 │
│ S03      │ 2026-08-04    │           7.8 │ [7.8, 3.5]                               │              2 │
│ S03      │ 2026-08-05    │           0.0 │ [0.0, 7.8, 3.5]                          │              2 │
│ S03      │ 2026-08-06    │           1.2 │ [1.2, 0.0, 7.8, 3.5]                     │              3 │
│ S03      │ 2026-08-07    │           5.8 │ [5.8, 1.2, 0.0, 7.8, 3.5]                │              4 │
│ S03      │ 2026-08-08    │           9.1 │ [9.1, 5.8, 1.2, 0.0, 7.8, 3.5]           │              5 │
│ S03      │ 2026-08-09    │          1.65 │ [1.65, 9.1, 5.8, 1.2, 0.0, 7.8, 3.5]     │              6 │
└──────────┴───────────────┴───────────────┴──────────────────────────────────────────┴────────────────┘

21 filas — 3 tiendas × 7 días, exactamente. Detente en S03, fila del 2026-08-05: daily_revenue = 0.0, y su active_days_7d se queda en 2 en vez de subir a 3 — la primera evidencia de que active_days_7d no cuenta días transcurridos, cuenta días con actividad real. Vas a volver sobre este número exacto en la lección 7.

Verificando contra el revenue semanal ya conocido

list_sum(revenue_array_7d) en la última fila de cada tienda (2026-08-09, el séptimo y último día) debería coincidir, exactamente, con el revenue total semanal que ya conoces desde el módulo 1 —106.15 repartido en S01: 38.3, S02: 38.8, S03: 29.05—:

print("=== Snapshot del 2026-08-09: list_sum(revenue_array_7d) vs revenue semanal conocido ===")
con.sql("""
    SELECT store_id, ROUND(list_sum(revenue_array_7d), 2) AS sum_7d, active_days_7d
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
""").show(max_width=200)

print("=== Revenue semanal por tienda, calculado directamente desde fact_orders ===")
con.sql("""
    SELECT store_id, ROUND(SUM(revenue), 2) AS total_week_revenue
    FROM fact_orders GROUP BY store_id ORDER BY store_id
""").show(max_width=200)

Qué esperar.

=== Snapshot del 2026-08-09: list_sum(revenue_array_7d) vs revenue semanal conocido ===
┌──────────┬────────┬────────────────┐
│ store_id │ sum_7d │ active_days_7d │
│ varchar  │ double │     int32      │
├──────────┼────────┼────────────────┤
│ S01      │   38.3 │              7 │
│ S02      │   38.8 │              7 │
│ S03      │  29.05 │              6 │
└──────────┴────────┴────────────────┘

=== Revenue semanal por tienda, calculado directamente desde fact_orders ===
┌──────────┬────────────────────┐
│ store_id │ total_week_revenue │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ S01      │               38.3 │
│ S02      │              38.8 │
│ S03      │              29.05 │
└──────────┴────────────────────┘

38.3, 38.8, 29.05 — idénticos en ambas consultas. Esto no es una coincidencia: por construcción, el séptimo día de una ventana de 7 elementos contiene exactamente los siete días de la semana, así que su suma tiene que coincidir con el revenue total de esa semana. Si en algún punto de tu implementación estos dos números no coincidieran, sería evidencia directa de un error en el mecanismo de acumulación —un día que se contó dos veces, o uno que se perdió en el recorte—, no una diferencia de negocio real.

Profundización: la versión 100% SQL, para comparar

El bucle Python de esta lección construye fact_store_activity de forma incremental — cada fila se calcula a partir de la fila anterior, sin releer días previos, exactamente el mecanismo de producción del cumulative table design. Existe una segunda forma de llegar al mismo resultado: recalcular todo desde cero con una función de ventana SQL, usando ROWS BETWEEN 6 PRECEDING AND CURRENT ROW para capturar los últimos 7 días de cada fila:

con.execute("""
    CREATE TABLE daily_store_revenue AS
    WITH stores AS (SELECT DISTINCT store_id FROM fact_orders),
         days AS (SELECT UNNEST(generate_series(DATE '2026-08-03', DATE '2026-08-09', INTERVAL 1 DAY))::DATE AS activity_date)
    SELECT s.store_id, d.activity_date,
           COALESCE((SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
                     WHERE f.store_id = s.store_id AND CAST(f.order_ts AS DATE) = d.activity_date), 0.0) AS daily_revenue
    FROM stores s CROSS JOIN days d
""")

print("=== Version SQL pura: recalculada desde cero con funcion de ventana, no incremental ===")
con.sql("""
    SELECT store_id, activity_date, daily_revenue,
           list_reverse(array_agg(daily_revenue) OVER (
               PARTITION BY store_id ORDER BY activity_date
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           )) AS revenue_array_7d_recalculado
    FROM daily_store_revenue
    ORDER BY store_id, activity_date
""").show(max_width=250)

Qué esperar (fragmento; la tabla completa tiene 21 filas, idéntica en forma a la tabla mostrada arriba).

=== Version SQL pura: recalculada desde cero con funcion de ventana, no incremental ===
┌──────────┬───────────────┬───────────────┬──────────────────────────────────────────┐
│ store_id │ activity_date │ daily_revenue │       revenue_array_7d_recalculado       │
│ varchar  │     date      │    double     │                 double[]                 │
├──────────┼───────────────┼───────────────┼──────────────────────────────────────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]                                   │
│ S01      │ 2026-08-04    │          3.45 │ [3.45, 8.45]                             │
│ S01      │ 2026-08-05    │          0.55 │ [0.55, 3.45, 8.45]                       │
│ S01      │ 2026-08-06    │           6.7 │ [6.7, 0.55, 3.45, 8.45]                  │
│ S01      │ 2026-08-07    │           5.0 │ [5.0, 6.7, 0.55, 3.45, 8.45]             │
│ S01      │ 2026-08-08    │         13.05 │ [13.05, 5.0, 6.7, 0.55, 3.45, 8.45]      │
│ S01      │ 2026-08-09    │           1.1 │ [1.1, 13.05, 5.0, 6.7, 0.55, 3.45, 8.45] │
└──────────┴───────────────┴───────────────┴──────────────────────────────────────────┘
  (21 filas en total -- S02 y S03 siguen el mismo patron)

Y la verificación formal, comparando ambas versiones fila por fila:

mismatches = con.sql("""
    WITH recalculated AS (
        SELECT store_id, activity_date,
               list_reverse(array_agg(daily_revenue) OVER (
                   PARTITION BY store_id ORDER BY activity_date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
               )) AS revenue_array_7d_recalc
        FROM daily_store_revenue
    )
    SELECT COUNT(*) AS mismatches
    FROM fact_store_activity f
    JOIN recalculated r ON f.store_id = r.store_id AND f.activity_date = r.activity_date
    WHERE f.revenue_array_7d != r.revenue_array_7d_recalc
""").fetchone()[0]
print(f"mismatches entre la version incremental y la recalculada: {mismatches}")
assert mismatches == 0
mismatches entre la version incremental y la recalculada: 0

Cero diferencias, otra vez. Esto confirma la misma idea que ya viste en la lección 4 con fact_sessions: cómo llegas al resultado (incremental, día a día, o recalculado desde cero con una ventana SQL) no cambia qué resultado obtienes, siempre que el patrón esté bien definido. La diferencia real entre ambos enfoques es de costo computacional a escala: la versión incremental de este script nunca vuelve a tocar un daily_revenue de hace varios días — solo lee la fila de ayer—; la versión con función de ventana, en cambio, recalcula el arreglo completo de cada fila cada vez que corre, releyendo todo el historial disponible. Con 7 días y 3 tiendas, la diferencia es invisible. Con años de historia y miles de tiendas —la escala real que motivó el patrón de Zach Wilson en Meta—, esa diferencia es, literalmente, la que separa un trabajo que corre en minutos de uno que corre en horas.

Diagrama: incremental vs recalculado

flowchart TD
    subgraph inc["Incremental (produccion real)"]
        A["Fila de ayer\n(arreglo YA calculado)"] --> B["+ dato de hoy"]
        B --> C["Fila de hoy\nNUNCA releyo fact_orders completo"]
    end

    subgraph win["Funcion de ventana (recalculo)"]
        D["fact_orders completo,\ncada vez que corre"] --> E["ROWS BETWEEN 6 PRECEDING\nAND CURRENT ROW"]
        E --> F["Arreglo recalculado\ndesde cero, cada fila"]
    end

    C -.->|"0 diferencias, verificado"| F

Errores comunes

Olvidar el recorte de tamaño y dejar que el arreglo crezca sin límite. Qué pasa: alguien implementa el list_prepend sin el [:7] (o el list_slice/slicing equivalente en SQL puro), y el arreglo crece un elemento por día indefinidamente. Por qué pasa: en una semana de 7 días, el arreglo de 7 días nunca llega a necesitar el recorte de verdad —el séptimo día es, coincidentemente, también el último día de datos disponibles—, así que el error puede pasar desapercibido en este dataset específico. Cómo detectarlo: si extendieras este ejercicio a un octavo día de datos, y revenue_array_7d tuviera 8 elementos en vez de 7, el recorte no está funcionando. Cómo corregirlo: siempre aplica el recorte —(nuevo_arreglo)[:7] en Python, o arr[1:7] en SQL puro de DuckDB— como parte del mismo paso que antepone el valor nuevo, nunca como un paso separado y opcional.

Confundir active_days_7d con len(revenue_array_7d). Qué pasa: alguien calcula active_days_7d como la longitud del arreglo, en vez de contar cuántos valores del arreglo son mayores a cero. Por qué pasa: en la mayoría de las filas de este dataset, ambos números coinciden —la tienda tuvo ventas casi todos los días—, así que la diferencia solo se nota en un caso específico. Cómo detectarlo: la fila de S03 del 2026-08-05 tiene len(revenue_array_7d) = 3 pero active_days_7d = 2 — si tu implementación da 3 para ambas, confundiste "días transcurridos" con "días con actividad real". Cómo corregirlo: active_days_7d cuenta valores mayores a cero (sum(1 for v in arr if v > 0) en Python, o len(list_filter(arr, x -> x > 0)) en SQL puro — la sintaxis exacta que vas a usar en la lección 7), nunca la longitud cruda del arreglo.

Asumir que daily_revenue = 0.0 significa que faltan datos. Qué pasa: alguien ve daily_revenue = 0.0 para S03 el 2026-08-05 y asume que es un error de carga —una fila que debería tener un valor y no lo tiene—. Por qué pasa: un cero se puede confundir fácilmente con un valor faltante, sobre todo en columnas donde casi todos los demás valores son positivos. Cómo detectarlo: si revisas fact_orders para S03 en esa fecha específica, vas a confirmar que, efectivamente, no hay ninguna orden registrada ese día para esa tienda — el cero es correcto, no un error. Cómo corregirlo: COALESCE(ROUND(SUM(revenue), 2), 0.0) en la consulta de daily_revenue está calculado a propósito para convertir "sin ninguna fila que sumar" en un 0.0 explícito, en vez de dejar un NULL que rompería list_sum y el resto de los cálculos de arreglo — un cero real, con significado de negocio ("esta tienda no vendió nada ese día"), no un valor faltante.

Ejercicios

Ejercicio 1 — Confirma que revenue_array_30d nunca superó los 7 elementos en esta semana de datos. Sin mirar la lección 7 todavía, escribe una consulta que muestre len(revenue_array_30d) para la última fila de cada tienda (2026-08-09), y explica por qué ningún valor supera 7.

Ver solución 
print(con.sql("""
    SELECT store_id, len(revenue_array_30d) AS elementos_en_array_30d
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
"""))

Salida esperada:

┌──────────┬────────────────────────┐
│ store_id │ elementos_en_array_30d │
│ varchar  │         int64          │
├──────────┼────────────────────────┤
│ S01      │                      7 │
│ S02      │                      7 │
│ S03      │                      7 │
└──────────┴────────────────────────┘

El tope de 30 elementos nunca se alcanza porque Kiosko solo tiene 7 días de historia disponibles en este dataset — el recorte [:30] sigue en el código y funcionaría correctamente si hubiera más datos, pero con solo 7 días acumulados, el arreglo de 30 días es, honestamente, idéntico al de 7 días. Este comportamiento es correcto, no un error: un negocio con menos historia que la ventana declarada simplemente tiene un arreglo más corto que su tope.

Ejercicio 2 — Verifica que S01 fue la única tienda con actividad los 7 días completos. Usando fact_store_activity, confirma cuáles tiendas tienen active_days_7d = 7 en la fila del 2026-08-09, y cuál tienda no.

Ver solución 
print(con.sql("""
    SELECT store_id, active_days_7d,
           CASE WHEN active_days_7d = 7 THEN 'activa los 7 dias' ELSE 'tuvo al menos un dia sin ventas' END AS estado
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
"""))

Salida esperada:

┌──────────┬────────────────┬──────────────────────────────────┐
│ store_id │ active_days_7d │              estado               │
│ varchar  │     int32      │              varchar              │
├──────────┼────────────────┼────────────────────────────────────┤
│ S01      │              7 │ activa los 7 dias                 │
│ S02      │              7 │ activa los 7 dias                 │
│ S03      │              6 │ tuvo al menos un dia sin ventas   │
└──────────┴────────────────┴────────────────────────────────────┘

S01 y S02 tuvieron ventas los 7 días de la semana; S03 (Santiago) tuvo un día sin ninguna venta registrada (2026-08-05), el mismo que ya identificaste en el ejemplo trabajado de esta lección.

Ejercicio 3 — Explica por qué la versión incremental y la versión de función de ventana dan el mismo resultado, aunque procesan los datos en un orden completamente distinto. En 2-3 frases, describe por qué el orden de cálculo (incremental día a día, o recalculado de una sola vez) no afecta el resultado final, siempre que los datos de entrada sean los mismos.

Ver solución

Ambos enfoques calculan exactamente la misma definición matemática: "los últimos 7 valores de daily_revenue, ordenados del más reciente al más antiguo, para cada tienda y cada fecha". La versión incremental llega a ese resultado acumulando un valor a la vez, mientras que la función de ventana lo recalcula de una sola pasada usando ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, pero ambos parten exactamente de los mismos 21 valores de daily_revenue en fact_orders. Como no hay ninguna ambigüedad en la definición —qué días entran en la ventana de cada fila está determinado únicamente por la fecha, no por el orden en que el motor procesa las filas—, cualquier algoritmo correcto que implemente esa misma definición tiene que llegar al mismo resultado, sin importar su mecánica interna.

Resumen y siguiente paso

Esta lección construyó fact_store_activity completo: 21 filas (3 tiendas × 7 días), con columnas revenue_array_7d y revenue_array_30d acumuladas día a día con list_prepend y recortadas a su tamaño máximo, verificadas de dos formas —contra el revenue semanal ya conocido de cada tienda (38.3/38.8/29.05), y contra una segunda implementación 100% SQL con función de ventana, con cero diferencias entre ambas—. También viste, con un caso real (S03 el 2026-08-05), que active_days_7d cuenta días con actividad, no días transcurridos — un cero en daily_revenue es un dato válido, no un dato faltante.

Antes de avanzar deberías poder: explicar por qué el recorte de tamaño ([:7]/[:30]) es una parte obligatoria del mecanismo, no un detalle opcional; distinguir len(arreglo) de active_days; y describir la diferencia de costo computacional entre el enfoque incremental y el de función de ventana, aunque ambos den el mismo resultado.

La lección 7 usa fact_store_activity ya construida para responder directamente la pregunta de negocio de este módulo: ¿cuántos días activos tuvo cada tienda en los últimos 7 y 30 días?, con list_sum y active_days como las herramientas centrales, sin volver a tocar fact_orders.

Recursos

← AnteriorCumulative table design: el patrón de Zach WilsonSiguiente →Calculando activos de 7 y 30 días por tienda