MySQL中按日期和用户列分组计算累计求和的实现方法

嘿，我来帮你搞定这个按日期和用户分组做累计求和的需求！先把你的表结构和示例数据整理清楚，再分几种常见场景给出解决方案：

你的表结构与示例数据 #

场景1：按用户+日期分组，计算每日各flag的总和 #

如果你的需求是先把同一天同一用户的所有记录合并，算出当天每个flag的总数值，用DATE()提取日期后分组聚合就行：

SELECT
    DATE(datetime) AS record_date,
    name,
    SUM(flag1) AS daily_flag1_sum,
    SUM(flag2) AS daily_flag2_sum,
    SUM(flag3) AS daily_flag3_sum
FROM your_table_name
GROUP BY record_date, name
ORDER BY name, record_date;

比如john在2018-05-06的flag1总和就是0+1+1=2，flag2是2+0+1=3，完全符合分组求和的需求。

场景2：按用户+日期分组，计算跨日期的累计总和 #

如果需要的是累计求和（比如用户john在2018-05-05的总和加上2018-05-06的总和，得到截至5月6日的累计值），MySQL 8.0及以上版本用窗口函数最方便：

WITH daily_sums AS (
    SELECT
        DATE(datetime) AS record_date,
        name,
        SUM(flag1) AS daily_flag1_sum,
        SUM(flag2) AS daily_flag2_sum,
        SUM(flag3) AS daily_flag3_sum
    FROM your_table_name
    GROUP BY record_date, name
)
SELECT
    record_date,
    name,
    SUM(daily_flag1_sum) OVER (PARTITION BY name ORDER BY record_date) AS cumulative_flag1,
    SUM(daily_flag2_sum) OVER (PARTITION BY name ORDER BY record_date) AS cumulative_flag2,
    SUM(daily_flag3_sum) OVER (PARTITION BY name ORDER BY record_date) AS cumulative_flag3
FROM daily_sums
ORDER BY name, record_date;

这里先用CTEdaily_sums算出每日总和，再用SUM() OVER (PARTITION BY name ORDER BY record_date)按用户分组、日期排序，自动计算从最早日期到当前日期的累计值，简洁高效。

如果你的MySQL版本低于8.0（不支持CTE和窗口函数），可以用子查询关联的写法兼容：

SELECT
    d1.record_date,
    d1.name,
    (SELECT SUM(d2.daily_flag1_sum) FROM (
        SELECT DATE(datetime) AS record_date, name, SUM(flag1) AS daily_flag1_sum
        FROM your_table_name GROUP BY record_date, name
    ) d2 WHERE d2.name = d1.name AND d2.record_date <= d1.record_date) AS cumulative_flag1,
    (SELECT SUM(d2.daily_flag2_sum) FROM (
        SELECT DATE(datetime) AS record_date, name, SUM(flag2) AS daily_flag2_sum
        FROM your_table_name GROUP BY record_date, name
    ) d2 WHERE d2.name = d1.name AND d2.record_date <= d1.record_date) AS cumulative_flag2,
    (SELECT SUM(d2.daily_flag3_sum) FROM (
        SELECT DATE(datetime) AS record_date, name, SUM(flag3) AS daily_flag3_sum
        FROM your_table_name GROUP BY record_date, name
    ) d2 WHERE d2.name = d1.name AND d2.record_date <= d1.record_date) AS cumulative_flag3
FROM (
    SELECT DATE(datetime) AS record_date, name
    FROM your_table_name GROUP BY record_date, name
) d1
ORDER BY d1.name, d1.record_date;

这种写法虽然长一点，但老版本MySQL也能跑，就是数据量大的时候要注意性能优化。

场景3：当日内按时间顺序的累计求和 #

如果你的“累计求和”是指当天内每条记录的实时累计（比如john在2018-05-06 00:00:05的flag1是0，到00:00:10累计为1，到00:00:15累计为2），直接对原始数据用窗口函数即可：

SELECT
    datetime,
    name,
    SUM(flag1) OVER (PARTITION BY name, DATE(datetime) ORDER BY datetime) AS cumulative_flag1_daily,
    SUM(flag2) OVER (PARTITION BY name, DATE(datetime) ORDER BY datetime) AS cumulative_flag2_daily,
    SUM(flag3) OVER (PARTITION BY name, DATE(datetime) ORDER BY datetime) AS cumulative_flag3_daily
FROM your_table_name
ORDER BY name, datetime;

这个查询会输出每条记录对应的当日累计值，完全贴合时间序列的累计需求。

注意：记得把所有SQL里的your_table_name替换成你实际的表名哦！

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