在 MySQL 中创建累积和列

我有一张桌子是这样的:

id   count
1    100
2    50
3    10

我想添加一个新列，名为  累积 _ sum，这样表格看起来就像这样:

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

是否有一个 MySQL 更新语句可以很容易地做到这一点? 什么是最好的方法来实现这一点？

130209 次浏览
小开

使用相关查询:

  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; 不能移植到其他数据库
  • ORDER BY非常重要; 它确保顺序与 OP 相匹配，并且可以对更复杂的变量使用产生更大的影响(例如: psuedo ROW _ NUMBER/RANK 功能，这是 MySQL 所不具备的)
小开 
UPDATE t
SET cumulative_sum = (
SELECT SUM(x.count)
FROM t x
WHERE x.id <= t.id
)
小开

您还可以创建一个触发器，该触发器将在每次插入之前计算总和

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

我还没测试过

小开
最佳答案

如果性能有问题，可以使用 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;

这是以一种流动的方式计算流动的总和:)

小开

示例查询

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
小开 
select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;
小开

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 │
└─────┴──────┴────────────────┘

分贝 < > 小提琴

小开

选择 id，count，sum (count) over (order by count desc)作为 tableName 的累积 _ sum;

我在 count 列上使用了 sum 累计函数，然后使用了 over 子句。它分别对每一行进行求和。第一排只有100个人。第二行是100 + 50。第三行是100 + 50 + 10等等。所以基本上每一行都是它和之前所有行的总和最后一行是所有行的总和。看待这个问题的方法是每一行是 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

一步一步来:

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: 通过 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

提交答案