Warum sind SQL-Fensterfunktionen in GROUP BY nicht erlaubt?

Es ist wahr - die SQL-Operationsreihenfolge bedeutet, dass Fensterfunktionen in GROUP BY nicht erlaubt sind. Aber mit diesem speziellen Trick können Sie sie trotzdem verwenden.

SQL-Fensterfunktionen sind eine der leistungsfähigsten Funktionen der Sprache. Allerdings ist die Syntax der Fensterfunktionen nicht einfach zu beherrschen. Sie enthält viele Details, die Anfänger ins Straucheln bringen können. Eine der häufigsten Fallen ist der Versuch, Fensterfunktionen in GROUP BY zu verwenden.

Fehler: Fensterfunktionen sind in GROUP BY nicht erlaubt

Stellen Sie sich vor, Sie haben eine Tabelle, Zwischenpruefung, die die Ergebnisse eines Zwischentests speichert, der von Studenten geschrieben wurde. Die Spalten in der Tabelle sind der Name des Studenten und die Anzahl der Punkte, die der Student bei dem Test erreicht hat. Sie möchten die Studenten auf der Grundlage ihrer Testergebnisse in vier gleich große Gruppen einteilen: die besten 25 % sind in einer Gruppe, die nächsten 25 % in der zweiten Gruppe, die dritten 25 % in der dritten Gruppe und die letzten 25 % in der letzten Gruppe. Dann möchten Sie den Bereich der Punkte und die Anzahl der Studenten in jeder Gruppe sehen.

Hier ist eine Abfrage, die Sie schreiben könnten:

SELECT
 ntile(4) OVER (ORDER BY Punkte),
 min(Punkte),
 max(Punkte),
 count(*)
FROM Zwischenpruefung
GROUP BY ntile(4) OVER (ORDER BY Punkte);

Die Funktion NTILE() teilt die Studenten in Gruppen ein und weist jedem Studenten die Nummer (1-4) seiner Gruppe zu. Anschließend sollen die Studenten anhand der Nummer ihrer Gruppe gruppiert und das Minimum, Maximum und die Anzahl für jede Gruppe berechnet werden. Wenn Sie diese Abfrage ausführen, erhalten Sie jedoch eine Fehlermeldung:

ERROR:  window functions are not allowed in GROUP BY
LINE 7: GROUP BY ntile(4) OVER (ORDER BY Punkte);

Warum Fensterfunktionen in GROUP BY nicht erlaubt sind

Der Grund, warum Fensterfunktionen in GROUP BY nicht zulässig sind, liegt in der Reihenfolge der Operationen in SQL. Die Klauseln einer SQL-Abfrage werden in einer anderen Reihenfolge verarbeitet, als sie in der Abfrage geschrieben sind. Die vollständige Reihenfolge der Operationen in SQL ist:

• FROM, JOIN
• WHERE
• GROUP BY
• Aggregat-Funktionen
• HAVING
• Fenster-Funktionen
• SELECT
• DISTINCT
• UNION/INTERSECT/EXCEPT
• ORDER BY
• OFFSET
• LIMIT/FETCH/TOP

Eine SQL-Abfrage ermittelt zunächst die Tabellen, aus denen abgefragt werden soll, wendet dann die WHERE Filter an und führt die GROUP BY Operation durch. Danach werden Aggregatfunktionen, HAVING Filter und schließlich Fensterfunktionen berechnet. Zu dem Zeitpunkt, zu dem GROUP BY ausgewertet wird, sind die Fensterfunktionen also noch nicht berechnet!

In der Praxis können Sie sich nur in den Klauseln SELECT und ORDER BY direkt auf SQL-Fensterfunktionen beziehen.

Die Reihenfolge der Operationen in SQL ist eines der wichtigsten Dinge, die Sie beachten müssen, wenn Sie eine Abfrage mit Fensterfunktionen schreiben. Wenn Sie Fensterfunktionen nicht oft verwenden, können Sie dies leicht vergessen. Deshalb haben wir eine Erinnerung an die Reihenfolge der Operationen in unseren SQL Fensterfunktionen Spickzettel aufgenommen. Setzen Sie ein Lesezeichen, wenn Sie ab und zu Fensterfunktionen verwenden!

In SQL ist es nicht möglich, Fensterfunktionen in ein GROUP BY einzubauen, aber es gibt eine Möglichkeit, dies zu umgehen ...

Wie man Fensterfunktionen in GROUP BY verwendet

Können wir die Abfrage so ändern, dass sie das gewünschte Ergebnis liefert? Ja. Die Lösung besteht darin, eine Unterabfrage zu verwenden, um die Fensterfunktion zu berechnen, die Sie in der Hauptabfrage verwenden möchten. Hier ist unser geändertes Beispiel:

SELECT 
  quartile,
  min(Punkte),
  max(Punkte),
  count(*)
FROM 
  (SELECT
     ntile(4) OVER (ORDER BY Punkte) AS quartile,
     Punkte
     FROM Zwischenpruefung) Gruppen
GROUP BY quartile;

In der Unterabfrage verwenden wir die Funktion NTILE(), um die Studenten in Gruppen einzuteilen. In der Hauptabfrage berechnen wir die Statistiken: das Minimum, das Maximum und die Anzahl der Studenten.

Eine andere Möglichkeit ist die Verwendung eines gemeinsamen Tabellenausdrucks (CTE = "Common Table Expression"), etwa so:

WITH Gruppen AS (
  SELECT
     ntile(4) OVER (ORDER BY Punkte) AS quartile,
     Punkte
  FROM Zwischenpruefung
) 
SELECT 
  quartile,
  min(Punkte),
  max(Punkte),
  count(*)
FROM Gruppen
GROUP BY quartile;

Die Abfrage ist ähnlich wie die vorherige Version mit der Unterabfrage. Mit einer CTE können wir jedoch die Hilfsabfrage vor der Hauptabfrage definieren, wodurch der Code lesbarer wird.

Beherrschen Sie Fensterfunktionen mit LearnSQL.com

Wenn Sie etwas über Fensterfunktionen lernen möchten, empfehlen wir Ihnen unseren interaktiven Fensterfunktionen-Kurs. Dort lernen Sie, wie man Fensterfunktionen verwendet und wie man häufige Anfängerfehler vermeidet. Wenn Sie sich nicht sicher sind, ob Fensterfunktionen etwas für Sie sind, lesen Sie, warum Sie sie lernen sollten, oder diesen Artikel über unseren Kurs Fensterfunktionen .

Wenn Sie die Fensterfunktionen bereits kennen, laden Sie sich unseren SQL Fensterfunktionen Spickzettel herunter.