Ten artykuł jest czwartą odsłoną serii o T-SQL bugs, pitfalls and best practices. Poprzednio omówiłem determinizm, podzapytania i złączenia. W tym miesiącu skupimy się na błędach, pułapkach i najlepszych praktykach związanych z funkcjami okna. Dziękuję Erlandowi Sommarskogowi, Aaronowi Bertrandowi, Alejandro Mesie, Umachandarowi Jayachandranowi (UC), Fabiano Neves Amorimowi, Milosowi Radivojevicowi, Simonowi Sabinowi, Adamowi Machanicowi, Thomasowi Grohserowi, Chan Ming Manowi i Paulowi White’owi za przedstawienie swoich pomysłów!

W moich przykładach użyję przykładowej bazy danych o nazwie TSQLV5. Skrypt tworzący i wypełniający tę bazę danych można znaleźć tutaj, a jej diagram ER tutaj.

Istnieją dwie powszechne pułapki związane z funkcjami okna, z których obie są wynikiem nieintuicyjnych domyślnych ustawień narzuconych przez standard SQL. Jedna pułapka ma związek z obliczeniami sum bieżących, gdzie otrzymujemy ramkę okna z niejawną opcją RANGE. Inna pułapka jest nieco powiązana, ale ma poważniejsze konsekwencje i dotyczy niejawnej definicji ramki dla funkcji FIRST_VALUE i LAST_VALUE.

Okno z niejawną opcją RANGE

Nasza pierwsza pułapka dotyczy obliczania sum bieżących przy użyciu zagregowanej funkcji okna, gdzie jawnie określamy klauzulę kolejności okna, ale nie określamy jawnie jednostki ramki okna (ROWS lub RANGE) i związanego z nią zakresu ramki okna, np, ROWS UNBOUNDED PRECEDING. Domyślna wartość domyślna jest sprzeczna z intuicją, a jej konsekwencje mogą być zaskakujące i bolesne.

Aby zademonstrować tę pułapkę, użyję tabeli Transakcje przechowującej dwa miliony transakcji na kontach bankowych z kredytami (wartości dodatnie) i debetami (wartości ujemne). Uruchom następujący kod, aby utworzyć tabelę Transakcje i wypełnić ją przykładowymi danymi:

 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;

Nasza pułapka ma zarówno logiczną stronę z potencjalnym błędem logicznym, jak i stronę wydajnościową z karą za wydajność. Kara wydajności ma znaczenie tylko wtedy, gdy funkcja okna jest zoptymalizowana za pomocą operatorów przetwarzania w trybie wierszowym. SQL Server 2016 wprowadza operator Window Aggregate w trybie wsadowym, który usuwa część pułapki związaną z karą wydajności, ale przed SQL Server 2019 ten operator jest używany tylko wtedy, gdy masz indeks columnstore obecny na danych. SQL Server 2019 wprowadza tryb wsadowy na wsparciu rowstore, więc możesz uzyskać przetwarzanie w trybie wsadowym, nawet jeśli nie ma indeksów columnstore obecnych na danych. Aby zademonstrować karę za wydajność przy przetwarzaniu w trybie wierszowym, jeśli uruchamiasz próbki kodu w tym artykule na SQL Server 2019 lub nowszym, lub na Azure SQL Database, użyj następującego kodu, aby ustawić poziom zgodności bazy danych na 140, aby nie włączyć jeszcze trybu wsadowego na row store:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;

Użyj następującego kodu, aby włączyć statystyki czasu i I/O w sesji:

 SET STATISTICS TIME, IO ON;

Aby uniknąć oczekiwania na wydrukowanie dwóch milionów wierszy w SSMS, proponuję uruchomić próbki kodu w tym rozdziale z włączoną opcją Discard results after execution (przejdź do Query Options, Results, Grid i zaznacz Discard results after execution).

Zanim przejdziemy do pułapki, rozważmy następujące zapytanie (nazwijmy je Kwerendą 1), które oblicza stan konta bankowego po każdej transakcji, stosując sumę bieżącą przy użyciu funkcji agregującej okna z jawną specyfikacją ramki:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Plan dla tego zapytania, wykorzystującego przetwarzanie w trybie wierszowym, przedstawiono na rysunku 1.

Rysunek 1: Plan dla zapytania 1, przetwarzanie w trybie wierszowym

Plan pobiera dane wstępnie uporządkowane z indeksu klastrowego tabeli. Następnie używa operatorów Segment i Sequence Project do obliczenia numerów wierszy, aby określić, które wiersze należą do bieżącej ramki wiersza. Następnie używa operatorów Segment, Window Spool oraz Stream Aggregate do obliczenia funkcji agregacji okien. Operator Window Spool jest używany do przesyłania wierszy ramek, które następnie muszą zostać zagregowane. Bez żadnej specjalnej optymalizacji, plan musiałby zapisywać wszystkie wiersze ramek na szpulę, a następnie je agregować. Spowodowałoby to czterokrotną lub N2 złożoność. Dobrą wiadomością jest to, że gdy ramka zaczyna się od UNBOUNDED PRECEDING, SQL Server identyfikuje przypadek jako przypadek przyspieszony, w którym po prostu bierze wartość bieżącą poprzedniego wiersza i dodaje wartość bieżącego wiersza, aby obliczyć bieżącą wartość bieżącą wiersza, co skutkuje skalowaniem liniowym. W tym trybie szybkiej ścieżki, plan zapisuje tylko dwa wiersze do szpuli na każdy wiersz wejściowy – jeden z agregatem, a drugi ze szczegółami.

Szpula okienkowa może być fizycznie zaimplementowana na jeden z dwóch sposobów. Albo jako szybka szpula w pamięci, która została zaprojektowana specjalnie dla funkcji okienkowych, albo jako wolna szpula na dysku, która jest zasadniczo tabelą tymczasową w tempdb. Jeśli liczba wierszy, które muszą być zapisane na szpulę na jeden wiersz bazowy może przekroczyć 10,000, lub jeśli SQL Server nie może przewidzieć tej liczby, użyje wolniejszej szpuli na dysku. W naszym planie zapytania mamy dokładnie dwa wiersze zapisane na szpulę na każdy wiersz bazowy, więc SQL Server używa szpuli in-memory. Niestety, nie ma sposobu, aby dowiedzieć się z planu, jaki rodzaj szpuli otrzymujemy. Istnieją dwa sposoby, aby się tego dowiedzieć. Jednym z nich jest użycie rozszerzonego zdarzenia o nazwie window_spool_ondisk_warning. Innym sposobem jest włączenie funkcji STATISTICS IO i sprawdzenie liczby logicznych odczytów zgłoszonych dla tabeli o nazwie Worktable. Liczba większa od zera oznacza, że dostałeś szpulę na dysku. Zero oznacza, że dostałeś szpulę in-memory. Oto statystyki I/O dla naszego zapytania:

Jak widać, mamy wykorzystaną szpulę in-memory. Tak jest na ogół, gdy używasz jednostki ramki okna ROWS z UNBOUNDED PRECEDING jako pierwszym ogranicznikiem.

Oto statystyki czasowe dla naszego zapytania:

Wykonanie tego zapytania na moim komputerze z odrzuconymi wynikami zajęło około 4,5 sekundy.

Teraz haczyk. Jeśli użyjesz opcji RANGE zamiast ROWS, z tymi samymi delimiterami, może wystąpić subtelna różnica w znaczeniu, ale duża różnica w wydajności w trybie wierszy. Różnica w znaczeniu jest istotna tylko wtedy, gdy nie masz całkowitego uporządkowania, tj. jeśli zamawiasz według czegoś, co nie jest unikalne. Opcja ROWS UNBOUNDED PRECEDING zatrzymuje się na bieżącym wierszu, więc w przypadku remisów, obliczenia są nondeterministyczne. Odwrotnie, opcja RANGE UNBOUNDED PRECEDING patrzy przed bieżącym wierszem i uwzględnia krawaty, jeśli są obecne. Używa ona podobnej logiki do opcji TOP WITH TIES. Jeśli masz całkowite uporządkowanie, tj. zamawiasz według czegoś unikalnego, nie ma więzów do uwzględnienia, a zatem ROWS i RANGE stają się logicznie równoważne w takim przypadku. Problem polega na tym, że kiedy używasz RANGE, SQL Server zawsze używa szpuli na dysku w trybie przetwarzania wierszy, ponieważ podczas przetwarzania danego wiersza nie może przewidzieć, ile jeszcze wierszy zostanie uwzględnionych. Może to mieć poważny wpływ na wydajność.

Rozważmy następujące zapytanie (nazwijmy je zapytaniem 2), które jest takie samo jak zapytanie 1, tylko z użyciem opcji RANGE zamiast ROWS:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Plan dla tego zapytania pokazano na rysunku 2.

Rysunek 2: Plan dla zapytania 2, przetwarzanie w trybie wierszowym

Zapytanie 2 jest logicznie równoważne zapytaniu 1, ponieważ mamy całkowite uporządkowanie; jednakże, ponieważ używa ono RANGE, zostaje zoptymalizowane przy użyciu szpuli na dysku. Zauważ, że w planie dla zapytania 2 szpula Window Spool wygląda tak samo jak w planie dla zapytania 1, a szacowane koszty są takie same.

Oto statystyki czasu i operacji wejścia/wyjścia dla wykonania zapytania 2:

Zauważ dużą liczbę odczytów logicznych względem tabeli 'Worktable’, wskazującą, że otrzymano szpulę na dysku. Czas wykonania jest ponad cztery razy dłuższy niż dla zapytania 1.

Jeśli myślisz, że jeśli tak jest, to po prostu unikniesz użycia opcji RANGE, chyba że naprawdę musisz uwzględnić krawaty, to jest to dobre myślenie. Problem polega na tym, że jeśli używasz funkcji okna, która obsługuje ramkę (agregaty, FIRST_VALUE, LAST_VALUE) z jawną klauzulą zamówienia okna, ale bez wzmianki o jednostce ramki okna i jej powiązanym zakresie, domyślnie otrzymujesz RANGE UNBOUNDED PRECEDING. Ta domyślna opcja jest podyktowana przez standard SQL, a standard wybrał ją, ponieważ ogólnie preferuje bardziej deterministyczne opcje jako domyślne. Poniższe zapytanie (nazwijmy je zapytaniem 3) jest przykładem, który wpada w tę pułapkę:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;

Często ludzie piszą w ten sposób zakładając, że domyślnie dostają ROWS UNBOUNDED PRECEDING, nie zdając sobie sprawy, że tak naprawdę dostają RANGE UNBOUNDED PRECEDING. Rzecz w tym, że ponieważ funkcja używa całkowitej kolejności, otrzymujesz taki sam wynik jak w przypadku ROWS, więc nie możesz powiedzieć, że istnieje problem z wynikiem. Ale numery wydajności, które otrzymasz są jak dla zapytania 2. Widzę ludzi wpadających w tę pułapkę cały czas.

Najlepszą praktyką, aby uniknąć tego problemu, jest w przypadkach, gdy używasz funkcji okna z ramką, bądź wyraźny o jednostce ramki okna i jej zakresie, i ogólnie preferuj ROWS. Zarezerwuj użycie RANGE tylko dla przypadków, w których zamawianie nie jest unikalne i musisz uwzględnić więzy.

Rozważmy następujące zapytanie ilustrujące przypadek, w którym istnieje różnica koncepcyjna między ROWS i 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;

To zapytanie generuje następujące dane wyjściowe:

 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 ...

Zwróć uwagę na różnicę w wynikach dla wierszy, w których ta sama data zamówienia pojawia się więcej niż raz, tak jak w przypadku 8 lipca 2017 roku. Zauważ, jak opcja ROWS nie zawiera krawatów, a zatem jest niedeterministyczna, a jak opcja RANGE zawiera krawaty, a zatem jest zawsze deterministyczna.

Wątpliwe jest jednak, czy w praktyce masz przypadki, w których zamawiasz przez coś, co nie jest unikalne, i naprawdę potrzebujesz włączenia krawatów, aby obliczenia były deterministyczne. To, co prawdopodobnie jest znacznie bardziej powszechne w praktyce, to robienie jednej z dwóch rzeczy. Jedną z nich jest zerwanie więzów poprzez dodanie czegoś do zamawiania okien, aby uczynić je unikalnymi i w ten sposób uzyskać deterministyczne obliczenia, jak na przykład:

 SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;

To zapytanie generuje następujące dane wyjściowe:

 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 ...

Inną opcją jest zastosowanie wstępnego grupowania, w naszym przypadku według daty zamówienia, jak poniżej:

 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;

Zapytanie to generuje następujące dane wyjściowe, gdzie każda data zamówienia pojawia się tylko raz:

 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 ...

W każdym razie, upewnij się, że pamiętasz o najlepszej praktyce!

Dobrą wiadomością jest to, że jeśli pracujesz na SQL Server 2016 lub nowszym i masz indeks columnstore obecny na danych (nawet jeśli jest to fałszywy filtrowany indeks columnstore), lub jeśli pracujesz na SQL Server 2019 lub nowszym, lub na Azure SQL Database, niezależnie od obecności indeksów columnstore, wszystkie trzy powyższe zapytania są optymalizowane za pomocą operatora Window Aggregate w trybie wsadowym. Operator ten eliminuje wiele nieefektywności związanych z przetwarzaniem w trybie wierszowym. Operator ten w ogóle nie korzysta ze szpuli, więc nie ma problemu między szpulą w pamięci a szpulą na dysku. Używa bardziej wyrafinowanego przetwarzania, w którym może zastosować wiele równoległych przejść przez okno wierszy w pamięci zarówno dla ROWS jak i RANGE.

Aby zademonstrować użycie optymalizacji trybu wsadowego, upewnij się, że poziom zgodności bazy danych jest ustawiony na 150 lub wyższy:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;

Wykonaj ponownie zapytanie 1:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Plan dla tego zapytania przedstawiono na rysunku 3.

Rysunek 3: Plan dla zapytania 1, przetwarzanie w trybie wsadowym

Tutaj znajdują się statystyki wydajności, które uzyskałem dla tego zapytania:

Czas wykonania spadł do 1 sekundy!

Wykonaj ponownie zapytanie 2 z opcją explicit RANGE:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Plan dla tego zapytania przedstawiono na rysunku 4.

Rysunek 2: Plan dla zapytania 2, przetwarzanie w trybie wsadowym

Tutaj znajdują się statystyki wydajności, które uzyskałem dla tego zapytania:

Wydajność jest taka sama jak w przypadku zapytania 1.

Przeprowadź ponownie zapytanie 3, z opcją implicit RANGE:

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;

Plan i liczby wydajności są oczywiście takie same jak w przypadku zapytania 2.

Kiedy skończysz, uruchom następujący kod, aby wyłączyć statystyki wydajności:

 SET STATISTICS TIME, IO OFF;

Nie zapomnij również wyłączyć opcji Discard results after execution w SSMS.

Implicit frame z FIRST_VALUE i LAST_VALUE

Funkcje FIRST_VALUE i LAST_VALUE są funkcjami okna offsetowego, które zwracają wyrażenie odpowiednio z pierwszego lub ostatniego wiersza w ramce okna. Zdradliwą częścią jest to, że często, gdy ludzie używają ich po raz pierwszy, nie zdają sobie sprawy, że obsługują one ramkę, a raczej myślą, że odnoszą się do całej partycji.

Rozważmy następującą próbę zwrócenia informacji o zamówieniu, plus wartości pierwszego i ostatniego zamówienia klienta:

 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;

Jeśli błędnie sądzisz, że te funkcje działają na całej partycji okna, co jest przekonaniem wielu osób, które używają tych funkcji po raz pierwszy, to naturalnie oczekujesz, że FIRST_VALUE zwróci wartość pierwszego zamówienia klienta, a LAST_VALUE zwróci wartość ostatniego zamówienia klienta. W praktyce jednak, funkcje te obsługują ramkę. Dla przypomnienia, w przypadku funkcji obsługujących ramkę, jeśli określisz klauzulę kolejności okna, ale nie określisz jednostki ramki okna i związanego z nią zakresu, domyślnie otrzymasz RANGE UNBOUNDED PRECEDING. W przypadku funkcji FIRST_VALUE otrzymasz oczekiwany wynik, ale jeśli twoje zapytanie zostanie zoptymalizowane za pomocą operatorów trybu wierszowego, zapłacisz karę za użycie szpuli na dysku. Z funkcją LAST_VALUE jest jeszcze gorzej. Nie dość, że zapłacisz karę za użycie on-disk spool, to zamiast otrzymać wartość z ostatniego wiersza w partycji, otrzymasz wartość z bieżącego wiersza!

Oto wynik powyższego zapytania:

 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 ...

Często, gdy ludzie widzą taki wynik po raz pierwszy, myślą, że SQL Server ma błąd. Ale oczywiście tak nie jest; jest to po prostu domyślne ustawienie standardu SQL. Błąd jest w zapytaniu. Zdając sobie sprawę, że jest to związane z ramką, chcesz być jednoznaczny ze specyfikacją ramki i użyć minimalnej ramki, która przechwytuje wiersz, którego szukasz. Upewnij się również, że używasz jednostki ROWS. Tak więc, aby uzyskać pierwszy wiersz w partycji, użyj funkcji FIRST_VALUE z ramką ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Aby uzyskać ostatni wiersz w partycji, użyj funkcji LAST_VALUE z ramką ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Tutaj jest nasze poprawione zapytanie z naprawionym błędem:

 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;

Tym razem otrzymasz poprawny wynik:

 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 ...

Można się zastanawiać, jaka była motywacja dla standardu, aby nawet wspierać ramkę z tymi funkcjami. Jeśli pomyślisz o tym, będziesz ich używał głównie do uzyskania czegoś z pierwszych lub ostatnich wierszy w partycji. Jeśli potrzebujesz wartości z, powiedzmy, dwóch rzędów przed bieżącym, zamiast używać FIRST_VALUE z ramką, która zaczyna się od 2 PRECEDING, czy nie jest o wiele łatwiej użyć LAG z jawnym przesunięciem o 2, jak w ten sposób:

 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;

To zapytanie generuje następujące dane wyjściowe:

 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 ...

Najwyraźniej istnieje różnica semantyczna między powyższym użyciem funkcji LAG a FIRST_VALUE z ramką, która zaczyna się od 2 PRECEDING. Z tym pierwszym, jeśli wiersz nie istnieje w żądanym przesunięciu, domyślnie otrzymujesz NULL. Z tym drugim, nadal otrzymujesz wartość z pierwszego wiersza, który jest obecny, tj. wartość z pierwszego wiersza w partycji. Rozważmy następujące zapytanie:

 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;

Zapytanie to generuje następujące dane wyjściowe:

 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 ...

Zauważmy, że tym razem na wyjściu nie ma żadnych NULL-ów. Tak więc istnieje pewna wartość w obsłudze ramki z FIRST_VALUE i LAST_VALUE. Upewnij się tylko, że pamiętasz o najlepszej praktyce, aby zawsze jawnie określać specyfikację ramki za pomocą tych funkcji i używać opcji ROWS z minimalną ramką, która zawiera wiersz, o który ci chodzi.

Podsumowanie

Ten artykuł skupiał się na błędach, pułapkach i najlepszych praktykach związanych z funkcjami okienkowymi. Pamiętaj, że zarówno funkcje agregujące okna, jak i funkcje przesunięcia okna FIRST_VALUE i LAST_VALUE obsługują ramkę oraz że jeśli określisz klauzulę zamówienia okna, ale nie określisz jednostki ramki okna i związanego z nią zakresu, domyślnie otrzymasz RANGE UNBOUNDED PRECEDING. Powoduje to karę za wydajność, gdy zapytanie jest optymalizowane za pomocą operatorów trybu wierszowego. W przypadku funkcji LAST_VALUE skutkuje to pobraniem wartości z bieżącego wiersza zamiast z ostatniego wiersza partycji. Pamiętaj, aby być jednoznacznym co do ramki i ogólnie preferować opcję ROWS zamiast RANGE. Bardzo dobrze jest zobaczyć poprawę wydajności operatora agregacji okien w trybie wsadowym. Kiedy ma on zastosowanie, przynajmniej ta pułapka wydajności jest wyeliminowana.