FILTER-Klausel

Die FILTER-Klausel kann optional auf eine Aggregatfunktion in einer SELECT-Anweisung folgen. Sie filtert die Zeilen, die in die Aggregatfunktion fließen, so wie eine WHERE-Klausel Zeilen filtert, aber lokal auf die jeweilige Aggregatfunktion beschränkt.

Das ist in mehreren Situationen nützlich, etwa wenn mehrere Aggregate mit unterschiedlichen Filtern ausgewertet werden oder wenn eine Pivot-Ansicht eines Datensatzes erzeugt wird. FILTER bietet eine sauberere Syntax zum Pivottieren von Daten als der traditionellere Ansatz mit CASE WHEN, der weiter unten behandelt wird.

Einige Aggregatfunktionen filtern NULL-Werte nicht heraus; mit einer FILTER-Klausel erhalten Sie dann gültige Ergebnisse, während der Ansatz mit CASE WHEN manchmal scheitert. Das betrifft die Funktionen first und last, die in einer nicht aggregierenden Pivot-Operation erwünscht sind, bei der die Daten nur in Spalten umorientiert und nicht neu aggregiert werden sollen. FILTER verbessert außerdem die NULL-Behandlung bei den Funktionen list und array_agg: Der Ansatz mit CASE WHEN nimmt NULL-Werte ins Listenresultat auf, die FILTER-Klausel entfernt sie.

Beispiele

Liefert Folgendes:

• Die Gesamtzahl der Zeilen
• Die Anzahl der Zeilen, in denen i <= 5
• Die Anzahl der Zeilen, in denen i ungerade ist

SELECT
    count() AS total_rows,
    count() FILTER (i <= 5) AS lte_five,
    count() FILTER (i % 2 = 1) AS odds
FROM generate_series(1, 10) tbl(i);

total_rows	lte_five	odds
10	5	5

Das bloße Zählen von Zeilen, die eine Bedingung erfüllen, geht auch ohne FILTER-Klausel, mit der booleschen Aggregatfunktion sum, z. B. sum(i <= 5).

Es können unterschiedliche Aggregatfunktionen verwendet werden, und mehrere WHERE-Ausdrücke sind ebenfalls erlaubt:

SELECT
    sum(i) FILTER (i <= 5) AS lte_five_sum,
    median(i) FILTER (i % 2 = 1) AS odds_median,
    median(i) FILTER (i % 2 = 1 AND i <= 5) AS odds_lte_five_median
FROM generate_series(1, 10) tbl(i);

lte_five_sum	odds_median	odds_lte_five_median
15	5.0	3.0

Die FILTER-Klausel kann auch Daten von Zeilen in Spalten pivottieren. Das ist ein statisches Pivot, weil Spalten in SQL vor der Laufzeit definiert sein müssen. Eine solche Anweisung kann jedoch in einer Host-Programmiersprache dynamisch erzeugt werden, um die SQL-Engine von DuckDB für schnelles Pivottieren größer als der Speicher zu nutzen.

Zuerst einen Beispieldatensatz erzeugen:

CREATE TEMP TABLE stacked_data AS
    SELECT
        i,
        CASE WHEN i <= rows * 0.25  THEN 2022
             WHEN i <= rows * 0.5   THEN 2023
             WHEN i <= rows * 0.75  THEN 2024
             WHEN i <= rows * 0.875 THEN 2025
             ELSE NULL
             END AS year
    FROM (
        SELECT
            i,
            count(*) OVER () AS rows
        FROM generate_series(1, 100_000_000) tbl(i)
    ) tbl;

Daten nach Jahr „pivottieren“ (jedes Jahr in eine eigene Spalte legen):

SELECT
    count(i) FILTER (year = 2022) AS "2022",
    count(i) FILTER (year = 2023) AS "2023",
    count(i) FILTER (year = 2024) AS "2024",
    count(i) FILTER (year = 2025) AS "2025",
    count(i) FILTER (year IS NULL) AS "NULLs"
FROM stacked_data;

Diese Syntax liefert dieselben Ergebnisse wie die FILTER-Klauseln oben:

SELECT
    count(CASE WHEN year = 2022 THEN i END) AS "2022",
    count(CASE WHEN year = 2023 THEN i END) AS "2023",
    count(CASE WHEN year = 2024 THEN i END) AS "2024",
    count(CASE WHEN year = 2025 THEN i END) AS "2025",
    count(CASE WHEN year IS NULL THEN i END) AS "NULLs"
FROM stacked_data;

2022	2023	2024	2025	NULLs
25000000	25000000	25000000	12500000	12500000

Der Ansatz mit CASE WHEN funktioniert jedoch nicht wie erwartet, wenn eine Aggregatfunktion verwendet wird, die NULL-Werte nicht ignoriert. Die Funktion first fällt in diese Kategorie, daher ist FILTER in diesem Fall vorzuziehen.

Daten nach Jahr „pivottieren“ (jedes Jahr in eine eigene Spalte legen):

SELECT
    first(i) FILTER (year = 2022) AS "2022",
    first(i) FILTER (year = 2023) AS "2023",
    first(i) FILTER (year = 2024) AS "2024",
    first(i) FILTER (year = 2025) AS "2025",
    first(i) FILTER (year IS NULL) AS "NULLs"
FROM stacked_data;

2022	2023	2024	2025	NULLs
1474561	25804801	50749441	76431361	87500001

Das liefert NULL-Werte, sobald die erste Auswertung der CASE WHEN-Klausel ein NULL zurückgibt:

SELECT
    first(CASE WHEN year = 2022 THEN i END) AS "2022",
    first(CASE WHEN year = 2023 THEN i END) AS "2023",
    first(CASE WHEN year = 2024 THEN i END) AS "2024",
    first(CASE WHEN year = 2025 THEN i END) AS "2025",
    first(CASE WHEN year IS NULL THEN i END) AS "NULLs"
FROM stacked_data;

2022	2023	2024	2025	NULLs
1228801	NULL	NULL	NULL	NULL

Syntax der Aggregatfunktion (einschließlich FILTER-Klausel)