MariaDB高效反向查询雨量传感器重置前数据的方法问询

问题背景 #

我有个存储气象传感器数据的MariaDB数据库，其中原始雨量值rain_raw是增量累加的，但设备的12位计数器溢出或者断电时，这个值会被重置为0。现在需要找到重置前的最后一条有效数据（比如示例里rain_raw=37.55905511796的那行），把它作为后续数据的累加基数。当前使用的正向查询在大数据量下效率极低，而且历史故障数据还容易导致误判，想找从最新记录反向查询重置点的高效方法，开发环境为Qt6+C++。

当前正向查询语句：

SELECT time,rain_raw 
FROM weather_data t1 
WHERE rain_raw > (
    SELECT rain_raw 
    FROM weather_data t2 
    WHERE t1.rain_raw > t2.rain_raw 
    ORDER BY time desc 
    LIMIT 1 
);

高效反向查询方案 #

1. 反向定位第一个"骤降"点 #

核心思路是从最新数据往前遍历，第一个出现当前rain_raw小于前一条rain_raw的位置，前一条数据就是重置前的最后有效数据。用MariaDB的窗口函数LAG()可以一次扫描完成查询，效率远高于正向全表扫描：

SELECT 
    LAG(time) OVER (ORDER BY time DESC) AS reset_prev_time,
    LAG(rain_raw) OVER (ORDER BY time DESC) AS reset_prev_rain_raw
FROM weather_data
ORDER BY time DESC
HAVING rain_raw < LAG(rain_raw) OVER (ORDER BY time DESC)
LIMIT 1;

如果没有找到这类骤降点，说明从最早记录到现在未发生过重置，直接取最早的rain_raw作为累加基数即可。

2. 大数据量场景优化 #

• 给time字段建立主键或唯一索引，确保排序和反向遍历的效率。
• 若只需要最近一次的重置点，可限定查询时间范围（根据计数器溢出周期调整，比如只查最近30天）：

SELECT 
    LAG(time) OVER (ORDER BY time DESC) AS reset_prev_time,
    LAG(rain_raw) OVER (ORDER BY time DESC) AS reset_prev_rain_raw
FROM weather_data
WHERE time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY time DESC
HAVING rain_raw < LAG(rain_raw) OVER (ORDER BY time DESC)
LIMIT 1;

3. Qt6+C++实现代码 #

用Qt的QSqlQuery执行上述SQL，提取结果作为累加基数：

QSqlDatabase db = QSqlDatabase::database();
QSqlQuery query(db);
// 查询最近一次重置前的有效基数
query.prepare(R"(
    SELECT 
        LAG(rain_raw) OVER (ORDER BY time DESC) AS reset_prev_rain_raw
    FROM weather_data
    ORDER BY time DESC
    HAVING rain_raw < LAG(rain_raw) OVER (ORDER BY time DESC)
    LIMIT 1
)");

if (query.exec()) {
    if (query.next()) {
        double baseRain = query.value("reset_prev_rain_raw").toDouble();
        // 使用baseRain作为后续数据的累加基数
    } else {
        // 无重置记录，取最早的rain_raw作为基数
        QSqlQuery baseQuery(db);
        baseQuery.prepare("SELECT rain_raw FROM weather_data ORDER BY time ASC LIMIT 1");
        if (baseQuery.exec() && baseQuery.next()) {
            double baseRain = baseQuery.value(0).toDouble();
            // 处理无重置的业务逻辑
        }
    }
}

关键说明 #

• 需确保MariaDB版本在10.2及以上，LAG()窗口函数从该版本开始支持。
• 反向查询找到第一个不符合增量逻辑的点即停止，无需全表扫描，效率提升显著。
• 若存在多次重置，该方法仅返回最近一次重置前的有效数据，符合后续累加需求。

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