MySQL 5.7中计算连续记录Count值差值的问题求助

当前查询的问题 #

• 未按Name分组处理: 全局变量@cnt在所有Name间共享，切换用户时不会重置，导致MIKE的Diff值继承自Mills的最后计算结果，不符合需求。
• 计算逻辑错误: 公式@cnt := @cnt - Count的差值方向错误，正确的Diff应为当前Count减去上一条记录的Count；同时第一条记录的Diff应为0，而非持续累积。
• 排序逻辑不完整: 仅按Date排序无法保证同一日期内的小时顺序，且未按Name分组排序，导致用户记录可能交叉，计算连续差值时顺序混乱。

正确的MySQL 5.7查询语句 #

由于MySQL 5.7不支持LAG()等窗口函数，需使用变量跟踪前一条记录的Name和Count值，实现分组计算连续差值：

SELECT 
    Name,
    Date,
    Hours,
    Count,
    CASE 
        WHEN @prev_name = Name THEN Count - @prev_count
        ELSE 0
    END AS Diff,
    @prev_count := Count,
    @prev_name := Name
FROM 
    Hourly,
    (SELECT @prev_name := '', @prev_count := 0) AS vars
ORDER BY 
    Name, Date, Hours;

核心逻辑说明 #

1. 变量初始化: 通过子查询初始化@prev_name（存储上一条记录的Name）和@prev_count（存储上一条记录的Count）。
2. 分组差值计算: 使用CASE语句判断当前记录是否属于同一用户：
   • 若属于同一用户，计算当前Count - 上一条Count作为Diff；
   • 若为新用户的第一条记录，Diff设为0。
3. 变量更新: 按顺序更新@prev_count和@prev_name，确保下一条记录能获取到当前的正确值。
4. 时序排序: 按Name, Date, Hours排序，保证每个用户的记录严格按时间顺序排列，确保连续差值计算的准确性。

执行结果 #

该语句将输出与期望完全一致的结果：每个用户的第一条记录Diff为0，后续记录为当前Count与前一条的差值，不同用户间的计算完全独立。

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