MySQL中对一组行进行累计求和

在本文中，我们将介绍如何在MySQL中对一组行进行累计求和（Cumulative sum over a set of rows），也就是对一列数据进行逐行求和并将结果依次存入另一列。

阅读更多：MySQL 教程

为什么需要对一组行进行累计求和？

MySQL中有时需要对一组行进行累计求和，例如：销售额累计、库存累计等。在实际应用中，可能需要计算的列数据并不是单纯的数值型数据，可能是一些经过条件判断转化而成的指标数据，这时候累计求和起来可能更有利于我们进行后续的统计分析和业务决策。

实现方法

方法1：使用变量

可以使用变量来累加求和，基本的实现思路如下：

设置变量@cumsum := 0。
将每一行数据的值与@cumsum相加得到累计值，并将累计值赋值给变量@cumsum。
将累计值存至另一列。

下面是一个简单的例子：

SET @cumsum := 0;

SELECT col, val, @cumsum := @cumsum + val AS cumsum
FROM table
ORDER BY col;

在上面的例子中，我们将table中的val列累加求和，并将结果存储在cumsum列中。

方法2：使用窗口函数

MySQL 8.0开始支持窗口函数，可以更为方便地实现对一组行进行累计求和。下面是一个使用窗口函数的例子：

SELECT col, val, SUM(val) OVER (ORDER BY col) AS cumsum
FROM table;

在上面的例子中，我们使用SUM()函数在col列上进行窗口求和，并将结果存储在cumsum列中。

示例应用

示例1

假设我们有一个表t1，它包含了销售订单的订单编号、销售日期和销售额，如下所示：

订单编号	销售日期	销售额
1	2022-01-01	100
2	2022-01-02	200
3	2022-01-03	150
4	2022-01-04	300

我们需要计算每个订单的销售额累计值（Cumulative Sales Amount），如下所示：

订单编号	销售日期	销售额	Cumulative Sales Amount
1	2022-01-01	100	100
2	2022-01-02	200	300
3	2022-01-03	150	450
4	2022-01-04	300	750

使用方法1可得到如下查询语句：

SET @cumsum := 0;

SELECT order_id, sale_date, amount, @cumsum := @cumsum + amount AS cumsum
FROM t1
ORDER BY order_id;

使用方法2可得到如下查询语句：

SELECT order_id, sale_date, amount, SUM(amount) OVER (ORDER BY order_id) AS cumsum
FROM t1;

示例2

假设我们有一个表t2，它包含了商品的生产日期和生产批次，如下所示：

生产日期	生产批次
2022-01-01	A001
2022-01-02	A002
2022-01-03	B001
2022-01-04	A003

我们需要计算每个生产批次的序号（Order），如下所示：

生产日期	生产批次	Order
2022-01-01	A001	1
2022-01-02	A002	2
2022-01-03	B001	1
2022-01-04	A003	3

其中，同一批次的产品应该具有相同的序号。

使用方法1可得到如下查询语句：

SET @order := 0;
SET @batch := '';

SELECT prod_date, prod_batch,
       CASE WHEN prod_batch = @batch THEN @order := @order + 1
            ELSE @order := 1 AND @batch := prod_batch END AS `Order`
FROM t2
ORDER BY prod_batch, prod_date;

使用方法2可得到如下查询语句：

SELECT prod_date, prod_batch,
       ROW_NUMBER() OVER (PARTITION BY prod_batch ORDER BY prod_date) AS `Order`
FROM t2
ORDER BY prod_batch, prod_date;