Risposta

Il calcolo dei totali cumulativi e delle somme correnti è tradizionalmente risolto in SQL tramite funzioni di finestra (ad esempio, SUM() OVER(ORDER BY ...)), tuttavia nelle versioni più antiche o semplificate del DBMS sono disponibili solo sottoquery e raggruppamenti. Storicamente, gli architetti di database cercavano soluzioni alternative fino all'arrivo dello standard SQL:2003 con supporto per le funzioni di finestra.

Problema — in assenza di funzioni di finestra, per ogni riga è necessario calcolare esplicitamente la somma di tutti i valori precedenti, il che porta a query annidate O(N^2) con set di dati piuttosto grandi, a meno che non si utilizzino stratagemmi.

Soluzione:

Si utilizzano comunemente sottoquery correlate o tabelle temporanee con aggiornamento dei valori:

Esempio di codice:

-- Somma cumulativa con sottoquery correlata
SELECT t1.id, t1.amount,
   (
     SELECT SUM(t2.amount)
     FROM transactions t2
     WHERE t2.id <= t1.id
   ) AS running_total
FROM transactions t1
ORDER BY t1.id;

-- Attraverso una tabella temporanea con aggiornamento manuale del valore
CREATE TEMPORARY TABLE temp_running (id INT, amount INT, running_total INT);
-- Si itera sulle righe tramite codice esterno (ad esempio, pl/pgsql), aggiungendo progressivamente la somma

Caratteristiche chiave:

Il metodo funziona solo se c'è un criterio unico di ordinamento (id, data di produzione)
La sottoquery correlata scala male — crescita esponenziale del tempo di esecuzione
Per grandi volumi di dati è logico utilizzare ETL con aggregazione al di fuori di SQL o con strumenti procedurali

Domande trabocchetto.

Garantisce l'ordinamento ORDER BY in una sottoquery correlata?

No — la sottoquery non influenza necessariamente il risultato stesso. L'ordinamento del risultato finale è sempre spesificato esternamente nella query principale: il risultato dipende solo dalla filtrazione tramite WHERE.

È possibile parallelizzare il calcolo della somma cumulativa in questo approccio?

No — la sequenza è molto importante, specialmente nei calcoli che dipendono dalle righe precedenti, il che rende impossibile una semplice parallelizzazione in SQL normale.

Perché la sottoquery correlata è così lenta con un gran numero di righe?

Perché per ogni riga viene nuovamente calcolata la somma su un insieme di righe precedenti. Questo porta a operazioni O(N^2). Su un campione di 100k righe, questo può già richiedere minuti o addirittura ore.

Errori comuni e antipattern

Filtraggio errato per id invece che per la data effettiva — le somme "saltano" su buchi negli id
Tentativo di eseguire somme senza ordinare i dati
Utilizzo di questo approccio per tabelle enormi, quando è necessario un ETL o un'elaborazione partizionata

Esempio nella vita reale

Caso negativo

Un analista ha calcolato il totale cumulativo giornaliero di guadagni per data tramite una sottoquery correlata, mentre nella tabella erano presenti periodicamente id rimossi (buchi). La somma finale aveva cali bruschi e dipendeva non dalla data, ma dall'ordine degli id.

Pro:

Funziona per piccoli campioni, non richiede funzioni di finestra

Contro:

I dati non sono corretti, calcola in modo diverso da quanto atteso
Supporto complesso

Caso positivo

Un ingegnere ha trasferito l'elaborazione della somma cumulativa in uno script ETL (Python/pandas), quindi ha caricato i valori finali in una tabella separata, sincronizzando solo le novità. I risultati sono sempre coerenti per data, e il codice funziona rapidamente anche con milioni di record.