Die FILTER-Klausel für bedingte Aggregation in Window Functions

1. Das klassische Problem: CASE WHEN in Aggregatfunktionen

Um innerhalb einer Aggregation nur bestimmte Zeilen zu berücksichtigen, greifen die meisten Entwickler intuitiv auf eine Kombination aus COUNT oder SUM und einem inneren CASE-WHEN-Ausdruck zurück. Die Aggregatfunktion zählt oder summiert dabei nur die Zeilen, für die der CASE-Ausdruck einen Wert ungleich NULL liefert, während alle anderen Zeilen effektiv aus der Aggregation herausfallen, weil NULL-Werte von COUNT, SUM und den meisten anderen Aggregatfunktionen ignoriert werden.

Dieses Muster funktioniert zuverlässig und ist in praktisch jeder Datenbank verfügbar, hat aber einen spürbaren Lesbarkeitsnachteil, sobald mehrere solcher bedingten Aggregationen in derselben Abfrage nebeneinander stehen. Der Leser muss für jede Spalte zunächst den CASE-Ausdruck gedanklich auflösen, bevor die eigentliche Aggregatfunktion und ihre Bedingung erkennbar werden, was bei fünf oder sechs parallelen Kennzahlen schnell zu einer unübersichtlichen Wand aus verschachtelten Ausdrücken führt.

SELECT
    customer_id,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
    SUM(CASE WHEN status = 'completed' THEN total_amount END) AS completed_revenue
FROM orders
GROUP BY customer_id;

2. Die FILTER-Klausel: deklarative Bedingung statt CASE-Verschachtelung

Die FILTER-Klausel wird direkt hinter eine Aggregatfunktion geschrieben und enthält eine WHERE-Bedingung, die festlegt, welche Zeilen in die Aggregation einfließen. Syntaktisch ist FILTER (WHERE Bedingung) damit ein integraler Bestandteil der Aggregatfunktion selbst, nicht eine Umformung des aggregierten Werts wie bei CASE WHEN. Diese Trennung macht sofort erkennbar, welche Aggregatfunktion mit welcher Bedingung kombiniert wird, ohne dass der Leser den inneren Ausdruck erst gedanklich entschlüsseln muss.

Funktional ist FILTER exakt äquivalent zur COUNT-CASE-WHEN-Kombination, das Ergebnis unterscheidet sich in keinem Fall. Der Unterschied liegt ausschließlich in der Lesbarkeit und, wie im Abschnitt zur Performance gezeigt wird, teilweise auch im vom Optimizer erzeugten Ausführungsplan. Für Teams, die viele bedingte Aggregationen in Reporting-Queries schreiben, ist der Umstieg auf FILTER daher meist eine reine Verbesserung ohne funktionale Nachteile.

SELECT
    customer_id,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_count,
    SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders
GROUP BY customer_id;

3. Mehrere parallele Kennzahlen in einer einzigen Query

Der eigentliche Mehrwert der FILTER-Klausel zeigt sich bei Reporting-Abfragen, die viele unterschiedliche Kennzahlen für dieselbe Gruppierung in einer einzigen Ergebniszeile liefern sollen, etwa ein Dashboard, das gleichzeitig Bestellanzahl nach Status, Umsatz nach Status und durchschnittlichen Bestellwert nach Status pro Kunde anzeigt. Mit CASE WHEN würde jede zusätzliche Kennzahl eine weitere Verschachtelungsebene bedeuten, mit FILTER bleibt jede Zeile der SELECT-Liste unabhängig lesbar.

Ein weiterer praktischer Vorteil ist, dass sich unterschiedliche Aggregatfunktionen mit unterschiedlichen Filterbedingungen frei kombinieren lassen, ohne dass die WHERE-Bedingung der eigentlichen Abfrage angepasst werden müsste. Eine einzige GROUP-BY-Abfrage kann so gleichzeitig Zählungen, Summen und Durchschnittswerte für beliebig viele unterschiedliche Teilmengen derselben Grundgesamtheit liefern, was für Dashboard-Backends besonders wertvoll ist, wo genau diese Art von Multi-Metrik-Abfragen den Regelfall darstellt.

SELECT
    customer_id,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
    ROUND(AVG(total_amount) FILTER (WHERE status = 'completed'), 2)
        AS avg_completed_value,
    SUM(total_amount) FILTER (WHERE order_date >= CURRENT_DATE - INTERVAL '30 days')
        AS revenue_last_30_days
FROM orders
GROUP BY customer_id;

4. FILTER kombiniert mit Window Functions

Die FILTER-Klausel ist nicht auf klassische GROUP-BY-Aggregation beschränkt, sondern lässt sich auch an Aggregatfunktionen anhängen, die als Window Function mit einer OVER-Klausel verwendet werden. Damit lassen sich laufende Summen oder gleitende Durchschnitte berechnen, die nur einen Teil der Zeilen innerhalb des Fensters berücksichtigen, ohne dass eine zusätzliche Subquery oder ein zweiter CASE-Ausdruck nötig wird.

Ein praktisches Beispiel ist eine laufende Summe des Umsatzes aus abgeschlossenen Bestellungen pro Kunde, sortiert nach Datum, während stornierte Bestellungen in der laufenden Summe konsequent ignoriert werden, aber dennoch als eigene Zeile im Ergebnis erscheinen. Diese Kombination aus FILTER und OVER ist mit CASE WHEN zwar ebenfalls möglich, wird aber durch die zusätzliche Verschachtelung innerhalb der Window-Function-Syntax noch schwerer lesbar als im reinen GROUP-BY-Fall.

SELECT
    order_id,
    customer_id,
    order_date,
    status,
    total_amount,
    SUM(total_amount) FILTER (WHERE status = 'completed')
        OVER (PARTITION BY customer_id ORDER BY order_date)
        AS running_completed_revenue
FROM orders
ORDER BY customer_id, order_date;

5. Wie der Optimizer FILTER im Vergleich zu CASE WHEN behandelt

In PostgreSQL führt die FILTER-Klausel oft zu einem effizienteren Ausführungsplan als die äquivalente CASE-WHEN-Formulierung, weil der Optimizer die Filterbedingung direkt als eigenständige Information erkennt und teilweise für eine gezieltere Auswahl relevanter Zeilen nutzen kann, statt die Bedingung erst innerhalb der Aggregatfunktion aufzulösen. Bei mehreren gleichzeitig verwendeten Filterbedingungen mit teilweiser Überschneidung kann der Optimizer zudem gemeinsame Teilmengen intern effizienter zusammenfassen.

In der Praxis sind die Laufzeitunterschiede bei kleinen bis mittleren Datenmengen meist gering und selten der ausschlaggebende Grund für einen Umstieg. Bei sehr großen Reporting-Tabellen mit vielen parallelen FILTER-Bedingungen lohnt sich dennoch ein Blick in EXPLAIN ANALYZE, da bereits kleine Unterschiede im Ausführungsplan bei Millionen von Zeilen spürbare Auswirkungen auf die Gesamtlaufzeit einer Reporting-Abfrage haben können.

6. Datenbank-Verfügbarkeit: Standard-SQL vs. proprietäre Alternativen

Die FILTER-Klausel ist Teil des SQL-Standards seit SQL:2003 und in PostgreSQL seit Version 9.4 vollständig implementiert, ebenso in SQLite seit einer vergleichsweise frühen Version. Beide Datenbanken unterstützen die Kombination mit Window Functions genau wie im vorherigen Abschnitt gezeigt, ohne Einschränkungen gegenüber der reinen GROUP-BY-Nutzung.

MySQL und Microsoft SQL Server unterstützen die FILTER-Klausel dagegen bis heute nicht nativ, dort bleibt die CASE-WHEN-Kombination die einzige verfügbare Lösung. Oracle kennt ebenfalls kein FILTER im Sinne des SQL-Standards, bietet aber mit dem KEEP-Zusatz und speziellen analytischen Funktionen teilweise ähnliche, aber syntaktisch vollständig anders aufgebaute Alternativen an. Wer datenbank-agnostischen Code schreiben muss, sollte FILTER daher bewusst nur für PostgreSQL- oder SQLite-spezifische Codepfade einsetzen und ansonsten konsequent bei CASE WHEN bleiben.

7. FILTER in Kombination mit DISTINCT und mehreren Bedingungen

FILTER lässt sich problemlos mit COUNT DISTINCT kombinieren, um beispielsweise die Anzahl unterschiedlicher Produkte zu zählen, die ein Kunde ausschließlich in stornierten Bestellungen gekauft hat, während andere Bestellstatus für denselben Ausdruck ignoriert werden. Diese Kombination aus DISTINCT und FILTER innerhalb derselben Aggregatfunktion ist mit einer reinen CASE-WHEN-Lösung zwar ebenfalls möglich, aber deutlich fehleranfälliger, weil eine falsch platzierte Klammer schnell zu einem semantisch falschen, aber syntaktisch weiterhin gültigen Ausdruck führt.

Auch komplexere Filterbedingungen mit mehreren UND- und ODER-Verknüpfungen lassen sich innerhalb der FILTER-Klausel genauso formulieren wie in einer normalen WHERE-Klausel, inklusive Subqueries und Funktionsaufrufen. Das macht FILTER zu einer vollwertigen Alternative für nahezu jede denkbare Bedingung, nicht nur für einfache Gleichheitsprüfungen wie in den bisherigen Beispielen.

SELECT
    customer_id,
    COUNT(DISTINCT product_id) FILTER (
        WHERE status = 'cancelled' AND total_amount > 50
    ) AS distinct_products_cancelled_high_value
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
GROUP BY customer_id;

8. FILTER und WHERE nicht verwechseln: unterschiedliche Auswertungszeitpunkte

Ein häufiges Missverständnis besteht darin, FILTER als reinen Ersatz für WHERE zu betrachten. Tatsächlich arbeiten beide auf vollständig unterschiedlichen Ebenen: WHERE entfernt Zeilen vollständig aus der Abfrage, bevor überhaupt gruppiert wird, und wirkt sich damit auf jede Aggregatfunktion in der SELECT-Liste gleichermaßen aus. FILTER dagegen schränkt nur eine einzelne Aggregatfunktion ein, während alle anderen Aggregatfunktionen in derselben Abfrage weiterhin die vollständige, ungefilterte Zeilenmenge der jeweiligen Gruppe sehen.

Dieser Unterschied wird relevant, sobald eine Abfrage sowohl eine Gesamtzahl über alle Zeilen als auch eine bedingte Teilzahl liefern soll. Wird die Bedingung fälschlich in eine WHERE-Klausel statt in FILTER geschrieben, verschwindet nicht nur die gewünschte Teilzahl, sondern auch die eigentlich gewünschte Gesamtzahl wird auf die gefilterte Teilmenge verkürzt, und Gruppen, die ausschließlich Zeilen außerhalb der Bedingung enthalten, fallen durch die WHERE-Klausel komplett aus dem Ergebnis, während sie mit FILTER korrekt mit dem Wert null für die bedingte Kennzahl erhalten bleiben.

-- Falsch: WHERE entfernt Zeilen für die gesamte Abfrage
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
WHERE status = 'completed';  -- total_orders zählt nur completed!

-- Richtig: FILTER schränkt nur die zweite Kennzahl ein
SELECT
    customer_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders
GROUP BY customer_id;

9. Bestehenden CASE-WHEN-Code auf FILTER migrieren

Bei der Migration bestehender Reporting-Abfragen von CASE WHEN auf FILTER lohnt sich ein systematisches Vorgehen: Zuerst werden alle Aggregatfunktionen identifiziert, deren innerer Ausdruck ausschließlich aus einem CASE-WHEN-Konstrukt mit genau einer Bedingung und einem impliziten oder expliziten ELSE NULL besteht, denn nur diese Fälle lassen sich mechanisch und ohne Verhaltensänderung in FILTER umwandeln. Komplexere CASE-Ausdrücke mit mehreren WHEN-Zweigen und unterschiedlichen Rückgabewerten sind dagegen keine reine Filterlogik und sollten nicht vorschnell umgebaut werden.

Da FILTER auf Datenbanken ohne native Unterstützung schlicht zu einem Syntaxfehler führt, empfiehlt sich vor jeder Migration ein automatisierter Test gegen alle produktiv eingesetzten Datenbankversionen. In gemischten Umgebungen, in denen sowohl PostgreSQL als auch MySQL parallel im Einsatz sind, bewährt sich ein zentraler Query-Builder oder eine Abstraktionsschicht, die je nach Zieldatenbank automatisch zwischen FILTER und der äquivalenten CASE-WHEN-Formulierung wechselt.

10. Zusammenfassung

11. FAQ: Die FILTER-Klausel: Das Wichtigste auf einen Blick