I filtri classici in SQL come ON, WHERE, e HAVING sono basati su predicati. TOP e OFFSET-FETCH sono filtri che si basano su un concetto diverso: si indica l’ordine e quante righe filtrare in base a tale ordine. Molti compiti di filtraggio sono definiti in base all’ordine e a un numero richiesto di righe. È certamente un bene avere un supporto linguistico in T-SQL che permette di formulare la richiesta in un modo che è simile al modo in cui si pensa al compito.
Questo capitolo inizia con gli aspetti di progettazione logica dei filtri. Poi utilizza uno scenario di paginazione per dimostrare la loro ottimizzazione. Il capitolo copre anche l’uso di TOP con dichiarazioni di modifica. Infine, il capitolo dimostra l’uso di TOP e OFFSET-FETCH nella risoluzione di problemi pratici come top N per gruppo e mediana.
I filtri TOP e OFFSET-FETCH
Si usano i filtri TOP e OFFSET-FETCH per implementare i requisiti di filtraggio nelle query in modo intuitivo. Il filtro TOP è una caratteristica proprietaria di T-SQL, mentre il filtro OFFSET-FETCH è una caratteristica standard. T-SQL ha iniziato a supportare OFFSET-FETCH con Microsoft SQL Server 2012. A partire da SQL Server 2014, l’implementazione di OFFSET-FETCH in T-SQL manca ancora di un paio di elementi standard – cosa interessante, quelli che sono disponibili con TOP. Con l’attuale implementazione, ognuno dei filtri ha capacità che non sono supportate dall’altro.
Inizierò descrivendo gli aspetti logici del design di TOP e poi coprirò quelli di OFFSET-FETCH.
Il filtro TOP
Il filtro TOP è un costrutto comunemente usato in T-SQL. La sua popolarità probabilmente può essere attribuita al fatto che il suo design è così ben allineato con il modo in cui molti requisiti di filtraggio sono espressi – per esempio, “Restituisci i tre ordini più recenti”. In questa richiesta, l’ordine per il filtro è basato su orderdate, discendente, e il numero di righe che volete filtrare in base a questo ordine è 3.
Si specifica l’opzione TOP nella lista SELECT con un valore di input digitato come BIGINT che indica quante righe volete filtrare. Si fornisce la specificazione dell’ordine nella classica clausola ORDER BY. Per esempio, usi la seguente query per ottenere i tre ordini più recenti.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Ho ottenuto il seguente output da questa query:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Invece di specificare il numero di righe che vuoi filtrare, puoi usare TOP per specificare la percentuale (del numero totale di righe nel risultato della query). Lo si fa fornendo un valore nell’intervallo da 0 a 100 (digitato come FLOAT) e aggiungendo la parola chiave PERCENT. Per esempio, nella seguente query si richiede di filtrare l’uno per cento delle righe:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server arrotonda il numero di righe calcolate in base alla percentuale inserita. Per esempio, il risultato dell’1% applicato a 830 righe nella tabella Ordini è 8,3. Arrotondando questo numero, si ottiene 9. Ecco l’output che ho ottenuto per questa query:
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
Nota che per tradurre la percentuale di input in un numero di righe, SQL Server deve prima capire il numero di righe nel risultato della query, e questo di solito richiede lavoro extra.
Interessante, la specificazione dell’ordine è opzionale per il filtro TOP. Per esempio, considerate la seguente query:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Ho ottenuto il seguente risultato da questa query:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
La selezione di quali tre righe restituire non è deterministica. Questo significa che se si esegue di nuovo la query, senza che i dati sottostanti cambino, teoricamente si potrebbe ottenere un diverso insieme di tre righe. In pratica, la selezione delle righe dipenderà da condizioni fisiche come le scelte di ottimizzazione, le scelte del motore di archiviazione, il layout dei dati e altri fattori. Se si esegue effettivamente la query più volte, finché queste condizioni fisiche non cambiano, c’è una certa probabilità di continuare a ottenere gli stessi risultati. Ma è fondamentale capire il principio di “indipendenza fisica dei dati” dal modello relazionale, e ricordare che a livello logico non si ha la garanzia di risultati ripetibili. Senza la specificazione dell’ordine, dovreste considerare l’ordine come arbitrario, risultando in una selezione di righe non deterministica.
Anche quando fornite la specificazione dell’ordine, ciò non significa che la query sia deterministica. Per esempio, una precedente query TOP ha usato orderdate, DESC come specifica di ordinamento. La colonna orderdate non è unica, quindi la selezione tra le righe con la stessa data d’ordine non è deterministica. Quindi cosa si fa nei casi in cui si deve garantire il determinismo? Ci sono due opzioni: usare WITH TIES o l’ordinamento unico.
L’opzione WITH TIES fa sì che i legami siano inclusi nel risultato. Ecco come applicarla al nostro esempio:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Ecco il risultato che ho ottenuto da questa query:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
SQL Server filtra le tre righe con le date d’ordine più recenti, in più include tutte le altre righe che hanno la stessa data d’ordine dell’ultima riga. Come risultato, si possono ottenere più righe del numero specificato. In questa query, hai specificato che volevi filtrare tre righe ma hai finito per ottenerne quattro. Quello che è interessante notare qui è che la selezione delle righe è ora deterministica, ma l’ordine di presentazione tra le righe con la stessa data d’ordine non è deterministico.
Il secondo metodo per garantire un risultato deterministico è quello di rendere unica la specifica dell’ordine aggiungendo un tiebreaker. Per esempio, si potrebbe aggiungere orderid, DESC come tiebreaker nel nostro esempio. Questo significa che, in caso di parità nei valori delle date d’ordine, una riga con un valore ID d’ordine più alto è preferita a una riga con un valore più basso. Ecco la nostra query con il tiebreaker applicato:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Questa query genera il seguente output:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
L’uso dell’ordinamento unico rende deterministica sia la selezione delle righe che l’ordine di presentazione. L’insieme dei risultati e l’ordine di presentazione delle righe sono garantiti per essere ripetibili finché i dati sottostanti non cambiano.
Se avete un caso in cui avete bisogno di filtrare un certo numero di righe ma non vi interessa veramente l’ordine, potrebbe essere una buona idea specificare ORDER BY (SELECT NULL), così:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
In questo modo, fate sapere a tutti che la vostra scelta di ordine arbitrario è intenzionale, il che aiuta ad evitare confusione e dubbi.
Come promemoria di ciò che ho spiegato nel Capitolo 1, “Elaborazione logica delle query”, riguardo ai filtri TOP e OFFSET-FETCH, l’ordine di presentazione è garantito solo se la query esterna ha una clausola ORDER BY. Per esempio, nella seguente query di presentazione, l’ordine non è garantito:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Per fornire una garanzia di ordine di presentazione, devi specificare una clausola ORDER BY nella query esterna, così:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS DORDER BY orderdate DESC, orderid DESC;
Il filtro OFFSET-FETCH
Il filtro OFFSET-FETCH è una funzione standard progettata in modo simile a TOP ma con un elemento extra. Potete specificare quante righe volete saltare prima di specificare quante righe volete filtrare.
Come potreste aver indovinato, questa caratteristica può essere utile nell’implementazione di soluzioni di paginazione-cioè, restituendo un risultato all’utente un pezzo alla volta su richiesta quando l’intero set di risultati è troppo lungo per essere inserito in una schermata o pagina web.
Il filtro OFFSET-FETCH richiede l’esistenza di una clausola ORDER BY, ed è specificato subito dopo. Si inizia indicando quante righe saltare in una clausola OFFSET, seguita da quante righe filtrare in una clausola FETCH. Per esempio, in base all’ordine indicato, la seguente query salta le prime 50 righe e filtra le successive 25 righe:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
In altre parole, la query filtra le righe da 51 a 75. In termini di paginazione, assumendo una pagina di 25 righe, questa query restituisce la terza pagina.
Per permettere un linguaggio dichiarativo naturale, potete usare la parola chiave FIRST invece di NEXT se volete, anche se il significato è lo stesso. Usare FIRST potrebbe essere più intuitivo se non state saltando nessuna riga. Anche se non si vuole saltare nessuna riga, T-SQL rende comunque obbligatorio specificare la clausola OFFSET (con 0 ROWS) per evitare ambiguità di analisi. Allo stesso modo, invece di usare la forma plurale della parola chiave ROWS, si può usare la forma singolare ROW in entrambe le clausole OFFSET e FETCH. Questo è più naturale se avete bisogno di saltare o filtrare solo una riga.
Se siete curiosi di sapere qual è lo scopo della parola chiave ONLY, significa non includere legami. L’SQL standard definisce l’alternativa WITH TIES; tuttavia, il T-SQL non la supporta ancora. Allo stesso modo, SQL standard definisce l’opzione PERCENT, ma T-SQL non la supporta ancora. Queste due opzioni mancanti sono disponibili con il filtro TOP.
Come detto, il filtro OFFSET-FETCH richiede una clausola ORDER BY. Se vuoi usare un ordine arbitrario, come TOP senza una clausola ORDER BY, puoi usare il trucco con ORDER BY (SELECT NULL), così:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
La clausola FETCH è opzionale. Se volete saltare un certo numero di righe ma non limitare quante righe restituire, semplicemente non indicate una clausola FETCH. Per esempio, la seguente query salta 50 righe ma non limita il numero di righe restituite:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Per quanto riguarda l’ordine di presentazione, il comportamento è lo stesso del filtro TOP; cioè, anche con OFFSET-FETCH l’ordine di presentazione è garantito solo se la query più esterna ha una clausola ORDER BY.