Cos'è una media mobile e come calcolarla in SQL

Volete immergervi in profondità nei dati delle serie temporali ed esaminare le tendenze a lungo termine? Volete sapere cos'è una media mobile e come calcolarla in SQL? Allora questo articolo fa per voi. Vi spiegherò una potente funzione di SQL, le funzioni finestra, e vi mostrerò come calcolare le medie mobili utilizzando tali funzioni.

Il modo migliore per imparare a conoscere le funzioni SQL a finestra è il corso interattivo Window Functions di LearnSQL.it. Contiene oltre 200 esercizi pratici che vi aiuteranno a prendere confidenza con il linguaggio SQL. Questo corso è solo uno degli oltre 30 corsi interattivi di SQL a vari livelli di difficoltà che offriamo. Iscrivetevi subito gratuitamente!

Che cos'è una media mobile?

La media mobile è una tecnica di analisi delle serie temporali per determinare le tendenze dei dati. Talvolta chiamata media mobile, media mobile o media mobile, viene calcolata come media del valore corrente e di un numero specifico di valori immediatamente precedenti per ogni punto nel tempo. L'idea principale è quella di esaminare il comportamento di queste medie nel tempo, invece di esaminare il comportamento dei punti di dati originali o grezzi.

Lavorando con le medie mobili si ottiene una migliore rappresentazione delle serie temporali, poiché le tendenze a lungo termine sono molto più facili da vedere con le medie mobili che con i punti di dati grezzi. Poiché le medie mobili sono spesso utilizzate nell'analisi finanziaria, utilizzerò i dati relativi ai prezzi giornalieri delle azioni di una particolare società come esempio per spiegare cosa sono.

Di seguito è riportata la tabella denominata stock_price che utilizzeremo in questo articolo:

In questa tabella abbiamo due colonne (date e price). Si tratta di una serie di dati temporali, poiché ogni prezzo delle azioni è associato a un punto specifico nel tempo; cioè, il prezzo di ogni giorno è memorizzato in questa tabella.

Il grafico a linee di questi prezzi nel tempo ha l'aspetto seguente:

Dalla tabella e dal grafico possiamo dedurre alcune cose prima ancora di fare qualsiasi calcolo:

• I prezzi nella nostra tabella stock_price si riferiscono al periodo compreso tra il 7 gennaio 2020 e il 30 giugno 2020.
• I prezzi variano approssimativamente da 1.000 a 1.400 (vedi l'asse delle ordinate).
• I prezzi sono soggetti a fluttuazioni; ci sono alcuni picchi a febbraio, diversi picchi a giugno, ecc.

Calcoliamo una media mobile per la colonna price e generiamo un grafico a linee delle medie per vedere cosa succede. Per questo esempio, lavoreremo con una media mobile a tre giorni. Per farlo, calcoliamo la media dei prezzi delle azioni di tre giorni consecutivi - il giorno in questione e i due giorni precedenti - e ripetiamo la stessa operazione per ogni giorno del set di dati. Si tratta di una media mobile a tre giorni, perché la media è calcolata su un periodo di tre giorni.

Ecco come viene calcolata la media mobile a tre giorni per il 9 gennaio 2020:

Per il 9 gennaio 2020, la media mobile a tre giorni è calcolata come media dei prezzi di quel giorno (1.300) e dei due giorni precedenti: 8 gennaio (1.300) e 7 gennaio (1.320). Quindi, la media mobile per il 9 gennaio 2020 è la media di questi tre valori, ovvero 1.306,66 come mostrato nell'immagine sopra.

La media mobile viene calcolata allo stesso modo per ciascuna delle date rimanenti, sommando i tre prezzi delle azioni della data in questione e dei due giorni precedenti e dividendo poi il totale per 3. Per il 30 giugno, la media mobile a tre giorni è 1.070, la media dei prezzi delle date 30 giugno (1.067), 29 giugno (1.067) e 28 giugno (1.076).

Se tracciamo i punti dei dati originali e la media mobile su un grafico a linee, otteniamo quanto segue:

La linea rossa rappresenta la media mobile, mentre la linea blu rappresenta i punti dati originali. Si può notare che la linea rossa è più morbida e non presenta i picchi visti nella linea blu. Questo smussamento è l'obiettivo principale della tecnica della media mobile, che serve a rimuovere il rumore dai dati. Con meno rumore, le tendenze reali delle serie temporali sono più facili da vedere.

In questo esempio abbiamo calcolato la media mobile a tre giorni. Tuttavia, possiamo calcolarla su qualsiasi periodo desiderato, come la media mobile a sette giorni, la media mobile a dieci giorni e così via.

Perché e dove si usano le medie mobili

Le medie mobili sono ampiamente utilizzate nel trading finanziario e tecnico, come nell'analisi dei prezzi delle azioni, per esaminare le tendenze a breve e a lungo termine. Se il prezzo delle azioni si mantiene al di sopra della media mobile, si ha una tendenza al rialzo; se si mantiene al di sotto, i trader affermano che siamo in una tendenza al ribasso. Segnali come le tendenze al rialzo e al ribasso informano i trader quando decidono di acquistare o vendere azioni.

Detto questo, l'analisi dei prezzi delle azioni non è l'unico caso di utilizzo della media mobile. Altre applicazioni commerciali sono:

• Analisi delle vendite: Le medie mobili attenuano le fluttuazioni e i picchi delle vendite settimanali o giornaliere.
• Analisi dei casi confermati di COVID-19: Le medie mobili aiutano a mostrare come il numero di casi confermati cambia nel tempo.
• Analisi del traffico web: Le medie mobili ci aiutano a vedere le tendenze a lungo termine del numero di visite e di pagine viste.
• Finanza personale: Le medie mobili ci aiutano a vedere l'andamento delle nostre spese (ad esempio, osservando le medie a dieci giorni degli ultimi due anni).

Medie mobili in SQL

Ora che sappiamo cos'è una media mobile, vediamo come calcolarla. In SQL è facile grazie alle funzioni finestra, una speciale funzione di SQL che consente di effettuare aggregazioni tra le righe.

Sebbene siano simili a GROUP BY, le funzioni finestra mantengono tutte le righe quando visualizzano il risultato; non vi è alcun collasso delle righe. Con le funzioni finestra, invece, si definisce un riquadro o una "finestra" di righe di una determinata dimensione intorno alla riga corrente e si esegue un calcolo su tale finestra. Quindi, per ogni riga di una tabella viene eseguita un'aggregazione; ogni riga ha la sua finestra su cui viene eseguito un calcolo.

Di seguito viene illustrato come viene calcolata in SQL la colonna moving_average del nostro esempio:

select *,
  avg(Price) OVER(ORDER BY Date 
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) 
     as moving_average 
from stock_price;

Per spiegare il codice in dettaglio:

• Utilizziamo una funzione finestra, indicata con la clausola OVER. Come spiegato in precedenza, le righe non vengono compresse e ogni riga ha una propria finestra su cui viene eseguito il calcolo.
• Nel nostro esempio la dimensione della finestra è di tre. Per ogni riga data, prendiamo la riga stessa e le due righe precedenti e calcoliamo il prezzo medio di queste tre righe. Ciò è indicato dalla parola chiave ROW nell'istruzione: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Questa affermazione dice che, per ogni riga della tabella, viene calcolato qualcosa come aggregazione della riga corrente e delle due righe precedenti. Ciò significa che la media mobile di ogni riga è calcolata come media dei prezzi del giorno in questione e dei due giorni precedenti.
• Abbiamo una finestra diversa per ogni giorno. Di seguito è riportata un'illustrazione della finestra utilizzata per la riga corrispondente al 9 gennaio (in verde) e della finestra utilizzata per la riga corrispondente al 27 giugno (in blu):
• È importante che i dati non presentino lacune nelle date. Per ogni giorno, dobbiamo calcolare la media dei prezzi di quel giorno e dei due giorni precedenti. Se ci sono date mancanti nei dati, l'analisi non avrà senso.
• La parola chiave ORDER BY all'interno della clausola OVER definisce l'ordine delle righe su cui calcolare la media mobile. Nel nostro esempio, le righe vengono prima ordinate in base alla colonna della data, quindi viene definita la finestra e viene eseguito il calcolo.
• Per questo esempio, non si utilizza la parola chiave PARTITION BY nella clausola OVER. PARTITION BY raggruppa le righe in gruppi logici in base a qualche categoria, ma in questo caso non stiamo raggruppando le righe in questo modo. In effetti, l'intero insieme di dati è solo una grande partizione. Più avanti in questo articolo, vedremo un esempio con PARTITION BY.

Ora sapete come calcolare le medie mobili in SQL! Il codice SQL sopra riportato può essere utilizzato in molti altri scenari aziendali; è sufficiente sostituire la tabella e i nomi delle colonne e regolare il numero di righe per le quali si desidera calcolare le medie. Tutto il resto può rimanere invariato.

Le funzioni della finestra hanno una sintassi specifica e occorre un po' di tempo e di pratica per acquisire familiarità con il loro utilizzo. Per imparare ed esercitarsi ulteriormente, consiglio il corso sulle funzioni di finestra su LearnSQL.it. Si tratta di un corso interattivo con molti esercizi, che vi darà l'opportunità di esercitarvi e di imparare rapidamente il nuovo materiale, perché si impara facendo! Per maggiori informazioni sul contenuto del corso e sulle funzioni finestra stesse, potete leggere l'articolo Corso SQL del mese: Window Functions. Sulla stessa piattaforma si possono trovare anche ottimi articoli su quando utilizzare le funzioni finestra ed esplorare alcuni esempi.

Media mobile su un numero specifico di giorni

Nell'esempio precedente abbiamo calcolato una media mobile a tre giorni. È possibile calcolare anche altre medie, prendendo un numero qualsiasi di valori precedenti. Più alto è il numero di valori precedenti, più morbida sarà la curva. Più piccolo è il numero di righe utilizzate per calcolare le medie, più il grafico della media mobile sarà simile a quello dei valori originali. Esiste un'enorme differenza tra, ad esempio, le medie mobili a due e a 30 giorni.

Prima di creare un grafico che lo dimostri, calcoliamo entrambe le medie mobili:

select *,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) 
     as 2day_moving_average,
  avg(Price) OVER(ORDER BY Date 
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ) 
      as 30day_moving_average
from stock_price;

Ora possiamo visualizzare la differenza tra le due linee:

La linea verde rappresenta la media mobile a 30 giorni (per il calcolo di ciascuna media vengono utilizzati 30 prezzi azionari), mentre la linea rossa rappresenta la media a due giorni. La linea rossa assomiglia quasi alla linea blu dei punti dati originali. La linea verde è più morbida e permette di vedere meglio le tendenze a lungo termine.

Ora vi chiederete: come si fa a sapere il numero giusto di righe da prendere? Non posso aiutarvi: dipende dalle esigenze e dalla situazione dell'azienda e dal motivo per cui si sta analizzando la metrica.

Esempio: Media mobile a sette giorni dei casi COVID

Ora facciamo un po' di pratica per ricapitolare ciò che abbiamo imparato finora. La COVID-19 è ancora molto presente nelle nostre vite, quindi calcoleremo la media mobile a sette giorni del numero totale di casi confermati per paese. Il numero di casi confermati per ogni giorno e per ogni Paese è memorizzato in una tabella denominata confirmed_covidnella colonna confirmed_day:

Per calcolare la media mobile a sette giorni del numero di casi confermati, possiamo procedere come segue:

SELECT *,
      avg(confirmed_day) OVER(
          PARTITION BY country 
          ORDER BY date 
          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
          AS 7day_moving_average
FROM confirmed_covid;

Avrete notato che questo codice assomiglia a quello scritto per la media mobile del prezzo delle azioni. L'unica differenza fondamentale è che qui abbiamo delle partizioni: ogni paese è una partizione, perché stiamo calcolando le medie a sette giorni separatamente per ogni paese. Per il resto, solo i nomi delle tabelle e delle colonne sono diversi. È possibile adattare facilmente questo codice a qualsiasi altro scenario aziendale.

Una volta eseguito il codice e calcolata la media mobile, possiamo generare il grafico con la linea che mostra la tendenza per il paese specifico, dall'inizio della pandemia fino al mese di luglio. Di seguito è riportato il grafico per il Paese della Croazia:

Anche in questo caso, si nota come la media mobile appiana e riduce i picchi e le fluttuazioni rispetto ai punti dati originali.

È possibile utilizzare altre funzioni di finestra per ottenere potenti approfondimenti dai dati di COVID-19. Se siete interessati a saperne di più, consultate il nostro recente articolo su come analizzare i dati COVID-19 utilizzando le funzioni finestra.

Medie mobili e Window Functions in SQL

In questo articolo abbiamo visto come calcolare le medie mobili in SQL utilizzando le funzioni finestra. Queste medie ci aiutano a vedere meglio le tendenze reali riducendo la quantità di rumore.

Se volete esercitarvi e imparare di più sulle funzioni finestra, vi consiglio il corso interattivo online sulle funzioni finestra pubblicato da LearnSQL.it. Il corso contiene oltre 200 esercizi, che vi permetteranno di mettere in pratica le competenze appena apprese. E credetemi, quando si tratta di SQL e di competenze di codifica, è importante fare pratica: si impara facendo!