filter – selektive Aggregate


Die filter-Klausel erweitert Aggregatfunktionen (sum, avg, count, …) um eine zusätzlichen where-Klausel. Das Ergebnis der Aggregatfunktion wird nur aus den Zeilen gebildet, die auch diese zusätzlich where-Klausel erfüllen.

Syntax

Die filter-Klausel wird Aggregatfunktionen nachgestellt:

SUM(<Ausdruck>) FILTER(WHERE <Bedingung>)

Mit Ausnahme von Unterabfragen und Window-Funktionen0 ist in der <Bedingung> der filter-Klausel alles erlaubt, das auch in einer normalen where-Klausel erlaubt ist.

Die filter-Klausel ist auf alle Aggregatfunktionen anwendbar: neben den altbekannten wie sum und count also auch auf array_agg und sortierte Mengenfunktionen wie listagg.

Wenn eine Aggregatfunktion als Window-Funktion (over-Klausel) genutzt werden, ist die syntaktische Reihenfolge: Aggregatfunktion, filter-Klausel, over-Klausel:

SUM(...) FILTER(WHERE ...) OVER (...)

Die filter-Klausel ist jedoch nicht generell vor over erlaubt: filter ist nur nach Aggregatfunktionen erlaubt, nicht aber nach anderen window-Funktionen wie zum Beispiel Rangfunktionen (rank, dense_rank, …).

Anwendungsfälle

Die folgenden Artikel beschreiben häufige Anwendungen von filter:

Kompatibilität

Die filter-Klausel wurde mit SQL:2003 als Teil der optionalen Funktion „Advanced OLAP operations“ (T612) eingeführt. Sie wird heute kaum unterstützt, kann aber leicht mit case emuliert werden (siehe Konforme Alternativen).

Konforme Alternativen

Die filter-Klausel kann generell durch einen case-Ausdruck in der Aggregatfunktion umgesetzt werden. Dazu wird die Filterbedingung in die when-Klausel, der zu aggregierende Wert in die then-Klausel übernommen. Da Aggregatfunktionen null generell nicht berücksichtigen1, genügt die implizite else null-Klausel, um nicht passende Zeilen zu ignorieren. Die folgenden Ausdrücke sind daher gleichwertig.

SUM(<Ausdruck>) FILTER(WHERE <Bedingung>)
SUM(CASE WHEN <Bedingung> THEN <Ausdruck> END)

Lediglich count(*) ist noch als Spezialfall zu nennen, da man „*“ nicht direkt in die then-Klausel schreiben kann. Es genügt jedoch, den Stern durch einen nicht-null Konstante zu ersetzen. Damit ist sichergestellt, dass jede passende Zeile gezählt wird. Für die nicht passenden Zeilen liefert der implizite else null-Zweig null, was auch von count ignoriert wird.

COUNT(*) FILTER (WHERE <Bedingung>)
COUNT(CASE WHEN <Bedingung> THEN 1 END)

Falls ein set qantifier (distinct oder all) verwendet wird, muss dieser in der Aggregatfunktion vor case erhalten bleiben.

Hinweis in eigener Sache

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

Proprietäre Erweiterungen

Unterabfragen Erlaubt (PostgreSQL)

Die PostgreSQL-Datenbank unterstützt die Verwendung von Unterabfragen in der filter-Klausel (z. B. mittels exists).

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Kaufen Sie sein Buch bei Amazon

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

0

SQL:2016-2: §10.9, Syntax Rule 8.

Beim der Emulation mit case werden lediglich Window-Funktionen ausgeschlossen: SQL:2016-2: §10.9, Syntax Rule 7a.

1

SQL:2016-2: §10.9, General Rule 7a (für gewöhnliche Aggregate), 8a (für binäre Set-Operationen), 10d (für inverse Set-Operationen). Siehe auch SQL:2016-2: §4.16.4.

Ausnahme: array_agg (SQL:2016-2: §10.9, General Rule 12gii, explizit in Bemerkung 510) – Bei dem Array-Konstruktor mitels Abfrage kann man null-Werte auch ohne filter-Klausel entfernen.

„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz