Fenster definieren

Sie können die SQL-Fenstererweiterungen verwenden, um die Grenzen eines Fensters und die Partitionierung und Reihenfolge der Eingabezeilen zu konfigurieren. Logischerweise werden, im Rahmen der Semantik der Ergebnisberechnung einer Abfragespezifikation, die Partitionen erstellt, nachdem die durch die GROUP BY-Klausel definierten Gruppen erstellt wurden, jedoch vor der Auswertung der endgültigen SELECT-Liste und der ORDER BY-Klausel der Abfrage. Daher ist die Reihenfolge der Auswertung der Klauseln in einer SQL-Anweisung die folgende:

FROM
WHERE
GROUP BY
HAVING
WINDOW
DISTINCT
ORDER BY

Wenn Sie Ihre Abfrage formulieren, sollten Sie die Auswirkung der Reihenfolge der Auflösung berücksichtigen. Es darf beispielsweise kein Prädikat für einen Ausdruck, der eine Fensterfunktion referenziert, in demselben SELECT-Abfrageblock verwendet werden. Wenn Sie allerdings den Abfrageblock in eine abgeleitete Tabelle platzieren, können Sie ein Prädikat für die abgeleitete Tabelle angeben. Die folgende Abfrage schlägt mit einer Meldung fehl, die angibt, dass der Fehler von einem Prädikat verursacht wurde, das für eine Fensterfunktion angegeben wird:

SELECT DepartmentID, Surname, StartDate, Salary,
       SUM( Salary ) OVER ( PARTITION BY DepartmentID
          ORDER BY StartDate
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Sum_Salary" 
   FROM Employees
   WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
      AND DepartmentID IN ( '100', '200' )
   GROUP BY DepartmentID, Surname, StartDate, Salary
   HAVING Salary > 0 AND "Sum_Salary" > 200
   ORDER BY DepartmentID, StartDate;

Verwenden Sie eine abgeleitete Tabelle (DT, derived table) und geben Sie für sie ein Prädikat an, um die gewünschten Ergebnisse zu erhalten.

SELECT * FROM ( SELECT DepartmentID, Surname, StartDate, Salary,
                       SUM( Salary ) OVER ( PARTITION BY DepartmentID
                         ORDER BY StartDate
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Sum_Salary"
                  FROM Employees
                  WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
                     AND DepartmentID IN ( '100', '200' )
                  GROUP BY DepartmentID, Surname, StartDate, Salary
                  HAVING Salary > 0
                  ORDER BY DepartmentID, StartDate ) AS DT
   WHERE DT.Sum_Salary > 200;

Da die Fensterpartitionierung nach einem GROUP BY-Operator erfolgt, stehen die Ergebnisse aller Aggregatfunktionen wie etwa SUM, AVG oder VARIANCE den Berechnungen zur Verfügung, die für eine Partition durchgeführt werden. Fenster bieten daher eine weitere Möglichkeit, Gruppierungen und Sortierungen durchzuführen, zusätzlich zu den GROUP BY- und ORDER BY-Klauseln einer Abfrage.

Eine Fensterspezifikation definieren

Bei der Definition eines Fensters, in dem eine Fensterfunktion ausgeführt wird, geben Sie eine oder mehrere der folgenden Einstellungen an:

Partitionierung (PARTITION BY-Klausel) Die PARTITION BY-Klausel legt fest, wie Eingabezeilen gruppiert werden. Ohne diese Einstellung wird die gesamte Eingabe als eine einzige Partition behandelt. Je nach Einstellung kann eine Partition aus einer, mehreren oder allen Eingabezeilen bestehen. Daten aus zwei Partitionen werden niemals vermischt. Dies bedeutet, wenn ein Fenster die Grenze zwischen zwei Partitionen erreicht, schließt es die Verarbeitung der Daten in der einen Partition ab, bevor es mit der Verarbeitung der Daten in der nächsten Partition beginnt. Die Größe eines Fensters kann daher am Beginn und am Ende einer Partition unterschiedlich sein, abhängig davon, wie die Grenzen für das Fenster definiert sind.
Sortierung (ORDER BY-Klausel) Die ORDER BY-Klausel legt fest, wie die Eingabezeilen sortiert werden, bevor sie durch die Fensterfunktion verarbeitet werden. Die ORDER BY-Klausel ist nur erforderlich, wenn Sie die Grenzen mithilfe einer RANGE-Klausel festlegen, oder wenn eine Rangfunktion auf das Fenster verweist. Andernfalls ist die ORDER BY-Klausel optional. Wenn sie weggelassen wird, verarbeitet der Datenbankserver die Eingabezeilen auf die effizienteste Art.

Grenzen (RANGE- und ROWS-Klauseln) Die aktuelle Zeile bietet den Referenzpunkt zum Bestimmen der Start- und Endzeilen eines Fensters. Sie können die RANGE- und ROWS-Klauseln der Fensterdefinition verwenden, um diese Grenzen festzulegen. RANGE definiert das Fenster als Bereich von Datenwerten, ausgehend vom Wert in der aktuellen Zeile. Wenn Sie daher RANGE angeben, müssen Sie auch eine ORDER BY-Klausel angeben, da Bereichsberechnungen erfordern, dass die Daten sortiert sind.

ROWS definiert das Fenster als Anzahl von Zeilen, ausgehend von der aktuellen Zeile.

Da RANGE eine Menge von Zeilen als Bereich von Datenwerten definiert, können die Zeilen, die in ein RANGE-Fenster einbezogen werden, Zeilen jenseits der aktuellen Zeile enthalten. Dies unterscheidet sich davon, wie ROWS gehandhabt wird. Das folgende Diagramm veranschaulicht den Unterschied zwischen den ROWS- und RANGE-Klauseln:

Vergleich der Fenstergröße zwischen einer ähnlichen ROWS- und RANGE-Spezifikation, wobei die letztere mehr Zeilen enthält.

Innerhalb der ROWS- und RANGE-Klauseln können Sie (optional) die Start- und Endzeile des Fensters relativ zur aktuellen Zeile angeben. Um dies zu tun, verwenden Sie die PRECEDING-, BETWEEN- und FOLLOWING-Klauseln. Diese Klauseln verwenden Ausdrücke und die Schlüsselwörter UNBOUNDED und CURRENT ROW. Wenn bei einem Fenster keine Grenzen definiert sind, werden die Standard-Fenstergrenzen folgendermaßen festgelegt:

Wenn die Fensterspezifikation eine ORDER BY-Klausel enthält, ist dies gleichwertig mit der Angabe von RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Wenn die Fensterspezifikation keine ORDER BY-Klausel enthält, ist dies gleichwertig mit der Angabe von ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Die folgende Tabelle enthält einige Beispiel-Fenstergrenzen und eine Beschreibung der Zeilen, die sie enthalten:

Spezifikation	Bedeutung
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Am Anfang der Partition starten und mit der aktuellen Zeile aufhören. Verwenden Sie dies, wenn Sie kumulative Ergebnisse berechnen, etwa kumulative Summen.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	Alle Zeilen in der Partition verwenden. Verwenden Sie dies, wenn der Wert einer Aggregatfunktion für jede Zeile einer Partition identisch sein soll.
ROWS BETWEEN x PRECEDING AND y FOLLOWING	Ein bewegliches Fenster fester Größe erstellen, beginnend bei einem Abstand von x von der aktuellen Zeile und endend bei einem Abstand von y von der aktuellen Zeile (inklusive). Verwenden Sie dieses Beispiel, wenn Sie einen veränderlichen Durchschnitt oder die Wertedifferenz zwischen angrenzenden Zeilen berechnen wollen. Bei einem beweglichen Fenster mit mehr als einer Zeile treten beim Berechnen der ersten und der letzten Zeile in der Partition Nullwerte auf. Der Grund dafür ist, dass es bei der ersten oder letzten Zeile der Partition keine vorhergehenden bzw. folgenden Zeilen gibt, die in der Berechnung verwendet werden können. Daher wird stattdessen NULL verwendet.
ROWS BETWEEN CURRENT ROW AND CURRENT ROW	Ein Fenster aus einer Zeile, nämlich der aktuellen Zeile.
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING	Ein Fenster erstellen, das auf den Werten in den Zeilen basiert. Beispiel: Für die aktuelle Zeile enthält die Spalte, die in der ORDER BY-Klausel angegeben wurde, den Wert 10. Wenn Sie die Fenstergröße mit `RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING` festlegen, wird das Fenster so groß eingerichtet, dass die erste Zeile eine 5 in dieser Spalte enthält und die letzte Zeile im Fenster eine 15. Wenn sich das Fenster in der Partition nach unten bewegt, kann es größer oder kleiner werden, da es sich in der Größe der Bereichsspezifikation anpasst.

Formulieren Sie Ihre Fensterspezifikation so explizit wie möglich. Ansonsten geben die Standardwerte möglicherweise nicht die Ergebnisse zurück, die Sie erwarten.

Verwenden Sie die RANGE-Klausel, um Probleme zu vermeiden, die durch Lücken bei der Eingabe für eine Fensterfunktion bewirkt werden, wenn die Wertemenge nicht kontinuierlich ist. Wenn Fenstergrenzen mit einer RANGE-Klausel festgelegt werden, behandelt der Datenbankserver automatisch angrenzende Zeilen und Zeilen mit mehrfach vorhanden Werten.

RANGE verwendet Ganzzahlen ohne Vorzeichen. Eine Kürzung des RANGE-Ausdrucks kann abhängig von der Domäne des ORDER BY-Ausdrucks und der Domäne des Wertes erfolgen, der in der RANGE-Klausel angegeben wurde.

Legen Sie die Grenzen eines Fensters nicht fest, wenn Sie eine Rang- oder Zeilennummerierungsfunktion benutzen.

Kommentieren Sie diese Seite in DocCommentXchange.
Senden Sie uns Feedback über diese Seite via E-Mail.