Un'introduzione alle funzioni della finestra SQL

La caratteristica molto potente che ami odiare (ma devi sapere)

Le funzioni della finestra SQL forniscono alcune funzionalità estremamente potenti e utili. Ma per molti, poiché sono così estranei all'SQL standard, sono difficili da imparare e comprendere, hanno una sintassi strana e molto spesso vengono evitati.

Le funzioni della finestra possono essere semplicemente spiegate come funzioni di calcolo simili all'aggregazione, ma dove la normale aggregazione tramite la clausola GROUP BY combina poi nasconde le singole righe che vengono aggregate, le funzioni della finestra hanno accesso alle singole righe e possono aggiungere alcuni degli attributi di quelle righe nel insieme di risultati.

In questo tutorial sulle funzioni della finestra SQL, ti introdurrò con le funzioni della finestra, spiegherò i vantaggi e quando le useresti e ti fornirò esempi reali per aiutarti con i concetti.

Una finestra sui tuoi dati

Una delle funzionalità più utilizzate e importanti in SQL è la capacità di aggregare o raggruppare righe di dati in modi particolari. In alcuni casi, tuttavia, il raggruppamento può diventare estremamente complesso, a seconda di quanto richiesto.

Hai mai desiderato scorrere i risultati della tua query per ottenere una classifica, una top x list o simili? Hai avuto progetti di analisi in cui volevi preparare i tuoi dati nel modo giusto per uno strumento di visualizzazione, ma l'hai trovato quasi impossibile o così complesso da non valerne la pena?

Le funzioni della finestra possono semplificare le cose. Dopo aver ottenuto il risultato della tua query, cioè dopo la clausola WHERE e qualsiasi aggregazione standard, le funzioni della finestra agiranno sulle righe rimanenti (la finestra di dati) e ti daranno ciò che desideri.

Alcune delle funzioni della finestra che esamineremo includono:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Più facile

La clausola OVER è ciò che specifica una funzione di finestra e deve essere sempre inclusa nell'istruzione. L'impostazione predefinita in una clausola OVER è l'intero set di righe. Ad esempio, esaminiamo una tabella dei dipendenti in un database aziendale e mostriamo il numero totale di dipendenti su ciascuna riga, insieme alle informazioni di ciascun dipendente, anche quando hanno iniziato con l'azienda.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Quanto sopra, come molte funzioni della finestra, può anche essere scritto in un modo più familiare senza finestre, il che, in questo semplice esempio, non è male:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Ma ora, diciamo di voler mostrare il numero di dipendenti che hanno iniziato nello stesso mese del dipendente di fila. Dovremo restringere o limitare il conteggio solo a quel mese per ogni riga. Come si fa? Usiamo la clausola window PARTITION , in questo modo:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Le partizioni consentono di filtrare la finestra in sezioni in base a un determinato valore o valori. Ogni sezione è spesso chiamata cornice della finestra.

Per andare oltre, diciamo che non solo volevamo scoprire quanti dipendenti hanno iniziato nello stesso mese, ma vogliamo mostrare in quale ordine hanno iniziato quel mese. Per questo, possiamo usare la familiare clausola ORDER BY . Tuttavia, all'interno di una funzione finestra, ORDER BY agisce in modo leggermente diverso rispetto alla fine di una query.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

In questo caso ORDER BY modifica la finestra in modo che vada dall'inizio della partizione (in questo caso il mese e l'anno di inizio del dipendente) alla riga corrente. Pertanto, il conteggio ricomincia da ogni partizione.

Classificalo

Le funzioni della finestra possono essere molto utili ai fini della classifica. In precedenza abbiamo visto che l'utilizzo della funzione di aggregazione COUNT ci ha consentito di vedere in quale ordine i dipendenti sono entrati in azienda. Avremmo anche potuto utilizzare funzioni di classificazione delle finestre, come ROW_NUMBER() , RANK() e DENSE_RANK() .

Le differenze possono essere viste dopo aver aggiunto un nuovo dipendente il mese successivo e aver rimosso la partizione:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Puoi vedere le differenze. ROW_NUMBER() fornisce un conteggio sequenziale all'interno di una determinata partizione (ma con l'assenza di una partizione, passa attraverso tutte le righe). RANK() fornisce il rango di ogni riga in base alla clausola ORDER BY . Mostra i pareggi e quindi salta la classifica successiva. DENSE_RANK mostra anche i pareggi, ma poi continua con il valore successivo consecutivo come se non ci fossero pareggi.

Altre funzioni di classificazione includono:

• CUME_DIST – Calcola il rango relativo della riga corrente all'interno di una partizione
• NTILE – Divide le righe per ciascuna partizione della finestra nel modo più uniforme possibile
• PERCENT_RANK – Rango percentuale della riga corrente

Nota anche in questo esempio che puoi avere più funzioni Window in una singola query e sia la partizione che l'ordine possono essere diversi in ciascuna!

Righe e intervalli e fotogrammi, oh mio

Per definire o limitare ulteriormente la cornice della finestra all'interno della clausola OVER() , puoi utilizzare ROWS e RANGE . Con la clausola ROWS , puoi specificare le righe incluse nella tua partizione come quelle precedenti o successive alla riga corrente.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

In questo esempio, la cornice della finestra va dalla prima riga alla riga corrente meno 1 e la dimensione della finestra continua ad aumentare per ogni riga.

L'intervallo funziona in modo leggermente diverso e potremmo ottenere un risultato diverso.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

L'intervallo includerà quelle righe nella cornice della finestra che hanno gli stessi valori ORDER BY della riga corrente. Pertanto, è possibile che tu possa ottenere duplicati con RANGE se ORDER BY non è univoco.

Alcuni descrivono ROWS come un operatore fisico mentre RANGE è un operatore logico. In ogni caso i valori di default per ROWS e RANGE sono sempre UNBOUNDED PRECEDING AND CURRENT ROW .

Cos'altro?

La maggior parte delle funzioni aggregate standard funzionano con le funzioni Window. Abbiamo già visto COUNT negli esempi. Altri includono SUM , AVG , MIN , MAX , ecc.

Con le funzioni della finestra, puoi anche accedere sia ai record precedenti che ai record successivi utilizzando LAG e LEAD e FIRST_VALUE e LAST_VALUE . Ad esempio, supponiamo di voler mostrare su ogni riga una cifra di vendita per il mese corrente e la differenza tra la cifra di vendita del mese scorso. Potresti fare qualcosa del genere:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Fondamentalmente, le funzioni della finestra SQL sono molto potenti

Sebbene questa sia una rapida introduzione alle funzioni della finestra SQL, si spera che susciti il ​​tuo interesse nel vedere tutto ciò che possono fare. Abbiamo appreso che le funzioni finestra eseguono calcoli simili a come fanno le funzioni di aggregazione, ma con l'ulteriore vantaggio di avere accesso ai dati all'interno delle singole righe, il che le rende piuttosto potenti. Contengono sempre la clausola OVER e possono contenere PARTITION BY , ORDER BY e una serie di funzioni di aggregazione ( SUM , COUNT , ecc.) e altre funzioni posizionali ( LEAD , LAG ). Abbiamo anche imparato a conoscere i frame delle finestre e come incapsulano sezioni di dati.

Si noti che diverse versioni di SQL possono implementare le funzioni della finestra in modo diverso e alcune potrebbero non aver implementato tutte le funzioni o le clausole della finestra. Assicurati di controllare la documentazione per la piattaforma che stai utilizzando.

Se, come sviluppatore SQL, sei interessato a ottimizzare le prestazioni del tuo database SQL, dai un'occhiata a Ottimizzazione delle prestazioni del database SQL per gli sviluppatori .

Buona finestratura!

Per ulteriori informazioni su implementazioni specifiche, vedere:

• Documentazione sulle funzioni della finestra di PostgreSQL per un'implementazione di PostgreSQL.
• Documenti SELECT - OVER Clause (Transact-SQL) di Microsoft.
• Funzioni della finestra in SQL Server per un'ottima panoramica sulle implementazioni di SQL Server e la sua parte 2.