在 MySQL 中创建累积总和列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2563918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a Cumulative Sum Column in MySQL
提问by Kirk Ouimet
I have a table that looks like this:
我有一张看起来像这样的表：
id count
1 100
2 50
3 10
I want to add a new column called cumulative_sum, so the table would look like this:
我想添加一个名为cumulative_sum 的新列，因此该表将如下所示：
id count cumulative_sum
1 100 100
2 50 150
3 10 160
Is there a MySQL update statement that can do this easily? What's the best way to accomplish this?
是否有可以轻松完成此操作的 MySQL 更新语句？实现这一目标的最佳方法是什么？
回答by OMG Ponies
Using a correlated query:
使用相关查询：
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
Using MySQL variables:
使用 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
Note:
笔记：
- The
JOIN (SELECT @running_total := 0) ris a cross join, and allows for variable declaration without requiring a separateSETcommand. - The table alias,
r, is required by MySQL for any subquery/derived table/inline view
- 这
JOIN (SELECT @running_total := 0) r是一个交叉连接，允许在不需要单独SET命令的情况下进行变量声明。 rMySQL 需要表别名，用于任何子查询/派生表/内联视图
Caveats:
注意事项：
- MySQL specific; not portable to other databases
- The
ORDER BYis important; it ensures the order matches the OP and can have larger implications for more complicated variable usage (IE: psuedo ROW_NUMBER/RANK functionality, which MySQL lacks)
- MySQL特定的；不可移植到其他数据库
- 该
ORDER BY是很重要的; 它确保顺序与 OP 匹配，并且可以对更复杂的变量使用产生更大的影响（即：psuedo ROW_NUMBER/RANK 功能，MySQL 缺乏）
回答by Andomar
If performance is an issue, you could use a MySQL variable:
如果性能是一个问题，您可以使用 MySQL 变量：
set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;
Alternatively, you could remove the cumulative_sumcolumn and calculate it on each query:
或者，您可以删除该cumulative_sum列并在每个查询上计算它：
set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;
This calculates the running sum in a running way :)
这以运行方式计算运行总和:)
回答by Lukasz Szozda
MySQL 8.0/MariaDB supports windowed SUM(col) OVER():
MySQL 8.0/MariaDB 支持窗口化SUM(col) OVER()：
SELECT *, SUM(cnt) OVER(ORDER BY id) AS cumulative_sum
FROM tab;
Output:
输出：
┌─────┬──────┬────────────────┐
│ id │ cnt │ cumulative_sum │
├─────┼──────┼────────────────┤
│ 1 │ 100 │ 100 │
│ 2 │ 50 │ 150 │
│ 3 │ 10 │ 160 │
└─────┴──────┴────────────────┘
回答by Ashutosh SIngh
select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;
回答by Dercsár
UPDATE t
SET cumulative_sum = (
SELECT SUM(x.count)
FROM t x
WHERE x.id <= t.id
)
回答by Jazz
Sample query
示例查询
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
回答by Greg
You could also create a trigger that will calculate the sum before each insert
您还可以创建一个触发器，在每次插入之前计算总和
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;
|
I have not tested this
我没有测试过这个
回答by Pavan Bashetty
select id,count,sum(count)over(order by count desc) as cumulative_sum from tableName;
从 tableName 中选择 id,count,sum(count)over(order by count desc) 作为cumulative_sum；
I have used the sum aggregate function on the count column and then used the over clause. It sums up each one of the rows individually. The first row is just going to be 100. The second row is going to be 100+50. The third row is 100+50+10 and so forth. So basically every row is the sum of it and all the previous rows and the very last one is the sum of all the rows. So the way to look at this is each row is the sum of the amount where the ID is less than or equal to itself.
我在 count 列上使用了 sum 聚合函数，然后使用了 over 子句。它单独总结每一行。第一行就是 100。第二行就是 100+50。第三行是 100+50+10 等等。所以基本上每一行都是它和之前所有行的总和，最后一行是所有行的总和。所以看待这个的方法是每一行是ID小于或等于自身的数量的总和。
回答by Flavio_cava
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
Step by step:
一步步：
1- Given the following table:
1- 给定下表：
select *
from table t1
order by t1.id;
id | count
1 | 11
2 | 12
3 | 13
2 - Get information by groups
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- Step 3: Sum all count by t1.id group
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
