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命令即可进行变量声明。 对于任何子查询/派生表/内联视图，MySQL都需要表别名
r。注意事项：
MySQL特定；无法移植到其他数据库
ORDER BY很重要；它确保顺序与OP匹配，并且对更复杂的变量使用可能具有更大的含义（即MySQL缺少的psuedo ROW_NUMBER / RANK功能）评论
我会在主查询中添加“ ORDER BY t.id ASC”，以确保其始终有效
–Wacek
2010年4月1日在22:20
我的第一个想法就是添加ORDER BY。但这无关紧要。直到加法变为非关联，至少:)
–Dercsár
2010年4月2日在7:24
@OMG Poines：我认为您需要在变量示例的JOIN（SELECT @running_total：= 0）部分中使用SELECT。
–丹尼尔·瓦萨洛（Daniel Vassallo）
10年4月28日在6:48
用于“使用相关查询”，您的表x来自哪里？
–allan.simon
16 Sep 12'在15:40
除非内部没有进行优化，否则相关子查询将等同于在O（N ^ 2）时间内执行的三角连接-这将不会扩展。
– Marc L.
16年2月2日在16:18
#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 │
└─────┴──────┴────────────────┘
db <>小提琴
评论
我正在使用Windows函数寻找累积和。谢谢。
–DatabaseCoder
19年8月30日在6:13
#4 楼
UPDATE t
SET cumulative_sum = (
SELECT SUM(x.count)
FROM t x
WHERE x.id <= t.id
)
评论
尽管OP确实要求更新，但此操作已被规范化，可能不便于正确维护。
–马修·弗拉申（Matthew Flaschen）
2010-4-1 22:05
#5 楼
select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;
评论
请解释你的答案
–罗希特·古普塔（Rohit Gupta）
15-10-27在0:26
答案是有效的，并且是一个班轮。在选择开始时，还将变量初始化/重置为零。
–raisercostin
17年2月13日在14:41
#6 楼
示例查询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
#7 楼
您还可以创建一个触发器，该触发器将在每次插入之前计算总和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;
|
我还没有测试过这个
#8 楼
从tableName中选择id，count，sum（count）over（按count desc排序）作为cumulative_sum；我在count列上使用了sum聚合函数，然后使用了over子句。它分别汇总每一行。第一行将是100。第二行将是100 + 50。第三行是100 + 50 + 10，依此类推。因此，基本上每一行都是它与先前所有行的总和，最后一行是所有行的总和。因此，查看此行的方式是每一行是ID小于或等于自身的数量之和。
评论
虽然这可能会解决问题，但最好对其进行一些解释，以使其他人受益：)
– Tiw
19年2月22日在2:40
这不是一个相关的子查询或与此相关的子查询...相关的子查询遵循SELECT ....，（SELECT .... FROM table2 WHERE table2.id = table1.id）FROM table1有一个窗口查询。
–雷蒙德·尼兰（Raymond Nijland）
19-2-23在23:47
#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-获取信息groups
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
评论
添加了一些逐步了解最终查询的内容
– Flavio_cava
5月20日14:13
评论
使用交叉联接来定义变量，而无需使用SET。
–OMG小马
2010-4-1 22:12
我的表有3600万条记录，因此这对于加快速度非常有帮助！
– Kirk Ouimet
2010年4月2日，凌晨1:20
请注意，按cumulative_sum排序可能会强制执行全表扫描。
–马特
2012年9月25日上午9:23
这确实有效，而且看起来很快。有什么建议可以将其扩展为一个小组的累加总和？例如按名称或类似名称分组，然后仅对具有相同名称的记录进行累计
– Zaitsman
17年8月31日在12:36
@zaitsman您可以将其用作子查询；在外部查询上，根据需要进行分组，然后使用MAX（）MySQL函数获取为组内的记录计算的正确的累积摘要（最后一个摘要）。
–帕斯卡
18年7月12日在6:51