Redgate Hub (Italiano)

Ho passato gli ultimi sei anni in giro per gli Stati Uniti raccontando ai professionisti del database le funzioni di T-SQL Window a SQL Saturdays e altri eventi. Sono stupito di come poche persone hanno sentito parlare di queste funzioni e ancora meno che li utilizzano. Alla fine di ogni presentazione, una o più persone vengono a dire che desideravano aver imparato a conoscere queste funzioni anni prima perché avrebbero potuto essere utili per così tante domande.,

Queste funzioni sono state promosse per migliorare le prestazioni rispetto ad altri metodi più tradizionali. Sono parzialmente d’accordo. Rendono molte query più facili da scrivere e, a volte, migliorano le prestazioni.

Nulla a che fare con il sistema operativo Windows

Queste funzioni fanno parte degli standard ANSI SQL 2003 e, nel caso di SQL Server, sono funzioni T-SQL utilizzate per scrivere query. Non hanno nulla a che fare con il sistema operativo Windows o qualsiasi chiamata API. Altri sistemi di database, come Oracle, hanno anche incluso questi come parte del proprio linguaggio SQL.,

Le funzioni Window (anche, windowing o windowed) eseguono un calcolo su un insieme di righe. Mi piace pensare a” guardare attraverso la finestra ” alle righe che vengono restituite e avere un’ultima possibilità di eseguire un calcolo. La finestra è definita dalla clausola OVER che determina se le righe sono partizionate in set più piccoli e se sono ordinate. Infatti, se si utilizza una funzione window si utilizzerà sempre una clausola OVER. La clausola OVER fa anche parte del VALORE SUCCESSIVO PER la sintassi richiesta per l’oggetto sequence, ma altrimenti viene utilizzata con le funzioni window.,

La clausola OVER può contenere una PARTIZIONE PER opzione. Questo rompe le righe in set più piccoli. Potresti pensare che questo sia lo stesso di GROUP BY, ma non lo è. Durante il raggruppamento, viene restituita una riga per gruppo univoco. Quando si utilizza PARTITION BY, tutte le righe di dettaglio vengono restituite insieme ai calcoli. Se nella tua casa hai una finestra divisa in riquadri, ogni riquadro è una finestra. Quando si pensa alle funzioni della finestra, l’intero set di risultati è una partizione, ma quando si utilizza PARTITION BY, ogni partizione può anche essere considerata una finestra., PARTITION BY è supportato-e opzionale-per tutte le funzioni di windowing.

La clausola OVER può anche contenere un’opzione ORDER BY. Questo è indipendente dalla clausola ORDER BY della query. Alcune delle funzioni richiedono ORDER BY e non sono supportate dalle altre. Quando l’ordine delle righe è importante quando si applica il calcolo, è richiesto l’ORDINE DA.

Le funzioni della finestra possono essere utilizzate solo nelle clausole SELECT e ORDER BY di una query. Vengono applicati dopo qualsiasi unione, filtraggio o raggruppamento.,

Funzioni di classificazione

Le funzioni della finestra più comunemente utilizzate, le funzioni di classificazione, sono disponibili dal 2005. Questo è quando Microsoft ha introdotto ROW_NUMBER, RANK, DENSE_RANK e NTILE. ROW_NUMBER è usato molto frequentemente, per aggiungere numeri di riga univoci a una partizione o all’intero set di risultati. L’aggiunta di un numero di riga, o una delle altre funzioni di classifica, di solito non è l’obiettivo, ma è un passo lungo la strada verso la soluzione.

ORDER BY è richiesto nella clausola OVER quando si utilizzano ROW_NUMBER e le altre funzioni in questo gruppo., Questo indica al motore di database l’ordine in cui devono essere applicati i numeri. Se i valori delle colonne o delle espressioni utilizzate in ORDER BY non sono univoci, RANK e DENSE_RANK si occuperanno dei legami, mentre ROW_NUMBER non si preoccupa dei legami. NTILE viene utilizzato per dividere le righe in secchi in base all’ordine DA.

Un vantaggio di ROW_NUMBER è la possibilità di trasformare righe non univoche in righe univoche. Questo potrebbe essere usato per eliminare le righe duplicate, ad esempio.

Per mostrare come funziona, inizia con una tabella temporanea contenente righe duplicate., Il primo passo è creare la tabella e popolarla.

L’aggiunta del NUMERO di RIGA e del partizionamento per ogni colonna riavvierà i numeri di riga per ogni set univoco di righe. È possibile identificare le righe univoche trovando quelle con un numero di riga uguale a uno.,

Ora, tutto quello che dovete fare è quello di eliminare tutte le righe che hanno un numero di righe maggiore di uno., Il problema è che non è possibile aggiungere funzioni di finestra alla clausola WHERE.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., È quindi possibile eliminare le righe direttamente dal CTE.

Successo! Le righe aggiuntive sono state eliminate e rimane un insieme univoco di righe.

Per vedere la differenza tra ROW_NUMBER, RANK e DENSE_RANK, esegui questa query:

L’ORDINE PER ogni clausola OVER è OrderDate che non è univoco. Questo cliente ha effettuato due ordini su 2013-10-24. ROW_NUMBER ha continuato ad assegnare numeri e non ha fatto nulla di diverso anche se c’è una data duplicata., CLASSIFICA assegnato 6 a entrambe le righe e poi raggiunto ROW_NUMBER con un 8 sulla riga successiva. DENSE_RANK ha anche assegnato 6 alle due righe ma assegnato 7 alla riga successiva.

Due spiegano la differenza, pensa a ROW_NUMBER come posizionale. Il RANGO è sia posizionale che logico. Queste due righe sono classificate logicamente uguali, ma la riga successiva è classificata in base alla posizione nel set. DENSE_RANK li classifica logicamente. Ordine 2013-11-04 è il 7 ° data unica.

La funzione finale in questo gruppo è chiamata NTILE. Assegna numeri bucket alle righe invece di numeri di riga o ranghi., Ecco un esempio:

NTILE ha un parametro, in questo caso 4, che è il numero di bucket che vuoi vedere nei risultati. L’ORDINE DA viene applicato alla somma delle vendite. Alle righe con il 25% più basso viene assegnato 1, alle righe con il 25% più alto viene assegnato 4. Infine, i risultati di NTILE vengono moltiplicati per 1000 per ottenere l’importo del bonus. Poiché 14 non può essere diviso equamente per 4, una riga in più va in ciascuno dei primi due bucket.

Aggregati di finestre

Gli aggregati di finestre sono stati introdotti anche con SQL Server 2005., Questi rendono facile scrivere alcune query difficili, ma spesso si comportano peggio delle tecniche più vecchie. Consentono di aggiungere la funzione di aggregazione preferita a una query non aggregata. Ad esempio, si desidera visualizzare tutti gli ordini del cliente insieme al totale parziale per ciascun cliente., Aggiungendo una SOMMA utilizzando la clausola OVER, è possibile farlo molto facilmente:

Aggiungendo la PARTIZIONE BY, viene calcolato un totale parziale per ogni cliente. Qualsiasi funzione aggregata può essere utilizzata e ORDER BY nella clausola OVER non è supportata.

Miglioramenti dell’aggregato di finestre nel 2012

A partire dal 2012, è possibile aggiungere un ORDER BY alla clausola OVER agli aggregati di finestre per produrre totali correnti e medie mobili, ad esempio. Allo stesso tempo, Microsoft ha introdotto il concetto di framing. Aggiungere una PARTIZIONE è come dividere una finestra in riquadri., Aggiungere inquadrature è come creare una vetrata. Ogni riga ha una singola finestra in cui verrà applicata l’espressione.

Con questo miglioramento, è possibile creare totali correnti anche senza aggiungere la sintassi framing., Here is an example that returns a running total by customer:

Il frame predefinito, che viene utilizzato se non viene specificato un frame, è COMPRESO TRA LA RIGA PRECEDENTE E QUELLA CORRENTE ILLIMITATA. Sfortunatamente, questo non funzionerà bene come se si specifica invece questo frame: RIGHE TRA LA RIGA PRECEDENTE E QUELLA CORRENTE ILLIMITATA. La differenza è la parola RIGHE. L’INTERVALLO è implementato solo parzialmente in questo momento ed è pensato per lavorare con periodi di tempo, mentre le RIGHE sono posizionali., Il frame, RIGHE TRA RIGA PRECEDENTE E RIGA CORRENTE ILLIMITATA, significa che la finestra è composta dalla prima riga della partizione e da tutte le righe fino alla riga corrente. Ogni calcolo viene eseguito su un diverso insieme di righe. Ad esempio, quando si esegue il calcolo per la riga 4, vengono utilizzate le righe da 1 a 4.

Quando si esegue il calcolo per la riga 5, le righe vanno da 1 a 5. La finestra si ingrandisce man mano che si passa da una riga all’altra.,

È anche possibile utilizzare le RIGHE di sintassi TRA N PRECEDENTE E RIGA CORRENTE o RIGHE TRA RIGA CORRENTE E N SUCCESSIVO. Questo potrebbe essere utile per il calcolo di una media mobile di tre mesi, per esempio. La figura seguente rappresenta LE RIGHE TRA 2 RIGA PRECEDENTE E CORRENTE.

Quando 5 è la riga corrente, la finestra si sposta; non cambia dimensione.,

Ecco l’elenco dei termini che devi sapere quando scrivi l’opzione framing:

Ammetto che questa sintassi è un po ‘ confusa, ma l’uso del prompt SQL aiuta a semplificare la scrittura dell’opzione framing!

Funzioni di offset

Anche incluso con il rilascio di SQL Server 2012 sono quattro funzioni che consentono di includere valori da altre righe – senza fare un self-join. Microsoft chiama queste “funzioni analitiche”, ma mi riferisco sempre a loro come “funzioni di offset” quando si presenta su questo argomento., Due delle funzioni consentono di estrarre colonne o espressioni da una riga prima (LAG) o dopo (LEAD) la riga corrente. Le altre due funzioni consentono di restituire valori dalla prima riga della partizione (FIRST_VALUE) o dall’ultima riga della partizione (LAST_VALUE). FIRST_VALUE e LAST_VALUE richiedono anche il framing, quindi assicurati di includere il frame quando usi queste funzioni. Tutte e quattro le funzioni richiedono l’opzione ORDER BY della clausola OVER. Ciò ha senso, perché il motore di database deve conoscere l’ordine delle righe per capire quale riga contiene il valore da restituire.,

Alcune persone hanno una band preferita; alcune persone hanno un film preferito. Ho una funzione preferita-LAG. È facile da usare (senza cornice!) e si comporta alla grande., Here is an example:

LAG e LEAD richiedono un argomento: la colonna o l’espressione che si desidera restituire. Per impostazione predefinita, LAG restituisce il valore dalla riga precedente e LEAD restituisce il valore dalla riga successiva. È possibile modificarlo fornendo un valore per il parametro OFFSET, che è 1 per impostazione predefinita. Si noti che la prima riga della partizione restituisce NULL. Se si desidera sovrascrivere i valori NULL, è possibile fornire un valore PREDEFINITO., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE e LAST_VALUE possono essere utilizzati per trovare un valore dalla prima riga o dall’ultima riga della partizione. Assicurati di specificare il frame, non solo per motivi di prestazioni, ma perché il frame predefinito non funziona come ci si aspetterebbe con LAST_VALUE. Il frame predefinito, COMPRESO TRA RIGA PRECEDENTE E RIGA CORRENTE ILLIMITATA, sale solo alla riga corrente. L’ultima riga della partizione non è inclusa., Per ottenere i risultati attesi, assicurarsi di specificare LE RIGHE TRA LA RIGA CORRENTE E IL SEGUITO ILLIMITATO quando si utilizza LAST_VALUE., Ecco un esempio di utilizzo FIRST_VALUE:

Funzioni Statistiche

Microsoft gruppi di queste quattro funzioni – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – insieme con l’offset funzioni di chiamata di tutte e otto le funzioni analitiche. Dato che mi piace distinguerli dalle funzioni di offset, li chiamo statistici.

PERCENT_RANK e CUME_DIST forniscono una classifica per ogni riga su una partizione. Differiscono leggermente. PERCENT_RANK restituisce la percentuale di righe di rango inferiore alla riga corrente., “Il mio punteggio è superiore al 90% dei punteggi.”CUME_DIST, o distribuzione cumulativa, restituisce il rango esatto. “Il mio punteggio è al 90% dei punteggi.”Ecco un esempio utilizzando la temperatura media elevata a St. Louis per ogni mese. Si noti che i ranghi sono stati determinati dalla temperatura Fahrenheit.

I ranghi non sono determinati dai valori relativi, ma dalle posizioni delle righe. Si noti che marzo e novembre hanno la stessa temperatura media elevata, quindi sono stati classificati allo stesso modo.

Potresti chiederti come calcolare PERCENT_RANK e CUME_DIST., Qui ci sono le formule:

PERCENTILE_DISC e PERCENTILE_CONT lavoro in modo opposto. Dato un rango percentuale, trova il valore in quel rango., Differiscono in quanto PERCENTILE_DISC restituirà un valore esistente nel set mentre PERCENTILE_CONT calcolerà un valore esatto se nessuno dei valori nel set cade esattamente in quel rango. È possibile utilizzare PERCENTILE_CONT per calcolare una mediana fornendo 0,5 come rango percentuale. Ad esempio, quale temperatura si colloca al 50% a St. Louis?

La funzione PERCENTILE_CONT prende la media dei due valori più vicini al centro, 67 e 69, e li calcola in media. PERCENTILE_DISC restituisce un valore esatto, 67., Si noti inoltre che queste due funzioni hanno una clausola extra non vista nelle altre funzioni, ALL’interno del GRUPPO, che contiene ORDER BY anziché all’interno della clausola OVER.

Sommario

Questo articolo è una panoramica molto rapida delle funzioni della finestra di T-SQL. Due tipi di funzioni sono state rilasciate con SQL Server 2005, le funzioni di classificazione e gli aggregati di finestre. Con 2012, hai migliorato l’aggregato di finestre con il framing e le funzioni analitiche. Mi piace separare le funzioni analitiche in due gruppi, l’offset e le funzioni statistiche., Le funzioni della finestra semplificano la scrittura di molte query e credo che questo sia il vantaggio principale. In alcuni casi, anche le query funzioneranno meglio, ma questa è una discussione per un altro giorno.

Spero che questo articolo ti abbia ispirato a saperne di più su queste fantastiche funzioni!