Funzioni SQL Window vs. Funzioni SQL Aggregate: Somiglianze e differenze

Se non si ha familiarità con le funzioni finestra di SQL, ci si può chiedere in cosa differiscano dalle funzioni aggregate. Quando si dovrebbero usare le funzioni finestra? In questo articolo esamineremo le funzioni finestra e le funzioni aggregate, esamineremo le loro somiglianze e differenze e vedremo quale scegliere a seconda delle esigenze.

Dopo aver affrontato l'SQL di base, è probabile che vogliate dedicarvi ad alcune delle sue funzioni più avanzate. È un'ottima cosa: queste funzioni facilitano la stesura dei rapporti e l'analisi.

Ben presto, però, ci si imbatterà in due misteriosi gruppi di funzioni: le funzioni finestra e le funzioni aggregate. Che cosa fanno? In che cosa si differenziano?

Lo scoprirete tra poco.

Cosa sono le funzioni aggregate di SQL?

Lefunzioni aggregate operano su un insieme di valori per restituire un unico valore scalare. Queste sono le funzioni aggregate SQL:

• AVG() restituisce la media dei valori specificati.
• SUM() calcola la somma di tutti i valori dell'insieme.
• MAX() e MIN() restituiscono rispettivamente il valore massimo e minimo.
• COUNT() restituisce il numero totale di valori dell'insieme.

Utilizzando la clausola GROUP BY, è possibile calcolare un valore aggregato per più gruppi in un'unica query.

Per esempio, supponiamo di avere i dati delle transazioni di due città, San Francisco e New York:

È possibile utilizzare le funzioni aggregate di SQL per calcolare l'importo medio delle transazioni giornaliere per ciascuna città. È necessario raggruppare i dati per data e città:

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city
FROM transactions
GROUP BY date, city;

Ecco il risultato di questa query:

Utilizzando le funzioni aggregate AVG() e GROUP BY, otteniamo risultati raggruppati per data e città. Abbiamo avuto due transazioni a New York il 2 novembre e due transazioni a San Francisco il 3 novembre, ma il set di risultati non include queste singole transazioni; le funzioni aggregate comprimono le singole righe e presentano il valore aggregato (in questo caso, la media) per tutte le righe del gruppo.

Cosa sono le funzioni SQL Window Functions?

In SQL, le funzioni finestra operano su un insieme di righe chiamato finestra. Esse restituiscono un singolo valore per ogni riga della query sottostante.

Il riquadro della finestra (o semplicemente finestra) viene definito utilizzando la clausola OVER(). Questa clausola consente anche di definire una finestra basata su una colonna specifica (simile a GROUP BY).

Per calcolare i valori restituiti, le funzioni finestra possono usare funzioni aggregate, ma le useranno con la clausola OVER().

Torniamo ai dati di San Francisco e New York. Ecco di nuovo la tabella:

Vogliamo aggiungere a questa tabella un'altra colonna con il valore medio giornaliero delle transazioni per ogni città. La seguente query SQL utilizza una funzione finestra per ottenere il risultato desiderato:

SELECT id, date, city, amount,
       AVG(amount) OVER (PARTITION BY date, city) AS  avg_daily_transaction_amount_for_city
FROM transactions
ORDER BY id;

Ecco il risultato:

Si noti che le righe non sono collassate; abbiamo ancora una riga per ogni transazione. Tutte le medie calcolate sono presentate nella colonna avg_daily_transaction_amount_for_city.

Per saperne di più sulle funzioni delle finestre, consultare questa guida approfondita. Essa fornisce diversi esempi, tra cui applicazioni semplici e più avanzate. Inoltre, il team di LearnSQL.it ha preparato un'ottima scheda informativa su SQL Window Functions . Stampatelo e appendetelo sulla scrivania, soprattutto se siete alle prime armi con le funzioni finestra.

Somiglianze e differenze tra le funzioni finestra e le funzioni aggregate

Ora che abbiamo visto entrambi i tipi di funzioni, possiamo riassumere le somiglianze e le differenze tra loro.

Sia le funzioni finestra che le funzioni aggregate

• Operano su un insieme di valori (righe).
• Possono calcolare importi aggregati (ad esempio AVG(), SUM(), MAX(), MIN(), o COUNT()) sull'insieme.
• Può raggruppare o suddividere i dati su una o più colonne.

Lefunzioni di aggregazione con GROUP BY differiscono dalle funzioni a finestra in quanto:

• Utilizzano GROUP BY() per definire un insieme di righe da aggregare.
• Raggruppano le righe in base ai valori delle colonne.
• Collassano le righe in base ai gruppi definiti.

Lefunzioni finestra differiscono dalle funzioni di aggregazione utilizzate con GROUP BY in quanto:

• Utilizzano OVER() invece di GROUP BY() per definire un insieme di righe.
• Possono utilizzare molte funzioni diverse dagli aggregati (ad esempio RANK(), LAG(), o LEAD()).
• Raggruppa le righe in base al rango, al percentile, ecc. della riga e al valore della colonna.
• Non collassa le righe.
• Può utilizzare una cornice a finestra scorrevole (che dipende dalla riga corrente).

Dimostriamo quest'ultima differenza con un altro esempio. In questo esercizio vogliamo calcolare la media delle vendite dei giorni precedenti e di quelli attuali per ogni data (cioè una media mobile di 2 giorni).

Suggerisco di iniziare con un' espressione di tabella comune (CTE) per definire la tabella daily_sales tabella, dove abbiamo il totale delle vendite per ogni giorno. Quindi, utilizziamo una funzione finestra con una finestra scorrevole per calcolare la media delle vendite totali per il giorno corrente e quello precedente. La query è la seguente:

WITH daily_sales AS (
    SELECT date, SUM(amount) AS sales_per_day
    FROM transactions
    GROUP BY date)
SELECT date, 
   AVG(sales_per_day) OVER (ORDER BY date ROWS 1 PRECEDING) 
AS avg_2days_sales
FROM daily_sales
ORDER BY date; 

Ecco l'insieme dei risultati:

Nella prima riga, la tabella mostra il totale delle vendite del 1° novembre perché non esiste una riga precedente per questa data. Poi, nella seconda riga, abbiamo la media delle vendite per l'1 e il 2 novembre; nella terza riga, la tabella include la media delle vendite per il 2 e il 3 novembre, e così via.

Le funzioni finestra sono ideali per calcolare le medie mobili, cosa che non è possibile fare utilizzando solo le funzioni aggregate e GROUP BY().

Esercitiamoci su Window Functions!

La tabella seguente riassume tutte le somiglianze e le differenze tra le funzioni aggregate di SQL e le funzioni finestra:

Anche se le funzioni finestra di SQL sono un argomento avanzato, è possibile esercitarsi da soli. Questa guida speciale per esercitarsi con le funzioni finestra di SQL fornisce alcuni suggerimenti utili per imparare la sintassi delle funzioni finestra e scrivere le query corrispondenti.

LearnSQL ha preparato un corso completo su Window Functions; in esso potrete esercitarvi a creare sofisticate cornici di finestre con 218 esercizi interattivi. Imparerete a sfruttare le funzioni finestra per calcolare totali e medie mobili, creare classifiche, trovare i migliori e i peggiori risultati e analizzare le tendenze nel tempo. Per saperne di più su questo corso, potete leggere la nostra intervista con Agnieszka Kozubek-Krycuń, Chief Content Officer di LearnSQL.it .

Grazie per aver letto e buon apprendimento!