Создание столбца совокупной суммы в MySQL

У меня есть таблица, которая выглядит так:

id   count
1    100
2    50
3    10

Я хочу добавить новый столбец cumulative_sum, поэтому таблица будет выглядеть так:

id   count  cumulative_sum
1    100    100
2    50     150
3    10     160

Есть ли инструкция по обновлению MySQL, которая может сделать это легко? Какой лучший способ выполнить это?

Ответы

Ответ 1

Если производительность является проблемой, вы можете использовать переменную MySQL:

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

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

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

Это вычисляет текущую сумму в бегущей строке:)

Ответ 2

Использование коррелированного запроса:

  SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

Использование переменных MySQL:

  SELECT t.id,
         t.count,
         @running_total := @running_total + t.count AS cumulative_sum
    FROM TABLE t
    JOIN (SELECT @running_total := 0) r
ORDER BY t.id

Примечание:

  • JOIN (SELECT @running_total := 0) r является перекрестным объединением и допускает объявление переменной без отдельной команды SET.
  • Атрибут таблицы r, требуется MySQL для любого подзапроса/производной таблицы/встроенного представления

Предостережения:

  • Спецификация MySQL; не переносится в другие базы данных.
  • Важна ORDER BY; он гарантирует, что порядок соответствует OP и может иметь более серьезные последствия для более сложного использования переменных (IE: psuedo ROW_NUMBER/RANK функциональность, которой не хватает MySQL)

Ответ 3

UPDATE t
SET cumulative_sum = (
 SELECT SUM(x.count)
 FROM t x
 WHERE x.id <= t.id
)

Ответ 4

Пример запроса

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(date) AS d,
       COUNT(*) AS c
    FROM  orders
    WHERE  hasPaid > 0
    GROUP  BY d
    ORDER  BY d) AS q1

Ответ 5

Вы также можете создать триггер, который будет вычислять сумму перед каждой вставкой

delimiter |

CREATE TRIGGER calCumluativeSum  BEFORE INSERT ON someTable
  FOR EACH ROW BEGIN

  SET cumulative_sum = (
     SELECT SUM(x.count)
        FROM someTable x
        WHERE x.id <= NEW.id
    )

    set  NEW.cumulative_sum = cumulative_sum;
  END;
|

Я не тестировал этот

Ответ 6

select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;