如何修正MySQL表中累计值的重置异常问题

首先，针对你遇到的模块重置导致charge数据断层的问题，我分临时修复和长期预防两种方案来给你建议，同时也会帮你优化现有统计查询的性能。

一、修正重置数据的方案 #

1. 快速临时修复（针对本次事故） #

如果只是想快速解决当前的数据断层，最简单的方式是找到重置发生的分界点（也就是charge骤降161Ah的那一行），给所有重置后的记录的charge值加上161000（从你的查询里charge/1000可以看出来，原始charge的单位是0.001Ah，所以161Ah对应161000）。

假设重置发生在id = 1234（你需要根据实际数据找到这个分界ID），执行：

-- 执行前务必备份数据！
UPDATE MeasurementData.SolarPower 
SET charge = charge + 161000 
WHERE id > 1234;

这种方式直接修改原始数据，适合快速恢复统计，但如果想保留原始数据的完整性，更推荐下面的长期方案。

2. 长期预防方案（重置日志表+动态修正查询） #

你提到的创建重置记录表的思路非常好，既能保留原始数据，又能自动处理未来可能的重置。具体步骤如下：

第一步：创建重置日志表

CREATE TABLE reset_log (
    reset_id INT AUTO_INCREMENT PRIMARY KEY,
    reset_time DATETIME NOT NULL, -- 重置发生的时间
    affected_table VARCHAR(100) NOT NULL, -- 关联的数据表（比如'SolarPower'）
    charge_offset INT NOT NULL, -- 需要补偿的偏移量（本次是161000）
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

第二步：插入本次重置记录

INSERT INTO reset_log (reset_time, affected_table, charge_offset)
VALUES ('2018-XX-XX XX:XX:XX', 'SolarPower', 161000); -- 替换成实际重置时间

第三步：修改查询自动修正数据

在查询时，通过关联reset_log表，给每条记录加上所有早于它的重置偏移量之和，得到修正后的charge值。比如优化后的日统计查询：

SELECT 
    sp.id AS _cid, 
    sp.curtime AS mynow, 
    DATE_FORMAT(sp.curtime, '%H:00') AS date, 
    ROUND(MIN(sp.CURRENT)/10,2) AS 'min current',
    ROUND(AVG(sp.CURRENT)/10,2) AS 'avg current',
    ROUND(MAX(sp.CURRENT)/10,2) AS 'max current',
    ROUND(MIN(sp.power)/1000,2) AS 'min power',
    ROUND(AVG(sp.power)/1000,2) AS 'avg power',
    ROUND(MAX(sp.power)/1000,2) AS 'max power',
    -- 计算修正后的charge：原始值 + 所有早于当前时间的重置偏移量之和
    (sp.charge + COALESCE(SUM(r.charge_offset), 0)) / 1000 AS 'charge',
    -- 修正后的充放电差值：分组最后一条的修正charge - 当前记录的修正charge
    ROUND(
        (
            -- 获取分组内最后一条记录的修正charge
            (SELECT (sp2.charge + COALESCE(SUM(r2.charge_offset), 0))
             FROM MeasurementData.SolarPower sp2
             LEFT JOIN reset_log r2 ON r2.affected_table = 'SolarPower' AND r2.reset_time <= sp2.curtime
             WHERE sp2.id = MAX(sp.id))
            - (sp.charge + COALESCE(SUM(r.charge_offset), 0))
        ) / 1000, 2
    ) AS chgDiff
FROM MeasurementData.SolarPower sp
LEFT JOIN reset_log r ON r.affected_table = 'SolarPower' AND r.reset_time <= sp.curtime
-- 这里把原来的Day(curtime)改成范围查询，方便利用索引
WHERE sp.curtime >= '2018-05-05 00:00:00' AND sp.curtime < '2018-05-06 00:00:00'
GROUP BY HOUR(sp.curtime)
ORDER BY mynow DESC;

未来再发生重置时，只需要往reset_log里插一条记录，所有查询就会自动修正数据，不用再改原始表。

二、查询性能优化建议 #

你的现有查询速度慢，主要是重复子查询和未利用索引导致的，优化点如下：

1. 用窗口函数替代重复子查询 #

原来的查询里，每次分组都要执行一次子查询获取最后一条记录的charge，非常低效。可以用LAST_VALUE()窗口函数一次性获取分组内的最后一条数据：

WITH corrected_data AS (
    -- 先计算所有记录的修正后charge
    SELECT 
        sp.*,
        sp.charge + COALESCE(SUM(r.charge_offset) OVER (), 0) AS corrected_charge
    FROM MeasurementData.SolarPower sp
    LEFT JOIN reset_log r ON r.affected_table = 'SolarPower' AND r.reset_time <= sp.curtime
),
grouped_stats AS (
    SELECT
        id AS _cid,
        curtime AS mynow,
        DATE_FORMAT(curtime, '%H:00') AS hour_date,
        ROUND(MIN(CURRENT)/10,2) AS 'min current',
        ROUND(AVG(CURRENT)/10,2) AS 'avg current',
        ROUND(MAX(CURRENT)/10,2) AS 'max current',
        ROUND(MIN(power)/1000,2) AS 'min power',
        ROUND(AVG(power)/1000,2) AS 'avg power',
        ROUND(MAX(power)/1000,2) AS 'max power',
        corrected_charge / 1000 AS 'charge',
        -- 获取当前小时分组内最后一条记录的修正charge
        LAST_VALUE(corrected_charge) OVER (PARTITION BY HOUR(curtime) ORDER BY curtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_corrected_charge
    FROM corrected_data
    WHERE curtime >= '2018-05-05 00:00:00' AND curtime < '2018-05-06 00:00:00'
)
SELECT
    _cid,
    mynow,
    hour_date AS date,
    `min current`,
    `avg current`,
    `max current`,
    `min power`,
    `avg power`,
    `max power`,
    `charge`,
    ROUND( (last_corrected_charge - corrected_charge) / 1000, 2 ) AS chgDiff
FROM grouped_stats
GROUP BY hour_date, mynow
ORDER BY mynow DESC;

2. 添加关键索引 #

创建以下索引，让MySQL不用全表扫描就能快速定位数据：

-- 针对时间过滤和分组的索引，支持WHERE和GROUP BY
CREATE INDEX idx_solarpower_curtime ON MeasurementData.SolarPower(curtime);
-- 组合索引，支持窗口函数快速获取分组最后一条记录
CREATE INDEX idx_solarpower_id_curtime ON MeasurementData.SolarPower(id, curtime);
-- 如果经常按charge排序或计算，可以添加这个索引
CREATE INDEX idx_solarpower_charge ON MeasurementData.SolarPower(charge);

3. 避免在索引字段上用函数 #

原来的Day(curtime) = Day('...')会让MySQL无法使用curtime的索引，改成范围查询：

-- 原来的写法
WHERE Day(curtime) = Day('2018-05-05 12:00:00')
-- 优化后的写法
WHERE curtime >= '2018-05-05 00:00:00' AND curtime < '2018-05-06 00:00:00'

这样MySQL可以直接利用idx_solarpower_curtime索引快速筛选出当天的数据。

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