Im folgenden Beispiel wird die SUM-Funktion als eine Fensterfunktion verwendet. Die Abfrage gibt eine Ergebnismenge zurück, die die Daten nach "DepartmentID" partitioniert und dann eine kumulative Summe der Gehälter beginnend mit dem am längsten im Unternehmen beschäftigten Mitarbeiter anzeigt. Die Ergebnismenge enthält nur die Mitarbeiter, die in Kalifornien, Utah, New York oder Arizona leben. Die Spalte "Sum_Salary" liefert die kumulative Gesamtsumme der Mitarbeitergehälter.

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' )
ORDER BY DepartmentID, StartDate;

Die nachstehende Tabelle stellt die Ergebnismenge dieser Abfrage dar. Die Ergebnismenge wird nach "DepartmentID" partitioniert.

Für die DepartmentID 100 ist die kumulative Gesamtsumme der Gehälter von Mitarbeitern in Kalifornien, Utah, New York und Arizona insgesamt $434,091.69 und die kumulative Gesamtsumme für die Mitarbeiter in DepartmentID 200 beträgt $250,200.00.

Weitere Hinweise zur genauen Syntax der SUM-Funktion finden Sie unter SUM-Funktion [Aggregat].

Deltawerte zwischen angrenzenden Zeilen berechnen

Durch die Verwendung von zwei Fenstern - ein Fenster für die aktuelle Zeile, das andere für die nächste Zeile - können Sie Deltawerte bzw. Unterschiede zwischen zwei angrenzenden Zeilen berechnen. Die folgende Abfrage berechnet beispielsweise den Deltawert (Delta) zwischen den Gehältern eines Mitarbeiters und des nächsten Mitarbeiters in den Ergebnissen:

SELECT EmployeeID AS EmployeeNumber, 
   Surname AS LastName,
   SUM( Salary ) OVER ( ORDER BY BirthDate 
      ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) 
      AS CurrentRow,
   SUM( Salary ) OVER ( ORDER BY BirthDate
      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) 
      AS PreviousRow,
   ( CurrentRow - PreviousRow ) AS Delta
FROM Employees
WHERE State IN ( 'NY' );

Beachten Sie, dass SUM nur auf die aktuelle Zeile für das CurrentRow-Fenster angewendet wird, da die Fenstergröße auf ROWS BETWEEN CURRENT ROW AND CURRENT ROW eingestellt wurde. Ebenso wird SUM nur auf die vorherige Zeile für das PreviousRow-Fenster angewendet, da die Fenstergröße auf ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING eingestellt wurde. Der Wert von PreviousRow in der ersten Zeile ist NULL, da es keine vorherige Zeile gibt und somit auch der Deltawert NULL beträgt.

Komplexe Analysen

In der folgenden Abfrage werden die Spitzenverkäufer (definiert nach Gesamtumsatz) für die einzelnen Produkte in der Datenbank aufgeführt:

SELECT s.ProductID AS Products, o.SalesRepresentative, 
     SUM( s.Quantity ) AS total_quantity,
     SUM( s.Quantity * p.UnitPrice ) AS total_sales
  FROM SalesOrders o KEY JOIN SalesOrderItems s 
    KEY JOIN Products p
  GROUP BY s.ProductID, o.SalesRepresentative
  HAVING total_sales = ( 
    SELECT First SUM( s2.Quantity * p2.UnitPrice ) 
        AS sum_sales
      FROM SalesOrders o2 KEY JOIN
        SalesOrderItems s2 KEY JOIN Products p2
      WHERE s2.ProductID = s.ProductID
      GROUP BY o2.SalesRepresentative
      ORDER BY sum_sales DESC )
  ORDER BY s.ProductID;

Diese Abfrage liefert das folgende Ergebnis:

Die ursprüngliche Abfrage wird durch eine korrelierte Unterabfrage gebildet, welche die höchsten Verkäufe für jedes einzelne Produkt ermittelt, da "ProductID" die korrelierte äußere Referenz der Unterabfrage ist. Wie auch in diesem Fall ist eine verschachtelte Abfrage jedoch oft eine kostenträchtige Option. Dies liegt daran, dass die Unterabfrage nicht nur eine GROUP BY-Klausel umfasst, sondern innerhalb dieser auch eine ORDER BY-Klausel. Dies macht es dem Abfrageoptimierer unmöglich, die verschachtelte Abfrage zu einem Join umzuschreiben und dabei die gleiche Semantik beizubehalten. Daher wird die Unterabfrage während der Abfrageausführung für jede abgeleitete Zeile ausgewertet, die im äußeren Block berechnet wird.

Beachten Sie das kostenträchtige Filterprädikat im grafischen Plan: Der Optimierer schätzt, dass 99 % der Ausführungskosten der Abfrage auf diesen Plan-Operator zurückzuführen sind. Der Plan für die Unterabfrage zeigt deutlich, warum der Filter-Operator im Hauptblock so kostenträchtig ist: Die Unterabfrage enthält zwei Nested Loops Joins, einen Hash-basierten GROUP BY-Vorgang und einen Sortiervorgang.

Umschreiben mit einer Rangfunktion

Ein Umschreiben der gleichen Abfrage mithilfe einer Rangfunktion berechnet das identische Ergebnis viel effizienter:

SELECT v.ProductID, v.SalesRepresentative, 
  v.total_quantity, v.total_sales
  FROM ( SELECT o.SalesRepresentative, s.ProductID,
            SUM( s.Quantity ) AS total_quantity,
            SUM( s.Quantity * p.UnitPrice ) AS total_sales,
            RANK() OVER ( PARTITION BY s.ProductID 
              ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC ) 
              AS sales_ranking
           FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
           GROUP BY o.SalesRepresentative, s.ProductID ) 
           AS v
  WHERE sales_ranking = 1
  ORDER BY v.ProductID;

Die umgeschriebene Abfrage ergibt einen einfacheren Plan:

Bedenken Sie, dass ein Fenster-Operator nach der Verarbeitung einer GROUP BY-Klausel und vor der Auswertung der Elemente der SELECT-Liste sowie der ORDER BY-Klausel der Abfrage berechnet wird. Wie im grafischen Plan zu erkennen ist, werden die verknüpften Zeilen nach der Verknüpfung der drei Tabellen durch die Kombination der Attribute "SalesRepresentative" und "ProductID" gruppiert. Daher können die SUM-Aggregatfunktionen "total_quantity" und "total_sales" für alle Kombinationen von "SalesRepresentative" und "ProductID" berechnet werden.

Nach der Auswertung der GROUP BY-Klausel wird die RANK-Funktion berechnet, um mithilfe eines Fensters die Rangfolge der Zeilen im Zwischenergebnis absteigend nach "total_sales" darzustellen. Beachten Sie, dass die WINDOW-Spezifikation eine PARTITION BY-Klausel umfasst. Dadurch wird das Ergebnis der GROUP BY-Klausel neu partitioniert (oder neu gruppiert) - dieses Mal nach "ProductID". Deshalb sortiert die RANK-Funktion die Zeilen für jedes Produkt absteigend nach "total_sales", jedoch nur für die Verkäufer, die dieses Produkt verkauft haben. Durch diese Rangfolge ist es zum Ermitteln der Spitzenverkäufer lediglich erforderlich, das Ergebnis der abgeleiteten Tabelle einzuschränken, um die Zeilen auszuschließen, in denen die Rangfolge nicht 1 ist. Bei gleichen Werten (Zeilen 7 und 8 in der Ergebnismenge), liefert RANK den gleichen Wert. Daher erscheinen die Verkäufer 690 und 949 beide im Endergebnis.

Siehe auch

• SUM-Funktion [Aggregat]