Как фильтровать результат оконных функций в ClickHouse
Когда пишешь SQL-запрос с оконками, часто необходимо сделать фильтрацию по данным, которые они возвращают, например, получить первую строку в каждой группе через ROW_NUMBER(). Для этого приходится оборачивать запрос в подзапрос и уже на уровне внешнего запроса фильтровать данные. Ну, либо использовать CTE.
В ClickHouse можно проще.
Недавно наткнулся на фичу, которая позволяет сделать такую фильтрацию вообще без использования подзапросов или CTE. Это предложение QUALIFY.
Оно работает по аналогии с WHERE, но с одним важным отличием. WHERE отрабатывает до вычисления оконных функций, поэтому оно просто их «не видит», а QUALIFY — после.
Поэтому раньше приходилось писать так:
SELECT *
FROM (
SELECT
id,
category,
ROW_NUMBER() OVER(PARTITION BY category ORDER BY created_at DESC) as rn
FROM my_table
)
WHERE rn = 1;
А с использованием QUALIFY запрос становится короче:
SELECT
id,
category,
ROW_NUMBER() OVER(PARTITION BY category ORDER BY created_at DESC) as rn
FROM my_table
QUALIFY rn = 1;
Несколько нюансов:
В QUALIFY можно фильтровать данные прямо по алиасу из SELECT и не дублировать весь код оконной функции.
Оконную функцию можно написать прямо внутри QUALIFY. Выводить её в итоговый SELECT не обязательно. Фильтрация всё равно сработает «под капотом».
Если в запросе нет ни одной оконки, QUALIFY выдаст ошибку. Для обычной фильтрации всё так же используем WHERE.
