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