MariaDB计算电表每日浮点数差值异常，求排查与修改方案

原语句的问题 #

1. 时间范围错误：由于DATE_TIME是datetime类型，BETWEEN '2023-12-11' AND '2023-12-12'实际匹配的是2023-12-11 00:00:00到2023-12-12 00:00:00之间的数据，2023-12-12的记录不在这个范围内，导致查询只计算了2023-12-11当天的最大值（10）和最小值（3）的差，结果为7。
2. 逻辑不符合需求：原语句计算的是单天内读数的波动差，而你需要的是前一天最后一次读数与目标日期读数的差值。

修改后的SQL语句 #

单日期差值计算 #

如果需要计算2023-12-11最后一次读数和2023-12-12的读数（若12号有多条记录则取最后一次）的差值，使用以下语句：

SELECT
    (
        SELECT PayLoad_REAL
        FROM TEST_STATUS
        WHERE Topic = 'test' AND DATE(DATE_TIME) = '2023-12-12'
        ORDER BY DATE_TIME DESC
        LIMIT 1
    ) - (
        SELECT PayLoad_REAL
        FROM TEST_STATUS
        WHERE Topic = 'test' AND DATE(DATE_TIME) = '2023-12-11'
        ORDER BY DATE_TIME DESC
        LIMIT 1
    ) AS PayLoad_REAL_Difference;

说明：两个子查询分别通过DATE(DATE_TIME)筛选日期，按DATE_TIME倒序后取第一条数据，确保拿到当天最晚的读数，再直接相减得到预期的10.5。

批量每日差值计算 #

如果需要批量计算每一天与前一天的读数差值（MariaDB 10.2+支持窗口函数），使用以下语句：

WITH daily_last_reading AS (
    SELECT
        DATE(DATE_TIME) AS read_date,
        FIRST_VALUE(PayLoad_REAL) OVER (PARTITION BY DATE(DATE_TIME) ORDER BY DATE_TIME DESC) AS last_reading
    FROM TEST_STATUS
    WHERE Topic = 'test'
    GROUP BY DATE(DATE_TIME)
)
SELECT
    read_date,
    last_reading - LAG(last_reading) OVER (ORDER BY read_date) AS daily_difference
FROM daily_last_reading;

说明：先用CTE获取每天的最后一次读数，再通过LAG()窗口函数获取前一天的读数，计算出每日差值。

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