Le 10 migliori domande di intervista sulle funzioni SQL Window

Molte posizioni lavorative interessanti richiedono competenze in SQL, tra cui le funzioni finestra, che non vengono comunemente insegnate nei corsi online. In questo articolo, vi illustrerò le principali domande sulle funzioni finestra per ogni livello di esperienza.

Se state affrontando un colloquio di lavoro per una posizione SQL avanzata o per una posizione di analista di dati di livello intermedio o avanzato, probabilmente vi verrà chiesta la vostra conoscenza delle funzioni finestra di SQL. Niente panico! Anche se queste funzioni non sono comunemente trattate nei corsi online, abbiamo qui le risposte.

Domande comuni nei colloqui di lavoro su SQL Window Functions

L'idea di questo articolo è di aiutarvi a prepararvi a rispondere alle domande sui diversi argomenti delle funzioni finestra. Non possiamo fornire l'esatta domanda che riceverete, ma possiamo essere abbastanza certi degli argomenti a cui le domande punteranno.

In alcuni casi, la domanda può essere molto aperta, lasciando a voi la decisione di quale sotto-argomento delle funzioni di window trattare. In questo caso, dovrete conoscere l'importanza relativa di ciascun sottoargomento. Per cominciare, dovreste essere preparati ad affrontare una domanda aperta come:

1. Che cos'è una funzione finestra in SQL?

Le funzioni finestra sono funzioni SQL che operano su un insieme di record chiamato "finestra" o "cornice". La "finestra" è un insieme di righe che sono in qualche modo collegate alla riga attualmente elaborata dalla query (ad esempio, tutte le righe prima della riga corrente, 5 righe prima della riga corrente o 3 righe dopo la riga corrente).

Le funzioni finestra sono simili alle funzioni aggregate in quanto calcolano le statistiche per un gruppo di righe. Tuttavia, le funzioni finestra non comprimono le righe, ma mantengono i dettagli delle singole righe.

Le funzioni finestra possono essere suddivise nelle seguenti quattro categorie: funzioni aggregate, funzioni di classificazione, funzioni analitiche e funzioni di distribuzione.

Le funzioni aggregate sono quelle che si usano con GROUP BY. Queste funzioni includono:

• COUNT() conta il numero di righe all'interno della finestra.
• AVG() calcola il valore medio di una determinata colonna per tutti i record della finestra.
• MAX() ottiene il valore massimo di una colonna per tutti i record della finestra.
• SUM() restituisce la somma di tutti i valori di una determinata colonna all'interno della finestra.

Nella categoria delle classifiche:

• ROW_NUMBER() restituisce la posizione della riga nel set di risultati.
• RANK() classifica le righe in base a un determinato valore. Quando due righe si trovano nella stessa posizione, assegna loro lo stesso rango e lascia vuota la posizione successiva (ad esempio, 1, 2, 3, 3, 5...).
• DENSE_RANK() Anche l'opzione "Classifica" classifica le righe in base a un determinato valore, ma non lascia vuota la posizione successiva (ad esempio, 1, 2, 3, 3, 4, 5...).

Per informazioni dettagliate, vedere questo articolo sulle funzioni di classificazione.

Nella categoria analitica, le funzioni LEAD(), LAG() o FIRST_VALUE() consentono di ottenere dati da altre righe della stessa finestra. LEAD() restituisce i valori delle righe inferiori alla riga corrente; LAG() quelli delle righe superiori alla riga corrente. Per maggiori dettagli, si veda il nostro articolo su LEAD vs LAG.

Infine, nella categoria delle distribuzioni ci sono funzioni come PERCENT_RANK() e CUME_DIST() che possono ottenere classifiche percentili o distribuzioni cumulative. Consultate il nostro corso Window Functions per le istruzioni passo-passo su come utilizzare queste funzioni.

Ecco un esempio di query con funzioni finestra:

SELECT
    employee_name,
    department_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary) position 
FROM employee

In questa query, la funzione finestra RANK() viene utilizzata per classificare i dipendenti in base allo stipendio. Più avanti in questo articolo, discuteremo in dettaglio la sintassi della clausola OVER() e delle sotto-clausole PARTITION BY e ORDER BY. Per ora ci limitiamo a dire che sono utilizzate per definire i record che compongono la cornice della finestra.

Domande di base sulle funzioni della finestra

2. Qual è la sintassi della clausola OVER ()?

La clausola OVER() viene utilizzata per definire quali righe saranno presenti nella cornice della finestra. Nella clausola OVER() possono essere presenti le seguenti sottoclausole:

• PARTITION BY definisce la partizione, ovvero i gruppi di righe all'interno della cornice della finestra, che la funzione finestra utilizzerà per creare un risultato. (Questo verrà spiegato più avanti).
• ORDER BY definisce l'ordine delle righe nella finestra.
• ROWS/RANGE definisce i limiti superiore e inferiore della finestra.

Tutte le sottoclausole di OVER() sono opzionali e possono essere omesse. In questo caso, le funzioni verranno eseguite sull'intero riquadro della finestra.

Il seguente SQL mostra la clausola OVER() al lavoro:

SELECT
first_name,
last_name,
department, 
salary,
AVG(salary) OVER (PARTITION BY department) 
FROM employee

Per ogni dipendente, la query restituisce il suo nome, il suo cognome, il suo stipendio e lo stipendio medio del suo reparto. La clausola OVER (PARTITION BY department) crea una finestra di righe per ogni valore della colonna reparto. Tutte le righe con lo stesso valore nella colonna reparto apparterranno alla stessa finestra. La funzione AVG() viene applicata alla finestra: la query calcola lo stipendio medio nel reparto indicato.

L'articolo Cos'è la clausola OVER contiene una spiegazione completa della clausola OVER.

3. Descrivere la differenza tra Window Functions e le funzioni aggregate.

La differenza principale tra le funzioni finestra e le funzioni aggregate è che le funzioni aggregate raggruppano più righe in un'unica riga di risultato; tutte le singole righe del gruppo vengono compresse e i loro dati individuali non vengono mostrati. Le funzioni finestra, invece, producono un risultato per ogni singola riga. Questo risultato viene solitamente mostrato come un nuovo valore di colonna in ogni riga della finestra.

Il collasso delle righe è una caratteristica importante delle funzioni aggregate. Ad esempio, non è possibile risolvere il problema "Restituire tutti i dipendenti con il loro stipendio e lo stipendio massimo nel loro reparto" con le funzioni aggregate a causa della limitazione del collasso.

Per quanto riguarda la somiglianza, sia le funzioni aggregate che quelle a finestra eseguono un'operazione simile all'aggregazione su un insieme di righe. Alcune funzioni come AVG(), MAX(), MIN() e SUM() possono essere utilizzate sia come funzioni aggregate che come funzioni finestra. Tuttavia, quando il risultato di queste funzioni deve essere combinato con dati a livello di riga, è meglio usare una funzione finestra invece di una funzione aggregata.

Mostreremo due query SQL che restituiscono il nome del reparto e lo stipendio massimo di ogni reparto. Nel primo esempio, utilizzeremo MAX() come funzione aggregata:

SELECT   department_name,
         MAX(salary) AS max_salary
FROM     employee
GROUP BY department_name

Di seguito, possiamo vedere il risultato della query precedente. Si noti che c'è un record per ogni reparto grazie all'effetto di collasso della clausola GROUP BY:

department_name	max_salary
Accounting	93000
Sales	134000
Human Resources	78000

Nel prossimo esempio, otterremo un risultato simile ma leggermente diverso utilizzando MAX() come funzione finestra:

SELECT employee_name, 
       salary,
       department_name,
       MAX(salary) OVER (PARTITION BY department_name) AS max_salary
FROM   employee

Come abbiamo detto in precedenza, le funzioni finestra non fanno collassare i record. Nel risultato seguente, abbiamo una riga per dipendente per un totale di 5 righe:

employee_name	salary	department_name	max_salary
John Doe	93000	Accounting	93000
Jeremy Smith	134000	Sales	134000
Donna Hayes	120000	Sales	134000
Mark Ron	78000	Human Resources	78000
Denis Serge	72000	Human Resources	78000

Si noti che le colonne employee_name e salary sono state aggiunte semplicemente aggiungendo i loro nomi all'elenco delle colonne di SELECT. Non è stato possibile aggiungerle alla query con GROUP BY a causa della limitazione di collasso.

Nell'articolo SQL Window Functions by Explanation, è possibile trovare una spiegazione dettagliata delle differenze tra le funzioni aggregate e quelle a finestra.

4. Qual è la differenza tra Window Functions e la clausola GROUP BY?

Le funzioni aggregate sono spesso utilizzate con la clausola GROUP BY, che definisce i gruppi di righe in cui la funzione aggregata opererà. La clausola GROUP BY raggruppa le singole righe in insiemi di righe, consentendo l'esecuzione di funzioni aggregate come SUM(), AVG() o MAX() su questi insiemi. Qualsiasi colonna delle singole righe non può far parte del risultato, come si può vedere nella seguente query SQL:

SELECT   
   department_name, 
   AVG(salary)      -- AVG is an aggregate function
FROM  employee
GROUP BY department_name

Nella query precedente, abbiamo inserito una sola colonna nell'elenco SELECT: department_name. Questo è possibile perché la colonna nome_reparto compare nella clausola GROUP BY. Tuttavia, non è possibile aggiungere altre colonne nell'elenco SELECT; sono consentite solo le colonne specificate nell'elenco GROUP BY.

La seguente query SQL è equivalente alla precedente, ma utilizza le funzioni di window invece di GROUP BY:

SELECT
  department_name,
  AVG(salary) OVER(PARTITION BY department_name) -- AVG is a window function
FROM employee

La query precedente non ha una clausola GROUP BY perché la funzione AVG() è usata come funzione finestra. Possiamo riconoscere che AVG() è una funzione finestra grazie alla presenza della clausola OVER.

Suggerisco l'articolo SQL Window Functions vs. GROUP BY per un confronto completo tra le funzioni finestra e la clausola GROUP BY.

5. Mostrare un esempio di SQL Window Functions.

Questa è una buona occasione per citare una query che mostra l'importanza delle funzioni finestra e allo stesso tempo è collegata alle query mostrate nelle domande precedenti. La query che propongo risolverebbe questo compito: "Ottenere i nomi dei dipendenti, gli stipendi, i nomi dei reparti e lo stipendio medio di quel reparto".

Questa query è un modo semplice per mostrare come sia possibile combinare dati aggregati e a livello di riga. (La funzione finestra restituisce i dati aggregati).

SELECT employee_name,
       salary,
       department_name,
       AVG(salary) OVER (PARTITION BY department) avg_salary 
FROM employee

Sopra, si possono vedere le colonne a livello di riga employee_name, salary e department con la retribuzione media di ciascun reparto, calcolata dalla funzione finestra AVG(). La sotto-clausola PARTITION BY definisce che le finestre di record saranno create in base al valore della colonna department_name. Tutti i record con lo stesso valore in department_name saranno nella stessa finestra. Il risultato sarebbe simile a questo:

employee_name	salary	department_name	avg_salary
John Doe	93000	Accounting	93000
Jeremy Smith	134000	Sales	127000
Donna Hayes	120000	Sales	127000
Mark Ron	78000	Human Resources	75000
Denis Serge	72000	Human Resources	75000

Per esaminare altri esempi di funzioni finestra, consultare l'articolo Esempi di funzioni finestra SQL con spiegazioni.

6. Nome di alcune funzioni comuni Window Functions.

Le funzioni finestra possono essere suddivise in quattro categorie: funzioni aggregate, funzioni di classificazione, funzioni analitiche e funzioni di distribuzione.

Le funzioni aggregate sono le normali funzioni aggregate che si usano con GROUP BY: MAX(), MIN(), AVG(), SUM() e COUNT(). Queste funzioni, come abbiamo già mostrato, possono essere utilizzate come funzioni finestra.

Le funzioni di classificazione sono ROW_NUMBER(), RANK() e DENSE_RANK(). Vengono utilizzate per ottenere diverse posizioni in una classifica. Una spiegazione dettagliata delle funzioni di classificazione è contenuta nel seguente articolo.

Le funzioni analitiche comprendono LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE() e NTH_VALUE(). Queste funzioni consentono di ottenere dati da righe diverse da quella corrente (ad esempio, la riga precedente, la riga successiva, l'ultima riga all'interno di una finestra, ecc.) La funzione NTILE() divide le righe di una partizione in n gruppi e restituisce il numero del gruppo.

Infine, le funzioni di distribuzione PERCENT_RANK() e CUME_DIST() consentono di ottenere dati sul percentile o sulla distribuzione cumulativa (rispettivamente) per ogni riga della finestra.

Preferisco le funzioni analitiche perché ci permettono di confrontare o calcolare le differenze tra i diversi record all'interno della finestra (tra le altre cose). Ad esempio, se ho una serie temporale con i valori delle azioni, posso calcolare di quanto sono aumentate le azioni in ogni momento.

Ecco un altro esempio di funzioni analitiche. Le funzioni analitiche LEAD() e LAG() restituiscono una colonna di una riga successiva/precedente. Quindi, se abbiamo una tabella con le criptovalute, con un timestamp e un valore di quotazione...

Symbol	Timestamp	Value
BTC	2021-05-25 10:30	61400
BTC	2021-05-25 10:40	60300
BTC	2021-05-25 10:50	59800
ETH	2021-05-25 10:30	2700
ETH	2021-05-25 10:40	2750
ETH	2021-05-25 10:50	2820

Tabella Azioni

... possiamo ottenere il seguente rapporto. Per calcolare la percentuale di variazione, abbiamo bisogno dei dati di due righe diverse: Il valore della riga corrente e il valore della riga precedente. La funzione LEAD() restituirà il valore della riga precedente. Questo è il risultato:

Symbol	Timestamp	Value	% Variation
BTC	2021-05-25 10:30	61400	--
BTC	2021-05-25 10:40	60300	-1.8%
BTC	2021-05-25 10:50	59800	-0.8%
ETH	2021-05-25 10:30	2700	--
ETH	2021-05-25 10:40	2750	1.8%
ETH	2021-05-25 10:50	2820	2.5%

La colonna % Variation è stata calcolata con questo tipo di espressione:

(Current_value - Previous_value ) / Previous_value

Si noti che il valore della criptovaluta del timestamp precedente può essere ottenuto con:

LEAD(value) OVER (PARTITION BY crypto_symbol ORDER BY timestamp) 

Ecco la query completa:

SELECT Symbol, 
       Timestamp, 
       Value, 
       (Value - LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) /   
       LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) AS “% variation”
FROM   Cryptocurrencies

Se si vuole approfondire l'argomento LAG() e LEAD(), si consiglia di leggere l'articolo La funzione LAG() e la funzione LEAD() in SQL. L'articolo contiene una spiegazione dettagliata del funzionamento delle funzioni finestra nelle finestre ordinate.

Domande intermedie sulle funzioni finestra

7. Come si definisce la cornice della finestra?

Le funzioni finestra calcolano un risultato aggregato in base a un insieme di record chiamato "finestra" o "cornice della finestra". Le cornici delle finestre sono definite dalla clausola OVER().

Una clausola OVER() vuota significa che la finestra è l'intero set di dati:

SELECT employee_name,
       salary,
       department_name,
       AVG(salary) OVER () avg_salary 
FROM employee

La query di cui sopra calcola il salario medio e lo visualizza accanto agli altri dati dei dipendenti per tutti i dipendenti della tabella.

Esistono diverse sotto-clausole che possono essere inserite all'interno della clausola OVER() per definire con precisione una finestra.

La sottoclausola PARTITION BY specifica che tutti i record che hanno lo stesso valore in una determinata colonna appartengono alla stessa finestra. In altre parole, PARTITION BY specifica come viene definita la finestra. Pertanto, la seguente query calcola il salario medio per ogni reparto; i calcoli vengono eseguiti in base ai raggruppamenti dei valori della colonna department_name.

SELECT 
   employee_name,
   salary,
   department_name,
   AVG(salary) OVER (PARTITION BY department_name) avg_salary 
FROM employee

ORDER BY può essere utilizzato anche all'interno di OVER(). Si usa per mettere le righe della finestra in un ordine specifico. Le finestre ordinate sono molto importanti perché consentono di utilizzare diverse funzioni analitiche come LAG(), LEAD() e FIRST_VALUE().

SELECT 
   employee_name,
   salary,
   department_name,
   LAG(salary) OVER (ORDER BY salary) prev_salary 
FROM employee

Questa query visualizza lo stipendio del dipendente immediatamente precedente a quello attuale nell'ordine degli stipendi. Si noti che è possibile combinare le clausole ORDER BY e PARTITION BY in un'unica query: l'ordinamento viene applicato a ogni partizione individualmente.

Due sotto-clausole simili a OVER() sono RANGE e ROWS. Esse definiscono i limiti della finestra, ponendo limiti superiori e/o inferiori alla finestra dei record. Ciò significa che le funzioni della finestra possono essere calcolate in base a un sottoinsieme di righe invece che a tutte le righe della finestra. La differenza tra ROW e RANGE è spiegata in dettaglio nella nostra scheda sulle funzioni finestra di SQL. Per saperne di più su ROWS e RANGE e sulle diverse opzioni di limite disponibili, consultare le prossime due domande.

8. Come funziona ORDER BY con OVER?

Alcune funzioni finestra (come LAG(), LEAD() e FIRST_VALUE()) lavorano su una finestra ordinata di record. Quando si usa una di queste funzioni, è necessaria la sottoclausola ORDER BY per definire i criteri di ordinamento. Un buon esempio è la query precedente utilizzata per calcolare la percentuale di variazione delle criptovalute:

SELECT Symbol, 
       Timestamp, 
       Value, 
       (Value - LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) /   
       LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) AS “% variation”
FROM   Cryptocurrencies

Nella query precedente, la clausola OVER ha due sottoclausole: PARTITION BY e ORDER BY. PARTITION BY definisce quali record sono presenti in ogni finestra e ORDER BY definisce l'ordine dei record nella finestra. (La funzione LEAD() restituisce il valore del record precedente.

Se la clausola OVER non include una ORDER BY e non abbiamo ROWS/RANGE, la finestra è formata da tutte le righe che rispettano la clausola PARTITION BY. Tuttavia, quando si utilizza una clausola ORDER BY senza ROWS/RANGE, la cornice della finestra include le righe tra la prima riga (basata sulla clausola ORDER BY) e la riga corrente. In altre parole, le righe successive alla riga corrente non vengono incluse nella cornice della finestra. (Per maggiori dettagli su questi limiti, si veda la prossima domanda).

Le funzioni della finestra che richiedono una sottoclausola ORDER BY sono:

• RANK()
• DENSE_RANK()
• LEAD()
• LAG()
• FIRST_VALUE()
• LAST_VALUE()
• NTH_VALUE()
• PERCENT_RANK()
• CUME_LIST()

Per saperne di più sul funzionamento di ORDER BY, consultate l'articolo Come calcolare la differenza tra due righe in SQL.

Domande sulla funzione finestra avanzata

9. Spiegare cosa fa UNBOUNDED PRECEDING.

Una finestra è un insieme di righe in qualche modo correlate alla riga corrente, che viene valutata separatamente all'interno di ogni partizione. Quando si utilizza la clausola ORDER BY, è possibile definire facoltativamente dei limiti superiori e inferiori per la cornice della finestra. I limiti possono essere definiti come:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Questi limiti possono essere definiti con le sotto-clausole RANGE o ROWS nella clausola OVER(). UNBOUNDED PRECEDING indica che il limite inferiore della finestra è il primo record della finestra; allo stesso modo, il limite superiore può essere definito con UNBOUNDED FOLLOWING o CURRENT ROW. Questi limiti devono essere utilizzati solo con finestre ordinate.

Nell'immagine seguente si può vedere come funzionano i diversi limiti:

Ad esempio, se si vuole ottenere il valore medio di una criptovaluta considerando solo i valori che si sono verificati fino al valore attuale, si può utilizzare la seguente clausola OVER():

AVG(value) OVER (PARTITION BY symbol_name 
                 ORDER BY timestamp 
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                 ) 

In questa clausola OVER() abbiamo definito CURRENT ROW come limite superiore della finestra per il calcolo della media. Questo è esattamente ciò di cui abbiamo bisogno, poiché non vogliamo includere nel calcolo della media i valori registrati dopo il timestamp corrente.

10. Descrivere l'ordine delle operazioni di SQL e la posizione di Window Functions in questo ordine.

Le sottoclausole di un SQL SELECT vengono eseguite nel seguente ordine:

1. FROM / JOINS
2. DOVE
3. GRUPPO PER
4. Funzioni aggregate
5. AVENTE
6. Window Functions
7. SELEZIONARE
8. DISTINTO
9. UNIONE / INTERSEZIONE / ECCETTO
10. ORDINARE PER
11. OFFSET
12. LIMITE / FETCH / TOP

Poiché le funzioni finestra vengono calcolate durante il passaggio 6, non è possibile inserirle nella clausola WHERE (che viene calcolata nel passaggio 2). Tuttavia, è possibile aggirare questa limitazione utilizzando una CTE (Common Table Expression), in cui è possibile chiamare le funzioni finestra e memorizzare i loro risultati come colonne nella CTE. La CTE sarà trattata come una tabella e i risultati delle funzioni finestra saranno valutati come normali valori di colonna da WHERE.

C'è un interessante articolo sul perché le funzioni finestra non sono consentite nelle clausole WHERE, che si consiglia di leggere se si vogliono trovare degli esempi.

D'altra parte, è possibile utilizzare i risultati delle aggregazioni/raggruppamenti nelle funzioni finestra, poiché sono già calcolati nel momento in cui le funzioni finestra vengono elaborate.

Volete migliorare le vostre competenze in SQL Window Functions?

Questo articolo tratta diverse possibili domande di colloquio di lavoro sulle funzioni finestra di SQL. Il mio consiglio finale è di collegare le domande di questo articolo con le domande che riceverete durante un colloquio. Eccolo:

Cercate di associare ogni domanda di questo articolo a un argomento relativo alle funzioni a finestra, come "OVER clausola", "nomina una funzione" o "ORDER BY sottoclausola". Quindi, se durante il colloquio vi viene chiesto di parlare delle funzioni di window, identificate l'argomento della domanda e utilizzate le informazioni qui contenute per discutere l'argomento.

Se volete approfondire le funzioni SQL a finestra, vi consiglio di leggere l'articolo Corso del mese - Window Functions, che descrive il nostro corso Window Functions. Accrescere le vostre competenze è un investimento che potrebbe aiutarvi ad ottenere il lavoro!