ZÄHLEN ÜBER TEILUNG DURCH: Eine Erläuterung mit 3 praktischen Beispielen

In SQL eröffnet die Kombination der Funktion COUNT() mit der OVER()-Klausel und PARTITION BY eine völlig neue Art, Zeilen zu zählen. In diesem Artikel lernen Sie, wie Sie mehrere COUNTs in einer einzigen Abfrage durchführen und Ausdrücke auf der Grundlage des Ergebnisses der COUNT-Funktion berechnen können.

In diesem Artikel wird beschrieben, wie Sie die Funktion COUNT() in Kombination mit den Klauseln OVER() und PARTITION BY verwenden können. Um mehr über dieses Thema zu erfahren, empfehle ich unseren interaktiven Kurs Fensterfunktionen. Er enthält über 200 interaktive Übungen zur Verwendung der Klausel OVER() mit Fensterfunktionen. Nach Abschluss dieses Kurses werden Sie dieses Thema mit Leichtigkeit angehen und sich bei der Verwendung von Fensterfunktionen in SQL-Datenbanken sicher fühlen.

Beispiel 1: Einführung in die Verwendung von COUNT OVER PARTITION BY

Nehmen wir an, wir haben eine Tabelle namens order mit einem Datensatz für jede eingegangene Bestellung in einer Zoohandlung. Die Tabelle hat Spalten wie order_id, order_date, customer_id, salesperson_id, ship_address, ship_state und amount_paid.

Die folgende Abfrage zeigt die Bestellungen, die das Unternehmen in der ersten Hälfte des Jahres 2023 erhalten hat. Beachten Sie, dass wir eine zusätzliche Spalte namens orders_this_customer hinzugefügt haben, die die Gesamtzahl der von jedem Kunden in diesem Zeitraum gesendeten Bestellungen anzeigt.

SELECT 
  order_id,
  order_date,
  customer_id,
  amount_paid,
 COUNT(*) OVER (PARTITION BY customer_id) AS orders_this_customer
FROM order
WHERE order_date >= '2023-01-01' AND order_date <= '2023-06-30';

In SQL wird die Funktion COUNT() allein oder in Kombination mit der Klausel GROUP BY verwendet, um Zeilen in einer Ergebnismenge oder in einer Gruppe von Zeilen zu zählen. OVER() und PARTITION BY wenden die Funktion COUNT() auf eine Gruppe oder Zeilen an, die durch PARTITION BY definiert sind. In unserem Beispiel ist die Gruppe durch die Werte in der Spalte customer_id definiert. Die Funktion COUNT() zählt die Anzahl der Aufträge mit demselben customer_id.

Die Kombination von COUNT() und OVER(PARTITION BY) ist leistungsfähiger als die Funktion COUNT() allein, da sie uns ermöglicht, die Anzahl der Zeilen für jeden spezifischen Wert einer Spalte zu ermitteln.

Bei der Verwendung von OVER() und PARTITION BY brauchen wir die Klausel GROUP BY nicht zu verwenden, um Datensätze zu gruppieren; dies ermöglicht uns, Ergebnismengen auf Zeilenebene zu haben. Jede Zeile in der Ergebnismenge enthält Informationen auf Bestellebene, aber der Wert in der Spalte orders_this_customer enthält die Gesamtzahl der von jedem Kunden aufgegebenen Bestellungen.

Mit anderen Worten, wir kombinieren Daten auf Berichtsebene mit Daten auf Kundenebene in ein und derselben Zeile. Und wir können Daten auf anderen Ebenen hinzufügen, indem wir verschiedene Spalten in der PARTITION BY Klausel verwenden. Nachstehend sehen Sie ein Teilergebnis dieser Abfrage:

Bevor ich diesen Abschnitt beende, möchte ich Ihnen den Artikel Die SQL-Funktion Count erklärt mit 7 Beispielen empfehlen, in dem Sie viele Beispielabfragen mit der Funktion COUNT() finden. Für diejenigen Leser, die tiefer in das Thema einsteigen wollen, empfehle ich den Artikel How to Use the SQL PARTITION BY With OVER, in dem Sie eine klare Erklärung mit Beispielen für die Klauseln OVER() und PARTITION BY finden.

Beispiel #2: Berechnen von Summen auf der Grundlage verschiedener Kriterien

Bevor wir fortfahren, zeigen wir die komplette Tabellenreihenfolge. Jede Zeile der Tabelle steht für eine Bestellung, die in der Tierhandlung eingegangen ist. Eine Bestellungszeile hat unter anderem eine customer_id, eine salesperson_id, eine order_date, eine ship_state und eine ship_city, die anderen Spalten sind selbsterklärend. Hier eine Teilansicht der Tabelle:

Nehmen wir an, dass die Tierhandlung am Ende des Monats einen Bericht mit allen Bestellungen haben möchte. Für jede Zeile sollen auch zwei berechnete Felder angezeigt werden: die Gesamtzahl der an diesem Tag verkauften Bestellungen und die Gesamtzahl der von diesem Verkäufer verkauften Bestellungen. Die Abfrage, um diesen Bericht zu erhalten, lautet:

SELECT 
  order_id,
  order_date,
  customer_id,
  salesperson_id,
  COUNT(1) OVER (PARTITION BY order_date) as orders_per_day,
  COUNT(1) OVER (PARTITION BY salesperson_id) as orders_per_salesperson
FROM order
WHERE order_date between '2023-06-01' AND '2023-06-30';

Das Ergebnis der Abfrage ist unten dargestellt:

In der obigen Abfrage haben wir den Ausdruck COUNT(1) verwendet, der genauso funktioniert wie COUNT(*). Die Klausel PARTITION BY gibt die Kriterien für die Gruppierung der zu zählenden Zeilen an.

Um das Feld orders_per_day zu erhalten, verwenden wir die Klausel OVER (PARTITION BY order_date);, für das Feld orders_per_salesperson verwenden wir die Klausel OVER (PARTITION BY salesperson_id).

Der einzige Unterschied besteht in dem Feld, das wir nach der Klausel PARTITION BY einfügen. Dieses Feld definiert die Kriterien für die Zählung der Zeilen, d. h. alle Zeilen mit demselben Wert in diesem Feld werden zusammen gezählt.

Die Wirkung dieser beiden Felder im Abfrageergebnis besteht darin, dass wir der Zeile zwei Felder mit unterschiedlicher Granularität hinzufügen; das Feld orders_per_salesperson ist eine nach Verkäufer gruppierte Summe, während das Feld orders_per_day eine nach Bestelldatum gruppierte Summe ist.

Hinweis: COUNT(DISTINCT) funktioniert nicht mit OVER(PARTITION BY)

Es gibt einige Variationen der Funktion COUNT(), z. B. COUNT(*), COUNT(1) oder COUNT(DISTINCT). Wenn Sie mehr darüber lesen möchten, lesen Sie unseren Artikel Was ist der Unterschied zwischen COUNT(*), COUNT(1), COUNT(Spaltenname) und COUNT(DISTINCT Spaltenname)? Dort finden Sie eine klare Erklärung dieser verschiedenen Möglichkeiten zur Verwendung der Funktion COUNT().

In einigen Fällen - z. B. wenn wir zählen müssen, wie viele verschiedene Personen an einem bestimmten Tag Bestellungen aufgegeben haben - könnten wir an die Verwendung des Ausdrucks COUNT(DISTINCT customer_id) OVER (PARTITION BY order_date) denken. Es ist jedoch wichtig klarzustellen, dass COUNT(DISTINCT) OVER(PARTITION BY) von den meisten gängigen Datenbanken (wie PostgreSQL, SQL Server und Snowflake, unter anderem) nicht unterstützt wird.

Beispiel #3: Verwendung von COUNT() mit OVER in Ausdrücken

In der nächsten Beispielabfrage werden wir einige Prozentsätze unter Verwendung arithmetischer Ausdrücke mit zwei verschiedenen COUNTs berechnen.

Nehmen wir an, die Marketingabteilung möchte die nächste Marketingkampagne gezielt auf eine bestimmte Gruppe von Kunden ausrichten. Dazu benötigt sie einige Kennzahlen zu den Geschäftsergebnissen: den prozentualen Anteil der aus jedem Bundesland eingegangenen Bestellungen, den prozentualen Anteil der für jede Produktfamilie eingegangenen Bestellungen und die Kombination beider Kennzahlen (z. B. den prozentualen Anteil der aus Texas eingegangenen Bestellungen für die DOG-Produktfamilie). Die Abfrage, um einen solchen Bericht zu erhalten, lautet:

SELECT DISTINCT
  ship_state,
  product_family,
  COUNT(1) OVER () AS total_orders,
  COUNT(1) OVER (PARTITION BY ship_state) state_orders,
  COUNT(1) OVER (PARTITION BY ship_state) / COUNT(1) OVER () AS state_percentage,
  COUNT(1) OVER (PARTITION BY product_family) AS family_orders,
  COUNT(1) OVER (PARTITION BY product_family) / COUNT(1) OVER () AS family_percentage
FROM   order
ORDER BY ship_state, product_family

In der obigen Abfrage verwendet das Feld total_orders den Ausdruck COUNT(1) OVER (), um die Gesamtmenge der vom Unternehmen erhaltenen Bestellungen zu berechnen. Dann wird im Feld state_orders der Ausdruck COUNT() verwendet, um die Menge der Bestellungen zu erhalten, die aus dem Zustand der aktuellen Zeile eingegangen sind (z. B. wenn der Wert ship_state der aktuellen Zeile TX (Texas) ist, werden alle Bestellungen aus Texas angezeigt); dieses Feld ist dem sehr ähnlich, was wir in den vorherigen Beispielen berechnet haben.

Der interessante Teil dieser Beispielabfrage liegt in dem Ausdruck des Feldes state_percentage, der zwei COUNTs verwendet, um einen Prozentsatz zu berechnen. Mit anderen Worten, wir teilen die Anzahl der Aufträge aus dem aktuellen Zustand (der rote Ausdruck) durch die Gesamtzahl der Aufträge (der blaue Ausdruck). In den letzten beiden Feldern wiederholen wir den gleichen Ansatz für das Feld product_family.

Das Ergebnis der Abfrage sehen Sie unten:

Verwendung von OVER PARTITION BY mit COUNT() und anderen Funktionen

In diesem Artikel haben wir die Funktion COUNT() in Kombination mit den Klauseln OVER und PARTITION BY behandelt. In SQL gibt es jedoch viele andere Funktionen, die mit PARTITION BY kombiniert werden können. Diese Funktionen werden als Fensterfunktionen bezeichnet, und Sie finden hervorragendes Lernmaterial zu ihnen in unserem KursFensterfunktionen . Er enthält über 200 interaktive Übungen und bietet ein komplettes Tutorial zu Fensterfunktionen.

Wenn Sie die Fensterfunktionen bereits kennen und mehr Übung brauchen, empfehle ich Ihnen unser Fensterfunktionen Practice Set. Dieser Kurs wurde entwickelt, um umfassende Übungen zu Fensterfunktionen an realen Beispielen anzubieten. Die 100 Übungen zu drei verschiedenen Datensätzen ähneln den Problemen, die Sie in der Praxis vorfinden.

Wenn Sie nach weiteren Ressourcen zu Fensterfunktionen suchen, schauen Sie sich unser SQL Fensterfunktionen Cheat Sheet und Die 10 besten SQL-Fensterfunktionen Interviewfragen an, wo Sie einiges an Material finden, um sich auf ein SQL-Vorstellungsgespräch vorzubereiten. Schlagen Sie zu, lernen Sie SQL, und investieren Sie in sich!