Как фильтровать результат оконных функций в 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.

Ссылка на доку.

Мои статьи по ClickHouse на Хабре.