Le funzioni a finestra di BigQuery spiegate

Tenersi al passo con le tendenze dell'analisi dei dati dà alla vostra organizzazione - e al vostro CV - un vantaggio. In questo articolo analizzeremo le funzioni finestra di BigQuery e come utilizzarle per ottenere informazioni più approfondite sui dati.

BigQuery di Google, lanciato nel 2010, si sta affermando come una scelta popolare tra le organizzazioni che hanno bisogno di analizzare rapidamente grandi quantità di informazioni e di confrontare i propri dati con quelli statistici di pubblico dominio.

Da quando Google ha allineato il linguaggio di recupero dei dati di BigQuery allo standard SQL e ha incluso caratteristiche avanzate come le funzioni SQL window, la sua popolarità è aumentata. Molte organizzazioni ora includono le competenze in BigQuery come un must, e questo significa che le competenze in SQL sono più richieste che mai. Questo articolo spiega perché la conoscenza di SQL è essenziale per lavorare con BigQuery.

L'SQL continua a essere la competenza principale per chiunque abbia bisogno di lavorare con i dati. Se non siete ancora dei guru dell'SQL, potreste essere interessati al nostro percorso di apprendimentoSQL from A to Z . Si tratta di 7 corsi che vi porteranno dal principiante assoluto all'esperto di SQL. Il corso contiene centinaia di sfide di codifica reali e richiede circa 84 ore per essere completato. Poiché è possibile accedere ai database di esempio attraverso il browser, non è necessario installare alcun software per iniziare.

Cos'è BigQuery?

Google BigQuery è un data warehouse ad alta velocità situato nel cloud. Progettato appositamente per contenere i dati utilizzati per l'analisi, può elaborare petabyte di dati in pochi minuti. Se non vi siete ancora abituati ai petabyte, un petabyte è un quadrilione di byte (o un milione di gigabyte).

In BigQuery si paga per quello che si usa, quindi il costo dell'archiviazione e dell'analisi di enormi quantità di dati è di solito molto più basso rispetto all'investimento in molti dischi rigidi. Inoltre, poiché tutti i tipi di dati statistici utili provenienti da governi e organizzazioni mondiali sono archiviati pubblicamente in BigQuery, è possibile accedervi per capire come migliorare le prestazioni della propria organizzazione.

Cosa sono le funzioni SQL Window Functions?

Le funzioni a finestra sono note anche come funzioni analitiche o funzioni OVER. Sono state aggiunte allo standard SQL nel 2003 e la maggior parte dei principali fornitori di database ha iniziato a implementarle a partire dal 2010. Si tratta quindi di un'aggiunta abbastanza recente a SQL.

Prima che le funzioni finestra fossero incluse in SQL, era possibile elencare singole righe o calcolare aggregati come totali e medie. Non era possibile fare entrambe le cose nella stessa query, a meno che non si scrivessero sottoquery complesse e probabilmente lente e inefficienti.

Ciò significa che si può avere un elenco come questo ...

Student ID	Subject	Score
1	Math	63
1	Science	50
2	Math	59

... o mostrare aggregati come questo:

Subject	Class Average
Math	52
Science	61
English	55
Overall Average	56

In questo esempio, se si volesse sapere come il punteggio di un singolo studente si confronta con la media della classe, bisognerebbe guardare i due report uno accanto all'altro. Sarebbe molto più bello se si potesse vedere la media della classe sulla stessa riga del punteggio di uno studente, come in questo caso:

Student ID	Subject	Score	Class Average
1	Math	63	62
1	Science	50	61
2	Math	59	52

Come ho già detto, si potrebbe ottenere questo risultato usando le subquery. Ma le subquery sono notoriamente lente da eseguire e possono rendere la query molto complicata.

Questo è il genere di cose che le funzioni finestra consentono di fare in modo semplice ed efficiente: includere gli aggregati insieme ai dettagli sulla stessa riga.

Perché si chiamano Window Functions?

Si chiamano funzioni finestra perché, mentre si osserva una singola riga, si può anche "guardare attraverso la finestra" ed estrarre informazioni dall'intero set di dati o dalle righe correlate alla riga corrente.

A titolo di esempio, consideriamo una tabella di voti degli studenti:

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Come nell'esempio precedente, vogliamo mostrare la media della classe accanto a ogni studente, in modo che una singola riga abbia questo aspetto:

Student ID	Subject	Grade	Class Average
4	Math	45	62

Per fare ciò, dobbiamo considerare la media di tutti gli altri studenti quando estraiamo questa riga e mostrare il risultato accanto agli altri dati.

In SQL, le funzioni finestra utilizzano una finestra scorrevole di righe per estrarre informazioni aggiuntive dall'intero set di dati o da un sottoinsieme relativo alla riga corrente.

Nella figura precedente, la riga corrente è un risultato matematico e la finestra include tutti i voti matematici.

Per estrarre questa riga ...

Student ID	Subject	Grade	Class Average
1	Science	70	62

... avremo bisogno che la finestra "scorra" in modo da visualizzare tutti i risultati di scienze per calcolare la media di scienze.

Le funzioni finestra hanno molte delle stesse capacità della clausola GROUP BY, ma la differenza è che consentono di visualizzare aggregati e dettagli fianco a fianco.

Sintassi di BigQuery Window Functions

La clausola OVER()

La clausola OVER() indica che si sta utilizzando una funzione finestra. Si scrive la query come di consueto e si includono gli aggregati desiderati accanto ai nomi delle altre colonne. Ogni aggregato è identificato con la clausola OVER(). Quando si usa questa clausola da sola, la "finestra" è l'intero set di dati. Le finestre scorrevoli saranno trattate più avanti nell'articolo.

Per esempio, se si volessero estrarre tutti i risultati di matematica e mostrare la media della classe e il voto più alto e più basso rispetto al voto di ogni studente, la query avrebbe questo aspetto:

SELECT 
  student_id,
  grade,
  AVG(grade) OVER() AS average,
  MIN(grade) OVER() AS lowest,
  MAX(grade) OVER() AS highest
FROM exam_results
WHERE subject = 'Math';

I risultati sarebbero quindi così:

student_id	grade	average	lowest	highest
1	63	62	45	80
2	80	62	45	80
3	60	62	45	80
4	45	62	45	80
5	52	62	45	80
6	70	62	45	80
7	65	62	45	80

La clausola PARTITION BY

Questa clausola utilizza una finestra scorrevole. Invece di una finestra contenente l'intero set di dati, include solo una partizione (o una parte) del set.

Nell'esempio precedente, ho incluso solo i risultati matematici escludendo tutti gli altri utilizzando la clausola WHERE. Se si desidera un report che mostri i risultati di tutti i soggetti, ma che calcoli la media utilizzando solo le righe in cui il soggetto corrisponde alla riga corrente, si deve utilizzare la clausola PARTITION BY:

SELECT 
  student_id,
  subject,
  grade,
  AVG(grade) OVER(PARTITION BY subject) AS average
FROM exam_results;

Guardate questa copia codificata a colori della tabella dei voti degli studenti per vedere come funzionano le partizioni:

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Quando si elabora ogni riga, le righe incluse nella finestra cambiano in base al valore della colonna subject. Ciò significa che la media viene calcolata solo per la partizione del set di dati in cui il soggetto corrisponde alla riga corrente. È possibile visualizzarlo in questo modo:

I risultati appaiono così:

Student ID	Subject	Grade	Class Average
1	Math	63	62
2	Math	80	62
3	Math	60	62
4	Math	45	62
5	Math	52	62
6	Math	70	62
7	Math	65	62
1	Science	70	61
2	Science	62	61
3	Science	90	61
4	Science	30	61
5	Science	53	61
1	English	59	59
3	English	70	59
5	English	45	59
6	English	62	59
1	History	55	60
3	History	67	60
4	History	58	60

La clausola ORDER BY

La clausola ORDER BY all'interno della funzione OVER() utilizza un tipo diverso di finestra scorrevole. Quando si utilizza OVER(ORDER BY column_name), la finestra include solo le righe in cui il valore della colonna specificata è inferiore o uguale al valore della colonna nella riga corrente.

La clausola ORDER BY è utile per calcolare i totali progressivi e le medie mobili. Come esempio, utilizzeremo una tabella chiamata monthly_transactions che contiene le transazioni dei conti bancari:

account_id	tran_date	transaction	value
1	2023-09-01	Opening Balance	500.00
1	2023-09-03	Deposit	137.45
1	2023-09-12	Withdrawal	-200.00
1	2023-09-18	Withdrawal	-250.00
2	2023-09-01	Opening Balance	1200.00
2	2023-09-14	Deposit	900.00
2	2023-09-20	Purchase	-318.90

La query seguente elenca le transazioni per l'ID conto 1, mostrando il saldo corrente.

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(ORDER BY tran_date) AS balance
FROM monthly_transactions
WHERE account_id = 1;

L'inclusione della clausola ORDER BY all'interno della clausola OVER controlla una finestra scorrevole.

Se si vuole, si può anche usare la clausola ORDER BY alla fine della query per controllare l'ordine finale delle righe nel report. Non è necessario che vengano mostrate nell'ordine originale.

Per impostazione predefinita, l'uso di ORDER BY all'interno della clausola OVER fa sì che la finestra scorra in modo da visualizzare solo le righe la cui data è inferiore o uguale a quella della riga corrente. Esistono altre parole chiave che possono modificare questa impostazione predefinita, ma sono un po' al di là dello scopo di questo articolo.

È possibile visualizzarlo in questo modo:

I risultati sarebbero i seguenti:

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Uso di ORDER BY con la clausola PARTITION BY

Se si desidera mostrare tutti i conti con i relativi saldi correnti, è possibile utilizzare insieme le clausole PARTITION BY e ORDER BY:

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(PARTITION BY account_id ORDER BY tran_date) AS balance
FROM monthly_transactions
ORDER BY account_id, tran_date

La clausola PARTITION BY farà sì che la finestra scorrevole includa solo le righe in cui account_id corrisponde alla riga corrente. La clausola ORDER BY fa sì che vengano incluse solo le righe all'interno della partizione in cui la data è inferiore o uguale alla data della riga corrente.

I risultati sarebbero:

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Ogni conto ha il proprio saldo corrente separato.

SQL Window Functions Disponibile in BigQuery

Abbiamo visto come le comuni funzioni aggregate SQL, come SUM(), AVG(), MIN() e MAX(), possano essere utilizzate insieme alla clausola OVER per estrarre aggregati da una finestra di dati.

Google BigQuery, come molti altri dialetti SQL, dispone di funzioni aggiuntive che possono fornire informazioni più approfondite sui dati. Ecco alcuni esempi.

RANK()

Questa funzione classifica il set di dati dal più alto al più basso su una colonna specificata. Può rispondere a domande come:

• Qual è il posto di ogni studente nella classe, in base ai risultati degli esami?
• Quali prodotti sono stati più redditizi?
• Quali clienti hanno speso di più?
• Quale magazzino ha ricevuto più reclami?

Utilizzando la tabella di esempio vista in precedenza, classifichiamo gli studenti in base ai risultati degli esami utilizzando questa query:

SELECT 
  student_id,
  subject,
  grade,
  RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS place
FROM exam_results
ORDER BY subject, grade DESC;

Il risultato sarebbe:

student_id	subject	grade	place
3	English	70	1
6	English	62	2
1	English	59	3
5	English	45	4
3	History	67	1
4	History	58	2
1	History	55	3
2	Math	80	1
6	Math	70	2
7	Math	65	3
1	Math	63	4
3	Math	60	5
5	Math	52	6
4	Math	45	7
3	Science	90	1
1	Science	70	2
2	Science	62	3
5	Science	53	4
4	Science	30	5

Nel calcolare questi risultati, la clausola PARTITION BY subject fa sì che SQL esamini solo i risultati relativi alla stessa materia della riga corrente. La clausola ORDER BY grade DESC li ordina in ordine decrescente di voto. La funzione RANK() classifica quindi gli studenti in quest'ordine.

Poiché lo studente 3 ha il voto più alto in Inglese, la sua posizione è 1; lo studente 6, il più alto, è al secondo posto in questa materia. Lo studente 2 ha il voto più alto in Matematica e viene classificato come 1.

DENSE_RANK()

DENSE_RANK() è utilizzato per lo stesso scopo di RANK. La differenza tra i due metodi può essere spiegata meglio osservando i risultati di queste query, che rappresentano i punteggi di una gara di tiro.

Utilizzando RANK, la query sarebbe:

SELECT 
  competitor_no,
  score,
  RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

I risultati sono:

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	4
2	76	5
6	76	5
7	72	7
3	70	8
8	68	9
1	62	10

I concorrenti 5 e 10 hanno pareggiato per il secondo posto e hanno ricevuto entrambi un punteggio di 2. Il concorrente 9 è il prossimo più alto e si classifica a 4. Il terzo posto è stato escluso.

Utilizzando DENSE_RANK, la query è:

SELECT 
  competitor_no,
  score,
  DENSE RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

I risultati sono:

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	3
2	76	4
6	76	4
7	72	5
3	70	6
8	68	7
1	62	8

I concorrenti 5 e 10 sono ancora entrambi classificati come 2, ma il terzo posto non viene escluso: Il concorrente 9 ha ora una posizione in classifica pari a 3.

Entrambe le funzioni hanno la stessa sintassi. Se volessimo ricodificare la query dei voti degli studenti precedenti usando la funzione DENSE_RANK(), il risultato sarebbe questo:

SELECT 
  student_id,
  subject,
  grade,
  DENSE_RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS class_place
FROM exam_results
ORDER BY subject, grade DESC

Ma poiché non ci sono valori di parità, il risultato sarebbe lo stesso.

NUMERO_RIGHE()

La funzione ROW_NUMBER() funziona in modo simile alle due funzioni precedenti, ma le righe sono semplicemente numerate in ordine. Se le righe hanno lo stesso valore, saranno numerate consecutivamente, a seconda di quale sia stata incontrata per prima. Ecco i risultati dell'interrogazione sulla gara di tiro utilizzando ROW_NUMBER() invece di RANK() o DENSE_RANK():

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	3
9	81	4
2	76	5
6	76	6
7	72	7
3	70	8
8	68	9
1	62	10

LAG()

Questa funzione consente di confrontare i dati della riga precedente del set di risultati con quelli della riga corrente. È ideale per i confronti anno per anno, per scoprire le tendenze e identificare i problemi di performance aziendale.

LAG() e la funzione correlata LEAD() può essere utilizzata solo in combinazione con la clausola OVER(ORDER BY).

A titolo di esempio, si consideri la tabella seguente, che contiene i dati di vendita di una piccola azienda:

year	sales_value	sales_quantity	profit
2019	54000	900	8000
2020	75000	1200	11000
2021	30000	450	1000
2022	60000	10000	7000

La query di esempio per confrontare i dati anno per anno è la seguente:

SELECT 
  year,
  sales_value,
  sales_quantity,
  profit,
  LAG(sales_value) OVER(ORDER BY year) as ly_value,
  LAG(sales_quantity) OVER(ORDER BY year) as ly_qty,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as inc_dec
FROM annual_sales
ORDER BY year;

I risultati di questa query sono:

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2019	54000	900	8000	NULL	NULL	NULL	NULL
2020	75000	1200	11000	54000	900	8000	3000
2021	30000	450	1000	75000	1200	11000	-10000
2022	60000	10000	7000	30000	450	1000	6000

Esaminiamo questa riga della query per vedere cosa ha effettivamente fatto:

LAG(sales_value) OVER(ORDER BY year) as ly_value

Su questa riga del risultato ...

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2020	75000	1200	11000	54000	900	8000	3000

... la nostra riga attuale si riferisce all'anno 2020. La funzione LAG() insieme a ORDER BY year fa sì che SQL esamini la riga dell'anno precedente (2019) e ne estragga il valore delle vendite sotto la voce ly_value.

Si noterà che nella prima riga le colonne calcolate dalla funzione LAG() contengono un valore nullo, poiché non esiste un record precedente.

LEAD()

La funzione LEAD() è l'inverso di LAG(): ottiene i dati dalla riga successiva a quella corrente, anziché da quella precedente.

Per confrontare i profitti tra l'anno in corso, l'anno precedente e l'anno successivo utilizzando la stessa tabella di esempio, la query sarebbe:

SELECT 
  year,
  profit,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as ly_inc_dec,
  LEAD(profit) OVER(ORDER BY year) as ny_profit,
  LEAD(profit) OVER(ORDER BY year) - profit as ny_inc_dec
FROM annual_sales
ORDER BY year;

I risultati di questa query sarebbero:

year	profit	ly_profit	ly_inc_dec	ny_profit	ny_inc_dec
2019	8000	NULL	NULL	11000	3000
2020	11000	8000	3000	1000	-10000
2021	1000	11000	-10000	7000	6000
2022	7000	1000	6000	NULL	NULL

Le colonne ny_profit e ny_inc_dec utilizzano la funzione LEAD(), che fa sì che SQL guardi alla riga successiva in sequenza per estrarre questi campi. Se la riga corrente è quella del 2019, questi due campi verranno estratti dalla riga del 2020.

Questo dovrebbe avervi dato un'idea di alcune delle utili funzioni SQL a finestra disponibili in BigQuery. L'elenco completo delle funzioni è disponibile nella documentazione di BigQuery SQL.

Per ulteriori esempi di funzioni finestra, consultare l'articolo Esempi di funzioni finestra SQL. È inoltre possibile trovare una guida rapida alla sintassi delle funzioni finestra SQL nel nostro Window Functions Cheat Sheet.

Usi pratici di BigQuery Window Functions

Nel mondo reale, le funzioni finestra di BigQuery possono fornire informazioni utili per migliorare le prestazioni dell'organizzazione. Sono molto potenti e consentono di produrre rapidamente report complessi.

Ecco alcune idee su come possono essere utilizzate le funzioni finestra di BigQuery:

• Classificare i dipendenti in base alle prestazioni per dare premi motivazionali. Per ulteriori informazioni sulla classificazione delle righe, consultate questo articolo.
• Trovare l'andamento di ogni linea di prodotti rispetto ad altri articoli simili.
• Scoprire come i singoli prodotti influenzano la media mobile dei profitti. Per saperne di più sul calcolo delle medie mobili in SQL, leggete qui.
• Confrontare i dati anno per anno per scoprire le tendenze. Per saperne di più sulla preparazione di confronti anno per anno in SQL, leggete questo articolo.
• Usare i totali progressivi per vedere esattamente quante vendite sono state effettuate in un determinato momento. Per saperne di più sul calcolo dei totali progressivi in SQL, consultate questo articolo.

I prossimi passi con BigQuery Window Functions

Ora che avete visto cosa possono fare le funzioni della finestra SQL in BigQuery e in altri sistemi di gestione di database, è il momento di pensare a migliorare le vostre competenze in questo importante settore.

Un buon punto di partenza è il corso SQL Window Functions di LearnSQL.it. Imparerete passo dopo passo come utilizzare le tecniche che avete visto in questo articolo, con una spiegazione completa di ogni argomento. Potrete anche fare molta pratica, con oltre 200 esercizi interattivi per assicurarvi di sapere come estrarre informazioni complesse in situazioni reali. L'aiuto è disponibile quando ci si blocca e si potrà accedere a database di esempio attraverso il browser. Il corso richiede circa 20 ore per essere completato.

Se volete davvero diventare degli esperti, potete fare più pratica nella risoluzione di problemi complessi lavorando con il nostro set di esercitazioni Window Functions. Affronterete 100 esempi di report complessi utilizzando tre diversi database.

Potete anche leggere questo articolo su come esercitarsi con le funzioni delle finestre e trovare maggiori informazioni sulla sintassi di BigQuery qui.

Se siete alla ricerca di un lavoro di alto livello nel campo dell'analisi dei dati o della scienza dei dati, è molto probabile che durante il colloquio vi venga chiesto di dimostrare la vostra conoscenza delle funzioni SQL a finestra. Per avere un'idea del tipo di domande che potrebbero esservi poste (e di come rispondere), ecco un articolo che illustra le principali domande di colloquio sulle funzioni SQL a finestra.

Fate i primi passi oggi stesso per portare le vostre capacità di analisi dei dati al livello successivo!