Урок 7.1: Оконные функции для продвинутого анализа данных

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

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

Что такое оконные функции?

Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Этот набор строк называется "окном" или "оконным фреймом". Ключевое отличие от обычных агрегатных функций заключается в том, что оконные функции не группируют строки в единую выходную строку — каждая строка сохраняет свою идентичность.

Представьте это как взгляд через движущееся окно, пока вы просматриваете свои данные. Для каждой строки вы можете видеть и вычислять значения на основе связанных строк вокруг неё, но каждая строка все равно появляется отдельно в результате.

Ключевые характеристики:

• Оконные функции работают с набором строк, определённым предложением OVER
• Они возвращают значение для каждой строки в результирующем наборе
• Они не уменьшают количество строк, возвращаемых запросом
• Они могут использоваться для ранжирования, агрегации и аналитических операций

Базовый синтаксис

Общий синтаксис оконной функции:

имя_оконной_функции(выражение) OVER (
    [PARTITION BY выражение_partиции]
    [ORDER BY выражение_сортировки]
    [предложение_оконного_фрейма]
)

Компоненты:

• имя_оконной_функции: Функция для применения (например, ROW_NUMBER, SUM, AVG)
• Предложение OVER: Определяет окно строк для функции
• PARTITION BY (опционально): Разделяет результирующий набор на партиции (группы)
• ORDER BY (опционально): Определяет порядок строк внутри каждой партиции
• предложение_оконного_фрейма (опционально): Дополнительно уточняет, какие строки включены в окно

Ваша первая оконная функция: ROW_NUMBER()

Начнём с одной из наиболее часто используемых оконных функций: ROW_NUMBER(). Эта функция присваивает уникальный последовательный номер каждой строке внутри партиции.

Пример 1: Нумерация всех платежей

SELECT
    payment_id,
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (ORDER BY payment_date) AS row_num
FROM
    payment
LIMIT 10;

Этот запрос присваивает последовательный номер каждому платежу, упорядоченному по дате платежа. Предложение OVER (ORDER BY payment_date) указывает SQL:

1. Упорядочить все строки по payment_date
2. Присвоить номера строк, начиная с 1

Пример 2: Нумерация внутри групп с использованием PARTITION BY

Истинная мощь оконных функций проявляется, когда вы используете PARTITION BY для создания отдельных окон для разных групп:

SELECT
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY payment_date
    ) AS payment_number
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id, 
    payment_date;

Вот что происходит:

• PARTITION BY customer_id создаёт отдельное окно для каждого клиента
• Внутри окна каждого клиента строки упорядочены по payment_date
• ROW_NUMBER() начинает отсчёт с 1 для каждого нового клиента
• Это позволяет увидеть 1-й, 2-й, 3-й платёж для каждого клиента

Визуализация:

Клиент 1:       Клиент 2:       Клиент 3:
Строка 1 ----\  Строка 1 ----\  Строка 1 ----\
Строка 2 -----\ Строка 2 -----\ Строка 2 -----\
Строка 3 ------\Строка 3 ------\Строка 3 ------\
   ...             ...             ...

Каждый клиент имеет свою независимую нумерацию строк.

Практическое применение

Поиск самой последней транзакции

Оконные функции упрощают идентификацию самой последней записи в каждой группе:

WITH numbered_payments AS (
    SELECT
        customer_id,
        amount,
        payment_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY payment_date DESC
        ) AS recency_rank
    FROM
        payment
)
SELECT
    customer_id,
    amount,
    payment_date
FROM
    numbered_payments
WHERE
    recency_rank = 1
ORDER BY
    customer_id
LIMIT 10;

Этот запрос находит самый последний платёж для каждого клиента:

1. Нумеруя платежи для каждого клиента в порядке убывания даты
2. Фильтруя по recency_rank = 1 (самый последний)

Сравнение каждой строки с агрегированными значениями

Оконные функции также могут выполнять агрегацию, сохраняя отдельные строки:

SELECT
    customer_id,
    amount,
    payment_date,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_spent,
    AVG(amount) OVER (PARTITION BY customer_id) AS avg_payment,
    amount - AVG(amount) OVER (PARTITION BY customer_id) AS diff_from_avg
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_date;

Для каждого платежа этот запрос показывает:

• Сумму отдельного платежа
• Общую сумму, потраченную этим клиентом (по всем его платежам)
• Среднюю сумму платежа для этого клиента
• Насколько этот конкретный платёж отличается от их среднего

Обратите внимание, как обычные агрегатные функции требовали бы GROUP BY и схлопывали строки, но оконные функции позволяют сохранить все детали, добавляя агрегированный контекст.

Оконные функции vs GROUP BY

Важно понимать разницу:

GROUP BY (Агрегатные функции):

SELECT
    customer_id,
    COUNT(*) AS payment_count,
    SUM(amount) AS total_amount
FROM
    payment
GROUP BY
    customer_id;

Результат: Одна строка на клиента

Оконные функции:

SELECT
    customer_id,
    payment_id,
    amount,
    COUNT(*) OVER (PARTITION BY customer_id) AS payment_count,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_amount
FROM
    payment;

Результат: Каждая строка платежа сохранена, с агрегированными значениями, добавленными как дополнительные столбцы

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

• Оконные функции выполняют вычисления по связанным строкам, сохраняя все отдельные строки в результирующем наборе.
• Предложение OVER является обязательным и определяет окно строк для работы функции.
• PARTITION BY разделяет данные на группы, при этом оконная функция применяется отдельно к каждой группе.
• ORDER BY внутри предложения OVER определяет порядок строк для функции (критично для функций вроде ROW_NUMBER()).
• Оконные функции идеальны для ранжирования, накопительных итогов, скользящих средних и сравнения отдельных значений с групповыми агрегатами.
• В отличие от GROUP BY, оконные функции не схлопывают строки — они добавляют вычисленные столбцы к вашим существующим данным.

В следующих уроках мы изучим больше оконных функций, таких как RANK(), DENSE_RANK(), NTILE(), и углубимся в оконные фреймы и продвинутые аналитические вычисления.