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

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ügit die implizite else null-Klausel, um nicht passende Zeilen zu ignori. 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.

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.

Fußnoten

  1. SQL:2011-2: §10.9, Syntax Rule 6.

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

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

„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