Основные оконные функции для аналитики

Прежде чем начать, представьте, что вы работаете с таблицей тысяч строк продаж. Ваша задача: определить, кто из продавцов номер один в каждой категории, кто второй, и так далее. Или, например, вам нужно пронумеровать все строки в выборке, чтобы отследить порядок. Всё это легко сделать с помощью оконных функций.

Оконные функции — это функции SQL, которые работают с подмножестком строк (назовем его "окном") из набора данных. В отличие от агрегатных функций, которые объединяют строки в одну (например, SUM() или AVG()), оконные функции оставляют строки нетронутыми, добавляя к ним вычисленные значения.

Отличие от агрегатных функций

Агрегатные функции "сжимают" данные, группируя строки:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Результат: всего пару строк, по количеству отделов.

Сравним с оконной функцией — тут строки остаются на месте, но добавляется новое поле, содержащее, например, ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Здесь вы получите все те же строки, но с дополнительной колонкой rank_within_department, где каждому сотруднику присвоен номер в рамках своего отдела.

Основные оконные функции

Синтаксис OVER()

Самая важная часть каждой оконной функции — это магическое слово OVER(). Оно определяет, каким именно "окном" данных будет работать эта функция. Внутри OVER() можно задавать разделение на группы (PARTITION BY) и/или порядок сортировки (ORDER BY).

Общий синтаксис:

<оконная_функция>() OVER (
   [PARTITION BY <группа>] 
   [ORDER BY <порядок>]
)

Компоненты:

• PARTITION BY: Делим строки на группы. Например, "раздели данные по отделам".
• ORDER BY: Указываем порядок обработки строк. Например, "сортируй сотрудников по зарплате от большего к меньшему".

Функция ROW_NUMBER()

Функция ROW_NUMBER() пронумеровывает строки, начиная с 1, внутри указанного "окна". Иногда это полезно для простого создания номера строки во временной таблице или для определения порядкового места записи.

Пример. Таблица sales (продажи):

Запрос:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Результат:

Как это работает:

1. Данные делятся на группы по product_category.
2. Каждая группа сортируется по revenue (в порядке убывания).
3. Строки внутри каждой группы получают порядковый номер.

Функция RANK()

Функция RANK() используется для ранжирования строк. В отличие от ROW_NUMBER(), она учитывает одинаковые значения и пропускает номера (ранги), если значения совпадают.

Пример:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Результат:

Функция DENSE_RANK()

DENSE_RANK() похожа на RANK(), за исключением одного момента: он не пропускает номера рангов, если есть совпадающие значения.

Пример. Добавим продажу с таким же доходом:

Запрос:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Результат:

Примеры использования: нумерация строк

Задача: нумеровать все заказы в таблице orders, отсортированные по дате.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Результат: получаем список заказов с нумерацией в порядке их выполнения.

Примеры использования: топ-3 продавцов в каждой категории

Задача: определить трех лучших продавцов в каждой категории товаров.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Примеры использования: выявление одинаковых показателей

Задача: определить, есть ли продавцы с одинаковым доходом в каждой категории.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Теперь можно увидеть ранги, где "залипают" одинаковые значения.