QUALIFY, оконные функции и FILTER в агрегатах

Оконные функции — это вычисления над «окном» строк вокруг текущей: ранг строки внутри группы, нарастающий итог, значение из предыдущей строки.

Window vs GROUP BY: где что использовать В отличие от агрегатов, они не сворачивают строки: на выходе столько же строк, сколько на входе, просто к каждой добавляется вычисленное значение. Это делает их незаменимыми для аналитики — «топ-3 товара в каждом регионе», «дни, где выручка выше скользящего среднего».

Но у оконных функций есть две болевые точки в стандартном SQL. Первая: отфильтровать по результату оконной функции нельзя ни в WHERE, ни в HAVING — приходится оборачивать запрос в подзапрос. Вторая, родственная: чтобы посчитать агрегат по подмножеству строк, нужен либо CASE внутри агрегата, либо отдельный подзапрос. DuckDB закрывает обе: QUALIFY фильтрует прямо по оконным функциям, а FILTER сужает агрегат декларативно. Разберём, почему обе нужны и как они вписываются в порядок исполнения запроса.

Почему оконную функцию нельзя положить в WHERE

Вернёмся к логическому порядку вычисления. WHERE исполняется третьим шагом — сразу после FROM, до GROUP BY. Оконные функции вычисляются гораздо позже: после группировки, фактически на уровне SELECT. Когда движок обрабатывает WHERE, оконная функция ещё не вычислена — фильтровать по ней физически нечем.

HAVING тоже не подходит: он работает с результатами агрегатов после GROUP BY, а оконная функция — не агрегат. В стандартном SQL остаётся один выход — подзапрос: вычислить оконную функцию во внутреннем запросе, отфильтровать во внешнем.

-- Задача: топ-2 товара по выручке в каждом регионе
-- Стандартный SQL: оконная функция внутри, фильтр снаружи
SELECT * FROM (
    SELECT
        region, product, revenue,
        row_number() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn
    FROM product_sales
) ranked
WHERE rn <= 2;

Подзапрос нужен исключительно ради порядка вычисления: вынести row_number() на «уровень глубже», чтобы внешний WHERE увидел его результат. Сам по себе он не несёт смысла — это структурный костыль.

QUALIFY убирает костыль. Это клауза, которая делает для оконных функций ровно то, что WHERE делает для строк, а HAVING — для групп: фильтрует по результату. Логически она исполняется после вычисления оконных функций.

-- DuckDB: QUALIFY фильтрует прямо по оконной функции
SELECT
    region, product, revenue,
    row_number() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn
FROM product_sales
QUALIFY rn <= 2;

В QUALIFY можно ссылаться на оконную функцию по алиасу (rn) или повторить её целиком — оба варианта работают:

-- Без алиаса: оконная функция прямо в QUALIFY
SELECT region, product, revenue
FROM product_sales
QUALIFY row_number() OVER (PARTITION BY region ORDER BY revenue DESC) <= 2;

Анатомия оконной функции

Раз QUALIFY фильтрует по окнам, стоит зафиксировать устройство самой оконной функции. Конструкция OVER (...) состоит из трёх частей, каждая опциональна:

функция() OVER (
    PARTITION BY ключи   -- на какие группы бьётся окно
    ORDER BY колонки     -- порядок строк внутри группы
    ROWS/RANGE рамка     -- сколько строк вокруг текущей входит в окно
)

PARTITION BY делит таблицу на независимые группы — окно не «видит» строки за границей своей партиции. ORDER BY задаёт порядок внутри партиции, он нужен для функций ранжирования (row_number, rank) и для рамочных вычислений. Рамка (ROWS BETWEEN ...) уточняет, какие строки вокруг текущей попадают в окно — это и есть механизм скользящих окон.

-- Скользящее среднее выручки за 3 дня (текущий + 2 предыдущих)
SELECT
    day, revenue,
    avg(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM daily_revenue;

Различие row_number / rank / dense_rank важно для QUALIFY-фильтров «топ-N». row_number даёт уникальные номера 1, 2, 3, 4 даже при равных значениях. rank при равенстве выдаёт одинаковый ранг и пропускает следующие (1, 1, 3). dense_rank тоже даёт одинаковый ранг, но без пропуска (1, 1, 2). Если нужно ровно две строки на партицию — row_number. Если нужны «все строки с двумя лучшими значениями, включая равные» — dense_rank() ... QUALIFY dr <= 2.

FILTER: агрегат по подмножеству строк

Вторая частая задача — посчитать агрегат не по всем строкам группы, а по подмножеству. Например, в одном запросе получить и общее число заказов, и число только оплаченных.

Классический приём — CASE внутри агрегата: count(CASE WHEN status = 'paid' THEN 1 END). Он работает, потому что count игнорирует NULL, а CASE без ELSE возвращает NULL для непопавших строк. Но читается это плохо: фильтрующее условие спрятано внутри CASE, а намерение «посчитай только оплаченные» приходится реконструировать.

DuckDB поддерживает стандартную клаузу FILTER — она выражает то же намерение прямо:

-- Три счётчика в одном проходе по таблице
SELECT
    region,
    count(*) AS total_orders,
    count(*) FILTER (WHERE status = 'paid')     AS paid_orders,
    sum(amount) FILTER (WHERE status = 'paid')  AS paid_revenue
FROM orders
GROUP BY ALL;

agg(...) FILTER (WHERE условие) означает: применить агрегат только к строкам группы, удовлетворяющим условию. Семантически это эквивалентно CASE-приёму, но FILTER декларативен — условие стоит рядом с агрегатом, отдельной клаузой, и читается как «посчитай X, где Y».

Преимущество FILTER перед несколькими подзапросами — один проход по таблице. Запрос выше читает orders один раз и считает все три метрики параллельно. Альтернатива через три отдельных подзапроса с разными WHERE прочитала бы таблицу трижды.

FILTER отличается от CASE-приёма для агрегатов, которые НЕ игнорируют структуру значений так же, как count. Для count(*) оба варианта дают одинаковый результат. Но, скажем, для count(column) против count(*) поведение с NULL уже различается, и FILTER тут однозначнее: он явно про строки, а не про значения.

FILTER сочетается и с оконными функциями:

-- Нарастающий счёт только оплаченных заказов
SELECT
    order_id, status,
    count(*) FILTER (WHERE status = 'paid')
        OVER (ORDER BY order_id) AS paid_running_count
FROM orders;

QUALIFY и FILTER вместе

QUALIFY и FILTER решают разные задачи, но часто встречаются в одном запросе. Типичный аналитический запрос: посчитать метрики с FILTER, проранжировать через оконную функцию, отфильтровать топ через QUALIFY — и всё это без единого подзапроса.

-- Регионы из топ-3 по оплаченной выручке
SELECT
    region,
    sum(amount) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders
GROUP BY ALL
QUALIFY row_number() OVER (ORDER BY paid_revenue DESC) <= 3;

Здесь работают сразу три расширения friendly SQL: FILTER сужает агрегат, GROUP BY ALL выводит ключ группировки, QUALIFY фильтрует по оконной функции. В стандартном SQL тот же запрос потребовал бы CASE внутри sum, явный GROUP BY и обёртку-подзапрос ради WHERE rn <= 3. Friendly SQL делает структуру запроса плоской — она читается сверху вниз, без вложенности.

Попробуй сам

Создайте таблицу:

CREATE TABLE orders AS
  SELECT * FROM (VALUES
    (1, 'EU', 'paid',    120),
    (2, 'EU', 'pending', 80),
    (3, 'EU', 'paid',    200),
    (4, 'US', 'paid',    150),
    (5, 'US', 'paid',    300),
    (6, 'US', 'pending', 50),
  ) t(order_id, region, status, amount);

Задания:

1. Через QUALIFY и row_number() выведите два заказа с наибольшим amount в каждом регионе.
2. Одним запросом с FILTER посчитайте по каждому региону: всего заказов, оплаченных заказов, оплаченную выручку.
3. Перепишите задание 2 через CASE внутри агрегатов. Сравните читаемость двух вариантов.
4. Замените в задании 1 row_number() на rank() и dense_rank(). На этих данных разницы не будет — добавьте строку с amount, равным уже существующему, и посмотрите, как разойдётся поведение.
5. Объясните себе, почему фильтр по row_number() нельзя записать в WHERE, опираясь на логический порядок исполнения запроса.