Wie man SUM() mit OVER(PARTITION BY) in SQL verwendet

Entdecken Sie reale Anwendungsfälle der Funktion SUMME() mit der Klausel OVER(PARTITION BY). Lernen Sie die Syntax und sehen Sie sich 5 verschiedene Beispiele an.

Wir verwenden SQL-Fensterfunktionen, um Operationen mit Datengruppen durchzuführen. Zu diesen Operationen gehören die mathematischen Funktionen SUM(), COUNT(), AVG(), und weitere. In diesem Artikel wird erklärt, was SUM() mit OVER(PARTITION BY) in SQL macht. Wir zeigen Ihnen die häufigsten Anwendungsfälle in realen Anwendungen, um das Verhältnis zwischen dem Wert einer einzelnen Zeile und dem Gesamtwert zu bestimmen, laufende Summen zu berechnen und eine benutzerdefinierte Bestellsumme zu finden, die Rabatte für bestimmte Produkte enthält.

Um eine ausführliche Anleitung zur Verwendung von SUM() mit OVER() und anderen Fensterfunktionen zu erhalten, besuchen Sie unseren interaktiven Fensterfunktionen Kurs. Er erklärt alle SQL-Fensterfunktionskonzepte im Detail und enthält über 200 Übungen.

Fangen wir an.

Die OVER()-Klausel in SQL

In SQL wird die OVER() Klausel verwendet, um Fensterfunktionen einzuführen. Die allgemeine Syntax lautet:

SELECT …
  <window function> OVER(...)
…

OVER() teilt der Datenbank mit, dass wir Fensterfunktionen verwenden wollen. Die Fensterfunktion kann eine Aggregatfunktion sein, wie SUM(), oder eine andere Fensterfunktion.

Eine Fensterfunktion arbeitet mit einem "Fensterrahmen" oder einer Reihe von Zeilen, die mit der aktuellen Zeile in Beziehung stehen. OVER() definiert den Fensterrahmen für jede Zeile. Eine leere OVER() Klausel teilt der Datenbank mit, dass die gesamte Ergebnismenge der Fensterrahmen ist.

Sehen wir uns ein Beispiel für die SUM() OVER() -Syntax an. Wir verwenden die employees Tabelle aus den Beispieldaten, die von Oracle Live SQL bereitgestellt werden. Diese Tabelle besteht aus den folgenden Spalten:

• emp_id ist die Kennung des Mitarbeiters.
• name ist der Name des Mitarbeiters.
• job ist die Stellenbezeichnung.
• dept_id Kennung der Abteilung.
• salary ist das Gehalt des Mitarbeiters.

Wir können die Funktion SUM() mit der Klausel OVER() verwenden, um das Gesamtgehalt aller Mitarbeiter zu erhalten und das Gesamtgehalt neben dem Gehalt jedes Mitarbeiters anzuzeigen.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary, 
  SUM(salary) OVER() AS total_salary
FROM employees;

Die Funktion SUM() wird für jede Zeile der Tabelle ausgeführt. Jedes Mal, wenn sie aufgerufen wird, holt sie die Gehaltsinformationen aus allen Zeilen der Tabelle, um den Gesamtbetrag des Gehalts zu berechnen. Das Gesamtgehalt wird neben den Angaben zu jeder einzelnen Zeile angezeigt. Die Details der einzelnen Zeilen werden beibehalten und neben dem Gesamtgehalt angezeigt.

In diesem Beispiel ist der Fensterrahmen (die Menge der Zeilen, mit denen SUM() arbeitet) der gesamte Datensatz. Sie können zusätzliche Klauseln in OVER() hinzufügen, um den Fensterrahmen zu ändern.

Beispiel 1A: Berechnen der Summe für jede Gruppe - SUM() mit OVER(PARTITION BY ...)

Die Klausel OVER() kann Details über die Art und Weise enthalten, wie die Daten partitioniert werden sollen. Wir verwenden die PARTITION BY Klausel in OVER(), um die Daten in Partitionen oder Gruppen aufzuteilen. Die Verwendung von PARTITION BY ähnelt der Verwendung von GROUP BY, da die Zeilen auf der Grundlage der Werte einiger Spalten in Gruppen aufgeteilt werden. Bei Verwendung von SUM() OVER(PARTITION BY …) können wir die Summe der Werte für jede Gruppe oder Partition der Daten berechnen. Zum Beispiel können wir das Gesamtgehalt für jede Abteilung berechnen:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary
FROM employees;

Hier ist das Ergebnis:

Wir verwenden SUM(salary) OVER(PARTITION BY dept_id), um das Gesamtgehalt pro Abteilung zu ermitteln. Die Klausel PARTITION BY teilt die Zeilen in Gruppen auf der Grundlage der Spalte dept_id. Zeilen mit dept_id gleich 10 kommen in eine Gruppe (in der obigen Tabelle gelb markiert), die Zeilen mit dept_id gleich 20 kommen in eine andere Gruppe (grün markiert) und schließlich die Zeilen mit dept_id gleich 30 in eine weitere Gruppe (rot markiert). Die Funktion SUM() berechnet die Summe der Zeilen in jeder Gruppe.

Beachten Sie, dass Sie bei der Verwendung von SUM() OVER(PARTITION BY) die Details der einzelnen Zeilen behalten. Sie können zum Beispiel die Details des Angestellten namens Ford sehen: seine Position, sein Gehalt und wie es im Vergleich zu den Gesamtgehältern in seiner Abteilung steht.

Dies ist die typischste Verwendung von SUM() OVER(PARTITION BY): Sie berechnen den Summenwert für jede Datengruppe und behalten die Details der einzelnen Zeilen. Schauen wir uns ein ähnliches Beispiel an.

Beispiel 1B: Berechnung des Gehalts für jede Stellenbezeichnung

Wir können SUM(Gehalt) OVER(PARTITION BY job) verwenden, um das Gesamtgehalt pro Stellenbezeichnung zu ermitteln. Schauen wir es uns an:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY job) AS job_total_salary
FROM employees;

Hier ist das Ergebnis:

Diesmal werden die Zeilen nach dem Wert der Stelle und nicht nach der Abteilungs-ID gruppiert. Mitarbeiter mit der gleichen Position werden in einer Gruppe zusammengefasst, und wir berechnen das Gesamtgehalt der Mitarbeiter in dieser Position. Die Funktion SUM() wird auf alle Gehälter in jeder Gruppe angewandt: Das Gesamtgehalt für die Gruppe "Manager" ist die Summe aus 2450, 2850 und 2975, also den Gehältern der drei Manager in unserer Tabelle.

Beispiel 2: Berechnen des Verhältnisses zwischen dem Wert einer einzelnen Zeile und der Gesamtsumme

Normalerweise wollen wir den Vergleich zwischen jeder einzelnen Zeile und der Gesamtsumme sehen. Berechnen wir also den prozentualen Anteil jedes einzelnen Gehalts an der Gesamtsumme der Gehälter in der jeweiligen Abteilung.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Diesmal werden die Zeilen nach dem Wert job und nicht nach der Abteilungs-ID gruppiert. Mitarbeiter mit der gleichen Position werden in einer Gruppe zusammengefasst, und wir berechnen das Gesamtgehalt der Mitarbeiter in dieser Position. Die Funktion SUM() wird auf alle Gehälter in jeder Gruppe angewandt: Das Gesamtgehalt für die Gruppe "Manager" ist die Summe von 2450, 2850 und 2975, die die Gehälter der drei Manager in unserer Tabelle sind.

Beispiel 2: Berechnen des Verhältnisses zwischen dem Wert einer einzelnen Zeile und der Gesamtsumme

Normalerweise wollen wir den Vergleich zwischen jeder einzelnen Zeile und der Gesamtsumme sehen. Berechnen wir den prozentualen Anteil jedes einzelnen Gehalts an den Gesamtgehältern in der jeweiligen Abteilung.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Wir können zum Beispiel sehen, dass der Analyst Scott ein individuelles Gehalt von 3000 hat; das sind 27,59 % der Gesamtgehälter in seiner Abteilung.

Nachfolgend eine Aufschlüsselung der Funktionen, die zur Erfüllung dieser Aufgabe verwendet werden:

• Wir nehmen jedes einzelne Gehalt und teilen es durch das Gesamtgehalt der Abteilung:

salary / SUM(salary) OVER(PARTITION BY dept_id)

• Um einen Prozentsatz zu erhalten, multiplizieren wir ihn mit 100%:
  
  • 0 * salary / SUM(salary) OVER(PARTITION BY dept_id)
• Anschließend verwenden wir die Funktion ROUND(), um zwei Dezimalstellen zu erhalten:

ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2)

Die Berechnung des Verhältnisses zwischen der einzelnen Zeile und der Summe für eine Gruppe ist ein weiterer häufiger Anwendungsfall von SUMME() mit OVER(PARTITION BY). Auf ähnliche Weise können Sie die Differenz zwischen der Gesamtsumme für die Gruppe und der einzelnen Zeile berechnen.

Was ist der Unterschied zwischen SUM() mit OVER(PARTITION BY …) und der Verwendung von SUM() mit GROUP BY?

Die Klausel PARTITION BY erfüllt eine ähnliche Aufgabe wie die Klausel GROUP BY. Sowohl OVER(PARTITION BY) als auch GROUP BY unterteilen den Datensatz in Partitionen oder Gruppen. Wenn Sie die Funktion SUM() verwenden, berechnen beide Ausdrücke die Summe für jede Gruppe. Welchen Ausdruck sollten Sie also wählen?

Bei der Verwendung von GROUP BY werden die Details der einzelnen Zeilen eingeklappt. Schauen Sie sich das an:

SELECT 
  job, 
  SUM(salary) AS total_salary
FROM employees
GROUP BY job;

Hier erhalten wir den Gesamtwert der Gehälter für jede Stellenbezeichnung, ohne die Gehälter der einzelnen Mitarbeiter zu berücksichtigen.

Wenn Sie jedoch den Gesamtsummenwert mit den Werten der einzelnen Zeilen vergleichen möchten (z. B. um das Verhältnis zwischen dem Einzelwert und dem Gesamtwert zu berechnen), sollten Sie OVER(PARTITION BY …) verwenden. Hier werden alle einzelnen Zeilen zusammen mit dem Gesamtsummenwert für jede Zeile zurückgegeben. Dieser Gesamtsummenwert kann unterschiedlich sein, je nachdem, zu welcher Partition die Zeile gehört.

SELECT 
  name, 
  job, 
  salary, 
  SUM(salary) OVER(PARTITION BY job) AS total_salary
FROM employees;

Der Wert total_salary entspricht den Ergebnissen der vorherigen Abfrage mit GROUP BY. Hier können Sie jedoch auch die einzelnen Gehälter sehen. Zum Beispiel gibt es zwei Analysten, die insgesamt 6000 verdienen; jeder von ihnen verdient 3000.

Als Faustregel gilt: Wenn Sie nur den Summenwert für jede Gruppe wissen wollen und nicht an den Details für jede einzelne Zeile interessiert sind, sollten Sie die Klausel GROUP BY verwenden. Wenn Sie sowohl an der Summe für jede Gruppe als auch an den Details der einzelnen Zeilen interessiert sind, sollten Sie SUM() OVER(PARTITION BY) verwenden.

Beispiel 3: Berechnen einer laufenden Summe mit SUM() mit OVER(PARTITION BY)

Eine weitere häufige Verwendung der Syntax SUM() OVER(...) ist die Berechnung der laufenden Summe.

Eine laufende Summe ist die kumulative Summe der vorherigen Zahlen in einer Spalte. Wir verwenden laufende Summen, um Werte zu berechnen, die sich im Laufe der Zeit ansammeln. Mit einer laufenden Summe können Sie beispielsweise die monatliche mobile Datennutzung berechnen, indem Sie den Wert jedes folgenden Tages zur Summe der Werte der vorherigen Tage addieren. In ähnlicher Weise können Sie berechnen, wie die Anzahl der registrierten Nutzer jeden Tag steigt oder wie der Gesamtumsatz mit jeder Transaktion zunimmt.

Wir verwenden die Syntax SUM() mit OVER(PARTITION BY … ORDER BY …), um die laufende Summe zu berechnen.

Schauen wir uns ein Beispiel an. Wir verwenden die orders Tabelle aus den Beispieldaten von Oracle Live SQL. Diese Tabelle speichert historische Auftragsdaten. Jeder Auftrag hat ein Datum (order_date), einen Vertriebsmitarbeiter (sales_rep_id) und einen Gesamtwert (order_total).

Hier berechnen wir den laufenden Gesamtwert für jeden Vertriebsmitarbeiter:

SELECT 
  order_date, 
  sales_rep_id, 
  order_total,
  SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date) 
          AS running_total
FROM orders;

Dazu partitionieren wir unsere Tabelle nach Vertriebsmitarbeitern und ordnen dann jede Partition nach Datum:

	SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date)

Das neue Syntaxelement ist hier ORDER BY. Es legt fest, wie die Zeilen in jedem Fensterrahmen sortiert werden. Die Funktion SUM() wird auf jede Zeile angewendet; sie addiert den aktuellen Wert order_total zum vorherigen Wert running_total; laufende Summen werden für jede Partition (hier für jede Vertreter-ID) separat berechnet.

Schauen wir uns den Vertriebsmitarbeiter mit der ID 153 genauer an. Sein erster Auftrag war am^29. März und hatte einen Gesamtwert von 10794,6. Zu diesem Zeitpunkt ist die laufende Summe gleich dem Auftragswert. Die zweite Bestellung erfolgte am¹⁶. August für 78279,6; jetzt ist die laufende Summe gleich der Summe der Werte der ersten und zweiten Bestellung (10794,6 + 78279,6 = 89074,2). Nach der dritten Bestellung ist die laufende Summe gleich der vorherigen laufenden Summe plus dem Wert der dritten Bestellung (89074,2 + 129 = 89203,2). Für den Vertriebsmitarbeiter mit der ID 154 ist der Vorgang ähnlich.

Das Berechnen einer laufenden Summe ist ein gängiges Muster bei der Verwendung von SQL für die Datenanalyse. Wie man eine laufende Summe in SQL berechnet, können Sie an anderer Stelle in unserem Blog nachlesen.

Beispiel 4: Zählen von Objekten in benutzerdefinierten Kategorien

Die Funktion SUM() wird oft mit der Anweisung CASE WHEN kombiniert, um Objekte in benutzerdefinierten Kategorien zu zählen. Sie möchten z. B. das Gesamtgehalt der Mitarbeiter in Führungspositionen in einer Abteilung berechnen und es neben den Details der einzelnen Mitarbeiter anzeigen. Dies können Sie erreichen, indem Sie SUM() OVER(PARTITION BY) in Kombination mit CASE WHEN verwenden.

Schauen wir uns zunächst die Anweisung CASE WHEN an. Sie ähnelt der Anweisung if, die in vielen Programmiersprachen verwendet wird. Wir verwenden sie, um den Wert eines Ausdrucks in verschiedenen Situationen oder Fällen zu definieren.

Im folgenden Beispiel verwenden wir die Anweisung CASE WHEN, um jeden Mitarbeiter als Führungskraft (Manager und Präsidenten) oder als normalen Mitarbeiter (alle anderen Stellen) zu identifizieren. Schauen Sie sich das an:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary
FROM employees;

Die Anweisung CASE wertet alle Bedingungen von WHEN aus. Wenn sie die passende Bedingung findet, gibt sie den Wert in der Verzweigung THEN zurück. Findet sie die passende Bedingung nicht, gibt sie den Wert zurück, der hinter ELSE angegeben ist. In unserem Beispiel werden Mitarbeiter in der Position des Präsidenten oder Managers als 'Management' bezeichnet. Allen anderen Stellen wird die Bezeichnung "Regular" zugewiesen. Hier ist das Ergebnis der Abfrage:

Weitere Informationen über CASE finden Sie in unserem Artikel Wie man CASE in SQL verwendet.

Sie können auch die Anweisung CASE mit SUM() verwenden, um Werte in benutzerdefinierten Kategorien zu summieren. Hier ist die Abfrage:

SELECT
  dept_id,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary
    ELSE 0 END) 
    AS dept_management_salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN 0
    ELSE salary END) 
    AS dept_regular_salary
FROM employees
GROUP BY dept_id;

Für Mitarbeiter in Führungspositionen gibt die erste Anweisung CASE den Wert der Spalte salary zurück. Die Funktion SUM() in Kombination mit GROUP BY summiert dann alle Gehälter für Angestellte in Führungspositionen mit der gleichen dept_id. Auf diese Weise wird das gesamte Managementgehalt pro Abteilung berechnet. Die zweite Anweisung CASE berechnet das gesamte Abteilungsgehalt für alle regulären Mitarbeiter.

Sie können das Gesamtgehalt für Mitarbeiter in leitender Position in einer Abteilung berechnen und es neben den Mitarbeiterdetails mit SUM() und PARTITION BY anzeigen:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary 
    ELSE 0 END) OVER(PARTITION BY dept_id) AS dept_management_salary
FROM employees;

Die Zeilen werden anhand der Spalte dept_id in Gruppen eingeteilt. Es gibt drei Gruppen, eine für jede Abteilung. Die Funktion SUM() wird auf den Ausdruck CASE WHEN angewendet. Anstatt das Gesamtgehalt in jeder Abteilung zu berechnen, berechnen wir das Gesamtgehalt für Angestellte in Führungspositionen in der Abteilung. Die Funktion CASE gibt 0 für normale Angestellte (die Summe wird nicht erhöht) und den Gehaltswert für leitende Angestellte zurück. Sie können dieses Muster in vielen verschiedenen Situationen verwenden. Im nächsten Abschnitt werden wir ein weiteres Beispiel für die Kombination von CASE WHEN mit SUM() und OVER(PARTITION BY) sehen.

Beispiel 5: Berechnen einer Auftragssumme mit Rabatten unter Verwendung von SUM() mit CASE WHEN

In diesem Beispiel wollen wir den Gesamtwert für jede Bestellung berechnen, einschließlich der Rabatte für einige Bestellpositionen. Wir verwenden die Syntax SUM() OVER(PARTITION BY …), um alle Positionswerte pro Bestellung zu summieren. Und um die Produktrabatte einzubeziehen, verwenden wir die Anweisung CASE WHEN.

Wir verwenden die order_items Tabelle aus den Beispieldaten, die von Oracle Live SQL bereitgestellt werden. In dieser Tabelle werden alle Bestellpositionen (product_id) gespeichert, die zu jeder Bestellung (order_id) gehören. Sie enthält Informationen über den Stückpreis des Produkts (unit_price) und die Bestellmenge (quantity).

Nun wollen wir die Bestellwerte berechnen: einen für jedes Produkt in der Bestellung und einen Gesamtwert für die Bestellung; der Gesamtwert der Bestellung sollte alle auf die Produkte angewandten Rabatte enthalten.

Die Gesamtsumme der Bestellung ist gleich dem Stückpreis multipliziert mit der Bestellmenge. Wenn jedoch ein Rabatt auf ein Produkt angewandt wird, verwenden wir eine CASE WHEN Erklärung, um ihn zu berücksichtigen.

SELECT 
  order_id, 
  product_id, 
  unit_price, 
  quantity,   

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id, product_id) 
        AS order_product_total_with_discount,

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id) 
        AS order_total_with_discount    

FROM order_items;

Um den Gesamtbetrag pro Produkt in einer Bestellung zu berechnen, verwenden wir die folgende Syntax:

SUM(CASE 
       WHEN product_id=3143 THEN unit_price*quantity*0.8 -- 20% discount 
       WHEN product_id=3106 THEN unit_price*quantity*0.6 -- 40% discount 
       ELSE unit_price*quantity END)
OVER(PARTITION BY order_id, product_id) AS order_product_total_with_discount

Wir übergeben die Anweisung CASE WHEN als Argument an die Funktion SUM(); wenn die Produkt-ID der aktuellen Zeile 3143 ist, dann wenden wir einen Rabatt von 20 % an; für die Produkt-ID 3106 beträgt der Rabatt 40 %. Als Nächstes partitionieren wir den Datensatz nach Bestell-ID und Produkt-ID, um die Gesamtwerte für jedes Produkt in einer Bestellung zu erhalten.

Beachten Sie, dass wir zwei verschiedene PARTITION BY Klauseln in der Abfrage verwenden. Um den Gesamtwert des Produkts zu berechnen, partitionieren wir den Datensatz nach Bestell-ID und Produkt-ID. Um den Gesamtwert pro Bestellung zu berechnen, partitionieren wir den Datensatz nur nach der Bestell-ID. Der Gesamtwert der Bestellung ist für alle Zeilen mit der gleichen Bestell-ID gleich. Das heißt, wenn Sie alle Produktgesamtwerte einer bestimmten Bestellung zusammenzählen, erhalten Sie den Gesamtwert der Bestellung. Für die Bestell-ID 2355 ergibt sich zum Beispiel Folgendes: 9200 + 10545 + 16337.2 + 3572 + 3230 + 211.2 + 216.7 + 4975 + 46226.4 = 94513.5.

Hier sind einige Beispiele für die Funktion SUMME() mit CASE WHEN.

Weiter und SUM() mit OVER() und PARTITION BY

Da Sie nun wissen, wie die Fensterfunktion SUMME() und ihre verschiedenen Syntaxoptionen verwendet werden, können Sie sie selbst ausprobieren.

Wir haben Beispiele für die Berechnung des prozentualen Anteils eines einzelnen Zeilenwerts an der Gesamtsumme der Partition vorgestellt und gezeigt, wie man die laufende Summe und die benutzerdefinierte Auftragssumme berechnet. Wir ermutigen Sie, mit Ihren eigenen Daten zu üben.

Wenn Sie mehr erfahren möchten, besuchen Sie unseren interaktiven Fensterfunktionen Kurs, in dem alle Konzepte der Fensterfunktionen im Detail erklärt werden. Oder schauen Sie sich unser Fensterfunktionen Cheat Sheet an, wenn Sie eine schnelle und einfache Referenz für SQL-Fensterfunktionen benötigen.

Viel Erfolg!