Come analizzare una serie temporale in SQL

I valori ordinati in base al tempo sono chiamati serie temporali. In questo articolo vi mostrerò i modelli più frequenti e vi insegnerò a scrivere query per le serie temporali in SQL con l'aiuto delle funzioni finestra.

Forse vi è capitato di analizzare alcune variabili in cui ogni valore era associato a un valore temporale. Tali dati, in cui i valori sono ordinati in base al tempo, sono chiamati dati di serie temporali. Dato che sono così frequentemente utilizzati nel mondo degli affari, è importante imparare i modelli comuni utilizzati per analizzare questo tipo di dati.

Esempi di dati di serie temporali

I dati delle serie temporali sono variabili con una componente aggiuntiva: il tempo. Ciò significa che ogni valore di un attributo è associato a una data o a un valore temporale. Ecco alcuni esempi di dati di serie temporali:

• Il prezzo giornaliero delle azioni di una particolare società nell'ultimo anno. (Ogni prezzo delle azioni è associato a un giorno specifico).
• La media giornaliera dell'indice Dow Jones degli ultimi 10 anni. (Ogni valore dell'indice è associato a un giorno specifico).
• Visite uniche a un sito web in un mese. (Ogni mese, il numero di visite varia).
• Gli utenti registrati di un sito web per ogni giorno.
• Dati di vendita settimanali.
• Entrate e spese annuali dell'azienda nell'arco di un decennio. (L'anno è il valore temporale).
• Accessi giornalieri alle app per due mesi. (Il giorno è il valore temporale).

In questo articolo analizzeremo la popolarità di due siti web fittizi attraverso una misura chiamata "numero totale di visite giornaliere". Le query SQL di cui parleremo possono essere utilizzate per altre analisi di serie temporali, ovvero sono applicabili ad altre serie di dati temporali.

Osserveremo il periodo dal 1° luglio 2019 al 31 dicembre 2019.

Ecco i dati:

tabellavisite_giornaliere

Questa tabella si chiama daily_visits e contiene i seguenti attributi:

• data - Qualsiasi giorno tra il 01 luglio e il 31 dicembre 2019.
• visite - Il numero totale di visite al sito web in una data specifica.
• weekend - Questo valore è 'N' se la data è un giorno feriale e 'Y' se è un sabato o una domenica.
• website - Il nome di dominio del sito web ("www.sqlanalysts.com" o "www.sqldevelopers.com").

Come si può notare, ogni riga della tabella daily_visits (cioè ogni valore dell'attributo visite ) è associato a un giorno (l'attributo data ). Questo è un esempio di serie temporale di dati.

Esecuzione dei totali con SQL Window Functions

Inizieremo la nostra analisi utilizzando uno schema chiamato totale corrente. Un totale progressivo è la somma cumulativa di tutti i numeri precedenti di una colonna.

Di seguito è riportato un totale progressivo per il numero di visite dal 01 luglio a un giorno specifico. Si noti che questo viene calcolato per ogni sito:

Nell'immagine qui sopra, si può notare come il 01 luglio il totale progressivo sia 2.805. (È uguale al numero di visite di quel giorno) Questo perché non ci sono dati per le date precedenti al 01 luglio; stiamo iniziando il calcolo da questa data.

Il giorno successivo (02 luglio) il valore della riga precedente viene aggiunto al numero attuale di visite. Il totale corrente per questo giorno è 7.203 - il numero di visite del 01 luglio più il numero di visite del 02 luglio. Il giorno successivo, il 03 luglio, aggiungiamo questo numero (6.744) al totale precedente (7.203) e otteniamo 13.947 visite. E così via.

Questo è solo uno degli esempi di business in cui viene utilizzato lo schema del totale progressivo; altri esempi includono:

• Numero totale di articoli venduti dal primo giorno di un mese fino al giorno osservato nello stesso mese.
• Totale corrente (somma cumulativa) delle transazioni di debito/credito di un conto bancario nell'ultimo trimestre o anno.
• Totale delle entrate da gennaio al mese osservato dello stesso anno.

Ora che sappiamo cos'è un totale progressivo, scriviamo una query SQL che ne calcola uno. Un totale progressivo (o una somma cumulativa) può essere calcolato in SQL utilizzando le funzioni finestra appropriate.

Le funzioni finestra sono funzioni SQL speciali che operano su un insieme di righe. Sono simili alla clausola GROUP BY, ma il risultato viene visualizzato in modo diverso alla fine. In un semplice GROUP BY, le righe vengono compresse (ogni gruppo viene visualizzato come una riga). Con le funzioni finestra, le righe non vengono compresse; ogni riga viene restituita e il valore del calcolo viene assegnato a ogni riga della tabella. Questo è esattamente ciò di cui abbiamo bisogno quando visualizziamo il totale corrente: un valore aggiuntivo in una tabella esistente.

Di seguito è riportata una query SQL che calcola il totale progressivo sui dati daily_visit dati:

SELECT
   *, 
  SUM(visits) OVER (PARTITION BY website ORDER BY date) AS running_total 
FROM daily_visits;

Quando si esegue questa query, tutte le righe vengono visualizzate e viene creata una colonna aggiuntiva, running_total. Ecco una breve spiegazione di ciò che accade:

• La clausola OVER, con PARTITION BY, indica a SQL che questa sarà una funzione finestra.
• PARTITION BY divide le righe in gruppi logici. Nel nostro esempio, le righe sono raggruppate a livello di sito web.
• ORDER BY imposta l'ordine delle righe. Nel nostro esempio, tutte le righe sono ordinate in base alla colonna data.
• ROW/RANGE non è definito esplicitamente in questo esempio. Queste parole chiave opzionali indicano che ogni finestra all'interno di una partizione comprende tutte le righe dall'inizio della partizione alla riga corrente. In altre parole, per ogni riga, la somma totale viene calcolata come somma dei valori dalla prima riga della partizione fino alla riga corrente.

Con questa istruzione SQL (OVER in combinazione con PARTITION BY e ORDER BY), si imposta un totale corrente delle visite a livello di sito web.

Non intendo approfondire la spiegazione e la sintassi di questa query. Se volete imparare le funzioni finestra in modo più dettagliato, vi consiglio il corso LearnSQL.it sulle funzioni finestra. Date un'occhiata all'articolo di accompagnamento, Corso SQL del mese - Window Functions; risponde a domande come perché dovreste imparare le funzioni finestra, cosa sono e perché questo corso è un'ottima scelta.

Le funzioni finestra sono utilizzate per altri calcoli di pattern. Nella prossima sezione, daremo un'occhiata al foglio illustrativo delle funzioni finestra e vi mostrerò come scrivere query SQL per la variazione percentuale e le medie mobili.

Variazione percentuale delle visite giornaliere al sito web

Vi capiterà spesso di dover descrivere come la vostra attività cambia nel tempo. Esiste un altro modello molto comune utilizzato nell'analisi delle serie temporali, chiamato "percentuale di variazione" (o percentuale/percentuale di variazione). Risponde a domande come:

• Come si colloca il fatturato di questo mese rispetto a quello del mese scorso? Sono aumentate o diminuite?
• Il numero di utenti registrati sul nostro sito web è aumentato o diminuito in questo trimestre?
• Ho venduto più articoli oggi rispetto a una settimana fa?
• Le nostre vendite stanno aumentando o diminuendo rispetto all'anno scorso?

Poi vi mostrerò come utilizzare SQL per calcolare le variazioni percentuali. Nell'esempio seguente, troveremo la percentuale di variazione del numero totale di visite al sito web (oggi rispetto a ieri e oggi rispetto a una settimana fa). Una volta appreso il metodo di calcolo delle variazioni percentuali con questo esempio, è possibile applicarlo a qualsiasi altro caso aziendale. La struttura della query è la stessa; differiscono solo i nomi delle tabelle e delle colonne e forse un argomento della funzione LAG(), che spiegherò tra poco.

Uso della funzione LAG() per recuperare il valore di una riga precedente

Il primo passo per calcolare la percentuale di variazione è recuperare un valore da una riga precedente. Perché ne abbiamo bisogno? Perché la variazione percentuale è calcolata dalla formula:

(current_value - previous_value)/previous value * 100.

Questa formula significa che per calcolare l'aumento o la diminuzione del numero di visite, è necessario che entrambi i valori siano presentati nella stessa riga.

Quindi, il nostro primo compito nel calcolare questo schema è recuperare un valore da una riga precedente. Questo compito può essere svolto con l'aiuto della funzione della finestra LAG(). Essa consente di accedere a un valore in una determinata riga che precede la riga corrente. Di seguito è riportata un'istruzione SELECT che assegna il valore di daily_visits della riga precedente a una nuova colonna(previous_day_visits) nella riga corrente:

SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Questa è una tipica funzione finestra: all'interno della clausola OVER, si definisce la partizione e l'ordinamento desiderato. La funzione LAG() prende un argomento (il nome della colonna che contiene i valori desiderati) e assegna il valore della riga precedente a ogni riga:

LAG() può essere utilizzata anche per assegnare i valori di n righe indietro, non solo della riga precedente. Ad esempio, si supponga di voler calcolare la variazione percentuale per lo stesso giorno della scorsa settimana. In questo caso, è necessario assegnare a ogni riga un valore di sette giorni fa.

A tale scopo, si utilizza il parametro opzionale offset di LAG. Si veda la seguente query:

SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
FROM daily_visits;

Il valore di offset predefinito di LAG è 1 (la riga precedente), ma è possibile modificarlo con qualsiasi altro valore. Nel nostro esempio, abbiamo usato un valore di 7, che assegna a ogni riga il numero di visite di 7 giorni prima:

Aumento/diminuzione di 1 giorno del numero totale di visite

Ora possiamo facilmente calcolare un aumento/decremento di 1 giorno con questo codice SQL:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits) OVER(PARTITION BY website ORDER BY date) AS previous_day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_day_visits)/previous_day_visits *100),0) AS percent_change
FROM daily_visits_lag;

Questa istruzione sembra complicata, ma non lo è affatto. Ecco cosa succede:

• La SELECT che assegna a ogni riga un valore della riga precedente è scritta all'interno di un'istruzione WITH. Ciò significa che stiamo usando una Common Table Expression o CTE (cioè un set di risultati temporanei con nome). Questo risultato temporaneo viene poi utilizzato nelle successive SELECT.
• L'insieme di risultati temporanei è denominato daily_visits_lag. Contiene i valori necessari per il calcolo della variazione percentuale (cioè il numero di visite per la riga corrente e il numero di visite del giorno precedente).
• daily_visits_lag viene utilizzato nella query principale. Una colonna aggiuntiva, percent_change, viene calcolata in COALESCE() con la formula(visite-visite_giornaliere_precedenti)/visite_giornaliere_precedenti*100.
• Dopo l'esecuzione di questa istruzione, il motore SQL elimina il set di risultati temporaneo, che non può essere utilizzato ulteriormente nel codice. (Questo è il funzionamento di una CTE).

Non c'è abbastanza spazio per approfondire le CTE in questa sede, ma il nostro corso Recursive Queries è una buona risorsa per saperne di più sull'uso e sulla sintassi delle CTE.

Dopo l'esecuzione di questa query, vengono visualizzati i seguenti valori:

Incremento/decremento di 7 giorni del numero totale di visite

Ora che si sa come calcolare una variazione percentuale di 1 giorno, si può usare un'istruzione molto simile per calcolare una diminuzione/aumento di 7 giorni del numero totale di visite:

WITH daily_visits_lag AS (
 SELECT
  *,
  LAG(visits,7) OVER(PARTITION BY website ORDER BY date) AS previous_7day_visits
  FROM daily_visits
)
SELECT
    *,
   COALESCE(round((visits — previous_7day_visits)/previous_7day_visits *100),0) AS percent_change
FROM daily_visits_lag;

L'unica differenza è che abbiamo usato LAG() con un parametro di offset di 7: stiamo recuperando i valori delle visite di 7 giorni prima (una settimana fa) e calcolando la percentuale di variazione con quei valori (giorno attuale rispetto a una settimana prima). In altre parole, stiamo calcolando l'aumento o la diminuzione del numero di visite in 7 giorni:

Tenete presente che la query che abbiamo usato qui può essere utilizzata per altri esempi di business. Basta modificare i nomi delle tabelle e delle colonne; il resto può rimanere invariato.

Medie mobili semplici: 7 giorni

Un altro modello molto utilizzato nell'analisi delle serie temporali è la media mobile semplice (SMA). Una SMA è la media non ponderata dei valori delle n righe precedenti; viene calcolata per ogni valore di una determinata colonna.

Le SMA sono spesso utilizzate per determinare le tendenze nell'analisi dei prezzi delle azioni o delle criptovalute. Queste informazioni ci aiutano a capire il comportamento della nostra variabile: invece di un solo valore, otteniamo stime migliori utilizzando i valori medi di una misura specifica. In questo modo si attenuano le fluttuazioni per ottenere una visione d'insieme.

Nel nostro esempio di sito web, ogni giorno avrà due dati di interesse:

• Il numero di visite che si sono verificate in quel giorno specifico.
• Il numero medio di visite degli ultimi 7 giorni.

Ancora una volta, utilizzeremo una funzione SQL window per calcolare la nostra SMA. A differenza dell'esempio precedente, in cui abbiamo usato LAG() per recuperare i valori delle righe precedenti, qui useremo il parametro ROW/RANGE all'interno della clausola OVER:

SELECT
   *, 
   AVG(visits) OVER(PARTITION BY website ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as SMA7
FROM daily_visits ;

All'inizio dell'articolo abbiamo detto che ROW all'interno di una clausola OVER definisce una finestra all'interno di ogni partizione. Quando abbiamo calcolato il totale progressivo, abbiamo usato i valori predefiniti per i parametri row/range. Ciò significa che il punto di partenza di ogni finestra all'interno della partizione era la prima riga di quella partizione e il punto di arrivo era la riga corrente.

Ora, ogni finestra è definita come 7 righe (i 6 valori delle righe precedenti + il valore della riga corrente). Questo è stato fatto con le righe comprese tra le 6 precedenti e la riga corrente. In altre parole, il punto di partenza non è la prima riga della partizione. In questo caso, il valore medio(avg(visite)) verrà calcolato sulle ultime 7 righe, compresa quella corrente. Questa è la definizione di SMA.

Una volta eseguita questa istruzione, si otterrà un valore medio associato a ogni riga. Ciò consentirà di osservare le tendenze delle visite in modo più dettagliato. Ecco il risultato:

Le funzioni finestra di SQL sono una funzione davvero potente per l'analisi delle tendenze e la SMA è solo uno degli indicatori che si possono ottenere con le funzioni finestra. Se volete vedere altri esempi relativi all'analisi dei trend, provate il nostro corso Revenue Trend Analysis in SQL. In esso viene illustrato come utilizzare SQL per analizzare l'andamento di qualsiasi serie temporale.

Uso di RANK() per trovare il maggior numero di visite

L'ultimo modello di analisi è il ranking. Come si può intuire, ordina i risultati in base a una determinata variabile. Supponiamo di voler vedere quali date hanno avuto il maggior numero di visite per ciascuno dei nostri siti web. Per farlo, dobbiamo classificare le visite giornaliere di ciascun sito web separatamente. Per farlo, possiamo utilizzare la funzione RANK() della finestra:

SELECT *,
  RANK() OVER (PARTITION by website ORDER BY visits DESC) AS rank 
FROM daily_visits;

Ecco cosa succede in questo codice:

• La classifica è determinata dalla colonna visite. Questa è definita in ORDER BY, che si trova nella clausola OVER). La riga con il valore più alto di visite ottiene il rango più alto.
• Le righe (visite) sono raggruppate separatamente per ogni sito web, in modo da determinare il ranking per ogni sito web. Questo è definito in PARTITION BY nella clausola OVER.
• Le righe con criteri di classificazione uguali (cioè con lo stesso valore di visite ) ricevono la stessa posizione.

Una volta eseguita questa SELECT, il motore SQL restituisce un set di risultati con una colonna aggiuntiva denominata rank. Ora possiamo facilmente vedere quali giorni hanno avuto il maggior numero di visite. L'aspetto positivo è che la classifica è definita per ogni sito, quindi non stiamo confrontando i due siti tra loro.

La posizione più alta di sqldevelopers.com

La posizione più alta per sqlanalysts.com

Per saperne di più sull'analisi delle serie temporali con SQL

Ora potete utilizzare le funzioni della finestra SQL per effettuare alcune analisi di base delle serie temporali. Sapete che cosa sono le serie temporali e come potete usare le funzioni SQL window per ottenere alcune intuizioni molto interessanti. Avete anche imparato a conoscere le CTE.

Cosa fare dopo? Consiglio vivamente di adottare un approccio organizzato per imparare di più sull'analisi delle serie temporali in SQL. Il corso Window Functions e Revenue Trend Analysis in SQL che ho già citato sono ottimi. Ricordate che è importante mettere in pratica le vostre competenze, quindi assicuratevi di svolgere esercizi reali da risolvere!