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 einem Case-Ausdruck emuliert werden (siehe Konforme Alternativen).

BigQueryDb2 (LUW)MariaDBMySQLaOracle DBPostgreSQLSQL ServerSQLiteFilter-KlauselEmulation mit Case
  1. Das filter_plugin eines Fremdanbieters verwendet reguläre Ausdrücke, um Filter als Case umzuschreiben

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.

Hinweis in eigener Sache

Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

Proprietäre Erweiterungen

Unterabfragen Erlaubt (PostgreSQL)

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

Proprietäre Alternativen

Da die oben beschriebene Standard-Alternative mit einem Case-Ausdruck weitestgehend unterstützt wird, rate ich von der Verwendung dieser proprietären Alternativen generell ab.

BigQueryDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLiteCountif

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(…)]

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

  1. 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.

  2. 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 mittels Abfrage kann man Null-Werte auch ohne Filter-Klausel entfernen.

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„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 und DSGVO | CC-BY-NC-ND 3.0 Lizenz