Dieser Artikel ist der vierte Teil einer Serie über T-SQL Bugs, Fallstricke und beste Praktiken. Zuvor habe ich Determinismus, Subqueries und Joins behandelt. In diesem Monat geht es um Bugs, Fallstricke und Best Practices im Zusammenhang mit Fensterfunktionen. Ich danke Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man und Paul White für ihre Ideen!

In meinen Beispielen werde ich eine Beispieldatenbank namens TSQLV5 verwenden. Das Skript, mit dem diese Datenbank erstellt und befüllt wird, finden Sie hier, und das ER-Diagramm hier.

Es gibt zwei häufige Fallstricke bei Fensterfunktionen, die beide das Ergebnis von kontraintuitiven impliziten Vorgaben sind, die vom SQL-Standard auferlegt werden. Ein Fallstrick hat mit Berechnungen von laufenden Summen zu tun, bei denen Sie einen Fensterrahmen mit der impliziten Option RANGE erhalten. Ein anderer Fall ist in gewisser Weise damit verwandt, hat aber schwerwiegendere Folgen und betrifft eine implizite Rahmendefinition für die Funktionen FIRST_VALUE und LAST_VALUE.

Fensterrahmen mit impliziter RANGE-Option

Unser erster Fall betrifft die Berechnung laufender Summen unter Verwendung einer Aggregat-Fensterfunktion, bei der Sie zwar explizit die Fensterordnungs-Klausel angeben, aber nicht explizit die Einheit des Fensterrahmens (ROWS oder RANGE) und die damit verbundene Ausdehnung des Fensterrahmens angeben, z.B., ROWS UNBOUNDED PRECEDING. Die implizite Voreinstellung ist kontraintuitiv, und ihre Folgen können überraschend und schmerzhaft sein.

Um diesen Fallstrick zu demonstrieren, verwende ich eine Tabelle mit dem Namen Transaktionen, die zwei Millionen Bankkontobewegungen mit Gutschriften (positive Werte) und Belastungen (negative Werte) enthält. Führen Sie den folgenden Code aus, um die Tabelle Transaktionen zu erstellen und sie mit Beispieldaten zu füllen:

 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;

Unser Fallstrick hat sowohl eine logische Seite mit einem potenziellen logischen Fehler als auch eine Leistungsseite mit einer Leistungseinbuße. Die Leistungseinbuße ist nur relevant, wenn die Fensterfunktion mit Zeilenmodus-Verarbeitungsoperatoren optimiert wird. Mit SQL Server 2016 wurde der Batch-Modus-Operator Window Aggregate eingeführt, der die Leistungseinbußen beseitigt, aber vor SQL Server 2019 wird dieser Operator nur verwendet, wenn ein Spaltenindex für die Daten vorhanden ist. SQL Server 2019 führt den Batch-Modus für die Unterstützung von Rowstores ein, sodass Sie die Batch-Modus-Verarbeitung auch dann nutzen können, wenn für die Daten keine Columnstore-Indizes vorhanden sind. Um die Leistungseinbußen bei der Verarbeitung im Zeilenmodus zu demonstrieren, verwenden Sie, wenn Sie die Codebeispiele in diesem Artikel auf SQL Server 2019 oder höher oder auf Azure SQL Database ausführen, den folgenden Code, um die Datenbankkompatibilitätsebene auf 140 zu setzen, damit der Batchmodus für den Zeilenspeicher noch nicht aktiviert ist:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;

Verwenden Sie den folgenden Code, um Zeit- und E/A-Statistiken in der Sitzung einzuschalten:

 SET STATISTICS TIME, IO ON;

Um zu vermeiden, dass in SSMS zwei Millionen Zeilen gedruckt werden, empfehle ich, die Codebeispiele in diesem Abschnitt mit aktivierter Option „Ergebnisse nach der Ausführung verwerfen“ auszuführen (gehen Sie zu Abfrageoptionen, Ergebnisse, Raster, und aktivieren Sie „Ergebnisse nach der Ausführung verwerfen“).

Bevor wir zu den Fallstricken kommen, betrachten wir die folgende Abfrage (nennen wir sie Abfrage 1), die den Kontostand nach jeder Transaktion berechnet, indem sie eine laufende Summe unter Verwendung einer Fensteraggregatfunktion mit einer expliziten Rahmenspezifikation anwendet:

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

Der Plan für diese Abfrage unter Verwendung der Verarbeitung im Zeilenmodus ist in Abbildung 1 dargestellt.

Abbildung 1: Plan für Abfrage 1, Zeilenmodusverarbeitung

Der Plan zieht die Daten vorgeordnet aus dem geclusterten Index der Tabelle. Dann verwendet er die Operatoren Segment und Sequence Project, um Zeilennummern zu berechnen und herauszufinden, welche Zeilen zum Rahmen der aktuellen Zeile gehören. Dann werden die Operatoren Segment, Window Spool und Stream Aggregate verwendet, um die Fensteraggregatfunktion zu berechnen. Der Window-Spool-Operator wird verwendet, um die Rahmenzeilen zu spoolen, die dann aggregiert werden müssen. Ohne eine spezielle Optimierung hätte der Plan pro Zeile alle zutreffenden Rahmenzeilen in den Spool schreiben und dann aggregieren müssen. Dies hätte zu einer quadratischen, d. h. N2, Komplexität geführt. Die gute Nachricht ist, dass SQL Server, wenn der Rahmen mit UNBOUNDED PRECEDING beginnt, den Fall als Fast-Track-Fall identifiziert, in dem er einfach die laufende Summe der vorherigen Zeile nimmt und den Wert der aktuellen Zeile addiert, um die laufende Summe der aktuellen Zeile zu berechnen, was zu einer linearen Skalierung führt. In diesem Fast-Track-Modus schreibt der Plan nur zwei Zeilen pro Eingabezeile in den Spool – eine mit der Gesamtsumme und eine mit den Details.

Der Fenster-Spool kann auf zwei Arten physisch implementiert werden. Entweder als schneller In-Memory-Spool, der speziell für Window-Funktionen entwickelt wurde, oder als langsamer On-Disk-Spool, der im Wesentlichen eine temporäre Tabelle in tempdb ist. Wenn die Anzahl der Zeilen, die pro zugrunde liegender Zeile in den Spool geschrieben werden müssen, 10.000 übersteigen könnte oder wenn SQL Server die Anzahl nicht vorhersagen kann, wird der langsamere On-Disk-Spool verwendet. In unserem Abfrageplan werden genau zwei Zeilen pro zugrunde liegender Zeile in den Spool geschrieben, so dass SQL Server den In-Memory-Spool verwendet. Leider gibt es keine Möglichkeit, aus dem Plan zu erkennen, welche Art von Spool Sie erhalten. Es gibt zwei Möglichkeiten, dies herauszufinden. Eine ist die Verwendung eines erweiterten Ereignisses namens window_spool_ondisk_warning. Eine andere Möglichkeit besteht darin, STATISTICS IO zu aktivieren und die Anzahl der logischen Lesevorgänge zu überprüfen, die für eine Tabelle namens Worktable gemeldet werden. Eine Zahl größer als Null bedeutet, dass Sie den On-Disk-Spool erhalten haben. Null bedeutet, dass Sie den In-Memory-Spool erhalten haben. Hier sind die E/A-Statistiken für unsere Abfrage:

Wie Sie sehen können, wurde der In-Memory-Spool verwendet. Das ist in der Regel der Fall, wenn Sie die ROWS-Fensterrahmeneinheit mit UNBOUNDED PRECEDING als erstes Begrenzungszeichen verwenden.

Hier sind die Zeitstatistiken für unsere Abfrage:

Die Abfrage dauerte auf meinem Rechner etwa 4,5 Sekunden, wobei die Ergebnisse verworfen wurden.

Jetzt kommt der Haken. Wenn Sie die Option RANGE anstelle von ROWS mit denselben Begrenzungszeichen verwenden, kann es einen kleinen Unterschied in der Bedeutung geben, aber einen großen Unterschied in der Leistung im Zeilenmodus. Der Bedeutungsunterschied ist nur dann relevant, wenn Sie keine vollständige Ordnung haben, d. h. wenn Sie nach etwas ordnen, das nicht eindeutig ist. Die Option ROWS UNBOUNDED PRECEDING stoppt mit der aktuellen Zeile, so dass die Berechnung im Falle von Gleichständen nicht deterministisch ist. Die Option RANGE UNBOUNDED PRECEDING hingegen geht der aktuellen Zeile voraus und schließt Gleichstände ein, falls vorhanden. Sie verwendet eine ähnliche Logik wie die Option TOP WITH TIES. Wenn Sie eine totale Ordnung haben, d. h. Sie ordnen nach etwas Eindeutigem, gibt es keine Bindungen, die berücksichtigt werden müssen, und daher sind ROWS und RANGE in einem solchen Fall logisch äquivalent. Das Problem ist, dass SQL Server bei der Verwendung von RANGE im Zeilenmodus immer den On-Disk-Spool verwendet, da er bei der Verarbeitung einer bestimmten Zeile nicht vorhersagen kann, wie viele weitere Zeilen einbezogen werden. Dies kann zu erheblichen Leistungseinbußen führen.

Betrachten Sie die folgende Abfrage (nennen Sie sie Abfrage 2), die die gleiche ist wie Abfrage 1, nur unter Verwendung der Option RANGE anstelle von ROWS:

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

Der Plan für diese Abfrage ist in Abbildung 2 dargestellt.

Abbildung 2: Plan für Abfrage 2, Verarbeitung im Zeilenmodus

Abfrage 2 ist logisch äquivalent zu Abfrage 1, da wir eine Gesamtreihenfolge haben; da sie jedoch RANGE verwendet, wird sie mit dem On-Disk-Spool optimiert. Beachten Sie, dass der Window-Spool im Plan für Abfrage 2 genauso aussieht wie im Plan für Abfrage 1, und die geschätzten Kosten sind die gleichen.

Hier sind die Zeit- und E/A-Statistiken für die Ausführung von Abfrage 2:

Beachten Sie die große Anzahl logischer Lesevorgänge für Worktable, was darauf hindeutet, dass Sie den On-Disk-Spool erhalten haben. Die Laufzeit ist mehr als viermal so lang wie bei Abfrage 1.

Wenn Sie denken, dass Sie in diesem Fall die Option RANGE einfach nicht verwenden werden, es sei denn, Sie müssen wirklich Schwellenwerte einbeziehen, ist das eine gute Überlegung. Das Problem ist, dass, wenn Sie eine Fensterfunktion verwenden, die einen Rahmen (Aggregate, FIRST_VALUE, LAST_VALUE) mit einer expliziten Fensterordnungsklausel unterstützt, aber die Einheit des Fensterrahmens und die damit verbundene Ausdehnung nicht erwähnt, Sie standardmäßig RANGE UNBOUNDED PRECEDING erhalten. Diese Vorgabe wird durch den SQL-Standard vorgegeben, und der Standard hat sie gewählt, weil er im Allgemeinen deterministischere Optionen als Vorgabe bevorzugt. Die folgende Abfrage (nennen wir sie Abfrage 3) ist ein Beispiel, das in diese Falle tappt:

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

Die meisten Leute schreiben so, weil sie davon ausgehen, dass sie standardmäßig ROWS UNBOUNDED PRECEDING erhalten, ohne zu erkennen, dass sie eigentlich RANGE UNBOUNDED PRECEDING erhalten. Da die Funktion die Gesamtreihenfolge verwendet, erhält man das gleiche Ergebnis wie bei ROWS, so dass man am Ergebnis nicht erkennen kann, dass es ein Problem gibt. Aber die Leistungszahlen, die Sie erhalten, sind wie bei Abfrage 2. Ich erlebe immer wieder, dass Leute in diese Falle tappen.

Um dieses Problem zu vermeiden, sollten Sie in Fällen, in denen Sie eine Fensterfunktion mit einem Rahmen verwenden, die Einheit des Fensterrahmens und seine Ausdehnung explizit angeben und generell ROWS bevorzugen. Reservieren Sie die Verwendung von RANGE nur für Fälle, in denen die Reihenfolge nicht eindeutig ist und Sie Bindungen einbeziehen müssen.

Betrachten Sie die folgende Abfrage, die einen Fall illustriert, in dem es einen konzeptionellen Unterschied zwischen ROWS und RANGE gibt:

 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;

Diese Abfrage erzeugt die folgende Ausgabe:

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

Beobachten Sie den Unterschied in den Ergebnissen für die Zeilen, in denen dasselbe Bestelldatum mehr als einmal vorkommt, wie im Fall des 8. Juli 2017. Beachten Sie, dass die Option ROWS keine Bindungen einbezieht und daher nicht deterministisch ist, während die Option RANGE Bindungen einbezieht und daher immer deterministisch ist.

Es ist jedoch fraglich, ob Sie in der Praxis Fälle haben, in denen Sie nach etwas ordnen, das nicht eindeutig ist, und Sie wirklich die Einbeziehung von Bindungen benötigen, um die Berechnung deterministisch zu machen. Was in der Praxis wahrscheinlich viel häufiger vorkommt, ist eines von zwei Dingen zu tun. Die eine besteht darin, Bindungen aufzulösen, indem man etwas zur Fensterreihenfolge hinzufügt, um sie eindeutig zu machen und auf diese Weise eine deterministische Berechnung zu erhalten, etwa so:

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

Diese Abfrage erzeugt die folgende Ausgabe:

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

Eine andere Möglichkeit ist die Anwendung einer vorläufigen Gruppierung, in unserem Fall nach Bestelldatum, wie folgt:

 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;

Diese Abfrage erzeugt die folgende Ausgabe, bei der jedes Bestelldatum nur einmal erscheint:

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

Auf jeden Fall sollten Sie sich hier an die beste Vorgehensweise erinnern!

Die gute Nachricht ist, dass alle drei oben genannten Abfragen mit dem Batchmodus-Operator „Window Aggregate“ optimiert werden, wenn Sie auf SQL Server 2016 oder höher ausgeführt werden und einen Columnstore-Index für die Daten haben (selbst wenn es sich um einen gefälschten gefilterten Columnstore-Index handelt), oder wenn Sie auf SQL Server 2019 oder höher oder auf Azure SQL Database ausgeführt werden, unabhängig vom Vorhandensein von Columnstore-Indizes. Mit diesem Operator werden viele der Ineffizienzen bei der Verarbeitung im Zeilenmodus beseitigt. Dieser Operator verwendet überhaupt keinen Spool, so dass es keinen Unterschied zwischen In-Memory- und On-Disk-Spool gibt. Er verwendet eine ausgefeiltere Verarbeitung, bei der er mehrere parallele Durchläufe über das Zeilenfenster im Speicher sowohl für ROWS als auch für RANGE durchführen kann.

Um die Verwendung der Batch-Modus-Optimierung zu demonstrieren, stellen Sie sicher, dass Ihre Datenbank-Kompatibilitätsebene auf 150 oder höher eingestellt ist:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;

Führen Sie Abfrage 1 erneut aus:

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

Der Plan für diese Abfrage ist in Abbildung 3 dargestellt.

Abbildung 3: Plan für Abfrage 1, Batch-Modus-Verarbeitung

Hier sind die Leistungsstatistiken, die ich für diese Abfrage erhalten habe:

Die Laufzeit sank auf 1 Sekunde!

Führen Sie Abfrage 2 mit der expliziten Option RANGE erneut aus:

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

Der Plan für diese Abfrage ist in Abbildung 4 dargestellt.

Abbildung 2: Plan für Abfrage 2, Batch-Modus-Verarbeitung

Hier sind die Leistungsstatistiken, die ich für diese Abfrage erhalten habe:

Die Leistung ist die gleiche wie für Abfrage 1.

Führen Sie Abfrage 3 erneut aus, mit der impliziten Option RANGE:

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

Der Plan und die Leistungszahlen sind natürlich die gleichen wie für Abfrage 2.

Wenn Sie fertig sind, führen Sie den folgenden Code aus, um die Leistungsstatistiken zu deaktivieren:

 SET STATISTICS TIME, IO OFF;

Vergessen Sie auch nicht, die Option „Ergebnisse nach Ausführung verwerfen“ in SSMS zu deaktivieren.

Impliziter Rahmen mit FIRST_VALUE und LAST_VALUE

Die Funktionen FIRST_VALUE und LAST_VALUE sind Offset-Fensterfunktionen, die einen Ausdruck aus der ersten bzw. letzten Zeile im Fensterrahmen zurückgeben. Das Tückische an ihnen ist, dass man bei ihrer erstmaligen Verwendung oft nicht merkt, dass sie einen Rahmen unterstützen, sondern denkt, sie gelten für die gesamte Partition.

Betrachten Sie den folgenden Versuch, Bestellinformationen sowie die Werte der ersten und letzten Bestellung des Kunden zurückzugeben:

 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;

Wenn Sie fälschlicherweise glauben, dass diese Funktionen auf die gesamte Fensterpartition wirken, was viele Leute glauben, die diese Funktionen zum ersten Mal verwenden, erwarten Sie natürlich, dass FIRST_VALUE den Bestellwert der ersten Bestellung des Kunden und LAST_VALUE den Bestellwert der letzten Bestellung des Kunden zurückgibt. In der Praxis unterstützen diese Funktionen jedoch einen Rahmen. Zur Erinnerung: Bei Funktionen, die einen Rahmen unterstützen, erhalten Sie standardmäßig RANGE UNBOUNDED PRECEDING, wenn Sie die Fensterordnungs-Klausel, aber nicht die Einheit des Fensterrahmens und den zugehörigen Umfang angeben. Mit der Funktion FIRST_VALUE erhalten Sie das erwartete Ergebnis, aber wenn Ihre Abfrage mit Zeilenmodusoperatoren optimiert wird, zahlen Sie die Strafe für die Verwendung des On-Disk-Spools. Mit der Funktion LAST_VALUE ist es noch schlimmer. Nicht nur, dass Sie die Strafe für das On-Disk-Spooling zahlen müssen, sondern Sie erhalten auch den Wert der aktuellen Zeile, anstatt den Wert der letzten Zeile in der Partition zu erhalten!

Hier ist die Ausgabe der obigen Abfrage:

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

Wenn Leute solche Ausgaben zum ersten Mal sehen, denken sie oft, dass SQL Server einen Fehler hat. Aber das ist natürlich nicht der Fall; es handelt sich einfach um die Standardeinstellung des SQL-Standards. Es ist ein Fehler in der Abfrage. Wenn Sie wissen, dass es sich um einen Frame handelt, sollten Sie die Frame-Spezifikation explizit angeben und den kleinsten Frame verwenden, der die gesuchte Zeile erfasst. Stellen Sie außerdem sicher, dass Sie die Einheit ROWS verwenden. Um also die erste Zeile in der Partition zu erhalten, verwenden Sie die Funktion ERSTER_WERT mit dem Rahmen ZEILE ZWISCHEN UNBEGRENZTER VORHERIGER UND AKTUELLER ZEILE. Um die letzte Zeile in der Partition zu erhalten, verwenden Sie die Funktion LAST_VALUE mit dem Rahmen ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Hier ist unsere überarbeitete Abfrage mit dem behobenen Fehler:

 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;

Diesmal erhalten Sie das richtige Ergebnis:

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

Man fragt sich, was die Motivation für den Standard war, überhaupt einen Rahmen mit diesen Funktionen zu unterstützen. Wenn man darüber nachdenkt, wird man sie meistens benutzen, um etwas aus den ersten oder letzten Zeilen der Partition zu erhalten. Wenn man den Wert von, sagen wir, zwei Zeilen vor der aktuellen benötigt, anstatt FIRST_VALUE mit einem Rahmen zu verwenden, der mit 2 PRECEDING beginnt, ist es nicht viel einfacher, LAG mit einem expliziten Offset von 2 zu verwenden, etwa so:

 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;

Diese Abfrage erzeugt die folgende Ausgabe:

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

Anscheinend gibt es einen semantischen Unterschied zwischen der obigen Verwendung der LAG-Funktion und FIRST_VALUE mit einem Rahmen, der mit 2 PRECEDING beginnt. Bei ersterer erhält man standardmäßig NULL, wenn eine Zeile im gewünschten Offset nicht vorhanden ist. Bei der zweiten Variante erhalten Sie den Wert der ersten vorhandenen Zeile, d. h. den Wert der ersten Zeile in der Partition. Betrachten Sie die folgende Abfrage:

 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;

Diese Abfrage erzeugt die folgende Ausgabe:

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

Beachten Sie, dass diesmal keine NULLs in der Ausgabe sind. Es ist also durchaus sinnvoll, einen Rahmen mit FIRST_VALUE und LAST_VALUE zu unterstützen. Achten Sie nur darauf, dass Sie sich an die bewährte Praxis erinnern, die Rahmenspezifikation mit diesen Funktionen immer explizit anzugeben und die Option ROWS mit dem minimalen Rahmen zu verwenden, der die gesuchte Zeile enthält.

Abschluss

Dieser Artikel konzentrierte sich auf Fehler, Fallstricke und bewährte Praktiken im Zusammenhang mit Fensterfunktionen. Denken Sie daran, dass sowohl die Fensteraggregatfunktionen als auch die Fenster-Offsetfunktionen FIRST_VALUE und LAST_VALUE einen Rahmen unterstützen, und dass Sie, wenn Sie die Fensterordnungs-Klausel angeben, aber nicht die Einheit des Fensterrahmens und die zugehörige Ausdehnung angeben, standardmäßig RANGE UNBOUNDED PRECEDING erhalten. Dies führt zu einer Leistungseinbuße, wenn die Abfrage mit Zeilenmodusoperatoren optimiert wird. Bei der Funktion LAST_VALUE führt dies dazu, dass die Werte aus der aktuellen Zeile statt aus der letzten Zeile in der Partition abgerufen werden. Denken Sie daran, den Rahmen explizit anzugeben und generell die Option ROWS der Option RANGE vorzuziehen. Die Leistungsverbesserungen mit dem Stapelmodus-Operator Fensteraggregat sind sehr erfreulich. Wenn er anwendbar ist, wird zumindest die Leistungsfalle beseitigt.