В чём разница между QUALIFY (ClickHouse/Snowflake/BigQuery) и подзапросом с ROW_NUMBER в PostgreSQL для фильтрации по результату оконной функции?

Короткий ответ

QUALIFY позволяет фильтровать строки прямо по значению оконной функции в том же SELECT, где она вычислена, тогда как в PostgreSQL (где QUALIFY не поддерживается) для этого нужно обернуть оконную функцию в подзапрос или CTE и фильтровать уже внешним WHERE, потому что WHERE не может напрямую ссылаться на оконные функции, вычисленные в том же уровне запроса.

Как это работает подробнее

логический порядок выполнения запроса — оконные функции вычисляются после WHERE, но до финальной проекции SELECT; QUALIFY — это дополнительный этап фильтрации именно после вычисления оконных функций, аналогичный тому, как HAVING работает после GROUP BY. В PostgreSQL такого отдельного этапа нет, поэтому оконную функцию нужно материализовать в подзапросе/CTE, чтобы потом сослаться на неё в обычном WHERE.

Пример кода

question-62.sqlКопировать

-- ClickHouse / Snowflake / BigQuery
SELECT customer_id, order_id, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) = 1;
-- PostgreSQL (эквивалент)
SELECT * FROM (
  SELECT customer_id, order_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) t WHERE rn = 1;

Что использовать на практике

• Практика. QUALIFY делает запрос короче и нагляднее там, где он поддерживается; в PostgreSQL эквивалентная логика требует дополнительного уровня вложенности, что не хуже по производительности, но многословнее. При переносе SQL-кода между диалектами (например, из BigQuery в PostgreSQL) наличие QUALIFY — одна из частых точек, которую нужно переписать.
• Проверка. Проверка: оба запроса должны вернуть одинаковое количество строк — ровно одну на каждого клиента.

Подводные камни

• Частая ошибка. пытаются написать WHERE ROW_NUMBER() OVER (...) = 1 напрямую в PostgreSQL без оборачивания в подзапрос и получают ошибку синтаксиса, так как оконные функции недопустимы в WHERE того же уровня запроса.