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:

• Pivot – Daten drehen – Filter in der Select-Klausel

• Null bei Array_agg entfernen

• Weitere 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).

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.

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.

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