MySQL连续行差值：特定月份日均用量平均值计算方法咨询

嗨，Andrewgerm！针对你开发家庭管理Web应用时遇到的这两个问题，我来分享一下实际开发中的思路：

因为你的数据表是每日一条唯一记录，我们可以利用SQL的窗口函数直接实时计算，不需要额外预处理。核心思路是先算出每日的实际用量（当日读数 - 前一日读数），再对目标月份的有效用量取平均值。

这里给你一个可直接复用的SQL示例：

SELECT 
    ROUND(AVG(daily_usage), 2) AS average_daily_usage -- 保留两位小数更直观
FROM (
    SELECT 
        DATE,
        -- 用LAG函数获取前一天的读数，按日期排序
        READING - LAG(READING, 1) OVER (ORDER BY DATE) AS daily_usage
    FROM your_table
    -- 筛选目标月份，替换成你需要的年月格式
    WHERE DATE_FORMAT(DATE, '%Y-%m') = '2024-06'
) AS monthly_daily_usage
-- 排除当月第一天（没有前一天数据，daily_usage为NULL）
WHERE daily_usage IS NOT NULL;

解释一下：

• LAG(READING, 1) OVER (ORDER BY DATE) 会按日期顺序，为每一行获取上一行的READING值；
• 相减得到的daily_usage就是当日实际用量；
• 最后对当月所有有效（非NULL）的daily_usage取平均值，就是你要的日均用量。

如果你的业务允许把当月第一天的用量视为0（比如默认第一天没有使用），可以把WHERE daily_usage IS NOT NULL去掉，或者用COALESCE(daily_usage, 0)把NULL替换成0再计算。

这个取决于你的业务场景，分两种情况来看：

• 不需要预存的情况：如果你的数据量不大（比如几年内的每日数据），且查询频率不高，完全没必要预存。实时计算的方式更简单，还能避免数据不一致的问题——比如某天的读数需要修改时，预存的用量值也得同步更新，很容易出错。
• 可以考虑预存的情况：当你的数据量非常大，或者日均查询次数很多，实时计算的性能开始下降时，可以考虑添加一个daily_usage字段来存储每日用量。

如果决定预存，推荐用触发器或者定时任务自动维护这个字段：

• 触发器：在插入新记录时，自动查询前一天的读数，计算出当日用量并存入；如果更新某条记录，同步更新当天和第二天的用量（因为第二天的用量依赖前一天的读数）。
• 定时任务：比如每天凌晨跑一次脚本，计算前一天的用量并更新到数据表中，这种方式适合对实时性要求不高的场景。

总结一下：初期优先选择实时计算，等遇到性能瓶颈再考虑预存，这样能平衡开发复杂度和性能需求。

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