Вычисление накопленного итога в MySQL: функции окон, переменные

Быстрый ответ

Вычисления кумулятивной суммы в MySQL удобно осуществлять с помощью сессионных переменных. Рассмотрим пример запроса для таблицы sales, содержащей столбцы id и amount:

SELECT id, amount, @total := @total + amount AS running_total
FROM sales, (SELECT @total := 0) AS var
ORDER BY id;

В этом запросе переменная @total увеличивается на значение из столбца amount на каждой строке, формируя кумулятивную сумму с сортировкой по id.

Понимание различных контекстов

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

Пользовательские переменные и значения NULL

Использование пользовательских переменных, например @total, может привести к непредсказуемым результатам в версиях MySQL до 8.0.22. В таких ситуациях для большей точности рекомендуется использовать оконные функции и проводить расчёт с заранее инициализированными значениями.

Подробнее об этом расскажет наш спикер на видео

Применение оконных функций

В версии MySQL 8.0 и новее поддерживается использование оконных функций, что удобно для расчёта кумулятивной суммы:

SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

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

Группировка и упорядочивание данных

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

SELECT
  date,
  SUM(daily_sales),
  SUM(SUM(daily_sales)) OVER (ORDER BY date ASC) AS running_total
FROM sales
GROUP BY date
ORDER BY date;

Производные таблицы и общие табличные выражения

Производные таблицы и общие табличные выражения (CTE) предоставляют дополнительный контроль над процессом выборки данных и расчётами:

WITH ordered_sales AS (
  SELECT id, date, amount
  FROM sales
  ORDER BY date, id
)
SELECT
  id,
  date,
  amount,
  SUM(amount) OVER (ORDER BY date, id) AS running_total
FROM ordered_sales;

Максимизация производительности с помощью индексов

Для оптимизации запросов необходимо добавить индексы к столбцам, использующимся в сортировке ORDER BY оконной функции или в JOIN. Эффективность обработки данных можно улучшить, применяя планы выполнения и выполняя фильтрацию данных через WHERE.

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

Представьте водопад:

Уровень скалы:    [1]    [2]    [3]    [4]    [5]
Объём воды:       [💧]   [💧💧]  [💧💧💧]  [💧💧💧💧]  [💧💧💧💧💧]

На каждом этапе объём воды увеличивается — так же, как и в SQL-запросе по расчёту кумулятивной суммы:

SELECT
  time,
  amount,
  SUM(amount) OVER (ORDER BY time ASC) as running_total
FROM transactions;

Результат:

Транзакции:       [1-я 💰]   [2-я 💰]   [3-я 💰]   [4-я 💰]
Накопительна сумма:  [💰]       [💰💰]     [💰💰💰]     [💰💰💰💰]

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

Полезно знать

За рамками SQL

Иногда кумулятивную сумму удобнее рассчитывать в коде приложения, что обеспечивает лучшую гибкость и производительность, особенно при работе с большими объёмами данных или при использовании кеширования.

Учёт объединений

Когда одного простого запроса недостаточно, объединения и подзапросы предоставляют больше возможностей для структурирования данных при расчёте кумулятивных сумм.

Полезные материалы

1. MySQL :: MySQL 8.0 Reference Manual :: 14.20 Window Functions — официальная документация MySQL по оконным функциям.
2. SQL Window Functions | Advanced SQL – Mode — обзор оконных функций SQL от компании Mode Analytics.
3. Not Acceptable! — учебник по созданию коррелированных подзапросов в MySQL.
4. Window Functions – MariaDB Knowledge Base — справочник по оконным функциям, применимый для MySQL.
5. [MySQL Cookbook [Book]](https://www.oreilly.com/library/view/mysql-cookbook/0596001452/) — коллекция готовых решений и примеров для MySQL.
6. Who has to add the right indexes to an SQL database? — статья о важности индексирования для работы с базами данных MySQL.