Date spine e metriche temporali in SQL

Anche questa lezione appartiene al binario ml-tabellare: spine, medie mobili e candele si costruiscono tutte con query su tabelle.

L’idea in una frase

La date spine impone una riga per ogni giorno del periodo, così che zeri reali e assenze restano distinguibili in ogni metrica temporale.

Il percorso in cinque passi

1. Genera una colonna di date consecutive con generate_series o GENERATE_DATE_ARRAY e con estremi pari alla finestra di analisi.
2. Aggrega i fatti alla stessa granularità della spina prima di agganciarli, per evitare moltiplicazioni di righe.
3. Aggancia i fatti con LEFT JOIN guidata dalla spina e riempi con COALESCE(..., 0) solo conteggi e importi, mai prezzi e tassi.
4. Calcola medie mobili e totali rolling con finestra ROWS BETWEEN su serie densificata e confronta l’anno precedente con scarto fisso.
5. Filtra solo sulla colonna della spina e materializza la serie quando la stessa logica compare in più query.

Perché raggruppare per data racconta una storia a buchi

Una tabella con due ordini il tre marzo e uno il sei marzo produce una query spontanea con due sole righe. Il quattro, il cinque e il sette marzo non esistono nell’output. Per un report tabellare è quasi accettabile, mentre per un grafico è una trappola, perché la libreria unisce i punti esistenti. Per i calcoli è peggio: una media mobile su righe sparse divide per i giorni presenti invece che per sette e gonfia il risultato proprio nei periodi fiacchi.

-- Ricavo giornaliero: semplice, ma i giorni vuoti spariscono
SELECT
  DATE(creato_il) AS giorno,      -- tronca il timestamp al giorno
  SUM(importo) AS ricavo          -- somma degli ordini del giorno
FROM ordini
WHERE creato_il >= DATE '2026-03-01'
  AND creato_il < DATE '2026-03-08'
GROUP BY 1
ORDER BY 1;

Lo stesso buco si apre con conteggi distinti per giorno e con troncamenti settimanali con settimane parziali ai bordi. Il punto concettuale è uno solo: in SQL le righe mancanti non sono zeri ma assenze. Finché non esiste una riga per ogni giorno, nessuna aggregazione distingue zero vendite da giorno non considerato.

Costruire una colonna di date consecutive su ogni dialetto

Una date spine è una tabella con una riga per ogni giorno dell’intervallo analizzato. Il modo di generarla cambia per dialetto, ma il risultato è identico: una sola colonna giorno. Gli estremi devono coincidere con la finestra di analisi da entrambi i lati. La granularità va decisa prima, perché mescolare granularità diverse produce duplicati silenziosi. Per analisi ricorrenti conviene una tabella calendario persistente con attributi di settimana, festività e mese fiscale.

-- PostgreSQL e DuckDB: generate_series, la via più leggibile
SELECT giorno::DATE AS giorno
FROM generate_series(DATE '2026-03-01', DATE '2026-03-31', INTERVAL '1 day') AS giorno;

-- BigQuery: generazione di array poi esploso in righe
SELECT d AS giorno
FROM UNNEST(
  GENERATE_DATE_ARRAY(DATE '2026-03-01', DATE '2026-03-31', INTERVAL 1 DAY)
) AS d;

-- Snowflake e Databricks: tabella di sistema con indice di riga
SELECT DATEADD(DAY, SEQ4(), DATE '2026-03-01') AS giorno
FROM TABLE(GENERATOR(ROWCOUNT => 31));

Densificare la serie con la join che riempie i vuoti

Con la spina pronta il pattern è sempre lo stesso: spina a sinistra, fatti a destra, trasformazione delle assenze in zeri dove ha senso. Aggregare i fatti per giorno prima della JOIN evita di moltiplicare le righe quando un giorno ha molti ordini. Partire dai fatti con JOIN inversa funziona, ma rende la query illeggibile appena si aggiunge una seconda metrica.

-- Ricavo giornaliero densificato: ogni giorno compare, anche a zero
WITH spine AS (
  -- una riga per giorno di marzo 2026
  SELECT giorno::DATE AS giorno
  FROM generate_series(DATE '2026-03-01', DATE '2026-03-31', INTERVAL '1 day') AS giorno
),
vendite AS (
  -- aggrega gli ordini per giorno prima della join: meno righe da agganciare
  SELECT DATE(creato_il) AS giorno, SUM(importo) AS ricavo
  FROM ordini
  WHERE creato_il >= DATE '2026-03-01'
    AND creato_il < DATE '2026-04-01'
  GROUP BY 1
)
SELECT
  s.giorno,
  COALESCE(v.ricavo, 0) AS ricavo   -- giorno senza ordini: zero, non assenza
FROM spine AS s
LEFT JOIN vendite AS v
  ON v.giorno = s.giorno
ORDER BY s.giorno;

Variante	Cosa succede ai giorni vuoti	Quando usarla
INNER JOIN tra spine e fatti	Spariscono di nuovo	Mai, vanifica la spine
LEFT JOIN + COALESCE(..., 0)	Diventano zeri	Ricavi, conteggi, quantità
LEFT JOIN senza COALESCE	Restano NULL	Prezzi e tassi, dove lo zero sarebbe falso

La terza riga della tabella è la più importante: densificare non significa sempre mettere zero. Per una metrica intensiva come il prezzo medio, il valore di un giorno senza osservazioni resta sconosciuto, e scriverci zero corrompe le medie successive.

Medie mobili e totali rolling con le window function

La media mobile su serie densificata usa una finestra per righe ordinata per giorno. Con una riga per giorno, la finestra di sette righe copre esattamente sette giorni di calendario; sui dati sparsi coprirebbe invece le ultime righe esistenti anche su tre settimane, e la media resterebbe alta pure con vendite ferme da giorni. I primi giorni della serie hanno finestra parziale e vanno filtrati oppure segnalati come transitori. Per totali cumulati da inizio anno la finestra con inizio non limitato resta più economica di una auto-JOIN.

-- Media mobile a 7 giorni e totale rolling a 28 giorni sul ricavo densificato
WITH giornaliero AS (
  -- la CTE densificata del paragrafo precedente: un giorno, una riga
  SELECT s.giorno, COALESCE(v.ricavo, 0) AS ricavo
  FROM spine AS s
  LEFT JOIN vendite AS v ON v.giorno = s.giorno
)
SELECT
  giorno,
  ricavo,
  AVG(ricavo) OVER (
    ORDER BY giorno
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- finestra di 7 righe: oggi + 6 giorni prima
  ) AS media_mobile_7g,
  SUM(ricavo) OVER (
    ORDER BY giorno
    ROWS BETWEEN 27 PRECEDING AND CURRENT ROW -- totale degli ultimi 28 giorni
  ) AS totale_rolling_28g
FROM giornaliero
ORDER BY giorno;

Confronti con l’anno precedente senza autojoin contorte

La variazione anno su anno con LAG a scarto fisso evita una auto-JOIN sulla data meno un anno. Lo scarto pari a cinquantadue settimane allinea il giorno della settimana e risulta più stabile dello scarto di trecentosessantacinque giorni, che sfasa weekend e festività. Per metriche mensili l’equivalente è lo scarto di dodici periodi su serie mensile. La protezione contro divisione per zero con NULLIF resta obbligatoria per i nuovi segmenti.

-- Ricavo giornaliero contro stesso giorno dell'anno prima e variazione percentuale
SELECT
  giorno,
  ricavo,
  LAG(ricavo, 364) OVER (ORDER BY giorno) AS ricavo_anno_prima,
  -- 364 = 52 settimane: allinea il giorno della settimana, più stabile del 365
  CASE
    WHEN LAG(ricavo, 364) OVER (ORDER BY giorno) = 0 THEN NULL  -- evita la divisione per zero
    ELSE (ricavo - LAG(ricavo, 364) OVER (ORDER BY giorno))
       / LAG(ricavo, 364) OVER (ORDER BY giorno)
  END AS variazione_yoy
FROM giornaliero
ORDER BY giorno;

Candele giornaliere quando servono davvero

Aperto, massimo, minimo e chiuso (OHLC) riassumono una serie intraday con primo e ultimo prezzo più gli estremi toccati. Servono in tesoreria e monitoraggio prezzi, dove la volatilità dentro il giorno conta quanto il livello. In SQL si ottengono con aggregazione per giorno più numerazione con ROW_NUMBER ordinata per orario. Il trucco con massimo condizionato estrae un valore posizionale dentro un raggruppamento per giorno, perché esiste una sola prima riga per giorno.

-- Candela giornaliera dei prezzi: primo, massimo, minimo e ultimo prezzo del giorno
WITH prezzi_ordinati AS (
  SELECT
    DATE(ts) AS giorno,          -- giorno di calendario della quotazione
    prezzo,                     -- prezzo rilevato in quel momento
    ROW_NUMBER() OVER (PARTITION BY DATE(ts) ORDER BY ts) AS rn_asc,
    -- numero di riga dal mattino: 1 = prima quotazione = apertura
    ROW_NUMBER() OVER (PARTITION BY DATE(ts) ORDER BY ts DESC) AS rn_desc
    -- numero di riga dalla sera: 1 = ultima quotazione = chiusura
  FROM quotazioni
  WHERE ts >= TIMESTAMP '2026-03-01 00:00:00'
    AND ts < TIMESTAMP '2026-04-01 00:00:00'
)
SELECT
  giorno,
  MAX(CASE WHEN rn_asc = 1 THEN prezzo END) AS apertura,   -- primo prezzo del giorno
  MAX(prezzo) AS massimo,                                  -- estremo superiore
  MIN(prezzo) AS minimo,                                   -- estremo inferiore
  MAX(CASE WHEN rn_desc = 1 THEN prezzo END) AS chiusura   -- ultimo prezzo del giorno
FROM prezzi_ordinati
GROUP BY giorno
ORDER BY giorno;

Apertura e chiusura hanno senso solo con ordinamento temporale totale e criteri secondari a parità di timestamp. Se i giorni senza quotazioni devono comparire, la spina torna in gioco con LEFT JOIN, ricordando che per i prezzi l’assenza resta NULL e mai zero.

Quanto costa una spina e come tenerla sotto controllo

Una spina giornaliera su tre anni sono circa millecento righe: non è mai un problema. I costi nascono con granularità fine incrociata con dimensioni ad alta cardinalità, come giorni per prodotto per magazzino. Tre regole tengono i costi in ordine: generare solo l’intervallo che serve, densificare dopo aver aggregato e materializzare la serie per report ricorrenti. La clausola di filtro sui fatti deve restare dentro il passaggio dei fatti, prima della JOIN. Filtrare dopo la LEFT JOIN sul risultato trasforma silenziosamente la LEFT JOIN in INNER JOIN per tutte le righe a zero.

Quando la date spine è la scelta sbagliata

La spina aggiunge righe senza informazione quando l’analisi riguarda solo giorni con attività, come tassi per giorno di campagna. Se la metrica è già una fotografia periodica, come saldi di fine mese, la serie è completa per costruzione. Per finestre lunghe a granularità fine conviene preaggregare prima di densificare, oppure densificare solo l’ultimo tratto mobile mostrato dal report. Per confronti tra coorti con compleanno diverso serve una spina di interi da zero a N giorni dall’evento origine, e non una spina di date di calendario.

In sintesi: usa righe per finestra temporale su serie densificata e mai su dati sparsi, con scarto di cinquantadue settimane per confronti annuali su dati settimanali.

Verdetto: densifica sempre con la spina prima di calcolare medie mobili e usa lo scarto di cinquantadue settimane per i confronti annuali; su dati sparsi le finestre per righe mentono.

Il caso Johns Hopkins: la media mobile che ha reso leggibile la pandemia

La Johns Hopkins University e Our World in Data, dal 2020, pubblicarono i contagi giornalieri con media mobile a sette giorni per levigare ritardi di notifica e crolli festivi. La media a sette giorni rese leggibili picchi e inversioni che i dati grezzi giornalieri nascondevano tra oscillazioni spurie. Le dashboard dichiararono in modo esplicito finestra e riempimento dei giorni mancanti. Il caso mostra perché la densificazione con spina e finestra per righe divenne lo standard per ogni serie epidemiologica e finanziaria successiva.

Domande per chiudere la lezione

1. Perché una media mobile su dati sparsi gonfia il risultato nei periodi fiacchi?
2. Quando un giorno vuoto deve diventare zero con COALESCE e quando deve restare NULL?
3. Perché lo scarto di cinquantadue settimane stabilizza il confronto annuale su dati settimanali?
4. Quale filtro trasforma per errore una LEFT JOIN con spina in una INNER JOIN?