Come calcolare la lunghezza di una serie con SQL

Come si calcola la lunghezza di una serie in SQL? La risposta è in due parole: funzioni finestra! Sì, per calcolare la lunghezza delle serie è necessaria una certa conoscenza delle funzioni finestra di SQL. Ma cos'è una serie temporale e perché si vuole sapere quanto è lunga?

Che cos'è una serie?

Sebbene vi siano alcune variazioni nella definizione di "serie temporale", si tratta fondamentalmente di una sequenza di dati elencati in ordine temporale.

In un database, questa sequenza è solitamente rappresentata da eventi separati da un tempo uguale. Ad esempio, gli accessi al sito web di un utente in giorni consecutivi sono considerati una serie. La tabella seguente mostra tali date di accesso:

Se volessi analizzare la durata di questa serie, conterei il numero di accessi consecutivi. La durata della prima serie è di tre giorni, poiché l'utente ha effettuato l'accesso il 2020-06-01, il 2020-06-02 e il 2020-06-03. La lunghezza della seconda serie è di sei giorni (l'utente si è collegato ogni giorno tra il 2020-06-06 e il 2020-06-11). Seguendo la stessa logica, le due serie successive sono lunghe rispettivamente quattro e due giorni.

Perché calcolare la lunghezza di una serie?

Le serie temporali sono molto utilizzate e ci sono molte situazioni in cui è necessario calcolare la lunghezza delle serie. Alcuni esempi di calcolo della lunghezza delle serie sono:

• Misurare una serie di accessi su Stack Overflow.
• Vedere la propria striscia di attività su Duolingo.
• Tracciare il numero di giorni di utilizzo di un'applicazione per il fitness.
• Analizzare una serie di vendite in un sito di e-commerce.
• Trovare l'aumento o la diminuzione consecutiva del valore di una valuta.

In pratica, ovunque si abbia una serie temporale, probabilmente sarà necessario calcolarne la lunghezza.

La domanda principale rimane: Come si calcola la lunghezza di una serie temporale e come si fa in SQL? Come mostra la tabella precedente, le serie temporali non sono un concetto difficile da comprendere. Nel momento in cui avete visto questo esempio, avete intuito come ottenere la lunghezza della serie.

Tuttavia, una cosa è trovare manualmente la lunghezza di una serie su diverse righe di dati. Ma non è possibile farlo su un database con centinaia, migliaia o milioni di righe. Per fortuna ci sono le funzioni SQL Window a salvare la situazione!

Se avete bisogno di rinfrescare la vostra conoscenza delle funzioni finestra, provate questo foglio informativo sulle funzioni finestra. Può essere particolarmente utile quando si arriva alla parte dei calcoli.

Se non sapete nemmeno cosa sono le funzioni finestra, vi consiglio di leggere Perché dovrei imparare SQL Window Functions prima di andare avanti. Il modo migliore per avere una conoscenza completa delle funzioni finestra è il corso LearnSQL.it Window Functions.

Come calcolare la lunghezza di una serie in SQL

Per questo esempio, immaginiamo che stiate imparando l'Alto Valyrian su Duolingo. La piattaforma Duolingo prevede una cosa chiamata striscia. Come spiega il sito, una striscia è "il numero di giorni consecutivi in cui si è completata una lezione". Una volta completata una lezione nell'app o nel sito web, la tua striscia aumenterà di un giorno. Riceverai la tua ricompensa giornaliera quando avrai raggiunto il tuo obiettivo di XP". (Il monitoraggio della propria striscia consente di ricevere incentivi specifici dalla piattaforma. Come fa Duolingo a sapere quanto è lunga la tua striscia?

La vostra striscia di apprendimento per luglio 2020 può essere presentata dalla tabella lesson_completed. Essa contiene le seguenti colonne:

• id: L'ID della lezione.
• date_completed: La data di completamento della lezione.

Ora scriviamo una query per trovare la tua striscia. Utilizzerò una Common Table Expression (CTE) per organizzare la query. Per coloro che non hanno familiarità, il nostro articolo sulle CTE è un buon punto di partenza. L'esecuzione di questa query calcolerà la lunghezza della serie:

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Vi spiego cosa ho fatto. La query può essere divisa in due parti:

• Creazione della CTE.
• Selezione dei dati dalla CTE.

Creazione della CTE

La parte della query che crea la CTE è riportata di seguito:

WITH groups AS (
	SELECT	RANK() OVER (ORDER BY date_completed) AS row_number,
			date_completed,
			DATEADD (day, -RANK() OVER (ORDER BY date_completed), 
date_completed) AS date_group
FROM lesson_completed)

La CTE è definita dalla clausola WITH. Ho deciso che il nome di questa CTE debba essere gruppi. Tutto ciò che è scritto tra le parentesi dopo la parola chiave AS è solo una normale istruzione SELECT.

Ora, che cosa fa questo? Innanzitutto, ho aggiunto il numero di righe alla tabella. Per farlo, ho utilizzato la funzione RANK(). Si tratta di una funzione a finestra, per questo è definita dalla clausola OVER(). Voglio che le righe siano aggiunte in sequenza in base alle date, quindi il risultato di questa funzione è ordinato dalla colonna date_completed.

Il CTE seleziona quindi la colonna date_completed. Ho utilizzato la funzione DATEADD (SQL Server) per dedurre il numero di riga da date_completed. In questa istruzione...

DATEADD (day, -RANK() OVER (ORDER BY date_completed), date_completed) AS date_group 

... Ho dovuto definire l'intervallo che verrà aggiunto (o detratto), ovvero il giorno. Quanti giorni voglio dedurre? I giorni che sono pari al numero di righe - ecco perché ho solo copiato la funzione della finestra RANK() che avevo già definito e ho aggiunto il segno negativo davanti ad essa. Da cosa voglio che venga dedotto? Da date_completed, naturalmente!

Questa parte della query darà il seguente risultato:

Perché ne ho bisogno? Mi aiuterà a calcolare la lunghezza della serie. Guardate la tabella. Avete notato che i giorni consecutivi appartengono allo stesso gruppo di date? Perché? Se le date sono consecutive e si sottrae il numero di riga, si otterrà sempre la stessa data. Guardate qui:

Deducete il numero di riga (1) dalla data (2020-07-01) e otterrete 2020-06-30. Questo è esattamente ciò che ho nella tabella.

E poi la riga successiva. Se si sottrae il numero di riga (2) dalla data (2020-07-02), il risultato è di nuovo 2020-06-30! Vediamo ora cosa succede quando le date non sono consecutive.

Per la riga 5, il risultato è ancora lo stesso, 2020-06-30. Va bene, ma che dire della riga successiva? Deduciamo la riga numero 6 dalla data (2020-07-08) e cosa otteniamo? 2020-07-02. Si tratta ora di un nuovo gruppo di date, il che non sorprende visto che 2020-07-08 non è consecutivo a 2020-07-05 in nessun calendario!

Un bel trucchetto, no? Per quanto riguarda le date nella colonna date_group, non è importante quali date si ottengono. Serviranno solo come valori, che verranno contati nella seconda parte della query. Contate quante volte si verifica ogni gruppo e avrete la lunghezza della serie!

Selezione dei dati dal CTE

La seconda parte della query seleziona i dati dalla CTE definita in precedenza:

SELECT	COUNT(*) AS days_streak,
		MIN (date_completed) AS min_date,
		MAX (date_completed) AS max_date
FROM groups
GROUP BY date_group;

Questa semplice istruzione SELECT conta il numero di righe e mostra i risultati nella nuova colonna days_streak. Quindi seleziona la data minima e massima nella colonna date_completed e i risultati vengono mostrati rispettivamente nelle colonne min_date e max_date. Tutti questi dati saranno selezionati dalla CTE denominata gruppi.

Infine, i dati devono essere raggruppati in base a date_group. Perché? Perché non mi serve il numero totale di righe, ma il numero di righe per ogni gruppo di date.

Ecco un modo per calcolare la lunghezza di una serie...

Questo piccolo esempio dovrebbe darvi un'idea di come calcolare la lunghezza di una serie con SQL. Tuttavia, non c'è un solo modo per farlo. E, come avete visto, non esiste una semplice funzione SQL che vi darà quello che volete. I dati e le esigenze di calcolo determineranno il codice SQL. Di solito è necessario un piccolo trucco; la vostra esperienza e conoscenza dell'SQL vi aiuterà a decidere quale!

Ciò che quasi certamente dovrete conoscere per calcolare la lunghezza della serie sono le funzioni SQL a finestra. Se non siete ancora convinti di dover imparare le funzioni finestra, leggete questa intervista con la creatrice del corso, che vi spiega tutti i vantaggi delle funzioni finestra.

Questo articolo vi ha mostrato solo un assaggio di cosa sono le serie temporali. Se siete interessati a imparare ad analizzare questo specifico tipo di dati, LearnSQL offre una guida molto dettagliata.

Se questo articolo vi ha aiutato a risolvere alcuni problemi relativi alle serie temporali, fatemelo sapere nella sezione commenti.