Come utilizzare la clausola PARTITION BY in SQL

Oggi ci occuperemo delle funzioni finestra. In particolare, ci concentreremo sulla funzione PARTIZIONE PER e spiegheremo cosa fa.

PARTITION BY è una delle clausole utilizzate nelle funzioni finestra. In SQL, le funzioni finestra sono utilizzate per organizzare i dati in gruppi e calcolare le statistiche per essi. Suona terribilmente familiare, vero? Anche se sembrano simili, le funzioni finestra e GROUP BY non sono la stessa cosa; le funzioni finestra sono più simili a GROUP BY con gli steroidi. Perché? Perché le funzioni finestra mantengono i dettagli delle singole righe e calcolano le statistiche per i gruppi di righe. GROUP BY non può farlo!

La clausola PARTITION BY è fondamentale per questa distinzione; è la clausola che divide i risultati di una funzione finestra in sottoinsiemi o partizioni di dati. In un certo senso, è il GROUP BY per le funzioni finestra. Imparerete presto come funziona.

Esiste una versione molto più completa (e interattiva) di questo articolo - il nostro Window Functions corso. Copre tutto ciò di cui parleremo e molto di più. Attraverso i suoi esercizi interattivi, imparerete tutto quello che c'è da sapere sulle funzioni delle finestre. Verranno analizzate le clausole OVER(), PARTITION BY e ORDER BY e si imparerà a utilizzare le funzioni di classificazione e di analisi delle finestre. Il corso offre anche 47 esercizi per esercitarsi e un quiz finale. Se siete indecisi, ecco perché dovreste imparare le funzioni di finestra.

Ora parliamo di PARTITION BY!

Sintassi di PARTITION BY

La sintassi della clausola PARTITION BY è la seguente:

SELECT column_name,
  	 window_function (expression) OVER (PARTITION BY column name)
FROM table;

Nella parte window_function si inserisce la funzione specifica della finestra.

La clausola OVER() è una clausola obbligatoria che fa funzionare la funzione finestra. Essa definisce virtualmente la funzione finestra.

La sottoclausola PARTITION BY è seguita dai nomi delle colonne. Le colonne specificate in questa clausola saranno le partizioni/gruppi in cui verranno raggruppati i risultati della funzione finestra.

I seguenti esempi chiariranno meglio questo aspetto. Sappiamo che non è possibile memorizzare tutto subito, quindi tenete a portata di mano il nostro SQL Window Functions Cheat Sheet durante la lettura degli esempi. È un utile promemoria delle diverse funzioni della finestra e della loro sintassi.

Esempi di PARTITION BY

Il set di dati di esempio è costituito da una tabella, dipendenti. Ecco le sue colonne:

• id - L'ID del dipendente.
• first_name - Il nome del dipendente.
• last_name - Il cognome del dipendente.
• job_title - Il titolo di lavoro del dipendente.
• department - Il reparto del dipendente.
• date_of_employment - La data di inizio del rapporto di lavoro del dipendente.
• salary - Lo stipendio del dipendente.

Date un'occhiata ai dati della tabella prima di iniziare a scrivere il codice:

Se si desidera seguire la procedura scrivendo le proprie query SQL, ecco il codice per la creazione di questo set di dati.

Utilizzo di OVER (PARTITION BY)

Ora è il momento di mostrare come funziona PARTITION BY con uno o due esempi.

Esempio n. 1

Nel primo esempio, l'obiettivo è mostrare gli stipendi dei dipendenti e lo stipendio medio per ogni reparto. Se avete prestato attenzione, sapete già come PARTITION BY può aiutarci in questo caso:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 AVG(salary) OVER (PARTITION BY department) AS average_salary_by_department
FROM employees;

Per calcolare la media, è necessario utilizzare la funzione aggregata AVG(). Scrivete la colonna salary tra le parentesi. Per ora si tratta di una normale funzione aggregata. Per renderla una funzione aggregata a finestra, scrivere la clausola OVER().

Ora, ricordate che non abbiamo bisogno della media totale (cioè di tutta l'azienda), ma della media per reparto. Per avere questa metrica, inserire la colonna reparto nella clausola PARTITION BY.

Questo restituisce l'output desiderato? Vediamo!

Si può notare che l'output elenca tutti i dipendenti e i loro stipendi. Per il reparto IT, lo stipendio medio è di 7.636,59. Questo valore viene ripetuto per tutti i dipendenti del reparto IT.

Quando arriviamo ai dipendenti di un altro reparto, la media cambia. In questo caso, è di 6.418,12 per il reparto Marketing. Seguendo questa logica, il salario medio nella Gestione del rischio è di 6.760,01.

Come si può notare, PARTITION BY ha utilizzato la funzione finestra per calcolare la media dei reparti.

Che differenza c'è con GROUP BY? Vediamo cosa succede se calcoliamo il salario medio per reparto utilizzando GROUP BY.

Come si può vedere, si ottengono tutti gli stessi stipendi medi per reparto. Tuttavia, una differenza sostanziale è che non si ottiene il salario del singolo dipendente. È possibile approfondire questa differenza leggendo un articolo sulla differenza tra PARTITION BY e GROUP BY.

Esempio n. 2

Ora vogliamo mostrare gli stipendi di tutti i dipendenti e lo stipendio più alto per titolo di lavoro.

La query è molto simile alla precedente. Le uniche due modifiche sono la funzione aggregata e la colonna in PARTITION BY.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 MAX(salary) OVER (PARTITION BY job_title) AS max_salary_by_job_title
FROM employees;

Questa volta utilizziamo la funzione aggregata MAX() e suddividiamo l'output per titolo di lavoro.

Ecco il risultato:

Osservate le prime due righe. Bob Mendelsohn e Frances Jackson sono analisti di dati che lavorano rispettivamente nel Risk Management e nel Marketing. La tabella mostra i loro stipendi e lo stipendio più alto per questa posizione lavorativa. È 5.412,47, lo stipendio di Bob Mendelsohn.

La stessa logica si applica al resto dei risultati. Naturalmente, quando c'è un solo titolo di lavoro, lo stipendio del dipendente e lo stipendio massimo per quel titolo di lavoro saranno gli stessi. Questo è il caso dell'ingegnere dei dati e dell'analista di sistema.

Questo esempio può anche mostrare i limiti di GROUP BY.

Il codice seguente mostrerà lo stipendio più alto in base al titolo di lavoro:

SELECT job_title,
	 MAX(salary) AS max_salary_by_job_title
FROM employees
GROUP BY job_title;

Ed ecco l'output:

Sì, gli stipendi sono gli stessi di PARTITION BY. Ma con questo risultato, non si ha idea di quale sia lo stipendio di ogni dipendente e di chi abbia lo stipendio più alto.

Uso di OVER (ORDER BY)

La clausola ORDER BY è un'altra sottoclausola della funzione finestra. Ordina i dati all'interno di una partizione o, se la partizione non è definita, l'intero set di dati.

Quando si parla di ordine, non si intende l'output. Quando viene utilizzata con le funzioni finestra, la clausola ORDER BY definisce l'ordine in cui una funzione finestra eseguirà i suoi calcoli.

ORDER BY può essere utilizzata con o senza PARTITION BY.

Vediamo prima come funziona senza PARTITION BY. La useremo per mostrare i dati dei dipendenti e classificarli in base alla data di assunzione. La classifica sarà fatta dalla data più antica a quella più recente.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 date_of_employment,
	 RANK() OVER (ORDER BY date_of_employment ASC) AS employment_date_rank
FROM employees;

La funzione finestra che utilizziamo ora è RANK(). È una delle funzioni utilizzate per classificare i dati. Anche in questo caso, la clausola OVER() è obbligatoria.

La clausola ORDER BY indica alla funzione di classificazione di assegnare i gradi in base alla data di assunzione in ordine decrescente.

Eseguire la query e ottenere questo risultato:

Tutti i dipendenti sono classificati in base alla data di assunzione. La prima persona assunta è al primo posto e l'ultima al decimo.

Uso di OVER (PARTITION BY ORDER BY)

Come abbiamo già detto, PARTITION BY e ORDER BY possono essere usati anche contemporaneamente. Vediamo alcuni esempi.

Esempio 1

Immaginate di dover classificare i dipendenti di ogni reparto in base al loro stipendio. Come si fa?

Ecco la soluzione:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Utilizziamo ancora una volta la funzione RANK(). Nella clausola OVER(), i dati devono essere suddivisi per reparto. Per ordinare i dipendenti, utilizzare la colonna stipendio in ORDER BY e ordinare i record in ordine decrescente.

Vediamo cosa fa questa query:

Nel reparto IT, Carolina Oliveira ha lo stipendio più alto. Seguono Ines Owen e Walter Tyson, mentre l'ultimo è Sean Rice. Tutti sono classificati di conseguenza.

Quando la funzione finestra passa al reparto successivo, si azzera e ricomincia la classifica dall'inizio. Così, Franck Monteblanc è il più pagato, mentre Simone Hill e Frances Jackson sono rispettivamente al secondo e al terzo posto.

Lo stesso avviene con i dipendenti della Gestione dei rischi.

Esempio n. 2

Facciamo pratica con un esempio leggermente diverso. Vogliamo ancora classificare i dipendenti in base allo stipendio. Questa volta, però, non in base al reparto, ma al titolo di lavoro.

Ecco come fare.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY job_title ORDER BY salary DESC) AS salary_rank
FROM employees;

I dati sono ora partizionati per mansione. La clausola ORDER BY rimane invariata: ordina ancora in ordine decrescente per stipendio.

Questo è l'output della query:

La logica è la stessa dell'esempio precedente. In questo esempio, c'è un massimo di due dipendenti con lo stesso titolo di lavoro, quindi i ranghi non vanno oltre.

Bob Mendelsohn è il più pagato dei due analisti di dati. Il rango 1 è riservato all'ingegnere dei dati, perché c'è un solo dipendente con questa qualifica. Il resto dei dati viene ordinato con la stessa logica.

Potete trovare altri esempi in questo articolo sulle funzioni finestra in SQL. E se conoscere le funzioni finestra vi fa venire voglia di una carriera migliore, sarete felici di sapere che abbiamo risposto alle 10 migliori domande di colloquio sulle funzioni finestra in SQL.

Quando usare PARTITION BY

Abbiamo risposto al "come". La seconda domanda importante a cui rispondere è quando utilizzare PARTITION BY.

Ci sono due usi principali. Il primo è quando si desidera raggruppare i dati e calcolare alcune metriche, ma anche mantenere le singole righe con i loro valori.

Il secondo uso di PARTITION BY è quando si desidera aggregare i dati in due o più gruppi e calcolare le statistiche per questi gruppi.

PARTITION BY Deve aver stuzzicato la vostra curiosità

PARTITION BY è una clausola meravigliosa con cui avere familiarità. Non solo significa che conoscete le funzioni finestra, ma aumenta anche la vostra capacità di calcolare le metriche, andando oltre le clausole obbligatorie utilizzate nelle funzioni finestra.

Volete soddisfare la vostra curiosità su cos'altro possono fare le funzioni finestra e PARTITION BY? Il corso vi aspetta! Window Functions corso vi aspetta!