在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更新语句可以轻松地完成这个任务？最好的方法是什么？

- Kirk Ouimet

Answer 1

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

使用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匹配，并且对于更复杂的变量使用（例如：伪ROW_NUMBER/RANK功能，MySQL缺乏这些功能）可能会产生更大的影响。

- OMG Ponies

---

我会在主查询中添加"ORDER BY t.id ASC"，只是为了确保它始终能正常工作。 - Wacek

我的第一反应也是要添加ORDER BY。但这并不重要。至少在添加变成非关联之前不会出问题 :) - Dercsár

@OMG Poines：我认为你需要在变量示例的 JOIN (SELECT @running_total := 0) 部分使用 SELECT。 - Daniel Vassallo

使用相关查询时，你的表x是从哪里来的？ - allan.simon

如果您想要从表中获取累计计数，则表x与表t相同（您需要在源表的每一行上迭代源表，以便在当前元素之前计算总元素数）。 - Evhz

Answer 2

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

这个函数可以以累加的方式计算运行总和 :)

- Andomar

---

使用交叉连接（cross join）来定义变量，无需使用 SET。 - OMG Ponies

我的表格有3600万条记录，所以这真的很有帮助，可以加快速度！ - Kirk Ouimet

注意，按cumulative_sum排序可能会强制执行全表扫描。 - matt

这样做起来很有效率，速度也很快；有没有建议可以扩展到在一个分组内做累加和呢？例如按姓名或类似方式进行分组，然后仅对具有相同名称的记录执行累加和操作。 - zaitsman

请参考 https://dev59.com/y3E85IYBdhLWcg3w43sW#52278657，MySQL 8.0+ 中 OLAP 函数的答案更佳。 - Yuki Inoue

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

db<>fiddle

- Lukasz Szozda

---

我正在寻找使用窗口函数的累积和。谢谢。 - DatabaseCoder

@lukasz szozda，您如何将此数据插入数据库表列中，以便在其他表中使用？谢谢。 - kejo

@kejo INSERT INTO table_name(id, cnt, cumulative_sum) SELECT ... FROM ... 或者 CREATE TABLE table_name AS SELECT ... FROM ... - Lukasz Szozda

Answer 4

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

- Dercsár

---

尽管原帖确实要求更新，但这种做法会导致数据冗余，且很可能难以正确维护。 - Matthew Flaschen

Answer 5

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

- Ashutosh SIngh

---

请解释你的答案。 - Rohit Gupta

答案可行且只有一行代码。它还会在选择语句的开头将变量初始化/重置为零。 - raisercostin

Answer 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

- Jazz

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

我没有测试过这个

- Greg

Answer 8

使用sum聚合函数在count列上，并使用over子句。它会对每一行进行单独求和。第一行将只是100。第二行将是100 + 50。第三行是100 + 50 + 10等等。因此，每一行基本上都是它自己和所有前面行的总和，最后一行是所有行的总和。因此，查看此方式是，每一行都是ID小于或等于自身的金额总和。

- Pavan Bashetty

---

虽然这可能解决问题，但最好稍微解释一下，以便让其他人受益 :) - Til

这不是一个相关子查询，也不是一个子查询... 相关子查询的语法如下：SELECT ...., (SELECT .... FROM table2 WHERE table2.id = table1.id ) FROM table1。而你所使用的是窗口查询。 - Raymond Nijland

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

第三步：按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

---

添加了一些逐步说明以理解最终查询。 - Flavio_cava