7 situazioni reali in cui è necessario un totale progressivo e come calcolarlo in SQL

Volete sapere cos'è un totale progressivo e come si calcola in SQL? In questo articolo descriverò diversi scenari aziendali in cui è necessario un totale progressivo. Vi insegnerò anche come calcolare un totale progressivo in SQL con l'aiuto delle funzioni della finestra. Pronti? Andiamo!

Definizione di totale progressivo

Un totale progressivo è la somma cumulativa di un valore e di tutti i valori precedenti della colonna.

Ad esempio, immaginiamo di lavorare nel settore delle vendite e di memorizzare informazioni sul numero di articoli venduti in un determinato giorno. Si potrebbe voler calcolare un totale progressivo, ovvero il numero totale di articoli venduti fino a una data specifica.

Di seguito è riportata una tabella con il numero di articoli venduti in un determinato giorno:

Numero di articoli venduti e totale progressivo

La terza colonna è il totale progressivo calcolato. Tenere presente che il valore della riga corrente è sempre incluso nel totale progressivo.

Ad esempio, il 05 gennaio 2021, il totale corrente è 66. Si tratta del numero totale di articoli venduti dal 01 gennaio 2021 al 05 gennaio 2021 (compreso il 05 gennaio 2021). In particolare, il calcolo è 10 + 12 + 15 + 9 + 20 = 66.

Calcolo del totale progressivo in SQL

In SQL, il calcolo del totale corrente viene effettuato con l'aiuto delle funzioni della finestra. Questa speciale funzione SQL consente di eseguire calcoli complessi con poche righe di codice SQL.

Ecco la funzione finestra per l'esempio precedente:

SELECT *,
      SUM(no_of_items) OVER(ORDER BY date) AS running_total 
FROM sales

Ecco la spiegazione del codice:

• Questa è una funzione finestra, indicata con la clausola OVER.
• All'interno della clausola OVER, c'è un ORDER BY Questo ci dice come vengono ordinate le righe prima di effettuare il calcolo. Nel nostro caso, le righe sono ordinate in base alla colonna data.
• Quando si utilizzano le funzioni finestra, ogni record viene calcolato in modo autonomo. Quindi, il risultato viene visualizzato con una colonna aggiuntiva. Quando si lavora con le funzioni a finestra, non ci sono righe collassate. Questa è la differenza principale tra la clausola GRUPPO PER e funzioni della finestra in SQL.
• All'interno dell'istruzione OVER, si può trovare anche la parola chiave PARTITION BY. PARTITION BY raggruppa le righe in partizioni in cui ogni partizione viene calcolata separatamente. Nel nostro esempio, le righe non sono organizzate in partizioni. Pertanto, l'intero set di dati viene trattato come un unico gruppo.
• Quando si lavora con le funzioni finestra, per ogni record di una tabella si definisce un riquadro finestra all'interno del quale viene effettuato un calcolo specifico. Questa è indicata dalla parola chiave ROW/RANGE all'interno dell'istruzione OVER. Se questa parola chiave non è definita (come nel nostro esempio), viene preso un valore predefinito per la cornice della finestra. Una cornice predefinita per la riga corrente include la riga corrente e tutte le righe precedenti.
• Nel nostro esempio, ogni record della somma totale della tabella viene calcolato sommando tutti i valori precedenti più quello attuale. Ad esempio, il totale corrente per il 05 gennaio 2021 è 66. Si tratta della somma dei quattro valori precedenti (10, 12, 15 e 9) più il valore della riga corrente (20).

Esempi di totali correnti

Diamo ora un'occhiata agli scenari reali in cui si utilizzano i totali progressivi.

1. Tenere traccia delle quote pianificate e di quelle realizzate

Nel settore delle vendite, ogni venditore deve raggiungere degli obiettivi per soddisfare i requisiti di lavoro specifici. Allo stesso modo, nei settori delle telecomunicazioni e delle banche, ogni divisione deve acquisire un numero specifico di nuovi clienti in ogni trimestre e vendere un numero specifico di prodotti ai clienti.

La direzione tiene traccia delle prestazioni utilizzando i totali progressivi. Ogni giorno, il totale progressivo viene aggiornato con nuovi dati. Di solito questi numeri vengono valutati mensilmente, trimestralmente o annualmente.

Ecco una tabella che tiene conto del numero di nuovi prestiti nel 2021 su base mensile:

Numero di nuovi prestiti, pianificati vs. realizzati

Per il primo semestre del 2021, si decide di vendere ai clienti 535 prestiti in questo arco di tempo. I piani vengono fatti per ogni mese separatamente e 535 è l'obiettivo totale corrente per questi sei mesi.

La direzione tiene traccia delle quote pianificate rispetto a quelle realizzate. Per ora sono stati venduti 112 nuovi prestiti. La tabella continuerà a essere aggiornata con il passare del tempo.

Di seguito è riportato il codice SQL che calcola i totali correnti:

SELECT 
      date,
      plan,
      SUM(plan) OVER(order by Date) AS plan_running_total,
      realization,
      SUM(realization) OVER(order by Date) AS realization_running_total
FROM sales;

Come nell'esempio precedente, per calcolare il totale progressivo viene utilizzata una funzione finestra. La funzione finestra è indicata con OVER e all'interno delle parentesi è definito l'ordine delle righe.

La funzione di aggregazione utilizzata è SUM. Abbiamo due totali correnti: le quote pianificate e quelle realizzate. Quindi, abbiamo due funzioni finestra e due colonne vengono aggiunte alla tabella.

2. Calcolo del saldo

I totali correnti vengono utilizzati anche per il calcolo del saldo. Ogni volta che viene effettuata una nuova transazione (un pagamento o un prelievo da un conto), la somma cumulativa viene aggiornata e viene visualizzato il saldo corrente.

Di seguito è riportata una tabella del saldo:

Importo delle transazioni e saldo corrente

Nella tabella qui sopra, vediamo che la prima transazione è avvenuta il 01 dic 2020-12-01, con un afflusso di 5.000 dollari. Il saldo di questo giorno era di 5.000 dollari. Successivamente, il cliente ha iniziato a spendere denaro.

Il 03 dic 2020, il cliente ha speso 50 dollari (questa transazione è indicata con un valore negativo). Il saldo è quindi sceso a 4.950 dollari. Il giorno successivo, il saldo è diminuito di altri 125 dollari, passando a 4.825 dollari, e così via.

Il saldo del conto viene calcolato come un totale progressivo. È la somma cumulativa di tutte le transazioni associate a quel conto. Ad ogni nuova transazione, il saldo viene aggiornato, ovvero il totale corrente viene ricalcolato.

Ecco la funzione finestra per questo esempio:

SELECT date,
            transaction,
           SUM(transaction) OVER(order by date) AS balance_amount 
FROM balance;

3. Operazioni di cassa

I totali correnti sono utilizzati anche nelle operazioni di cassa.

Ad esempio, quando un cliente acquista articoli in un negozio, la cassiera esamina gli articoli nel carrello. Per ogni nuovo articolo scansionato, sullo schermo viene visualizzato il totale corrente aggiornato. Questo mostra quanto il cliente deve pagare per gli articoli scansionati fino a quel momento.

Di seguito è riportato un esempio di cestino in fase di scansione. I pannolini sono l'ultimo articolo scansionato. Finora il cliente deve pagare 19,70 euro. Lo shampoo non è ancora stato elaborato, quindi il suo prezzo non è ancora calcolato nel totale corrente:

Registratore di cassa: scansione dei prodotti

Questa è quasi la stessa funzione della finestra dell'esempio precedente (solo i nomi delle tabelle e delle colonne differiscono):

SELECT 
       *,
      SUM(price) OVER(order by date) as running_total 
FROM cash_register;

Quindi, ogni volta che la cassiera esamina un articolo, la somma cumulativa viene aggiornata. A ogni scansione, il cassiere sa quanto il cliente deve pagare finora.

4. Conteggio dell'apporto calorico giornaliero

Se si vuole perdere peso, si può utilizzare un totale progressivo per calcolare l'apporto calorico giornaliero. Ogni giorno si inizia il conteggio delle calorie da zero e poi si aggiorna il totale corrente in base a cosa e quanto si mangia.

Di seguito è riportato il conteggio delle calorie di un giorno:

Il totale corrente viene aggiornato ogni volta che si mangia qualcosa. In questo modo, si sa sempre quanto si è già mangiato e quanto si può mangiare ancora quel giorno. Questo approccio è chiamato dieta del conteggio delle calorie, che è un'opzione molto diffusa.

Ecco il codice che calcola il totale corrente:

SELECT calorie_intake.*,
      SUM(calories) OVER(ORDER BY hours) AS running_total
FROM calorie_intake;

Vediamo alcuni altri scenari in cui si utilizzano i totali progressivi. Tenete presente che le funzioni della finestra che calcolano i totali di corsa rimangono per lo più le stesse. Pertanto, d'ora in poi, spiegherò brevemente gli esempi senza includere il codice SQL.

5. Casi COVID-19 confermati

Ogni paese raccoglie ogni giorno informazioni sul numero totale di casi COVID-19 confermati, di decessi e di casi guariti. Con queste nuove informazioni, vengono aggiornati i totali progressivi in modo che ogni Paese sappia quanti casi confermati, morti e guariti ha avuto finora, dall'inizio della pandemia a oggi.

Per maggiori dettagli, si veda come viene calcolato il totale progressivo utilizzando i dati John Hopkins: come analizzare i dati COVID-19.

6. Utenti registrati di un'applicazione mobile

I proprietari di applicazioni mobili di solito vogliono vedere la somma cumulativa dei nuovi utenti registrati e delle installazioni/disinstallazioni effettuate nell'ultimo mese, trimestre e/o anno. Anche in questo caso, è possibile utilizzare i totali progressivi per ottenere maggiori informazioni. Queste statistiche indicano ai proprietari come è stata accolta l'applicazione o una specifica funzionalità e come sviluppare il prodotto in futuro.

Analogamente, se gestite un sito web, una delle metriche più importanti da monitorare è il numero totale di pagine viste o di visite. Di solito queste informazioni vengono raccolte quotidianamente. Calcolando il totale progressivo, si può vedere come si comporta la somma cumulativa nel tempo e come si confronta con i totali progressivi passati.

7. Programmi di fidelizzazione delle compagnie aeree

Questo concetto è probabilmente noto a chi vola spesso. Con ogni volo si raccolgono punti. Una volta raccolto un certo numero di punti, si ottengono sconti e/o premi.

Per calcolare il numero totale di punti accumulati nel proprio conto, viene utilizzato un totale progressivo. Ogni volta che si acquista un biglietto, il totale corrente viene aggiornato e i punti aumentano.

Riassunto

In questo articolo ho mostrato come i totali progressivi vengono utilizzati in diversi scenari. Come avete appreso, i totali progressivi hanno molte applicazioni.

Le funzioni finestra di SQL consentono di calcolare i totali progressivi in modo relativamente semplice, con poche righe di codice. Le funzioni finestra vengono utilizzate quando si tratta di calcoli complessi. Una volta imparata la sintassi, si potrà scrivere codice più pulito e comprensibile.

Anche se ho spiegato come utilizzare le funzioni finestra solo per il calcolo dei totali progressivi, le funzioni finestra sono ampiamente utilizzate per una serie di calcoli. In questo articolo con esempi di funzioni finestra, potrete conoscere altri modi di utilizzare le funzioni finestra.

Offriamo anche un ottimo corso interattivo su Window Functions con molti esempi. Se volete approfondire le funzioni finestra, vi consiglio di seguire questo corso. Fornisce molti esercizi per esercitarsi, il che è importante quando si acquisiscono nuove competenze in SQL.