Накопительная сумма по набору строк в MySQL

ОБНОВЛЕНИЕ

MySQL 8.0 представляет «оконные функции», функциональные возможности, эквивалентные «оконным функциям» SQL Server (с разделением и упорядочиванием, обеспечиваемым синтаксисом Transact-SQL OVER), и «аналитическими функциями» Oracle.

Справочное руководство MySQL 12.21 Функции окна https://dev.mysql.com/doc/refman/8.0/en/window-functions.html.

Приведенный здесь ответ - подход для версий MySQL до 8.0.

ОРИГИНАЛЬНЫЙ ОТВЕТ

MySQL не предоставляет аналитическую функцию типа, которую вы использовали бы для получения текущей «совокупной суммы», как аналитические функции, доступные в других СУБД (например, Oracle или SQL Server).

Но можно эмулировать некоторые аналитические функции, используя MySQL.

Есть (как минимум) два работоспособных подхода:

Один из них - использовать коррелированный подзапрос для получения промежуточной суммы. Этот подход может быть дорогостоящим для больших наборов и сложным, если предикаты внешнего запроса сложны. Это действительно зависит от того, насколько сложным является «несколько объединений в нескольких таблицах». (К сожалению, MySQL также не поддерживает CTE.)

Другой подход - использовать пользовательские переменные MySQL для обработки прерывания управления. «Уловка» здесь заключается в том, чтобы результаты вашего запроса были отсортированы (с использованием ORDER BY), а затем ваш запрос был заключен в другой запрос.

Приведу пример последнего подхода.

Из-за порядка, в котором MySQL выполняет операции, столбец cumulative_total должен быть вычислен до того, как значение из id и day из текущей строки будет сохранено в пользовательских переменных. Проще всего поставить эту колонку на первое место.

Встроенное представление с псевдонимом i (в запросе ниже) предназначено только для инициализации пользовательских переменных, на всякий случай, если они уже установлены в сеансе. Если им уже присвоены значения, мы хотим игнорировать их текущие значения, и самый простой способ сделать это - инициализировать их.

Исходный запрос заключен в круглые скобки, а в приведенном ниже примере ему будет присвоен псевдоним c. Единственное изменение в исходном запросе - это добавление предложения ORDER BY, поэтому мы можем быть уверены, что обрабатываем строки из запроса последовательно.

Внешний выбор проверяет, совпадают ли значения id и day из текущей строки с предыдущей строкой. Если это так, мы добавляем amount из текущей строки к совокупному промежуточному итогу. Если они не совпадают, мы сбрасываем совокупный промежуточный итог до нуля и добавляем сумму из текущей строки (или, проще говоря, просто назначаем сумму из текущей строки).

После того, как мы вычислили совокупную сумму, мы сохраняем значения id и day из текущей строки в пользовательские переменные, чтобы они были доступны при обработке следующей строки.

Например:

SELECT IF(@prev_id = c.id AND @prev_day = c.day
         ,@cumtotal := @cumtotal + c.amount
         ,@cumtotal := c.amount) AS cumulative_total
     , @prev_id  := c.id  AS `id`
     , @prev_day := c.day AS `day`
     , c.hr
     , c.amount AS `amount'
  FROM ( SELECT @prev_id  := NULL
              , @prev_day := NULL
              , @subtotal := 0
       ) i
  JOIN (

         select id, day, hr, amount from
         ( //multiple joins on multiple tables)a
         left join
         (//unions on multiple tables)b
         on a.id=b.id

         ORDER BY 1,2,3
       ) c

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

SELECT d.id
     , d.day
     , d.hr
     , d.amount
     , d.cumulative_total
FROM (
       // query from above
     ) d

Если вы используете MySQL 8 или новее, вы должны использовать оконные функции для этого. Ваш запрос будет выглядеть так:

SELECT
  id, day, hr, amount,
  SUM (amount) OVER (PARTITION BY id, day ORDER BY hr) AS `cumulative total`
FROM t

Где t - ваш стол, b слева присоединенный к a. Некоторые примечания:

• Предложение PARTITION BY гарантирует, что вы получите кумулятивную сумму по id и day, поэтому каждый день мы начинаем суммировать заново.
• Предложение ORDER BY определяет, в каком порядке должна происходить кумуляция.

вот и ваша кульминация ...

select f1.id, f1.day, f1.hr, f1.amount, sum(f2.amount) as culminative_total from foo f1
 inner join foo f2 on (f1.day = f2.day and f1.id=f2.id)
 where f2.hr <= f1.hr
 group by f1.id, f1.day, f1.hour;