Che cos'è un totale corrente in SQL e come si calcola?

Il totale progressivo SQL è un modello molto comune, usato spesso in finanza e nell'analisi dei trend. In questo articolo imparerete cos'è un totale progressivo e come scrivere una query SQL per calcolarlo.

Il modo migliore per imparare l'SQL è la pratica. LearnSQL.it offre oltre 30 corsi interattivi di SQL a vari livelli di difficoltà. Ogni corso è interattivo: c'è un po' di lettura, seguita da un esercizio per mettere in pratica ciò che si è appena letto. Con ogni esercizio risolto si acquisisce fiducia nelle proprie competenze in SQL. Iscrivetevi subito gratuitamente!

Cos'è un totale corrente in SQL?

In SQL, un totale corrente è la somma cumulativa dei numeri precedenti in una colonna. Guardate l'esempio seguente, che presenta la registrazione giornaliera degli utenti di un negozio online:

La prima colonna indica la data. La seconda colonna mostra il numero di utenti registrati in quella data. La terza colonna, total_users, somma il numero totale di utenti registrati in quel giorno.

Ad esempio, il primo giorno (2020-03-05) si sono registrati 32 utenti e il valore totale degli utenti registrati è stato 32. Il giorno successivo (2020-03-06) si sono registrati 15 utenti; il valore di total_users è diventato 47 (32+15). Il terzo giorno (2020-03-07), sei utenti si sono registrati e il valore di total_users era 53. In altre parole, total_users è un valore continuo che cambia di giorno in giorno. È il numero totale di utenti in ogni giorno.

L'esempio successivo utilizza la colonna total_running per gestire le entrate dell'azienda in modo simile. Osservare la tabella seguente:

Per ogni giorno, la colonna total_revenue calcola l'ammontare delle entrate generate fino al giorno in questione. Il giorno 2020-04-04, l'azienda ha ottenuto un fatturato totale di 270.500 dollari, perché è la somma di tutti i ricavi dal 2020-04-02 al 2020-04-04.

I database relazionali (come SQL Server, Oracle, PostgreSQL e MySQL) e anche i motori non relazionali come Hive e Presto forniscono funzioni di finestra che consentono di calcolare un totale progressivo. Per conoscere le funzioni finestra, vi consiglio il corso interattivo Window Functions . Contiene oltre 200 esercizi per imparare le funzioni finestra utilizzandole.

A seguire, parleremo della query SQL che crea una somma di questo tipo e impareremo di più sulle funzioni finestra.

Come calcolare una somma cumulativa in SQL

Se si desidera calcolare un totale progressivo in SQL, è necessario conoscere le funzioni finestra fornite dal database. Le funzioni finestra operano su un insieme di righe e restituiscono un valore aggregato per ogni riga dell'insieme di risultati.

La sintassi della funzione finestra SQL che calcola una somma cumulativa di righe è la seguente:

window_function ( column ) 
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

È obbligatorio utilizzare la clausola OVER in una funzione finestra, ma gli argomenti di questa clausola sono facoltativi. Li discuteremo nei prossimi paragrafi di questo articolo.

Esempio 1

In questo esempio, calcoleremo la somma totale degli utenti registrati ogni giorno.

Questa query ...

SELECT registration_date,registred_users,
  SUM(registred_users) OVER (ORDER BY registration_date)
  AS total_users
FROM registration;

... seleziona la data di registrazione di tutti gli utenti. Abbiamo anche bisogno della somma di tutti gli utenti per ogni giorno, a partire dal primo giorno dato (2020-03-05) fino al giorno in quella riga.

Questo è l'insieme dei risultati:

Per calcolare il totale progressivo, utilizziamo la funzione aggregata SUM() e inseriamo come argomento la colonna registered_users; vogliamo ottenere la somma cumulativa degli utenti da questa colonna.

Il passo successivo consiste nell'utilizzare la clausola OVER. Nel nostro esempio, questa clausola ha un solo argomento: ORDER BY registration_date. Le righe del set di risultati sono ordinate in base a questa colonna (registration_date). Per ogni valore della colonna registration_date, viene calcolata la somma totale dei valori delle colonne precedenti (cioè la somma del numero di utenti prima della data della riga corrente) e viene aggiunto il valore corrente (cioè gli utenti registrati nel giorno della riga corrente).

Si noti che la somma totale viene mostrata nella nuova colonna, che abbiamo chiamato total_users.

Nella prima fase (data di registrazione 2020-03-05), abbiamo 57 utenti registrati. La somma degli utenti registrati in questo giorno è uguale a 57. Nel passo successivo, aggiungiamo a questo valore totale (57). Che cosa aggiungiamo? Il numero di utenti registrati alla data attuale (2020-03-06), che è 27; in questo modo otteniamo un totale progressivo di 84. Nell'ultima riga del set di risultati (per l'ultima data di registrazione, 2020-03-07) il totale progressivo è 100.

Grazie alle funzioni della finestra SQL, è facile trovare il numero totale cumulativo di utenti in un determinato periodo di tempo. Ad esempio, nel periodo 2020-03-05 - 2020-03-06, il numero totale di utenti registrati era 84.

Esempio 2

Nel secondo esempio, entreremo nel dettaglio degli utenti. Mostreremo gli utenti con i loro Paesi. Osservare la tabella sottostante:

Si noti che per ogni giorno viene mostrato separatamente il numero di utenti per ogni Paese. In questo esempio, calcoleremo una somma cumulativa separata di utenti registrati per ogni Paese.

Questa query ...

SELECT country, registration_date,registred_users,
  SUM(registred_users) 
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

... calcola la somma degli utenti per ogni giorno, prima per gli utenti inglesi e poi per quelli polacchi.

Ecco l'insieme dei risultati:

Per ogni paese, ogni giorno di registrazione riceve un totale progressivo. La clausola PARTITION BY nella clausola OVER ha come argomento la colonna country. In questo modo le righe vengono suddivise per paese, consentendo a SQL di calcolare un totale progressivo solo per quel paese (anziché per entrambi i paesi). Così, in Inghilterra dal 2020-03-05 al 2020-03-07, abbiamo un totale di 47 utenti. Per lo stesso periodo in Polonia, il totale degli utenti registrati era 53.

Esempio 3

Nell'ultimo esempio, analizzeremo i dati della tabella competition che contiene le colonne game_id, gamer_id, game_level, competition_date e score.

Dobbiamo controllare il punteggio totale cumulativo di ogni giocatore per ogni giorno in due giochi diversi. Guardate la query qui sotto, che crea questo totale progressivo:

SELECT game_id,game_level,gamer_id,competition_date,score,
  SUM(score)
  OVER (PARTITION BY game_id, gamer_id 
        ORDER BY competition_date)
  AS total_score
FROM competition;

Il risultato:

In questa tabella di risultati, possiamo leggere che il giocatore con ID=4 parte da un punteggio di 4 e finisce con un punteggio totale di 11. Il migliore è stato il giocatore con ID=7, che ha finito con un punteggio totale di 12.

Ancora una volta, nella clausola OVER utilizziamo PARTITION BY. Questa volta, utilizziamo un elenco di colonne (game_id, gamer_id). In questo modo è possibile creare due partizioni: una per la partita 1 e una per la partita 2.

Quindi, le righe sono state divise per gamer_id per ogni gioco. Nel gioco 1, abbiamo i giocatori 4 e 5; nel gioco 2, abbiamo i giocatori 6, 7 e 8. Per ogni gruppo (un dato giocatore gioca in un dato gioco), le righe sono ordinate per data_gara e il punteggio di ogni giorno viene sommato. In ogni gruppo, possiamo osservare la variazione del punteggio di ogni giocatore in una determinata partita.

Come si usa SQL Running Totals?

L'uso di un valore totale corrente nei report SQL può essere molto utile, soprattutto per gli specialisti di finanza. Per questo motivo, è utile sapere cos'è una somma cumulativa e come utilizzare le funzioni di SQL window per crearne una. In questo articolo sono stati presentati alcuni casi d'uso selezionati. Per saperne di più sulle funzioni finestra, consultate il nostro articolo SQL Window Function Example With Explanations o il corso LearnSQL. Window Functions.