Оконные функции MySQL: ранжируем расписание и выручку

Часть 14 из 16

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

В этой части построим нумерацию слотов и рейтинг событий, а затем сравним оконный расчёт с группировкой, чтобы было ясно, какой инструмент выбирать.

Нумеруем слоты в событии

SELECT event_id,starts_at,artist_id,
 ROW_NUMBER() OVER (
  PARTITION BY event_id ORDER BY starts_at
 ) AS slot_number
FROM performances
ORDER BY event_id,starts_at;

PARTITION BY начинает нумерацию отдельно внутри каждого события. Внутренний ORDER BY задаёт расчёт, внешний — порядок показа всего результата.

Ранжируем выручку

WITH per_event AS (
 SELECT event_id,SUM(price) AS revenue
 FROM ticket_sales GROUP BY event_id
)
SELECT event_id,revenue,
 DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM per_event;

DENSE_RANK присвоит одинаковый ранг при равной выручке без пропуска следующего номера. ROW_NUMBER всегда выдаёт разные номера строк.

Смотрим на предыдущий слот

SELECT event_id,starts_at,
 LAG(ends_at) OVER (
  PARTITION BY event_id ORDER BY starts_at
 ) AS previous_end
FROM performances;

LAG берёт значение предыдущей строки. У первого слота предыдущего значения нет, поэтому будет NULL.

Где читать подробности

Оконные функции поддерживаются в современных MySQL 8.4 и 9.7. Описание синтаксиса есть в официальном справочнике. Дальше проверим, какой план выбрал сервер, и настроим права пользователя.

Чем окно отличается от GROUP BY

Если у события пять выступлений, запрос с GROUP BY event_id вернёт одну строку на событие. ROW_NUMBER() OVER (...) оставляет все пять строк и добавляет каждой номер. Поэтому можно показать расписание и одновременно обозначить порядок выступлений.

PARTITION BY event_id делит результат на отдельные группы расчёта; ORDER BY starts_at задаёт очередность внутри группы. Если два слота начинаются одновременно, укажите дополнительное поле сортировки, например performance_id, чтобы порядок был детерминированным.

Считаем накопительную выручку

SELECT event_id, sold_at, price,
       SUM(price) OVER (
         PARTITION BY event_id
         ORDER BY sold_at, sale_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM ticket_sales
ORDER BY event_id, sold_at, sale_id;

Рамка окна начинается с первой строки события и заканчивается текущей. Результат показывает, как накапливается выручка после каждой продажи. Это не меняет строки таблицы и не сохраняет расчёт.

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

Ранги для ничьих и стабильный порядок

У ROW_NUMBER() каждая строка получает уникальный номер, даже если две продажи имеют одинаковое время. Чтобы номер не менялся от запуска к запуску, добавьте уникальный ключ последним полем сортировки: ORDER BY sold_at, sale_id. RANK() и DENSE_RANK() предназначены для рейтингов: одинаковые значения делят место.

RANK пропускает последующий номер, DENSE_RANK — нет.

Окно не ускоряет автоматически

Оконный запрос всё ещё должен прочитать строки, упорядочить разделы и вычислить значения. На небольшом фестивале это удобно и быстро, но при больших объёмах полезно изучить EXPLAIN и индексы.

Не переносите вычисляемый результат в отдельную таблицу без причины: тогда понадобится поддерживать его актуальность при каждом изменении продажи.

Практика: выберите несколько продаж одного события, добавьте running_revenue и вручную проверьте накопительную сумму. Затем удалите PARTITION BY event_id в копии запроса и сравните: сумма начнёт накапливаться по всем событиям вместе. Это наглядно показывает, за что отвечает секционирование окна.

Группировка или оконная функция?

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

← Предыдущая статья: Представления MySQL: сохраняем повторяемый отчёт

Следующая статья: EXPLAIN и права MySQL: ускоряем и защищаем базу →