Perché dovrei imparare le funzioni finestra di SQL?

SQL esiste da più di 25 anni. La sua sintassi di base, come SELECT, WHERE, GROUP BY, HAVING e ORDER BY, è ben nota. Ma c'è qualcosa di diverso dall'SQL tradizionale? È possibile andare oltre le basi e migliorare le proprie competenze?

La risposta a questa domanda è sì. Esiste una versione moderna di SQL. In questo articolo ci immergeremo nella versione moderna e impareremo a conoscere le funzioni SQL a finestra. Se siete principianti avanzati e volete analizzare i dati con l'aiuto delle funzioni finestra di SQL (chiamate anche funzioni analitiche), questo articolo fa per voi.

Breve storia di SQL

Storia dello sviluppo di SQL - standardizzazione

SQL è un linguaggio famoso ma molto vecchio. È stato introdotto negli anni '70 da IBM. Nel 1986, i gruppi di standard ANSI e ISO adottarono ufficialmente una definizione standard di "Database Language SQL" (SQL-1986).

Nel 1992 è stata effettuata una revisione importante (SQL- 92) dello standard iniziale. È l'SQL standard che utilizziamo oggi. Esatto, sono passati 28 anni da quando SQL-92 è stato adottato ufficialmente. I suoi concetti di base (SELECT, WHERE, ecc.) sono familiari, indipendentemente dal fatto che si utilizzi Oracle, DB2, MySQL, PostgreSQL o un altro database. In ogni DBMS si scrivono istruzioni SQL simili, grazie alla stessa standardizzazione.

I concetti di base dell'SQL sono vecchi, ma molto utili. Direi che sono universali, in quanto funzionano indipendentemente dal settore in cui vengono applicati. Per imparare l'SQL, o per crescere come analista, dovrete imparare questi concetti.

Tuttavia, il mondo dell'informatica è cambiato molto dal 1992. Sicuramente SQL si è evoluto da allora? Sì, SQL si è evoluto e sono state fatte nuove revisioni degli standard. Negli anni 2000 è nato l'SQL moderno, un concetto che va oltre le dichiarazioni di base. Nel 2003 sono state introdotte le funzioni SQL window. Parliamo dei vantaggi delle funzioni SQL a finestra e del perché conoscerle è una buona cosa.

Prima, però, esaminiamo qualcosa che i neofiti spesso confondono con le funzioni finestra: le funzioni aggregate di SQL.

Un ripasso sulle funzioni aggregate

Se avete familiarità con l'SQL tradizionale, probabilmente avete usato le funzioni aggregate, che consentono di eseguire calcoli su gruppi di righe e di ottenere un'unica riga di output o risultato. Per esempio, forse avete calcolato dei totali o delle medie su un insieme di righe o forse avete contato il numero di righe per categoria. In questo caso, avete utilizzato almeno alcune delle funzioni aggregate di SQL: SUM() AVG() , MIN(), MAX() e COUNT(). Vengono spesso utilizzate con le clausole GROUP BY e HAVING all'interno delle istruzioni SELECT.

Vediamo un esempio: come calcolare il prezzo medio per gruppo di righe utilizzando GROUP BY.

Utilizzeremo i dati Forex sui tassi di cambio delle valute. Ecco la nostra tabella di input:

Tassi di cambio - Tabella CURRENCYTRADE

La tabella contiene i prezzi di chiusura dei cambi GBP-USD e EUR-USD. In questo esempio, utilizziamo sei prezzi di chiusura per ciascuna coppia di valute. Da questi dati, calcoleremo il prezzo medio di chiusura per ogni coppia di valute (GBPUSD, EURUSD) separatamente.

Ecco l'istruzione SELECT che calcola il prezzo medio di chiusura per ogni coppia di valute:

select ticker,avg(close) as average_price from CURRENCYTRADE group by ticker;

L'immagine sottostante mostra il risultato sulla destra:

Il risultato è presentato come una singola riga di output per ogni coppia di valute. I calcoli (il valore medio per ogni coppia) sono stati effettuati su sei righe per ogni coppia. Questo è un semplice esempio di funzione aggregata.

Passiamo ora alle funzioni finestra.

Che cos'è una funzione finestra SQL?

Le funzioni finestra di SQL sono simili alla sintassi aggregata GROUP BY? Mi piace pensare di sì. Come una funzione aggregata utilizzata con la clausola GROUP BY, anche una funzione finestra esegue calcoli su un insieme di righe. Tuttavia, il risultato di una funzione finestra non viene presentato come una singola riga di output per ogni gruppo; in altre parole, le righe non vengono compresse nella tabella risultante. Viene invece restituita ogni riga della tabella di input.

La differenza tra una funzione aggregata e una funzione finestra in SQL è semplice. Una funzione aggregata comprime tutte le righe in un unico risultato, perdendo così l'accesso alle singole righe. Una funzione finestra consente l'accesso a ogni riga nella finestra definita. Questo è mostrato nell'immagine seguente:

Differenza tra le funzioni aggregate e le funzioni finestra di SQL

Per imparare le funzioni finestra SQL, consiglio il corso interattivo Window Functions all'indirizzo LearnSQL.it.

Torniamo al nostro esempio. Se si esegue questa parte di codice...

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

... il prezzo medio di chiusura di ogni coppia di valute sarà assegnato a ogni riga della tabella. CURRENCYTRADE tabella.

Il risultato sarà simile a questo:

Come si può notare, la funzione finestra non ha raggruppato l'output in una singola riga per gruppo di coppie di valute. Invece, ogni riga contiene informazioni aggiuntive: il prezzo medio di chiusura per la coppia di valute appropriata. Questo può essere molto utile, poiché molte analisi richiedono informazioni aggiuntive per ogni riga, pur mantenendo tutte le colonne del set di dati iniziale.

Sintassi della funzione finestra

Nell'ultimo esempio di funzione finestra, abbiamo usato alcune parole chiave speciali come OVER() e PARTITION BY:

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

Queste sono le parole chiave principali che definiscono una funzione finestra. Ecco una breve spiegazione:

1. OVER indica che si tratta di una funzione finestra. All'interno della clausola OVER, si possono avere PARTITION, ORDER BY e altre clausole che indicano come viene inquadrata la finestra (cioè i gruppi e il loro ordine). Anche se nel nostro esempio non abbiamo usato la clausola window frame (ROW o RANGE), tenete presente che è possibile usare entrambe all'interno della clausola OVER per specificare le cornici all'interno delle partizioni.
2. PARTITION indica il modo in cui i dati sono raggruppati o incorniciati. Viene inserita all'interno della clausola OVER. Nel nostro esempio, abbiamo calcolato il prezzo medio per ogni coppia di valute; pertanto, abbiamo partizionato la finestra in base alla colonna ticker.
3. ORDER BY (che non è stato utilizzato nel nostro esempio) viene spesso utilizzato per determinare l'ordine delle righe all'interno di ciascun frame.
4. ROW o RANGE vengono utilizzati se si desidera limitare ulteriormente le righe all'interno della partizione. A tale scopo, si specificano i punti di inizio e fine della partizione. Qualunque sia la clausola utilizzata, deve essere usata con ORDER BY. La sintassi è la seguente:

[ROWS | RANGE] BETWEEN  AND 

La <fila_iniziale> è indicata da uno dei seguenti elementi:

• PRECEDENTE NON LIMITATO: La finestra inizia dalla prima riga della partizione.
• FILA CORRENTE: La finestra inizia nella riga corrente.
• <letterale intero senza segno> PRECEDENTE o SEGUENTE.

E l'opzione <fila_finale> utilizza una delle seguenti opzioni:

• UNBOUNDED FOLLOWING: La finestra termina nell'ultima riga della partizione.
• FILA CORRENTE: La finestra termina nella riga corrente.
• <letterale intero senza segno> PRECEDENTE o SEGUENTE.

Qui si possono trovare altri esempi di clausole di cornice della finestra.

Non preoccupatevi se non vi sentite a vostro agio con questa sintassi. La pratica vi aiuterà; posso consigliarvi il corso di LearnSQL.it Window Functions che contiene molte informazioni utili.

Dopo aver appreso come utilizzare le funzioni analitiche di SQL nelle query, il passo successivo consiste nel dare un'occhiata ai tipi di funzioni finestra disponibili in SQL. Finora abbiamo mostrato solo come AVG() (una funzione aggregata) possa essere usata come funzione finestra. Vediamo cosa possono fare le altre funzioni.

Tipi di funzioni finestra

Esistono tre tipi principali di funzioni finestra:

• Funzioni finestra aggregate: AVG(), MIN(), MAX(), COUNT(), SUM(). Queste funzioni servono a calcolare i valori medi, minimi o massimi, il numero totale di righe o la somma totale all'interno di ogni cornice definita. Le funzioni di finestra aggregata restituiscono un singolo valore per ogni riga della query sottostante.

• Funzioni finestra di classificazione: RANK(), ROW_NUMBER(), e simili. Le funzioni finestra di classificazione sono utilizzate per classificare le righe all'interno di ciascun frame. Ad esempio, RANK() classifica un valore in un gruppo di valori. L'espressione ORDER BY nella clausola OVER determina il valore di rango. Ogni valore viene classificato all'interno della sua partizione. Le righe con valori uguali per i criteri di classificazione ricevono lo stesso rango.

  Vediamo un altro esempio di funzione finestra di classificazione. ROW_NUMBER() determina il numero ordinale della riga corrente all'interno della sua partizione. Ancora una volta, l'ORDER BY nella clausola OVER determina questo numero. Ogni valore viene ordinato all'interno della sua partizione.

• Funzioni della finestra dei valori: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Queste funzioni sono molto utili per la creazione di rapporti e riepiloghi dei dati. Le funzioni LAG() e LEAD() restituiscono il valore della riga precedente (LEAD()) o successiva (LAG()) alla riga corrente in una partizione. Se non esiste alcuna riga, viene restituito un null. Analogamente, la funzione FIRST_VALUE() / LAST_VALUE() restituisce il valore dell'espressione specificata per la prima (o l'ultima) riga della finestra.

Abbiamo già visto un esempio di utilizzo delle funzioni finestra aggregate, quindi possiamo capire perché sono utili nell'analisi dei dati. In realtà, tutti e tre i tipi di funzioni finestra di SQL sono utilizzati frequentemente in analisi complesse. Sono un'ottima caratteristica di SQL.

Avete bisogno di un esempio reale? Supponiamo che si tratti di un'attività di trading sul mercato Forex. Spesso, quando si vendono o si acquistano posizioni, si esamina il prezzo di chiusura del minuto o dell'ora precedente; per questo si usa la funzione LAG(). Potete anche classificare i prezzi di chiusura utilizzando le funzioni di ranking window all'interno di una finestra temporale specifica. Oppure si può trovare il prezzo di chiusura iniziale o finale utilizzando le funzioni di finestra del valore.

Utilizzo delle funzioni SQL window: LAG()

Approfondiamo l'uso delle funzioni analitiche SQL nella vita reale. Per ogni riga, vediamo il prezzo di chiusura della riga precedente. Utilizzeremo una funzione finestra di classificazione:

select *,LAG(close) OVER(PARTITION BY ticker ORDER BY datetime) AS previous_close from CURRENCYTRADE;

Utilizziamo LAG(), che restituisce il valore della riga precedente. L'OVER indica che si tratta di una funzione finestra che raggruppa le righe per coppia di valute. Poiché si utilizza LAG(), è necessaria anche una clausola ORDER BY per ordinare i dati all'interno di ciascun frame prima di assegnare i prezzi di chiusura della riga precedente. Ordiniamo i dati in base alla colonna datetime, il che significa che avremo il prezzo di chiusura del minuto precedente in ogni riga corrente. L'immagine seguente mostra l'aspetto dell'output:

Funzione Window Lag

Questo codice è più semplice e facile da mantenere. Questo è uno dei principali vantaggi dell'uso delle funzioni finestra. In effetti, i vantaggi sono molti, come vedremo.

Vantaggi dell'uso dell'SQL Window Functions

Le funzioni finestra sono utili quando non è necessario comprimere le righe dell'insieme dei risultati, cioè raggruppare i dati dei risultati in un'unica riga di output. Invece di una singola riga di output, viene restituito un singolo valore per ogni riga della query sottostante. Questo è il vantaggio principale, a mio avviso.

Tra gli altri vantaggi delle funzioni analitiche SQL vi sono:

• Le funzioni a finestra consentono di raccogliere contemporaneamente valori aggregati e non aggregati. Infatti, per ogni valore di riga restituito, non vi è alcun raggruppamento o collasso della riga stessa. È possibile mantenere tutte le colonne di ogni riga e aggiungere altri valori calcolati dalla funzione finestra. Questo è un grande vantaggio quando si ha bisogno di valori aggregati e non aggregati in una tabella.
• La sintassi è semplice ed è più facile mantenere il codice in produzione. Immaginate quanto tempo ci vorrebbe per implementare l'equivalente delle funzioni LEAD(), LAG() o RANK() utilizzando l'SQL tradizionale. Oppure per assegnare le medie a ogni riga senza le funzioni finestra! Dovreste prima utilizzare la funzione aggregata GROUP BY, seguita da una LEFT JOIN con la tabella dei dati di input originali. Il codice sarebbe più complicato e più difficile da mantenere. Ecco un interessante articolo che mostra come un esempio può essere risolto sia con i cursori che con le funzioni analitiche SQL. Il codice delle funzioni analitiche è più pulito e semplice, non credete?
• È possibile assegnare facilmente a una riga corrente un valore di una riga precedente o successiva. In alcuni database questa è un'opzione molto più veloce rispetto all'utilizzo di una soluzione con il cursore o di una subquery correlata. Tali codici sono più complessi e difficili da mantenere. Ecco un bell'articolo che confronta le prestazioni (funzioni finestra vs. cursore vs. subquery) in un database MS SQL. Bello, vero?

Per saperne di più su SQL Window Functions

La maggior parte degli utenti di SQL sono principianti avanzati (secondo il modello Dreyfus) e potrebbero non conoscere bene l'SQL moderno. Sapere come usare le funzioni di finestra è una tecnica più avanzata, ma che vale la pena di imparare! Se decidete di imparare le funzioni finestra, cercate un corso che contenga molti esempi di funzioni analitiche SQL utilizzate in casi aziendali concreti. E, soprattutto, cercatene uno con molti esercizi per esercitarvi! Non dimenticate che più vi esercitate, più velocemente e meglio imparerete.

Oltre ai corsi online, potete sempre consultare il blog LearnSQL. È pieno di articoli interessanti che spiegano le funzioni finestra, come ad esempio:

• Esempio di funzione finestra SQL con spiegazioni
• SQL comune Window Functions: funzioni posizionali
• Come usare le funzioni di rango in SQL
• SQL comune Window Functions: Usare le partizioni con le funzioni di rango