如何实现按用户分组，日期间隔仅一天时的数值求和

这个需求的核心是识别每个用户的连续日期块（间隔1天即视为连续），然后对每个块的value求和对吧？我给你提供一个通用的SQL解决方案，以MySQL为例，思路清晰且容易扩展：

原始数据 #

需求回顾 #

按user分组，仅将日期间隔为1天的连续记录合并为一组，对value求和；非连续的日期则保留单独记录。预期结果如下：

解决方案代码 #

WITH ranked_data AS (
    SELECT 
        daytime,
        user,
        value,
        -- 标记每个用户的连续日期分组ID
        SUM(
            CASE 
                WHEN DATEDIFF(STR_TO_DATE(daytime, '%d%b%Y'), LAG(STR_TO_DATE(daytime, '%d%b%Y')) OVER (PARTITION BY user ORDER BY STR_TO_DATE(daytime, '%d%b%Y'))) != 1 
                THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY user ORDER BY STR_TO_DATE(daytime, '%d%b%Y')) AS group_id
    FROM your_table_name -- 替换成你的实际表名
)
SELECT 
    MIN(daytime) AS daytime, -- 取每组最早的日期作为结果日期
    user,
    SUM(value) AS value
FROM ranked_data
GROUP BY user, group_id
ORDER BY STR_TO_DATE(daytime, '%d%b%Y');

代码逻辑拆解 #

1. 日期格式转换：先用STR_TO_DATE把字符串格式的daytime转成MySQL能识别的日期类型，这样才能计算日期差。
2. 识别连续分组：
   • 用LAG()窗口函数获取当前用户的上一条记录的日期。
   • 用DATEDIFF计算当前日期和上一条日期的差值，如果差值不等于1，说明这是一个新的连续块，给group_id加1。
   • 通过SUM() OVER()累计这个标记值，最终每个记录都会得到一个唯一的分组ID，同一个连续日期块的记录ID相同。
3. 聚合求和：按user和group_id分组，对value求和，同时取每组的最早日期作为结果的日期字段，最后按日期排序。

执行这段代码后，就能完美得到你想要的结果啦～

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