MySQL中实现遇Flag重置的滚动求和方法问询

需求说明 #

需要对数据表中的value字段求和，当flag为0时重置求和。原始数据表如下：

期望输出为每次重置后的求和结果：

用户推测可以先按规则分组（如下表）再求和，但不知道如何实现分组：

解决方案 #

MySQL 8.0及以上版本（推荐） #

利用窗口函数可以简洁实现分组和求和：

1. 生成分组标识

通过累计flag=0的次数来生成分组ID，每遇到一个flag=0，分组ID递增，同时让flag=0的行作为新分组的起点：

SELECT 
    id,
    value,
    flag,
    SUM(CASE WHEN flag = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) + 1 AS `group`
FROM your_table;

替换your_table为你的实际表名，执行后就能得到你需要的分组表。

2. 按分组求和

嵌套上面的查询，直接按分组求和即可：

SELECT SUM(value) AS sum
FROM (
    SELECT 
        value,
        SUM(CASE WHEN flag = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) + 1 AS `group`
    FROM your_table
) t
GROUP BY `group`
ORDER BY `group`;

MySQL 5.x版本 #

如果使用不支持窗口函数的旧版本，用用户变量实现：

SELECT SUM(value) AS sum
FROM (
    SELECT 
        value,
        @group_id := @group_id + (flag = 0) AS `group`
    FROM your_table, (SELECT @group_id := 1) init
    ORDER BY id
) t
GROUP BY `group`
ORDER BY `group`;

这里通过初始化变量@group_id为1，每遇到flag=0时变量自增1，生成分组ID后再求和。

内容的提问来源于stack exchange，提问作者Kevin Jones