Node-RED+MySQL+Grafana项目中实现Grafana面板相邻行电量差计算日能耗

核心实现逻辑 #

针对业务表存储每日日终累计电量值的场景，单条记录的读数无法直接代表当日实际能耗，需通过相邻历史累计值差分得到真实日指标：当日各能源指标值 = 当前行累计读数 - 按时间排序上相邻的上一条记录累计读数。
实现该逻辑可使用MySQL内置的LAG()窗口函数，直接按时间顺序读取上一行的字段值完成计算，同时需要解决Grafana时间筛选的边界缺失问题，避免第一行展示数据无法获取前置累计值导致计算偏差。

关键注意事项 #

• 不能直接在全表查询最外层套用$__timeFilter时间筛选逻辑：这种方式会提前过滤掉时间范围外的前置历史记录，导致时间范围第一行数据拿不到上一条的累计值，计算结果失真
• 如果使用的MySQL版本为5.7及以下（不支持窗口函数语法），可通过自关联方式实现相邻行差分：给表内记录按时间排序生成自增行号，再关联行号差为1的记录做减法
• 计算得到的差值如果出现负数，通常是累计读数重置（如换电表、数据异常）导致的；后续完成KwhFECSA字段的存储偏差修正时，不需要改动整体查询结构即可自动适配新的数据源逻辑

调整后的可直接复用的SQL（适配MySQL 8.0+） #

WITH base_data AS (
  -- 先查询Grafana选定时间范围内的所有记录，同时多取时间范围前的1条历史累计记录
  SELECT 
    TimeSTMP,
    KwhX1,
    KwhFV1,
    KwhFECSA,
    KwhEXCD
  FROM ContKwh
  WHERE TimeSTMP >= (
    -- 定位时间范围最早时间之前的最近1条记录的时间点
    SELECT TimeSTMP 
    FROM ContKwh 
    WHERE TimeSTMP < $__timeFrom(TimeSTMP)
    ORDER BY TimeSTMP DESC 
    LIMIT 1
  ) OR TimeSTMP <= $__timeTo(TimeSTMP)
  ORDER BY TimeSTMP
),
calc_diff AS (
  -- 对基础数据集逐行计算和上一相邻记录的累计值差
  SELECT
    TimeSTMP AS "time",
    KwhX1 - LAG(KwhX1, 1, 0) OVER(ORDER BY TimeSTMP) AS "Consum",
    KwhFV1 - LAG(KwhFV1, 1, 0) OVER(ORDER BY TimeSTMP) AS "FotoVoltaica",
    KwhFECSA - LAG(KwhFECSA, 1, 0) OVER(ORDER BY TimeSTMP) AS "Xarxa",
    KwhEXCD - LAG(KwhEXCD, 1, 0) OVER(ORDER BY TimeSTMP) AS "Excedent"
  FROM base_data
)
-- 仅保留Grafana选定时间范围内的计算结果用于面板展示
SELECT * FROM calc_diff 
WHERE "time" >= $__timeFrom(TimeSTMP) AND "time" <= $__timeTo(TimeSTMP)
ORDER BY "time"

方案适配性说明 #

• 第一层基础数据查询会在Grafana选定的时间范围基础上，额外多拉取范围前最近的1条累计记录，保证面板上第一行展示数据能拿到正确的前置累计值做差
• 第二层差分计算完全匹配原表的字段映射关系，计算结果的字段名和原查询定义的Grafana面板展示字段完全一致，不需要改动面板的配置逻辑
• 最终结果过滤环节会把前置拉取的历史记录排除，不会在交互面板上展示多余数据，完全适配Grafana的时间筛选交互逻辑

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