Questo articolo è la quarta puntata di una serie su T-SQL bugs, pitfalls and best practices. In precedenza ho parlato di determinismo, subquery e join. Il focus dell’articolo di questo mese sono i bug, le insidie e le migliori pratiche relative alle funzioni finestra. Grazie Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man e Paul White per aver offerto le vostre idee!
Nei miei esempi userò un database di esempio chiamato TSQLV5. Potete trovare lo script che crea e popola questo database qui, e il suo diagramma ER qui.
Ci sono due trabocchetti comuni che coinvolgono le funzioni finestra, entrambi i quali sono il risultato di default impliciti controintuitivi che sono imposti dallo standard SQL. Un’insidia ha a che fare con i calcoli dei totali in corso, dove si ottiene una finestra con l’opzione implicita RANGE. Un’altra insidia è in qualche modo collegata, ma ha conseguenze più gravi, coinvolgendo una definizione implicita di frame per le funzioni FIRST_VALUE e LAST_VALUE.
Window frame con opzione RANGE implicita
La nostra prima insidia coinvolge il calcolo dei totali in esecuzione usando una funzione di finestra aggregata, dove si specifica esplicitamente la clausola di ordine della finestra, ma non si specifica esplicitamente l’unità del frame della finestra (ROWS o RANGE) e la sua relativa estensione, ad es, RIGHE SENZA LIMITI PRECEDENTI. L’impostazione predefinita implicita è controintuitiva e le sue conseguenze potrebbero essere sorprendenti e dolorose.
Per dimostrare questa insidia, userò una tabella chiamata Transactions che contiene due milioni di transazioni di conto bancario con crediti (valori positivi) e debiti (valori negativi). Eseguite il seguente codice per creare la tabella Transazioni e popolarla con dati di esempio:
SET NOCOUNT ON; USE TSQLV5; -- http://tsql.solidq.com/SampleDatabases/TSQLV5.zip DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) -- creates POC index ); DECLARE @num_partitions AS INT = 100, @rows_per_partition AS INT = 20000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
La nostra trappola ha sia un lato logico con un potenziale bug logico che un lato di performance con una penalità di performance. La penalizzazione delle prestazioni è rilevante solo quando la funzione finestra è ottimizzata con operatori di elaborazione in modalità riga. SQL Server 2016 introduce l’operatore Window Aggregate in modalità batch, che rimuove la parte di penalizzazione delle prestazioni del trabocchetto, ma prima di SQL Server 2019 questo operatore viene utilizzato solo se si dispone di un indice columnstore presente sui dati. SQL Server 2019 introduce la modalità batch sul supporto rowstore, quindi è possibile ottenere l’elaborazione in modalità batch anche se non sono presenti indici columnstore sui dati. Per dimostrare la penalizzazione delle prestazioni con l’elaborazione in modalità riga, se stai eseguendo gli esempi di codice in questo articolo su SQL Server 2019 o successivo, o su Azure SQL Database, utilizza il seguente codice per impostare il livello di compatibilità del database su 140 in modo da non abilitare ancora la modalità batch su row store:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
Utilizza il seguente codice per attivare le statistiche di tempo e I/O nella sessione:
SET STATISTICS TIME, IO ON;
Per evitare di aspettare la stampa di due milioni di righe in SSMS, suggerisco di eseguire gli esempi di codice in questa sezione con l’opzione Scarta risultati dopo l’esecuzione attivata (andare in Opzioni query, Risultati, Griglia, e selezionare Scarta risultati dopo l’esecuzione).
Prima di arrivare al trabocchetto, considerate la seguente query (chiamatela Query 1) che calcola il saldo del conto bancario dopo ogni transazione applicando un totale corrente usando una funzione aggregata a finestra con una specifica esplicita del frame:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Il piano per questa query, usando l’elaborazione in modalità riga, è mostrato nella Figura 1.
Figura 1: Piano per la query 1, elaborazione in modalità riga
Il piano estrae i dati preordinati dall’indice clustered della tabella. Poi usa gli operatori Segment e Sequence Project per calcolare i numeri di riga per capire quali righe appartengono al frame della riga corrente. Poi usa gli operatori Segment, Window Spool e Stream Aggregate per calcolare la funzione window aggregate. L’operatore Window Spool è usato per fare lo spool delle righe del frame che poi devono essere aggregate. Senza alcuna ottimizzazione speciale, il piano avrebbe dovuto scrivere per riga tutte le sue righe di frame applicabili allo spool, e poi aggregarle. Questo avrebbe comportato una complessità quadratica, o N2. La buona notizia è che quando il frame inizia con UNBOUNDED PRECEDING, SQL Server identifica il caso come un caso fast track, in cui prende semplicemente il totale in esecuzione della riga precedente e aggiunge il valore della riga corrente per calcolare il totale in esecuzione della riga corrente, ottenendo una scalatura lineare. In questa modalità fast track, il piano scrive solo due righe nello spool per ogni riga di input – una con l’aggregato e una con il dettaglio.
Lo spool Window può essere fisicamente implementato in uno dei due modi. O come un veloce spool in-memoria che è stato appositamente progettato per le funzioni di finestra, o come un lento spool su disco, che è essenzialmente una tabella temporanea in tempdb. Se il numero di righe che devono essere scritte nello spool per ogni riga sottostante potrebbe superare i 10.000, o se SQL Server non può prevedere il numero, userà lo spool più lento su disco. Nel nostro piano di query, abbiamo esattamente due righe scritte nello spool per riga sottostante, quindi SQL Server usa lo spool in-memoria. Sfortunatamente, non c’è modo di dire dal piano che tipo di spool si sta ottenendo. Ci sono due modi per capirlo. Uno è usare un evento esteso chiamato window_spool_ondisk_warning. Un’altra opzione è attivare STATISTICS IO, e controllare il numero di letture logiche riportate per una tabella chiamata Worktable. Un numero maggiore di zero significa che hai ottenuto lo spool on-disk. Zero significa che avete lo spool in-memoria. Ecco le statistiche I/O per la nostra query:
Come potete vedere, abbiamo utilizzato lo spool in-memory. Questo è generalmente il caso quando si usa l’unità di frame della finestra ROWS con UNBOUNDED PRECEDING come primo delimitatore.
Ecco le statistiche di tempo per la nostra query:
Ci sono voluti circa 4,5 secondi per completare questa query sulla mia macchina con i risultati scartati.
Ora il trucco. Se usate l’opzione RANGE invece di ROWS, con gli stessi delimitatori, ci può essere una sottile differenza di significato, ma una grande differenza di prestazioni in modalità riga. La differenza di significato è rilevante solo se non avete un ordinamento totale, cioè se state ordinando per qualcosa che non è unico. L’opzione ROWS UNBOUNDED PRECEDING si ferma alla riga corrente, quindi in caso di pareggio, il calcolo non è deterministico. Al contrario, l’opzione RANGE UNBOUNDED PRECEDING guarda avanti alla riga corrente, e include i legami se presenti. Usa una logica simile all’opzione TOP WITH TIES. Quando si ha un ordinamento totale, cioè si ordina per qualcosa di unico, non ci sono legami da includere, e quindi ROWS e RANGE diventano logicamente equivalenti in tal caso. Il problema è che quando si usa RANGE, SQL Server usa sempre lo spool su disco in modalità di elaborazione delle righe, poiché quando elabora una data riga non può prevedere quante altre righe saranno incluse. Questo può avere una grave penalizzazione delle prestazioni.
Considera la seguente query (chiamala Query 2), che è la stessa della Query 1, solo che usa l’opzione RANGE invece di ROWS:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Il piano per questa query è mostrato nella Figura 2.
Figura 2: Piano per la query 2, elaborazione in modalità riga
La query 2 è logicamente equivalente alla query 1 perché abbiamo un ordine totale; tuttavia, poiché usa RANGE, viene ottimizzata con lo spool su disco. Si osservi che nel piano per la Query 2 lo spool a finestra ha lo stesso aspetto del piano per la Query 1, e i costi stimati sono gli stessi.
Ecco le statistiche di tempo e I/O per l’esecuzione della Query 2:
Lettura logica della tabella ‘Worktable’: 12044701. Tabella ‘Transazioni’ letture logiche: 6208.
Nota il gran numero di letture logiche contro Worktable, che indica che hai ottenuto lo spool su disco. Il tempo di esecuzione è più di quattro volte più lungo rispetto alla Query 1.
Se state pensando che se questo è il caso, eviterete semplicemente di usare l’opzione RANGE, a meno che non abbiate davvero bisogno di includere dei legami, è una buona idea. Il problema è che se usate una funzione finestra che supporta un frame (aggregati, FIRST_VALUE, LAST_VALUE) con una clausola esplicita di ordine della finestra, ma nessuna menzione dell’unità del frame della finestra e della sua estensione associata, state ottenendo RANGE UNBOUNDED PRECEDING per default. Questo default è dettato dallo standard SQL, e lo standard lo ha scelto perché generalmente preferisce opzioni più deterministiche come default. La seguente query (chiamatela Query 3) è un esempio che cade in questa trappola:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Spesso la gente scrive così supponendo di ottenere ROWS UNBOUNDED PRECEDING per default, senza rendersi conto che in realtà sta ottenendo RANGE UNBOUNDED PRECEDING. Il fatto è che poiché la funzione usa l’ordine totale, si ottiene lo stesso risultato come con ROWS, quindi non si può dire che ci sia un problema dal risultato. Ma i numeri delle prestazioni che otterrete sono come per la Query 2. Vedo persone che cadono sempre in questa trappola.
La pratica migliore per evitare questo problema è nei casi in cui si usa una funzione finestra con un frame, essere espliciti sull’unità del frame della finestra e sulla sua estensione, e generalmente preferire ROWS. Riservate l’uso di RANGE solo ai casi in cui l’ordinamento non è unico e avete bisogno di includere legami.
Considerate la seguente query che illustra un caso in cui c’è una differenza concettuale tra ROWS e RANGE:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS sumrows, SUM(val) OVER( ORDER BY orderdate RANGE UNBOUNDED PRECEDING ) AS sumrange FROM Sales.OrderValues ORDER BY orderdate;
Questa query genera il seguente output:
orderdate orderid val sumrows sumrange ---------- -------- -------- -------- --------- 2017-07-04 10248 440.00 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2303.40 2017-07-08 10250 1552.60 3856.00 4510.06 2017-07-08 10251 654.06 4510.06 4510.06 2017-07-09 10252 3597.90 8107.96 8107.96 ...
Osserva la differenza nei risultati per le righe in cui la stessa data d’ordine appare più di una volta, come nel caso dell’8 luglio 2017. Notate come l’opzione ROWS non include i vincoli e quindi è non deterministica, e come l’opzione RANGE include i vincoli, e quindi è sempre deterministica.
E’ discutibile però se in pratica avete casi in cui ordinate per qualcosa che non è unico, e avete davvero bisogno di includere i vincoli per rendere il calcolo deterministico. Ciò che è probabilmente molto più comune nella pratica è fare una delle due cose. Una è quella di rompere i legami aggiungendo qualcosa all’ordinamento della finestra per renderlo unico e in questo modo risultare in un calcolo deterministico, così:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
Questa query genera il seguente output:
orderdate orderid val runningsum ---------- -------- --------- ----------- 2017-07-04 10248 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2017-07-08 10250 1552.60 3856.00 2017-07-08 10251 654.06 4510.06 2017-07-09 10252 3597.90 8107.96 ...
Un’altra opzione è quella di applicare un raggruppamento preliminare, nel nostro caso, per data dell’ordine, così:
SELECT orderdate, SUM(val) AS daytotal, SUM(SUM(val)) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues GROUP BY orderdate ORDER BY orderdate;
Questa query genera il seguente output dove ogni data d’ordine appare solo una volta:
orderdate daytotal runningsum ---------- --------- ----------- 2017-07-04 440.00 440.00 2017-07-05 1863.40 2303.40 2017-07-08 2206.66 4510.06 2017-07-09 3597.90 8107.96 ...
In ogni caso, assicurati di ricordare la best practice qui!
La buona notizia è che se state girando su SQL Server 2016 o successivo e avete un indice columnstore presente sui dati (anche se è un falso indice columnstore filtrato), o se state girando su SQL Server 2019 o successivo, o su Azure SQL Database, indipendentemente dalla presenza di indici columnstore, tutte e tre le query di cui sopra vengono ottimizzate con l’operatore Window Aggregate in modalità batch. Con questo operatore, molte delle inefficienze dell’elaborazione in modalità riga vengono eliminate. Questo operatore non usa affatto uno spool, quindi non c’è nessun problema di spool in memoria o su disco. Utilizza un’elaborazione più sofisticata dove può applicare più passaggi paralleli sulla finestra di righe in memoria sia per ROWS che per RANGE.
Per dimostrare l’utilizzo dell’ottimizzazione in modalità batch, assicuratevi che il livello di compatibilità del vostro database sia impostato a 150 o superiore:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
Eseguite nuovamente la query 1:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Il piano per questa query è mostrato nella figura 3.
Figura 3: Piano per la query 1, elaborazione in modalità batch
Ecco le statistiche sulle prestazioni che ho ottenuto per questa query:
Lettura logica della tabella ‘Transazioni’: 6208.
Il tempo di esecuzione è sceso a 1 secondo!
Esegui di nuovo la Query 2 con l’opzione RANGE esplicita:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
Il piano per questa query è mostrato nella Figura 4.
Figura 2: Piano per la query 2, elaborazione in modalità batch
Ecco le statistiche sulle prestazioni che ho ottenuto per questa query:
Tabella ‘Transazioni’ letture logiche: 6208.
Le prestazioni sono le stesse della Query 1.
Esegui di nuovo la Query 3, con l’opzione RANGE implicita:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Il piano e i numeri delle prestazioni sono ovviamente gli stessi della Query 2.
Quando hai finito, esegui il seguente codice per disattivare le statistiche sulle prestazioni:
SET STATISTICS TIME, IO OFF;
Non dimenticare di disattivare l’opzione Discard results after execution in SSMS.
Cornice implicito con FIRST_VALUE e LAST_VALUE
Le funzioni FIRST_VALUE e LAST_VALUE sono funzioni di finestra offset che restituiscono un’espressione rispettivamente dalla prima o dall’ultima riga del frame della finestra. La parte difficile di queste funzioni è che spesso, quando le persone le usano per la prima volta, non si rendono conto che supportano un frame, piuttosto pensano che si applichino all’intera partizione.
Considera il seguente tentativo di restituire le informazioni sull’ordine, più i valori del primo e dell’ultimo ordine del cliente:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Se credete erroneamente che queste funzioni operino sull’intera partizione della finestra, che è la convinzione di molte persone che usano queste funzioni per la prima volta, vi aspettate naturalmente che FIRST_VALUE restituisca il valore del primo ordine del cliente, e LAST_VALUE restituisca il valore dell’ultimo ordine del cliente. In pratica, però, queste funzioni supportano un frame. Come promemoria, con le funzioni che supportano un frame, quando specificate la clausola di ordine della finestra ma non l’unità del frame della finestra e la sua estensione associata, otterrete RANGE UNBOUNDED PRECEDING per default. Con la funzione FIRST_VALUE, otterrete il risultato atteso, ma se la vostra query viene ottimizzata con operatori in modalità riga, pagherete la penalità di usare lo spool su disco. Con la funzione LAST_VALUE è ancora peggio. Non solo pagherete la penalità dello spool su disco, ma invece di ottenere il valore dall’ultima riga della partizione, otterrete il valore dalla riga corrente!
Ecco l’output della query di cui sopra:
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 814.50 1 2018-10-03 10692 878.00 814.50 878.00 1 2018-10-13 10702 330.00 814.50 330.00 1 2019-01-15 10835 845.80 814.50 845.80 1 2019-03-16 10952 471.20 814.50 471.20 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 88.80 2 2018-08-08 10625 479.75 88.80 479.75 2 2018-11-28 10759 320.00 88.80 320.00 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 403.20 3 2018-04-15 10507 749.06 403.20 749.06 3 2018-05-13 10535 1940.85 403.20 1940.85 3 2018-06-19 10573 2082.00 403.20 2082.00 3 2018-09-22 10677 813.37 403.20 813.37 3 2018-09-25 10682 375.50 403.20 375.50 3 2019-01-28 10856 660.00 403.20 660.00 ...
Spesso quando la gente vede un tale output per la prima volta, pensa che SQL Server abbia un bug. Ma naturalmente non è così; è semplicemente il default dello standard SQL. C’è un bug nella query. Rendendosi conto che c’è un frame coinvolto, si vuole essere espliciti sulle specifiche del frame, e usare il frame minimo che cattura la riga che si sta cercando. Inoltre, assicurati di usare l’unità ROWS. Quindi, per ottenere la prima riga nella partizione, usate la funzione FIRST_VALUE con il frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Per ottenere l’ultima riga della partizione, usate la funzione LAST_VALUE con il quadro ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
Ecco la nostra query rivista con il bug corretto:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Questa volta si ottiene il risultato corretto:
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 933.50 1 2018-10-03 10692 878.00 814.50 933.50 1 2018-10-13 10702 330.00 814.50 933.50 1 2019-01-15 10835 845.80 814.50 933.50 1 2019-03-16 10952 471.20 814.50 933.50 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 514.40 2 2018-08-08 10625 479.75 88.80 514.40 2 2018-11-28 10759 320.00 88.80 514.40 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 660.00 3 2018-04-15 10507 749.06 403.20 660.00 3 2018-05-13 10535 1940.85 403.20 660.00 3 2018-06-19 10573 2082.00 403.20 660.00 3 2018-09-22 10677 813.37 403.20 660.00 3 2018-09-25 10682 375.50 403.20 660.00 3 2019-01-28 10856 660.00 403.20 660.00 ...
Ci si chiede quale sia stata la motivazione dello standard per supportare addirittura un frame con queste funzioni. Se ci pensate, le userete per lo più per ottenere qualcosa dalla prima o dall’ultima riga della partizione. Se avete bisogno del valore da, diciamo, due righe prima della corrente, invece di usare FIRST_VALUE con una cornice che inizia con 2 PRECEDING, non è molto più semplice usare LAG con un offset esplicito di 2, così:
SELECT custid, orderdate, orderid, val, LAG(val, 2) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Questa query genera il seguente output:
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 NULL 1 2018-10-03 10692 878.00 NULL 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 NULL 2 2018-08-08 10625 479.75 NULL 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 NULL 3 2018-04-15 10507 749.06 NULL 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
Apparentemente, c’è una differenza semantica tra l’uso sopra della funzione LAG e FIRST_VALUE con un frame che inizia con 2 PRECEDENTI. Con la prima, se una riga non esiste nell’offset desiderato, si ottiene di default un NULL. Con la seconda, si ottiene comunque il valore della prima riga presente, cioè il valore della prima riga della partizione. Considerate la seguente query:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Questa query genera il seguente output:
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 814.50 1 2018-10-03 10692 878.00 814.50 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 88.80 2 2018-08-08 10625 479.75 88.80 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 403.20 3 2018-04-15 10507 749.06 403.20 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
Osservate che questa volta non ci sono NULL nell’output. Quindi c’è un certo valore nel supportare un frame con FIRST_VALUE e LAST_VALUE. Assicuratevi solo di ricordare la migliore pratica di essere sempre espliciti riguardo alla specifica del frame con queste funzioni, e di usare l’opzione ROWS con il frame minimo che contiene la riga che state cercando.
Conclusione
Questo articolo si è concentrato su bug, insidie e migliori pratiche relative alle funzioni finestra. Ricordate che entrambe le funzioni di aggregazione delle finestre e le funzioni di offset delle finestre FIRST_VALUE e LAST_VALUE supportano un frame, e che se specificate la clausola di ordine della finestra ma non specificate l’unità del frame della finestra e la sua estensione associata, otterrete RANGE UNBOUNDED PRECEDING per default. Questo comporta una penalizzazione delle prestazioni quando la query viene ottimizzata con operatori row-mode. Con la funzione LAST_VALUE questo risulta nell’ottenere i valori dalla riga corrente invece che dall’ultima riga della partizione. Ricordatevi di essere espliciti riguardo alla struttura e di preferire generalmente l’opzione ROWS a RANGE. È bello vedere i miglioramenti delle prestazioni con l’operatore Window Aggregate in modalità batch. Quando è applicabile, almeno il trabocchetto delle prestazioni viene eliminato.