MySQL查询：带缺失值插值的能耗计数器差值计算

问题背景 #

MySQL数据库存储每15分钟一次的能耗计数器（kWh）数据，因停电、系统重启等原因存在部分缺失记录。数据表结构示例如下：

id      Time                Energy
27800   13.02.2024 23:30:01 651720048
27801   13.02.2024 23:45:00 651720672
(missing)
27802   14.02.2024 00:15:02 651721917
27803   14.02.2024 00:30:00 651722540
27804   14.02.2024 00:45:00 651723129
27805   14.02.2024 01:00:02 651723769
27806   14.02.2024 01:15:01 651724405
27807   14.02.2024 01:30:01 651725030
(missing)
27808   14.02.2024 02:00:01 651726275
...

需求是编写SQL查询，实现：

• 补全缺失的15分钟时间点，通过线性插值生成对应Energy估计值
• 计算指定时间跨度（如15分钟、60分钟）内的能耗差值（计数器差值）
• 输出格式需包含原始/插值记录、各跨度能耗，示例如下：

id      Time                Energy      Consumption 15m     Consumption 1h
27800   13.02.2024 23:30:01 651720048   -   
27801   13.02.2024 23:45:00 651720672   624 
(missing)                   651721294.5 622.5               -
27802   14.02.2024 00:15:02 651721917   622.5   
...

实现思路与SQL代码 #

核心逻辑是先生成完整的15分钟时间序列，关联原始数据后通过前后有效记录做线性插值，最后计算各跨度的能耗差值。

完整SQL代码 #

WITH RECURSIVE time_series AS (
    -- 取数据最小时间，调整为最近的15分钟整点作为起始
    SELECT 
        DATE_FORMAT(MIN(STR_TO_DATE(Time, '%d.%m.%Y %H:%i:%s')), '%d.%m.%Y %H:%i:00') AS series_time
    FROM energy_data
    UNION ALL
    -- 递归生成后续每15分钟的时间点
    SELECT 
        DATE_FORMAT(DATE_ADD(series_time, INTERVAL 15 MINUTE), '%d.%m.%Y %H:%i:00') AS series_time
    FROM time_series
    WHERE series_time < (SELECT DATE_FORMAT(MAX(STR_TO_DATE(Time, '%d.%m.%Y %H:%i:%s')), '%d.%m.%Y %H:%i:00') FROM energy_data)
),
-- 关联原始数据，获取每个时间点的前后有效记录
data_with_context AS (
    SELECT
        ts.series_time,
        ed.id,
        ed.Time AS original_time,
        ed.Energy AS original_energy,
        -- 前一条有效Energy及对应时间
        LAG(ed.Energy) OVER (ORDER BY ts.series_time) AS prev_energy,
        LAG(STR_TO_DATE(ed.Time, '%d.%m.%Y %H:%i:%s')) OVER (ORDER BY ts.series_time) AS prev_time,
        -- 后一条有效Energy及对应时间
        LEAD(ed.Energy) OVER (ORDER BY ts.series_time) AS next_energy,
        LEAD(STR_TO_DATE(ed.Time, '%d.%m.%Y %H:%i:%s')) OVER (ORDER BY ts.series_time) AS next_time
    FROM time_series ts
    LEFT JOIN energy_data ed 
        ON STR_TO_DATE(ed.Time, '%d.%m.%Y %H:%i:%s') BETWEEN DATE_SUB(ts.series_time, INTERVAL 2 MINUTE) 
                                                            AND DATE_ADD(ts.series_time, INTERVAL 2 MINUTE)
),
-- 计算插值后的Energy值
interpolated_data AS (
    SELECT
        id,
        CASE WHEN original_time IS NOT NULL THEN original_time ELSE series_time END AS `Time`,
        CASE
            WHEN original_energy IS NOT NULL THEN original_energy
            WHEN prev_energy IS NOT NULL AND next_energy IS NOT NULL THEN
                prev_energy + (next_energy - prev_energy) * 
                TIMESTAMPDIFF(SECOND, prev_time, series_time) / TIMESTAMPDIFF(SECOND, prev_time, next_time)
            ELSE NULL
        END AS Energy
    FROM data_with_context
)
-- 最终查询，计算各跨度能耗差值
SELECT
    id,
    `Time`,
    Energy,
    -- 15分钟能耗：当前值 - 前一个时间点的值
    CASE WHEN LAG(Energy) OVER (ORDER BY `Time`) IS NOT NULL THEN 
        ROUND(Energy - LAG(Energy) OVER (ORDER BY `Time`), 1) 
    ELSE '-' END AS `Consumption 15m`,
    -- 1小时能耗：当前值 - 前4个时间点的值（4*15分钟=1小时）
    CASE WHEN LAG(Energy, 4) OVER (ORDER BY `Time`) IS NOT NULL THEN 
        ROUND(Energy - LAG(Energy, 4) OVER (ORDER BY `Time`), 1) 
    ELSE '-' END AS `Consumption 1h`
FROM interpolated_data
ORDER BY `Time`;

代码说明 #

1. 时间序列生成：递归CTE time_series生成覆盖原始数据时间范围的所有15分钟间隔点，确保没有遗漏的时间槽。
2. 前后数据匹配：data_with_context通过左连接关联原始数据，用LAG()和LEAD()窗口函数获取每个时间点相邻的有效记录，为插值提供计算依据。
3. 线性插值：当原始Energy缺失时，根据前后有效记录的时间差和Energy差，按时间比例计算当前时间点的估计值，保证能耗数据的连续性。
4. 能耗计算：通过LAG()函数偏移对应步数（1步对应15分钟，4步对应1小时），直接计算计数器差值得到对应跨度的能耗。

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