Totale mobile in SQL: Cos'è e come si calcola

Il calcolo di un totale mobile in SQL è semplice se si utilizzano le funzioni della finestra. Queste pratiche metriche sono simili ai totali progressivi e alle medie mobili e vi aiuteranno a costruire un quadro chiaro dei vostri dati. In questo articolo spiegheremo ed esploreremo i totali mobili.

In SQL, il totale mobile è una metrica importante che aiuta a vedere come le tendenze cambiano nel tempo, fornendo un quadro chiaro dei dati. Calcola una somma su una "finestra" o un sottoinsieme di righe che si muove attraverso il set di risultati. Ad esempio, per monitorare le visite a un sito web, si può interrogare il totale delle visite per ogni giorno e sommare anche i 7 giorni precedenti per individuare le tendenze. Utilizzando le funzioni finestra di SQL, è possibile vedere queste tendenze in ogni riga dei risultati della query, senza dover ricorrere a strumenti esterni.

Per calcolare facilmente i totali mobili in SQL, è necessario utilizzare le funzioni finestra. Il nostro Window Functions (Funzioni Finestra) è perfetto se volete approfondire questo argomento. Il corso prevede 218 esercizi che coprono la sintassi e la semantica delle funzioni finestra. Imparerete a conoscere tutte le potenzialità delle funzioni finestra e i casi d'uso tipici. Inoltre, si acquisirà una comprensione dell'SQL avanzato che consentirà di eseguire analisi più complesse ed efficienti.

Un semplice totale mobile in SQL

Vediamo una situazione aziendale comune in cui il totale mobile dimostra la sua utilità. Consideriamo una tabella chiamata daily_sales che registra i totali delle vendite giornaliere di un negozio:

Se osserviamo questi dati, o anche se ne creiamo un grafico, ecco cosa vediamo:

Possiamo analizzare eventi specifici, ad esempio trovare date con vendite insolitamente basse. Ad esempio, le vendite del 2024-08-03 sono inferiori a quelle degli altri giorni. Tuttavia, questo modo di visualizzare le informazioni non è efficace per individuare le tendenze nell'evoluzione delle vendite. A tal fine, è necessario esaminare i totali progressivi. E per questo abbiamo bisogno delle funzioni SQL Window.

Se avete bisogno di una guida rapida alle funzioni SQL a finestra, tenete a portata di mano questo Cheat Sheet Window Functions (Funzioni Finestra) . Vi consentirà di velocizzare le vostre attività quotidiane di analisi dei dati.

Calcolo di un totale mobile in SQL

Scriviamo una query SQL che restituisca le due colonne da daily_sales e aggiunge un totale mobile di tre giorni all'insieme dei risultati:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (
        	ORDER BY purchase_date
        	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) AS rolling_total
FROM daily_sales;

Nell'esempio precedente, la clausola OVER dopo la funzione SUM() definisce la finestra (cioè il gruppo di righe) che vogliamo sommare per ogni riga. In questo caso, indica a SQL che la somma deve includere i dati delle due righe precedenti e di quella corrente. L'ordine è basato sulla data di acquisto. Questa è la nostra finestra mobile: il giorno corrente e i due giorni precedenti.

La query produce questi dati:

Nella prima riga, si noti che il totale mobile ripete semplicemente il valore della colonna total_sales - non ci sono righe precedenti da sommare. Nella seconda riga, viene sommato il valore di total_sales per la prima e la seconda riga; dalla terza riga in poi, viene restituita la somma dei valori di vendita delle due righe precedenti e di quella attuale.

Creiamo un grafico con i dati del totale mobile per capire come ci aiuta ad analizzare le tendenze:

La prima cosa che salta all'occhio guardando questo grafico è che dovremmo scartare i primi due punti di dati (le prime due date), perché non rappresentano un totale di tre giorni come le altre date. Dal 2024-08-03 in poi, la linea diventa quasi orizzontale, indicando che i totali delle vendite in tre giorni rimangono stabili. Questo è vero fino all'ultima data, che mostra un leggero aumento perché il totale non è più influenzato dal "calo" delle vendite dal 2024-08-03.

Con un set di dati così piccolo, anche le fluttuazioni minori influenzano la tendenza generale; con un set di dati di grandi dimensioni, questo non sarebbe il caso. Tuttavia, possiamo concludere che la combinazione di funzioni aggregate e funzioni finestra "attenua" l'effetto delle fluttuazioni o delle anomalie nei dati. Ciò consente una chiara visualizzazione delle tendenze dei dati.

Totale mobile, totale corrente e media mobile

Oltre al totale mobile, esistono altri due casi d'uso comuni per le funzioni finestra: il totale progressivo e la media mobile.

• Il totale progressivo è un calcolo cumulativo che scorre attraverso un insieme di risultati. Per ogni riga, calcola la somma di tutti i valori di una colonna, dalla prima riga alla riga corrente. È simile al totale mobile, ma esegue sempre la somma a partire dalla prima riga del set di risultati.
• La media mobile è simile al totale mobile in quanto prende i valori dalla finestra definita in relazione alla riga corrente. La differenza è che restituisce la media di questi valori invece del totale.

Il calcolo di queste metriche è abbastanza simile a quello di un totale mobile. Vediamo un paio di esempi.

Calcolo di un totale progressivo in SQL

Ecco un esempio di totale progressivo:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (ORDER BY purchase_date) AS running_total
FROM
    daily_sales;

La differenza tra il totale corrente e il totale mobile in SQL è la definizione della finestra nella clausola OVER. Invece di definire un sottoinsieme che copre un certo numero di righe rispetto alla riga corrente, è sufficiente specificare il criterio dell'ordine; si intende che il totale copre dalla prima riga a quella corrente. Per ulteriori informazioni sui totali correnti, consultare l'articolo Cos'è un totale corrente e come si calcola in SQL.

Calcolo di una media mobile in SQL

Il concetto di media mobile è molto simile a quello di totale mobile. La funzione aggregata AVG() opera su una finestra che scorre su un numero definito di righe. In effetti, la query per creare una media mobile in SQL è praticamente identica a quella utilizzata per il totale mobile. Basta cambiare la funzione SUM() in AVG():

SELECT
    purchase_date,
    total_sales,
    AVG(total_sales) OVER (
        	ORDER BY purchase_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_average
FROM
    daily_sales;

Questo restituirà la media delle vendite su un periodo di tre giorni: il giorno corrente e i due giorni precedenti. Per ulteriori informazioni sulle medie mobili, consultare questo articolo.

Altri casi d'uso per i totali continui

Consumo di energia settimanale

Esistono molti altri scenari in cui è possibile applicare un totale mobile. La maggior parte degli esempi riguarda tabelle di ordine cronologico, in cui la finestra dei dati rappresenta un intervallo di tempo (ad esempio 7 giorni, 30 giorni, 3 mesi, ecc.).

Un esempio potrebbe essere quello di analizzare l'evoluzione del consumo energetico di una famiglia nel tempo. Tali informazioni potrebbero essere memorizzate in una tabella (chiamata daily_consumption_per_day) che registra i chilowattora totali consumati dalla famiglia ogni giorno.

Ecco la query:

SELECT
    measure_date,
    daily_consumption,
    SUM(daily_consumption) OVER (
        ORDER BY measure_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_consumption
FROM
    daily_consumption_per_day;

Per ogni data, il risultato mostra la somma dei consumi degli ultimi 7 giorni (compreso il giorno corrente). Questo ci permette di vedere la variazione delle tendenze del consumo energetico nel tempo.

Le funzioni finestra hanno un grande potenziale. Scoprite tutto quello che potete fare con esse nella nostra guida completa alle funzioni finestra di SQL.

Utilizzo dei totali mobili oltre l'analisi delle serie temporali

La maggior parte dei casi di utilizzo dei totali mobili è legata all'analisi delle serie temporali. Tuttavia, queste statistiche possono essere applicate a qualsiasi serie di dati ordinati in base a una dimensione in cui abbia senso effettuare calcoli aggregati (ad esempio, totali, medie) su finestre scorrevoli che isolano sottoinsiemi di quella dimensione.

Un esempio potrebbe essere un'analisi del costo delle prestazioni mediche in base all'età. Supponiamo di avere una tabella che registra il costo delle prestazioni mediche ricevute da persone di varie età. Se ordiniamo le righe in base all'età, un totale progressivo ci permetterà di vedere come variano le spese mediche in base alla fascia d'età.

Supponiamo che la tabella si chiami medical_costs_by_age e che abbia due colonne: età e total_medical_cost. Per analizzare queste informazioni, scriveremo una query con un totale mobile che copre una finestra di cinque anni centrata sull'età della riga corrente (da due anni sotto a due anni sopra):

SELECT
    age,
    total_medical_cost,
    SUM(total_medical_cost) OVER (
        ORDER BY age
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS weighted_medial_cost
FROM
    medical_costs_by_age;

Da questo esempio, possiamo ottenere una comprensione chiara e abbastanza granulare della relazione tra gruppi di età e costi sanitari.

Volete saperne di più sui totali continui in SQL?

In questo articolo abbiamo visto diversi modi per utilizzare un totale mobile in SQL. Lo abbiamo confrontato con metriche simili, in particolare il totale progressivo e la media mobile.

Se prima di leggere questo articolo non conoscevate le funzioni finestra di SQL, probabilmente sarete impressionati da ciò che possono fare. Se volete imparare a conoscere le funzioni finestra da zero, vi consiglio il nostro Window Functions (Funzioni Finestra) corso. Se invece le conoscete già, potete prendere il set di esercizi Window Functions (Funzioni Finestra) . Contiene 100 esercizi interattivi sulle funzioni finestra che utilizzano tre diversi database reali. La nostra piattaforma di apprendimento fornisce tutti gli strumenti necessari, senza bisogno di installare nulla. Basta usare il browser e una connessione a Internet e il gioco è fatto!

Infine, potete mantenere la vostra padronanza dei totali arrotolati in SQL con questa serie di esercizi gratuiti sulle funzioni finestra. E se aspirate a un lavoro che richiede la padronanza di SQL e volete prepararvi per il colloquio di lavoro, non dimenticate di leggere queste domande per il colloquio sulla funzione finestra in SQL. Grazie per aver letto questo articolo e buon apprendimento!