Основы оконных функций: ROW_NUMBER, RANK, DENSE_RANK

Оконные функции позволяют выполнять вычисления над набором строк, связанных с текущей строкой, при этом сохраняя детализацию исходных данных — в отличие от GROUP BY, который сворачивает строки в агрегаты. Функции ранжирования — ROW_NUMBER, RANK и DENSE_RANK — особенно полезны для нумерации записей внутри групп, поиска топ-N элементов и определения порядка событий.

Синтаксис оконных функций

Оконная функция применяется с использованием конструкции OVER(), которая определяет "окно" — набор строк для вычисления:

SELECT 
    column1,
    column2,
    WINDOW_FUNCTION() OVER (
        PARTITION BY column3 
        ORDER BY column4
    ) AS result
FROM table_name;

Ключевые компоненты:

• PARTITION BY — разбивает данные на группы (аналог GROUP BY), но без потери детализации. Функция применяется независимо внутри каждой группы.
• ORDER BY — определяет порядок строк внутри окна или партиции. Для функций ранжирования этот параметр обязателен.

ROW_NUMBER: последовательная нумерация

ROW_NUMBER() присваивает уникальный последовательный номер каждой строке внутри партиции. Даже если значения в столбце сортировки одинаковые, номера будут различаться.

Пример: Пронумеруем заказы каждого клиента по дате оформления:

SELECT 
    customer_id,
    order_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS order_number
FROM orders;

Нумерация начинается заново для каждого клиента. Это позволяет легко найти первый заказ каждого клиента, добавив WHERE order_number = 1 во внешний запрос.

RANK и DENSE_RANK: ранжирование с учетом равенства

Когда несколько строк имеют одинаковые значения в столбце сортировки, ROW_NUMBER все равно присвоит им разные номера. Функции RANK и DENSE_RANK работают иначе:

• RANK() — присваивает одинаковый ранг строкам с равными значениями, но следующий ранг "пропускает" номера. Если две строки получили ранг 2, следующая строка получит ранг 4.
• DENSE_RANK() — также присваивает одинаковый ранг равным значениям, но следующий ранг идет последовательно без пропусков (после двух строк с рангом 2 идет ранг 3).

Пример: Ранжируем продукты по выручке внутри каждой категории:

SELECT 
    category,
    product_name,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rank
FROM products;

Обратите внимание: Laptop и Tablet имеют одинаковую выручку, поэтому оба получили ранг 1. RANK() присвоил следующему продукту ранг 3 (пропустив 2), а DENSE_RANK() — ранг 2.

Практическое применение: топ-3 товара по продажам

Частая задача аналитика — найти топ-N элементов в каждой группе. С помощью DENSE_RANK это решается в два шага:

WITH ranked_products AS (
    SELECT 
        category,
        product_name,
        total_sales,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS sales_rank
    FROM product_sales
)
SELECT 
    category,
    product_name,
    total_sales
FROM ranked_products
WHERE sales_rank <= 3
ORDER BY category, sales_rank;

Этот запрос вернет три самых продаваемых товара в каждой категории, даже если на третьем месте несколько товаров с одинаковыми продажами.

Ключевые выводы

• ROW_NUMBER() — для уникальной нумерации строк, когда важна последовательность (первый заказ, последнее действие).
• RANK() — для ранжирования с пропусками, когда нужно показать "настоящее" место в рейтинге.
• DENSE_RANK() — для компактного ранжирования без пропусков, удобно для фильтрации топ-N.
• PARTITION BY создает независимые группы для ранжирования, ORDER BY определяет критерий сортировки внутри окна.