blocks|key|1072481|text|UPDATE+t
SET+cumulative_sum+=+(
+SELECT+SUM(x.count)
+FROM+t+x
+WHERE+x.id+<=+t.id
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1072482|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>UPDATE t
SET cumulative_sum = (
 SELECT SUM(x.count)
 FROM t x
 WHERE x.id &lt;= t.id
)
</code></pre>

blocks|key|1287375|text|还可以创建一个触发器，该触发器将在每次插入之前计算总和|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1287376|delimiter+%7C

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;
%7C|code-block|syntax|javascript|1287377|我还没有测试过这个|1287378|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You could also create a trigger that will calculate the sum before each insert 

<pre><code>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 &lt;= NEW.id
 )

 set NEW.cumulative_sum = cumulative_sum;
 END;
|
</code></pre>

I have not tested this

blocks|key|1287446|text|如果性能有问题，您可以使用MySQL变量：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1287447|set+@csum+:=+0;
update+YourTable
set+cumulative_sum+=+(@csum+:=+@csum+%2B+count)
order+by+id;|code-block|syntax|javascript|1287448|或者，您可以删除cumulative_sum列并在每个查询上计算它：|offset|length|style|CODE|1287449|set+@csum+:=+0;
select+id,+count,+(@csum+:=+@csum+%2B+count)+as+cumulative_sum
from+YourTable
order+by+id;|1287450|这将以一种运行的方式计算运行和:)|1287451|entityMap^0|0|0|8|E|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

If performance is an issue, you could use a MySQL variable:

<pre><code>set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;
</code></pre>

Alternatively, you could remove the <code>cumulative_sum</code> column and calculate it on each query:

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

This calculates the running sum in a running way :)

blocks|key|1072628|text|示例查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072629|SET+@runtot:=0;
SELECT
+++q1.d,
+++q1.c,
+++(@runtot+:=+@runtot+%2B+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|code-block|syntax|javascript|1072630|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Sample query 

<pre><code>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 &gt; 0
 GROUP BY d
 ORDER BY d) AS q1
</code></pre>

blocks|key|1072638|text|select+Id,+Count,+@total+:=+@total+%2B+Count+as+cumulative_sum
from+YourTable,+(Select+@total+:=+0)+as+total+;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1072639|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;
</code></pre>

blocks|key|1072723|text|select+id，count，sum(+count+)over(order+by+Count+desc)+as+cumulative_sum+from+tableName；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072724|我对count列使用了sum聚合函数，然后使用了over子句。它分别对每一行求和。第一行将是100。第二行将是100%2B50。第三行是100%2B50%2B10等等。所以基本上每一行都是它和前面所有行的和，最后一行是所有行的和。所以看这一点的方法是，每一行都是ID小于或等于其自身的总量的总和。|1072725|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

select id,count,sum(count)over(order by count desc) as cumulative_sum from tableName;

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.

I have a table that looks like this:

<pre><code>id count
1 100
2 50
3 10
</code></pre>

I want to add a new column called cumulative_sum, so the table would look like this:

<pre><code>id count cumulative_sum
1 100 100
2 50 150
3 10 160
</code></pre>

Is there a MySQL update statement that can do this easily? What's the best way to accomplish this?

Create a Cumulative Sum Column in MySQL

MySQL

我有一个表，看起来像这样：id   count1    1002    503    10我想添加一个名为cumulative_sum的新列，因此该表将如下所示：id   count  cumulative_sum1    100    1002    50     1503    10     160有没有一条MyS...

问在MySQL中创建累计和列
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中创建累计和列EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中创建累计和列
EN