В мире больших данных: “QUALIFY: фильтруем результаты оконных функций QUALIFY

QUALIFY: фильтруем результаты оконных функций

QUALIFY — SQL-конструкция, которая позволяет отфильтровать результаты после применения оконных функций 😍

Она работает аналогично WHERE, но с той разницей, что QUALIFY применяется после оконных функций, а WHERE — до них.

Напомню порядок выполнения запроса:
1. From
2. Where
3. Group by
4. Having
5. Window
6. QUALIFY
7. Distinct
8. Order by
9. Limit

Представим, что у нас есть таблица продаж, и мы хотим выбрать топ-5 продаж по каждой категории товаров. С помощью QUALIFY это можно сделать просто и эффективно:


SELECT
    category,
    product,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rn
FROM sales
QUALIFY rn <= 5;

В этом запросе сначала нумеруем продажи в каждой категории по убыванию суммы продаж с помощью ROW_NUMBER(). Затем с помощью QUALIFY оставляем только первые пять записей в каждой категории. Легко и понятно.

Почему нельзя использовать WHERE вместо QUALIFY? Потому что WHERE фильтрует данные до выполнения оконных функций, а нам нужно отфильтровать данные после. Если попробовать использовать WHERE rn <= 5, SQL не поймёт, что такое rn, потому что на этапе выполнения WHERE эта колонка ещё не создана.

Конечно, можно использовать QUALIFY и с другими оконными функциями, например, RANK():


SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) 
                  AS salary_rank
FROM employees
QUALIFY salary_rank = 1;

Этот запрос выберет сотрудников с наивысшей зарплатой в каждом отделе.

Стоит отметить, что QUALIFY поддерживается не во всех СУБД. Например, в Snowflake и Teradata эта функция есть, а в PostgreSQL или MySQL её нет. В таких случаях приходится использовать подзапросы или CTE (Common Table Expressions).

Например так:


WITH ranked_sales AS (
    SELECT
        category,
        product,
        sales_amount,
        RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rn
    FROM sales
)
SELECT *
FROM ranked_sales
WHERE rn <= 5;

Согласитесь, что использование QUALIFY делает код более кратким и читаемым. Используйте его, когда это возможно 😎

#sql