请求协助编写MySQL脚本：基于时间差计算更新设备电能累计值

针对你的需求，我会提供一个基于MySQL递归CTE的解决方案，完美匹配你描述的累计规则，并且可以每小时自动运行。

核心思路 #

1. 按设备分组，将每条记录按时间顺序排序，确保我们可以按顺序计算累计值。
2. 使用递归CTE从0开始累加：
   • 每个设备的第一条记录累计值为0.0000。
   • 后续记录根据与上一条的时间差、当前ActivePower计算增量：
     • 如果ActivePower为0，累计值与上一条保持一致；
     • 否则，累计值 = 上一条累计值 + (时间差秒数 × ActivePower ÷ 3600)，结果保留4位小数。
3. 用计算结果批量更新原表的CounterReading字段。

完整SQL脚本 #

WITH RECURSIVE device_chronological AS (
    -- 第一步：给每个设备的记录按时间排序，生成行号
    SELECT
        deviceID,
        DATE,
        ActivePower,
        ROW_NUMBER() OVER (PARTITION BY deviceID ORDER BY DATE) AS rn
    FROM device_data
),
recursive_cumulative AS (
    -- 递归基础：每个设备的第一条记录，累计值初始化为0.0000
    SELECT
        deviceID,
        DATE,
        ActivePower,
        rn,
        0.0000 AS CounterReading
    FROM device_chronological
    WHERE rn = 1

    UNION ALL

    -- 递归计算后续每条记录的累计值
    SELECT
        dc.deviceID,
        dc.DATE,
        dc.ActivePower,
        dc.rn,
        ROUND(
            CASE
                -- ActivePower为0时，累计值与上一条相同
                WHEN dc.ActivePower = 0 THEN rc.CounterReading
                -- 否则计算增量并累加
                ELSE rc.CounterReading + (TIMESTAMPDIFF(SECOND, rc.DATE, dc.DATE) * dc.ActivePower / 3600)
            END,
            4  -- 保留4位小数
        ) AS CounterReading
    FROM device_chronological dc
    -- 关联上一条记录（行号+1）
    JOIN recursive_cumulative rc ON dc.deviceID = rc.deviceID AND dc.rn = rc.rn + 1
)
-- 更新原表的CounterReading字段
UPDATE device_data dd
JOIN recursive_cumulative rc ON dd.deviceID = rc.deviceID AND dd.DATE = rc.DATE
SET dd.CounterReading = rc.CounterReading;

关键细节说明 #

• 时间差计算：使用TIMESTAMPDIFF(SECOND, 上一条时间, 当前时间)获取两条记录之间的秒数差，确保计算精度。
• 设备独立性：通过PARTITION BY deviceID保证每个设备的累计值独立计算，不会跨设备干扰。
• 递归逻辑：递归CTE会按时间顺序逐条处理记录，确保每一步的累计值都依赖于上一条的正确结果。
• 小数保留：用ROUND()函数强制保留4位小数，符合你的精度要求。

每小时自动运行设置 #

你可以将这个脚本保存为update_counter_reading.sql，然后通过定时任务工具每小时执行：

• Linux/macOS：使用cron，添加如下任务（替换你的数据库信息和脚本路径）：

  0 * * * * mysql -u your_username -p'your_password' your_database < /path/to/update_counter_reading.sql
  

• Windows：使用「任务计划程序」创建定时任务，执行mysql.exe并传入脚本参数。

测试示例 #

假设你有如下测试数据：

INSERT INTO device_data (deviceID, DATE, ActivePower) VALUES
(1, '2024-01-01 00:00:00', 100),
(1, '2024-01-01 00:01:00', 100),
(1, '2024-01-01 00:02:00', 0),
(1, '2024-01-01 00:03:00', 150);

运行脚本后，device_data表的CounterReading字段会被更新为：

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