Di Kathi Kellenberger
Ho usato ROW_NUMBER nel mio codice T-SQL per anni senza rendermi conto che è una di un gruppo di funzioni incredibilmente utili chiamate funzioni finestra. Solo quando ho messo insieme una presentazione sulle nuove caratteristiche T-SQL nella versione di SQL Server 2012 ho capito che c’era così tanto di cui parlare che ho creato una presentazione separata solo sulle nuove funzioni finestra. Alla fine quella presentazione si è trasformata in due presentazioni, il mio libro pubblicato da Apress e un corso Pluralsight. Ho parlato a chiunque mi ascoltasse di queste grandi funzioni, e mi ritrovo ad usarle quotidianamente al lavoro.
Le funzioni finestra permettono di eseguire calcoli su un insieme di righe. Sono simili nel funzionamento al raggruppamento con le funzioni aggregate, ma permettono una differenza importante: Le query aggregate restituiscono una riga per gruppo, e i dettagli vengono eliminati nei risultati. Le query che coinvolgono le funzioni finestra restituiscono tutte le righe di dettaglio insieme al risultato dell’espressione della funzione finestra. Avete il dettaglio e ottenete anche l’aggregato.
Quando parlo a SQL Server e agli eventi per sviluppatori sulle funzioni finestra, di solito circa metà della stanza ha familiarità con ROW_NUMBER. Una manciata saprà qualcosa di più. Questo è deludente perché queste utili funzioni sono state disponibili per anni.
La mia funzione finestra preferita si chiama LAG. LAG vi permette di prendere qualsiasi colonna da un’altra riga nel set di risultati. Potete ottenere la stessa cosa con altri metodi, ma le prestazioni sono molto peggiori. Di seguito ci sono due query contro il database di AdventureWorks. Entrambe le query danno lo stesso risultato. La prima lo fa attraverso un outer join, mentre la seconda sfrutta LAG.
–Lista dei clienti con le date degli ordini e i giorni trascorsi dall’ordine precedente
SELECT CustomerID, OrderDate,
DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder
FROM Sales.SalesOrderHeader AS SOH
OUTER APPLY (
SELECT TOP(1) OrderDate AS PrevOrder
FROM Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
AND OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) AS OAPPLY
ORDER BY CustomerID, OrderDate;
–Produrre gli stessi risultati usando LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
La seconda query usando la funzione LAG è più semplice da scrivere e funziona meglio. Il piano di esecuzione attuale mostra che la prima query prende il 99% delle risorse del batch. La seconda query prende solo l’1% delle risorse. Ecco i due piani di esecuzione, e potete vedere voi stessi:
Se non avete familiarità con la sintassi delle funzioni finestra, la query LAG può essere un po’ confusa. Tutte le funzioni finestra avranno una clausola OVER che definisce la finestra, o insieme di righe, per il calcolo. Non voglio confrontare le date degli ordini tra i clienti, quindi ho usato l’opzione PARTITION BY per dividere le righe in gruppi per CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
La clausola OVER ha anche un’opzione ORDER BY. Questo è necessario nel caso di LAG, in modo che le righe siano allineate nell’ordine corretto affinché il motore prenda i rispettivi valori dalle loro righe corrette. La mia invocazione finale di LAG appare come segue:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG è solo una delle tante funzioni della finestra. Queste funzioni non vi permettono di fare nulla che sia impossibile senza di esse, ma rendono sicuramente più facile la codifica. Troverete anche che molte volte le vostre query vengono eseguite più velocemente quando formulate usando queste nuove funzioni.
Finisco per usare le funzioni finestra su base giornaliera. Mi rendono la vita più facile e migliorano le prestazioni delle mie query. Amo usarle, e dovreste farlo anche voi.
Informazioni sull’autore
Kathi Kellenberger è una consulente di Linchpin People. Le piace scrivere e parlare di argomenti SQL Server, avendo scritto più di due dozzine di articoli, contribuito a quattro libri e presentato a molti eventi SQL Server. Nel suo tempo libero, Kathi ama passare il tempo con la famiglia e gli amici, cantare e andare in bicicletta.
Impara ancora di più sulle funzioni finestra e la loro potenza nel risolvere difficili problemi di query dal libro di Kathi Kellenberger Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Gli altri libri di Kathi includono Beginning SQL Server Reporting Services (2016) e Beginning T-SQL (2014).
Lascia un commento