Die OVER-Klausel wurde in SQL Server 2005 „zurück“ zu SQL Server hinzugefügt und in SQL erweitert Server 2012. Es wird überwiegend mit den „Fensterfunktionen“ verwendet; Die einzige Ausnahme ist die Sequenzfunktion NEXT VALUE FOR. Die OVER-Klausel wird verwendet, um zu bestimmen, welche Zeilen aus der Abfrage auf die Funktion angewendet werden, in welcher Reihenfolge sie von dieser Funktion ausgewertet werden und wann die Berechnungen der Funktion neu gestartet werden sollen. Da es in Verbindung mit anderen Funktionen verwendet wird und in diesem Artikel speziell nur die OVER-Klausel behandelt wird, werden diese Funktionen nur behandelt, da sie sich in den angegebenen Beispielen auf die OVER-Klausel beziehen.

Die Syntax von Die OVER-Klausel lautet:

<function> OVER ( )

Bei Betrachtung der Syntax scheinen alle Unterklauseln optional zu sein. Tatsächlich bestimmt jede Funktion, die die OVER-Klausel verwenden kann, welche der Unterklauseln zulässig und welche erforderlich sind. Abhängig von der verwendeten Funktion kann die OVER-Klausel selbst optional sein. Am Ende dieses Artikels befindet sich eine Tabelle, die zeigt, welche Funktionen welche Teile der OVER-Klausel zulassen / erfordern.

Die PARTITION BY-Klausel wird verwendet, um die Ergebnismenge aus der Abfrage in Datenuntermengen zu unterteilen, oder Partitionen. Wenn die Klausel PARTITION BY nicht verwendet wird, ist die gesamte Ergebnismenge der Abfrage die Partition, die verwendet wird. Die verwendete Fensterfunktion wird auf jede Partition separat angewendet, und die von der Funktion ausgeführte Berechnung wird für jede Partition neu gestartet. Sie definieren eine Reihe von Werten, die die Partition (en) bestimmen, in die die Abfrage unterteilt werden soll. Diese Werte können Spalten, Skalarfunktionen, Skalarunterabfragen oder Variablen sein.

Untersuchen wir beispielsweise die folgende Abfrage:

SELECT COUNT(*)FROM .sys.indexes;

Diese Abfrage gibt die folgende Ergebnismenge zurück:

Dies ist einfach die Anzahl der von der Abfrage zurückgegebenen Zeilen – in diesem Fall Die Anzahl der Indizes in der msdb-Datenbank. Fügen wir dieser Abfrage nun die OVER-Klausel hinzu:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Die verkürzten Ergebnisse sind:

Diese Abfrage gibt die Objekt-ID und die Index-ID für jeden Index sowie die Gesamtzahl der Indizes in der Ergebnismenge zurück. Da keine PARTITION BY-Klausel verwendet wurde, wurde die gesamte Ergebnismenge als einzelne Partition behandelt. Es ist jetzt an der Zeit, die PARTITION BY-Klausel hinzuzufügen und zu sehen, wie sich dadurch die Ergebnisse ändern:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Die verkürzten Ergebnisse sind:

Diese Abfrage gibt eine Zeile für jeden Index zurück. Jetzt gibt die Abfrage eine PARTITION BY-Klausel der Spalte object_id an, sodass die Zählfunktion die Anzahl der Indizes zurückgibt auf dieser bestimmten object_id. Die ORDER BY-Klausel steuert die Reihenfolge, in der die Zeilen von der Funktion ausgewertet werden. Dies wird in Kürze demonstriert. Die ROWS- oder RANGE-Klausel bestimmt die Teilmenge der Zeilen innerhalb der Partition, die auf die Funktion angewendet werden sollen. Bei Verwendung von ROWS oder RANGE geben Sie den Anfangs- und Endpunkt des Fensters an. Folgende Werte sind zulässig:

Es gibt zwei Syntaxen zum Festlegen des Fensters:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Wenn nur der Anfangsrahmen angegeben ist, lautet der Standardendrahmen CURRENT ROW.

Das Schlüsselwort UNBOUNDED gibt den Anfang der Partition (für PRECEDING) oder das Ende von an die Partition (für FOLGENDES). AKTUELLE REIHE gibt an, dass die aktuelle Zeile entweder der Anfang des Fensters oder das Ende des Fensters ist, je nachdem, in welcher Fensterrahmenposition sie verwendet wird. „N“ gibt eine Anzahl von Zeilen entweder vor der aktuellen Zeile an (für PRECEDING) ) oder nach der aktuellen Zeile (für FOLLOWING), die für den Fensterrahmen verwendet werden soll.

Die folgenden Fenster sind gültig:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Um die ROWS- oder RANGE-Klausel verwenden zu können, müssen Sie auch die ORDER BY-Klausel angeben. Wenn Sie dagegen die ORDER BY-Klausel verwenden und keine ROWS- oder RANGE-Klausel angeben, wird die Standard-RANGE ZWISCHEN UNBOUNDED PRECEDING AND CURRENT verwendet ROW wird verwendet.

Um die Klauseln ORDER BY und ROWS oder RANGE zu demonstrieren, erstellen wir einige Testdaten: zwei Konten, vier Daten pro Konto und einen Betrag für jedes Datum. In der Abfrage werden beide angezeigt Klauseln, die auf unterschiedliche Weise verwendet werden:

Diese Abfrage gibt die folgende Ergebnismenge zurück:

The “ Die Spalte RowNbr wird verwendet g die COUNT-Funktion, um zurückzugeben, wie viele Zeilen sich in der Partition befinden. Die Partition wird von TranDate sortiert, und wir geben einen Fensterrahmen für alle Zeilen vom Beginn der Partition bis zur aktuellen Zeile an. Für die erste Zeile gibt es nur eine Zeile im Fensterrahmen, daher wird der Wert „1“ zurückgegeben. Für die zweite Zeile gibt es jetzt zwei Zeilen im Fensterrahmen, sodass der Wert „2“ zurückgegeben wird. Und so weiter durch die restlichen Zeilen in diesem Konto.

Da die PARTITION BY-Klausel das Konto angibt, werden beim Ändern des Kontos die Funktionsberechnungen zurückgesetzt. Dies lässt sich anhand der Zeilen für das zweite Konto in der Ergebnismenge feststellen. Dies ist ein Beispiel für eine „laufende“ Aggregation, bei der die Aggregation auf früheren Berechnungen aufbaut. Ein Beispiel dafür, wann Sie dies verwenden würden, wäre die Berechnung Ihres Kontostands nach jeder Transaktion (auch als laufende Summe bezeichnet).

In der Spalte „DateCount“ wird gezählt, wie viele Zeilen nach Datum unterteilt sind. In diesem Beispiel hat jedes Konto an jedem der gleichen vier Daten eine Transaktion, sodass jedes Datum zwei Transaktionen hat (eine für jedes Konto). Dies führt dazu, dass für jede Zeile der Wert „2“ zurückgegeben wird. Dies ähnelt der Durchführung einer Zählung, bei der GROUP BY für das Datum verwendet wird. Der Unterschied besteht darin, dass die Gesamtsumme für jede Zeile anstatt nur einmal für jedes Datum zurückgegeben wird. Ein Beispiel Wenn Sie diese Methode verwenden würden, würden Sie eine „Zeile X von Y“ anzeigen oder einen Prozentsatz der aktuellen Zeile zur Gesamtsumme berechnen.

In der Spalte „Last2Count“ werden die Zeilen gezählt Innerhalb der Partition wird für die aktuelle Zeile und die unmittelbar davor stehende Zeile. Für die erste Zeile in jedem Konto wird der Wert „1“ zurückgegeben, da keine Zeilen davor stehen. Für die verbleibenden Zeilen in jedem Konto wird der Wert „2“ zurückgegeben. Dies ist ein Beispiel für eine „sich bewegende“ oder „gleitende“ Aggregation. Ein Beispiel für die Verwendung dieser Methode wäre die Berechnung eines Bonus basierend auf dem Verkäufe der letzten zwei Monate.

An dieser Stelle habe ich nur die ROWS-Klausel gezeigt. Die RANGE-Klausel funktioniert auf ähnliche Weise, aber anstatt die Zeilen positionell zu behandeln, behandelt sie die Von dieser Zeile zurückgegebene Werte. Da die Klauseln N PRECEDING / FOLLOWING nicht positionell sind, können sie nicht verwendet werden. Lassen Sie uns einen kurzen Blick auf den Unterschied zwischen ROWS und RANGE werfen, indem Sie beide in derselben Abfrage verwenden. Hier haben wir a Liste der Personen (nennen wir sie DBAs) und ihrer Stundensätze. Beachten Sie, dass die Zeilen mit den Zeilen-IDs 4 & 5 und 12 & Die Abfrage summiert die Raten zweimal, einmal mit ROWS und einmal mit RANGE:

Diese Abfrage erzeugt die folgende Ergebnismenge:

In den Spalten SumByRows und SumByRange ist die OVER-Klausel mit Ausnahme der ROWS / RANGE-Klausel identisch. Beachten Sie auch, dass, da der Endbereich nicht angegeben wurde, standardmäßig CURRENT ROW verwendet wird. Da wir das Gehalt vom Anfang der Ergebnismenge bis zur aktuellen Zeile summieren, berechnen wir wirklich eine laufende Summe der Spalte „Gehalt“. In der Spalte SumByRows wird der Wert mithilfe der ROWS-Klausel berechnet, und wir können sehen, dass die Summe der aktuellen Zeile das Gehalt der aktuellen Zeile plus die Gesamtsumme der vorherigen Zeile ist. Die RANGE-Klausel basiert jedoch auf dem Wert der Spalte Gehalt, sodass alle Zeilen mit demselben oder einem niedrigeren Gehalt zusammengefasst werden. Dies führt dazu, dass der SumByRange-Wert für alle Zeilen mit demselben Gehalt der gleiche Wert ist.

Ein wichtiger Hinweis: Die ORDER BY-Klausel in der OVER-Klausel steuert nur die Reihenfolge, in der die Zeilen in der Partition verwendet werden durch die Fensterfunktion. Die Reihenfolge der Endergebnismenge wird nicht gesteuert. Ohne eine ORDER BY-Klausel in der Abfrage selbst kann die Reihenfolge der Zeilen nicht garantiert werden. Möglicherweise stellen Sie fest, dass Ihre Abfrage möglicherweise in der Reihenfolge der zuletzt angegebenen OVER-Klausel zurückgegeben wird. Dies liegt an der Art und Weise, wie dies derzeit in SQL Server implementiert ist. Wenn das SQL Server-Team von Microsoft die Funktionsweise ändert, ordnet es Ihre Ergebnisse möglicherweise nicht mehr so, wie Sie es gerade beobachten. Wenn Sie eine bestimmte Reihenfolge für die Ergebnismenge benötigen, müssen Sie eine ORDER BY-Klausel für die Abfrage selbst angeben.

Schließlich finden Sie hier eine Tabelle der verschiedenen Funktionen, die die OVER-Klausel sowie verwenden können Welche Teile der Klausel sind zulässig / erforderlich / optional?

R-Erforderlich, O-Optional, X-Nicht zulässig