Оператор FILTER

Оператор FILTER может необязательно следовать за агрегатной функцией в операторе SELECT. Это отфильтрует строки данных, которые передаются в агрегатную функцию, так же, как оператор WHERE фильтрует строки, но локально для конкретной агрегатной функции. FILTER в настоящее время не могут использоваться, когда агрегатная функция находится в контексте окна.

Существует несколько ситуаций, когда это полезно, включая оценку нескольких агрегатов с разными фильтрами и создание сводной таблицы набора данных. FILTER предоставляет более чистый синтаксис для сводки данных по сравнению с более традиционным подходом CASE WHEN, обсуждаемым ниже.

Некоторые агрегатные функции также не отфильтровывают нулевые значения, поэтому использование оператора FILTER вернёт корректные результаты, когда подход CASE WHEN не сработает. Это происходит с функциями first и last, которые желательны в не агрегирующей сводке, где цель — просто переориентировать данные в столбцы, а не повторно агрегировать их. FILTER также улучшает обработку нулевых значений при использовании функций list и array_agg, так как подход CASE WHEN включает нулевые значения в результатах, в то время как оператор FILTER их удаляет.

Примеры

Возвращает следующее:

• Общее количество строк.
• Количество строк, где i <= 5
• Количество строк, где i нечётное

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

Можно использовать разные агрегатные функции, и разрешено несколько выражений WHERE.

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

Оператор FILTER также можно использовать для сводки данных из строк в столбцы. Это статическая сводка, так как столбцы должны быть определены до выполнения запроса в SQL. Однако этот вид оператора можно динамически генерировать в языке хоста, чтобы использовать движок SQL DuckDB для быстрой сводки данных, превышающих объём оперативной памяти.

Сначала сгенерируем примерный набор данных:

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;

«Сведём» данные по годам (переместим каждый год в отдельный столбец):

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;

Этот синтаксис даёт те же результаты, что и операторы FILTER выше:

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;

Однако, подход CASE WHEN не будет работать как ожидается, при использовании агрегатной функции, которая не игнорирует NULL значения. Функция first относится к этой категории, поэтому FILTER предпочтительнее в этом случае.

«Сведём» данные по годам (переместим каждый год в отдельный столбец):

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;

Это приведет к NULL значениям, всякий раз, когда первое вычисление оператора CASE WHEN возвращает NULL:

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;

Синтаксис агрегатных функций (включая оператор FILTER)