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

Question

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

id   count
1    100
2    50
3    10

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

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

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

Answer 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;

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

Answer 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)

Answer 3

MySQL 8.0/MariaDB поддерживает оконный SUM(col) OVER():

SELECT *, SUM(cnt) OVER(ORDER BY id) AS cumulative_sum
FROM tab;

Выход:

┌─────┬──────┬────────────────┐
│ id  │ cnt  │ cumulative_sum │
├─────┼──────┼────────────────┤
│  1  │ 100  │            100 │
│  2  │  50  │            150 │
│  3  │  10  │            160 │
└─────┴──────┴────────────────┘

дб <> скрипка

Answer 4

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

Answer 5

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

Answer 6

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

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;
|

Я не проверял это

Answer 7

Выберите id,count,sum(count)over(order by count desc) как cumulative_sum из tableName;

Я использовал статистическую функцию суммы в столбце count, а затем использовал предложение over. Он суммирует каждый из рядов в отдельности. Первый ряд просто будет 100. Второй ряд будет 100+50. Третий ряд - 100+50+10 и т. Д. Таким образом, каждая строка является суммой всех предыдущих строк, а самая последняя - сумма всех строк. Таким образом, способ посмотреть на это - каждая строка представляет собой сумму суммы, в которой идентификатор меньше или равен самому себе.

Answer 8

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

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

Answer 9

  select t1.id, t1.count, SUM(t2.count) cumulative_sum
    from table t1 
        join table t2 on t1.id >= t2.id
    group by t1.id, t1.count

Шаг за шагом:

1- Учитывая следующую таблицу:

select *
from table t1 
order by t1.id;

id  | count
 1  |  11
 2  |  12   
 3  |  13

2 - Получение информации по группам

select *
from table t1 
    join table t2 on t1.id >= t2.id
order by t1.id, t2.id;

id  | count | id | count
 1  | 11    | 1  |  11

 2  | 12    | 1  |  11
 2  | 12    | 2  |  12

 3  | 13    | 1  |  11
 3  | 13    | 2  |  12
 3  | 13    | 3  |  13

3- Шаг 3: Суммируйте все подсчеты по группе t1.id

select t1.id, t1.count, SUM(t2.count) cumulative_sum
from table t1 
    join table t2 on t1.id >= t2.id
group by t1.id, t1.count;


id  | count | cumulative_sum
 1  |  11   |    11
 2  |  12   |    23
 3  |  13   |    36