SELECT – OVER-Klausel (Transact-SQL)

• 11.08.2017
• 17 Minuten zum Lesen
• • V
  • L
  • c
  • j
  • M
  • +11

Gilt für: SQL Server (alle unterstützte Versionen) Azure SQL-Datenbank Verwaltete Azure SQL-Instanz Azure Synapse Analytics Paralleles Data Warehouse

Bestimmt die Partitionierung und Reihenfolge eines Rowsets, bevor die zugehörige Fensterfunktion angewendet wird. Das heißt, die OVER-Klausel definiert ein Fenster oder eine benutzerdefinierte Reihe von Zeilen innerhalb einer Abfrageergebnismenge. Eine Fensterfunktion berechnet dann einen Wert für jede Zeile im Fenster. Sie können die OVER-Klausel mit Funktionen verwenden, um aggregierte Werte wie gleitende Durchschnitte, kumulative Aggregate, laufende Summen oder ein Top-N pro Gruppenergebnis zu berechnen.

• Ranking-Funktionen

• Aggregatfunktionen

• Analysefunktionen

• NÄCHSTER WERT FÜR Funktion

Transact-SQL-Syntaxkonventionen

Syntax

Argumente

Fensterfunktionen haben möglicherweise die folgenden Argumente in ihrer OVER -Klausel:

• PARTITION BY, die die Abfrageergebnismenge in Partitionen unterteilt.
• ORDER BY, das die logische Reihenfolge der Zeilen in jeder Partition der Ergebnismenge definiert.
• ROWS / RANGE, die die Zeilen innerhalb der Partition durch Angabe von Start- und Endpunkten innerhalb der Partition begrenzt. Es erfordert das Argument ORDER BY und der Standardwert ist vom Beginn der Partition bis zum aktuellen Element, wenn das Argument ORDER BY angegeben ist.

Wenn Sie kein Argument angeben, werden die Fensterfunktionen auf die gesamte Ergebnismenge angewendet.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Teilt die Abfrageergebnismenge in Partitionen. Die Fensterfunktion wird für jede Partition separat angewendet und die Berechnung für jede Partition neu gestartet ion.

Wenn PARTITION BY nicht angegeben ist, behandelt die Funktion alle Zeilen der Abfrageergebnismenge als einzelne Partition. Die Funktion wird auf alle Zeilen angewendet Wenn Sie in der Partition keine ORDER BY -Klausel angeben.

PARTITION BY value_expression

Gibt die Spalte an, nach der das Rowset partitioniert wird. value_expression kann nur auf Spalten verweisen, die durch die FROM-Klausel verfügbar gemacht werden. value_expression kann nicht auf Ausdrücke oder Aliase in der Auswahlliste verweisen. value_expression kann ein Spaltenausdruck, eine skalare Unterabfrage, eine skalare Funktion oder eine benutzerdefinierte Variable sein.

ORDER BY

Definiert die logische Reihenfolge der Zeilen in jeder Partition der Ergebnismenge. Das heißt, es gibt die logische Reihenfolge an, in der die Fensterfunktionsberechnung durchgeführt wird.

• Wenn diese nicht angegeben wird, lautet die Standardreihenfolge ASC und Die Fensterfunktion verwendet alle Zeilen in der Partition.
• Wenn dies angegeben ist und kein ROWS / RANGE angegeben ist, wird standardmäßig RANGE UNBOUNDED PRECEDING AND CURRENT ROW als Standard für Fenster verwendet Rahmen durch die Funktionen, die optionale ROWS / RANGE-Spezifikationen akzeptieren können (z. B. min oder max).

order_by_expression
Gibt eine Spalte oder einen Ausdruck an, nach dem sortiert werden soll. order_by_expression kann sich nur auf Spalten beziehen, die durch die FROM-Klausel verfügbar gemacht werden. Es kann keine Ganzzahl angegeben werden, die einen Spaltennamen oder Alias darstellt.

COLLATE-Kollatierungsname
Gibt an, dass die ORDER BY-Operation gemäß der in Kollatierungsname angegebenen Kollatierung ausgeführt werden soll. Der Kollatierungsname kann entweder ein Windows-Kollatierungsname oder ein SQL-Kollatierungsname sein. Weitere Informationen finden Sie unter Kollatierungs- und Unicode-Unterstützung. COLLATE gilt nur für Spalten vom Typ char, varchar, nchar und nvarchar.

ASC | DESC
Gibt an, dass die Werte in der angegebenen Spalte in aufsteigender oder absteigender Reihenfolge sortiert werden sollen. ASC ist die Standardsortierreihenfolge.Nullwerte werden als niedrigstmögliche Werte behandelt.

ROWS oder RANGE

Gilt für: SQL Server 2012 (11.x) und höher.

Weitere Grenzwerte die Zeilen innerhalb der Partition durch Angabe von Start- und Endpunkten innerhalb der Partition. Dies erfolgt durch Angabe eines Zeilenbereichs in Bezug auf die aktuelle Zeile entweder durch logische Zuordnung oder durch physische Zuordnung. Die physische Zuordnung wird mithilfe der ROWS-Klausel erreicht.

Die ROWS-Klausel begrenzt die Zeilen innerhalb einer Partition, indem eine feste Anzahl von Zeilen vor oder nach der aktuellen Zeile angegeben wird. Alternativ begrenzt die RANGE-Klausel die Zeilen innerhalb einer Partition logisch, indem ein Wertebereich in Bezug auf den Wert in der aktuellen Zeile angegeben wird. Vorhergehende und folgende Zeilen werden basierend auf der Reihenfolge in der ORDER BY-Klausel definiert. Der Fensterrahmen „RANGE … CURRENT ROW …“ enthält alle Zeilen, die im ORDER BY-Ausdruck dieselben Werte wie die aktuelle Zeile haben. Beispiel: REIHEN ZWISCHEN 2 VORHERIGEN UND AKTUELLEN REIHEN bedeutet, dass das Zeilenfenster, in dem die Funktion ausgeführt wird, drei Zeilen groß ist, beginnend mit zwei Zeilen vor und einschließlich der aktuellen Zeile

ROWS oder RANGE erfordern die Angabe der ORDER BY-Klausel. Wenn ORDER BY mehrere Ordnungsausdrücke enthält, berücksichtigt CURRENT ROW FOR RANGE alle Spalten in der ORDER BY-Liste, wenn die aktuelle Zeile ermittelt wird.

UNBOUNDED PRECEDING

Gilt für: SQL Server 2012 (11.x) und höher.

Gibt an, dass das Fenster in der ersten Zeile der Partition beginnt. UNBOUNDED PRECEDING kann nur als Fensterstartpunkt angegeben werden.

< Spezifikation für vorzeichenlose Werte > PRECEDING
Angegeben mit < vorzeichenlose Wertespezifikation >, um die Anzahl der Zeilen oder Werte anzugeben, die der aktuellen Zeile vorangehen sollen. Diese Angabe ist für RANGE nicht zulässig.

AKTUELLE REIHE

Gilt für: SQL Server 2012 (11.x) und höher.

Gibt an, dass das Fenster gestartet wird oder endet bei Verwendung mit ROWS in der aktuellen Zeile oder bei Verwendung mit RANGE am aktuellen Wert. CURRENT ROW kann sowohl als Start- als auch als Endpunkt angegeben werden.

ZWISCHEN UND

Gilt für: SQL Server 2012 (11.x) und höher.

Wird entweder mit ROWS oder RANGE verwendet, um die unteren (Anfangs-) und oberen (End-) Grenzpunkte des Fensters anzugeben. < Fensterrahmen gebunden > definiert den Grenzstartpunkt und < Fensterrahmen gebunden > definiert den Grenzendpunkt. Die Obergrenze darf nicht kleiner als die Untergrenze sein.

UNBEGRENZT FOLGT

Gilt für: SQL Server 2012 (11.x) und höher.

Gibt dies an Das Fenster endet in der letzten Zeile der Partition. UNBOUNDED FOLLOWING kann nur als Fensterendpunkt angegeben werden. Beispiel: BEREICH ZWISCHEN AKTUELLER REIHE UND UNBEGRENZTEM FOLGENDES definiert ein Fenster, das mit der aktuellen Zeile beginnt und mit der letzten Zeile der Partition endet.

< vorzeichenlose Wertespezifikation > FOLGT
Angegeben mit < vorzeichenlose Wertespezifikation >, um die Anzahl von anzugeben Zeilen oder Werte, die der aktuellen Zeile folgen sollen. Wenn < vorzeichenlose Wertespezifikation > FOLLOWING als Fensterstartpunkt angegeben ist, muss der Endpunkt < vorzeichenlose Wertespezifikation > FOLGT. Beispielsweise definieren REIHEN ZWISCHEN 2 FOLGENDEN UND 10 FOLGENDEN ein Fenster, das mit der zweiten Zeile beginnt, die der aktuellen Zeile folgt, und mit der zehnten Zeile endet, die der aktuellen Zeile folgt. Diese Angabe ist für RANGE nicht zulässig.

Ganzzahlliteral ohne Vorzeichen
Gilt für: SQL Server 2012 (11.x) und höher.

Ist ein positives Ganzzahlliteral (einschließlich 0) ), die die Anzahl der Zeilen oder Werte angibt, die der aktuellen Zeile oder dem aktuellen Wert vorangehen oder folgen sollen. Diese Angabe gilt nur für ROWS.

Allgemeine Hinweise

In einer einzelnen Abfrage mit einer einzelnen FROM-Klausel kann mehr als eine Fensterfunktion verwendet werden. Die OVER-Klausel für jede Funktion kann sich in Partitionierung und Reihenfolge unterscheiden.

Wenn PARTITION BY nicht angegeben ist, behandelt die Funktion alle Zeilen der Abfrageergebnismenge als eine einzige Gruppe.

Wichtig !

Wenn ROWS / RANGE angegeben ist und der < Fensterrahmen vor > für Fensterrahmenausdehnung > (kurze Syntax), dann wird diese Spezifikation für den Startpunkt der Fensterrahmengrenze und CURRENT ROW für den Grenzendpunkt verwendet. Zum Beispiel ist „ROWS 5 PRECEDING“ gleich „ROWS ZWISCHEN 5 PRECEDING UND CURRENT ROW“.

Einschränkungen und Einschränkungen

Die OVER-Klausel kann nicht mit der Aggregatfunktion CHECKSUM verwendet werden.

RANGE kann nicht mit < vorzeichenloser Wertespezifikation > PRECEDING oder < vorzeichenlose Wertespezifikation > FOLGENDES.

Abhängig von der Rangfolge, dem Aggregat oder der Analyse Funktion, die mit der OVER-Klausel < ORDER BY-Klausel > und / oder der < verwendet wird ROWS- und RANGE-Klausel > wird möglicherweise nicht unterstützt.

Beispiele

A. Verwenden der OVER-Klausel mit der Funktion ROW_NUMBER

Das folgende Beispiel zeigt die Verwendung der OVER-Klausel mit der Funktion ROW_NUMBER, um eine Zeilennummer für jede Zeile innerhalb einer Partition anzuzeigen. Die in der OVER-Klausel angegebene ORDER BY-Klausel ordnet die Zeilen in jeder Partition nach der Spalte SalesYTD. Die ORDER BY-Klausel in der SELECT-Anweisung bestimmt die Reihenfolge, in der die gesamte Abfrageergebnismenge zurückgegeben wird.

Hier ist die Ergebnismenge.

B. Verwenden der OVER-Klausel mit Aggregatfunktionen

Im folgenden Beispiel wird die OVER -Klausel mit Aggregatfunktionen für alle von der Abfrage zurückgegebenen Zeilen verwendet. In diesem Beispiel ist die Verwendung der Klausel OVER effizienter als die Verwendung von Unterabfragen zum Ableiten der Aggregatwerte.

Hier ist die Ergebnismenge.

Das folgende Beispiel zeigt die Verwendung der Klausel OVER mit einer Aggregatfunktion in einem berechneten Wert.

Hier ist die Ergebnismenge. Beachten Sie, dass die Aggregate mit SalesOrderID berechnet werden und die Percent by ProductID für jede Zeile jeder SalesOrderID.

C. Erstellen eines gleitenden Durchschnitts und einer kumulierten Summe

Im folgenden Beispiel werden die Funktionen AVG und SUM mit der OVER-Klausel verwendet, um einen gleitenden Durchschnitt und eine kumulierte Summe der jährlichen Verkäufe für jedes Gebiet in der Tabelle. Die Daten werden nach TerritoryID partitioniert und nach SalesYTD logisch sortiert. Dies bedeutet, dass die AVG-Funktion für jedes Gebiet basierend auf dem Verkaufsjahr berechnet wird. Beachten Sie, dass für TerritoryID 1 zwei Zeilen für das Verkaufsjahr 2005 vorhanden sind, die die beiden Vertriebsmitarbeiter mit Verkäufen in diesem Jahr darstellen. Der durchschnittliche Umsatz für diese beiden Zeilen wird berechnet, und dann wird die dritte Zeile, die den Umsatz für das Jahr 2006 darstellt, in die Berechnung einbezogen.

Hier ist die Ergebnismenge.

In diesem Beispiel Die OVER-Klausel enthält keine PARTITION BY. Dies bedeutet, dass die Funktion auf alle von der Abfrage zurückgegebenen Zeilen angewendet wird. Die in der OVER-Klausel angegebene ORDER BY-Klausel bestimmt die logische Reihenfolge, auf die die AVG-Funktion angewendet wird. Die Abfrage gibt einen gleitenden Durchschnitt der Verkäufe pro Jahr für alle in der WHERE-Klausel angegebenen Verkaufsgebiete zurück. Die in der SELECT-Anweisung angegebene ORDER BY-Klausel bestimmt die Reihenfolge, in der die Zeilen der Abfrage angezeigt werden.

Hier ist die Ergebnismenge.

D. Angeben der ROWS-Klausel

Gilt für: SQL Server 2012 (11.x) und höher.

Im folgenden Beispiel wird mit der ROWS-Klausel ein Fenster definiert, über das die Zeilen berechnet werden die aktuelle Zeile und die N Anzahl der folgenden Zeilen (1 Zeile in diesem Beispiel).

Hier ist die Ergebnismenge.

Im folgenden Beispiel wird die ROWS-Klausel mit UNBOUNDED PRECEDING angegeben. Das Ergebnis ist, dass das Fenster in der ersten Zeile der Partition beginnt.

Hier ist die Ergebnismenge.

Beispiele: Parallel Data Warehouse

E. Verwenden der OVER-Klausel mit der ROW_NUMBER-Funktion

Im folgenden Beispiel wird die ROW_NUMBER für Vertriebsmitarbeiter basierend auf ihrem zugewiesenen Vertriebskontingent zurückgegeben.

Hier ist eine Teilergebnismenge.

F. Verwenden der OVER-Klausel mit Aggregatfunktionen

Die folgenden Beispiele zeigen die Verwendung der OVER-Klausel mit Aggregatfunktionen. In diesem Beispiel ist die Verwendung der OVER-Klausel effizienter als die Verwendung von Unterabfragen.

Hier ist die Ergebnismenge.

Das folgende Beispiel zeigt die Verwendung der OVER-Klausel mit einer Aggregatfunktion in einem berechneten Wert. Beachten Sie, dass die Aggregate mit SalesOrderNumber berechnet werden und der Prozentsatz des gesamten Kundenauftrags für jede Zeile jeder SalesOrderNumber berechnet wird.

Der erste Start dieser Ergebnismenge lautet:

Siehe auch

Aggregatfunktionen (Transact-SQL)
Analysefunktionen (Transact-SQL)
Ausgezeichneter Blog-Beitrag über Fensterfunktionen und OVER auf sqlmag.com von Itzik Ben-Gan