Come calcolare le differenze anno per anno in SQL

Come è andata la vostra azienda quest'anno rispetto all'anno scorso? L'anno precedente? Scoprite come utilizzare SQL per calcolare le differenze tra un anno e l'altro e tra un mese e l'altro.

I confronti anno su anno (YOY) sono un modo popolare ed efficace per valutare le prestazioni di diversi tipi di organizzazioni. Le differenze anno su anno sono facili da capire: ad esempio, è facile capire che la crescita dei ricavi è del 35% su base annua. Tuttavia, calcolare questa metrica in SQL non è così semplice!

In questo articolo scopriremo come calcolare le differenze anno su anno e mese su mese utilizzando SQL.

Siete pronti a investire nelle vostre competenze in SQL? Andiamo!

Cosa sono le metriche anno su anno dal punto di vista aziendale?

Per sapere se un'organizzazione aziendale sta ottenendo buoni risultati, di solito dobbiamo fare un confronto basato su metriche aziendali. A volte possiamo fare un confronto con aziende simili o con la media di diverse aziende dello stesso mercato. Tuttavia, un indicatore di performance fondamentale è la crescita periodica: il confronto tra i risultati attuali dell'azienda e la stessa metrica di un periodo precedente. Questo è il motivo dei confronti tra anni: Si può facilmente vedere se la propria organizzazione sta facendo meglio (o peggio) dell'anno precedente. Inoltre, i confronti anno per anno possono essere applicati a diverse metriche (ad esempio, vendite, profitti, numero di clienti) per comprendere meglio l'evoluzione dei diversi indicatori aziendali.

Nella tabella seguente, possiamo vedere i risultati per il 2019 e il 2020:

Se aggiungiamo una terza colonna chiamata YOY, possiamo facilmente vedere la variazione da un anno all'altro. Questo ci permette di capire meglio come la nostra organizzazione si è comportata nell'ultimo anno e quali aree possono essere migliorate:

In questo articolo spiegheremo come calcolare i valori della colonna YOY. Iniziamo mostrando la tabella di base, dove abbiamo le metriche per il 2019 e il 2020. Nell'immagine successiva, possiamo vedere questi valori nella tabella yearly_metrics tabella.

Nella prossima sezione, spiegheremo come interrogare la tabella yearly_metrics tabella utilizzando SQL per ottenere i valori della colonna YOY. Prima di ciò, vorrei suggerire il corso di LearnSQL.it Window Functions dove è possibile apprendere le basi delle funzioni SQL a finestra. Inoltre, l'articolo Quando usare SQL Window Functions fornisce molti esempi di query che possono essere utili.

Come calcolare le metriche YOY con SQL

Le funzioni finestra sono una funzione SQL molto potente. Esse restituiscono il risultato dell'applicazione di una funzione (come MAX(), AVG() o COUNT()) a un insieme di record (chiamato "finestra") in una tabella. L'insieme di record è definito dalla clausola OVER(); questa clausola è obbligatoria per le funzioni finestra. È anche possibile ordinare i record della finestra in base a diversi criteri e quindi utilizzare funzioni come FIRST_VALUE(), LAST_VALUE(), LEAD(), o LAG() per restituire il valore di record specifici in relazione al record corrente.

Se volete ripassare le basi delle funzioni finestra, vi suggerisco l'articolo Cos'è la clausola over? La comprensione delle funzioni SQL window e della clausola OVER vi aiuterà a capire i concetti che discuteremo in questo articolo.

Vediamo ora un esempio di query SQL che restituisce i ricavi di ogni anno e dell'anno precedente:

SELECT year, 
       revenue, 
       LAG(revenue) OVER ( ORDER BY year ) AS Revenue_Previous_Year
FROM   yearly_metrics

In questo esempio, si utilizza la funzione LAG() per ottenere il valore della colonna revenue per il record precedente. In altre parole, se il record corrente è del 2020, LAG(revenue) restituirà il valore della colonna ricavi per l'anno 2019. I risultati di questa query sono:

Il passo successivo per ottenere il valore YOY delle entrate è semplice: basta calcolare la differenza tra le entrate del 2020 e quelle del 2019. Ecco come fare:

SELECT year, 
       revenue, 
       LAG(revenue) OVER ( ORDER BY year ) AS Revenue_Previous_Year
       revenue - LAG(revenue) OVER ( ORDER BY year ) AS YOY_Difference
FROM   yearly_metrics

Abbiamo calcolato YOY Difference facendo la differenza aritmetica tra le entrate del 2020 e quelle del 2019. Nel risultato seguente, possiamo vedere che il record del 2019 non ha un valore YOY Difference perché non abbiamo dati per il 2018. Inoltre, il record del 2021 non ha un valore coerente in YOY Difference perché i dati del 2021 non sono completi.

Prima di passare alla sezione successiva, vorrei suggerire l'articolo Come calcolare la differenza tra due righe in SQL, che approfondisce il calcolo delle differenze utilizzando LAG() e LEAD().

Calcolo delle differenze da mese a mese e da trimestre a trimestre

Nella sezione precedente non è stato possibile calcolare un valore coerente per YOY Difference per il 2021 perché non si dispone di risultati completi per il 2021. Tuttavia, per vedere come si sta comportando un'azienda nell'anno in corso, dovremmo disporre di alcune metriche che ne descrivano le prestazioni. Buoni indicatori di performance possono essere i confronti mese per mese o trimestre per trimestre (ad esempio, confrontando il primo trimestre del 2021 con il primo trimestre del 2020).

Prima di calcolare le differenze mese su mese o trimestre su trimestre, esaminiamo la tabella daily_metrics tabella. Per ogni giorno c'è un record che descrive i ricavi, i costi e il numero di nuovi clienti di quel giorno.

Successivamente, calcoleremo la CTE monthly_metrics (una CTE è simile a una vista, ma viene creata durante l'esecuzione della query). Questa ha uno schema simile a quello della tabella yearly_metrics utilizzata in precedenza. La SELECT di creazione di monthly_metrics è mostrata in rosso; in blu, possiamo vedere la query SQL che utilizza questa CTE come una normale tabella.

WITH monthly_metrics AS (
 SELECT 
   extract(year from day) as year,
   extract(month from day) as month,
   SUM(revenue) as revenue
 FROM daily_metrics 
 GROUP BY year, month 
)
SELECT 
  year, month, revenue,
  LAG(revenue) OVER (ORDER BY year, month) as Revenue_previous_month,
  revenue - LAG(revenue) OVER (ORDER BY year, month) as Month_to_month_difference
FROM monthly_metrics
ORDER BY 1,2;

In the above query, we first generate the monthly_metrics CTE using a SELECT that extracts the year and month from the day column. Then grouping by year and month, we calculate the SUM of revenue for each month. The CTE monthly_metrics works like a regular table with the columns year, month, and revenue; when the query ends, the CTE is destroyed. You can compute the quarter-to-quarter difference in a similar way.

After that (in blue), we obtain the revenue for the previous month using the LAG() window function. Then we calculate the difference between each month and the previous month. Here’s a partial view of the result:

There is another way to calculate a month-over-month difference. Instead of comparing against the previous month, we can compare against the same month in the previous year. To make this comparison, we need to use the LAG() function’s optional offset parameter, as we can see in the following query:

WITH monthly_metrics AS (
SELECT EXTRACT(year from day) as year,
	 EXTRACT(month from day) as month,
       SUM(revenue) as revenue
  FROM daily_metrics 
  GROUP BY 1,2
)
SELECT year AS current_year, 
       month AS current_month, 
       revenue AS revenue_current_month, 
       LAG(year,12) OVER ( ORDER BY year, month) AS previous_year, 
       LAG(month,12) OVER ( ORDER BY year, month) AS month_comparing_with,
       LAG(revenue,12) OVER ( ORDER BY year, month) AS revenue_12_months_ago,
       revenue - LAG(revenue,12) OVER (ORDER BY year, month) AS month_to_month_difference
FROM monthly_metrics
ORDER BY 1,2;

Sopra abbiamo usato la funzione LAG() con il parametro opzionale offset, che ci permette di ottenere il valore di una colonna da un record N posizioni prima del record corrente. Se utilizziamo un offset di 12, otterremo il record dello stesso mese ma dell'anno precedente. Di seguito, possiamo vedere il risultato:

Ed ecco fatto! Ora sapete come trovare le differenze mese su mese, trimestre su trimestre e anno su anno con le funzioni SQL window. Suggerisco il corso Window Functions come buona introduzione al lavoro con queste funzioni. Se volete saperne di più, consultate questo articolo che descrive il corso Window Functions .

Cosa c'è dopo?

Le funzioni della finestra sono una risorsa chiave di SQL. In questo articolo abbiamo utilizzato la funzione finestra LAG() per calcolare le differenze tra anno e mese. In realtà, è possibile utilizzarla per calcolare la differenza tra qualsiasi periodo di tempo: trimestri, semestri, mesi o settimane.

In questo articolo ci siamo concentrati sulle differenze tra periodi di tempo, ma le funzioni finestra possono essere applicate per risolvere molti tipi diversi di problemi di dati. Infine, ma non per questo meno importante, voglio suggerirvi il nostro cheat sheet Window Functions , che ho attaccato su una lavagna di sughero davanti alla mia scrivania. Investite su di voi e fate crescere le vostre competenze in SQL!