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, Window-Funktionen und äußeren Referenzen0 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:
Pivot – Daten drehen –
Filterin derSelect-KlauselWeitere folgen: Bestell den Newsletter!
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 einem Case-Ausdruck 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 quantifier (Distinct oder All) verwendet wird, muss dieser in der Aggregatfunktion vor Case erhalten bleiben.
Wenn du diese Seite magst, magst du vielleicht auch …
… meine Newsletter abonnieren, gratis Sticker bestellen, mein Buch kaufen oder an einer Schulung teilnehmen.
Erweiterungen
Unterabfragen Erlaubt (PostgreSQL)
Die PostgreSQL-Datenbank unterstützt die Verwendung von Unterabfragen in der Filter-Klausel (z. B. mittels Exists).
Nicht-standard Alternativen
Da die oben beschriebene Standard-Alternative mit einem Case-Ausdruck weitestgehend unterstützt wird, rate ich von der Verwendung dieser nicht-standard Alternativen generell ab.
Countif (Google BigQuery)
Google BigQuery bietet die Aggregatfunktion Countif an, die vor allem aus Microsoft Excel bekannt ist.
COUNTIF(<Bedingung>) [OVER(…)]Dasselbe Ergebnis kann man mit der Case-Alternative erreichen, die dann ein allen gängigen Datenbanken funktioniert:
COUNT(CASE WHEN <Bedingung> THEN 1 END) [OVER(…)]
