MySQL数据库中检测节点电池电压从2.7V跃迁至4.2V的SQL查询需求

假设表结构 #

首先假设你的数据存储表结构如下（可根据实际情况调整）：

CREATE TABLE node_data (
    idx INT AUTO_INCREMENT PRIMARY KEY,  -- 全局自增记录ID，对应你提到的idx
    node_id VARCHAR(50) NOT NULL,        -- 节点唯一标识
    battery_voltage DECIMAL(3,1) NOT NULL,  -- 电池电压
    record_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 记录采集时间
);

核心查询思路 #

我们需要匹配同一node_id下，先出现低电压（2.7V左右），之后出现**高电压（4.2V左右）**的记录对，且两条记录之间可以间隔其他节点的数据。以下提供两种可行方案：

方案1：自连接+存在性校验（适合MySQL 5.x及以上版本）

该方案通过自连接关联同一节点的高低电压记录，并确保两条记录之间没有该节点的其他数据（即匹配低电压后第一次出现的高电压）：

SELECT
    low.idx AS low_voltage_idx,
    low.node_id,
    low.battery_voltage AS low_voltage,
    high.idx AS high_voltage_idx,
    high.battery_voltage AS high_voltage,
    TIMESTAMPDIFF(MINUTE, low.record_time, high.record_time) AS downtime_minutes
FROM
    node_data low
JOIN
    node_data high ON low.node_id = high.node_id
WHERE
    -- 匹配低电压范围（可根据采集精度调整）
    low.battery_voltage BETWEEN 2.6 AND 2.8
    -- 匹配高电压范围
    AND high.battery_voltage BETWEEN 4.1 AND 4.3
    -- 高电压记录在低电压记录之后
    AND high.idx > low.idx
    -- 确保两条记录之间无该节点的其他数据（避免重复匹配）
    AND NOT EXISTS (
        SELECT 1
        FROM node_data mid
        WHERE mid.node_id = low.node_id
          AND mid.idx > low.idx
          AND mid.idx < high.idx
    )
ORDER BY
    low.node_id, low.idx;

方案2：窗口函数（适合MySQL 8.0及以上版本）

利用LEAD()窗口函数直接获取同一节点的下一条记录，简化逻辑：

WITH node_voltage_sequence AS (
    SELECT
        idx,
        node_id,
        battery_voltage,
        record_time,
        -- 获取同一节点的下一条记录电压、ID和时间
        LEAD(battery_voltage) OVER (PARTITION BY node_id ORDER BY idx) AS next_voltage,
        LEAD(idx) OVER (PARTITION BY node_id ORDER BY idx) AS next_idx,
        LEAD(record_time) OVER (PARTITION BY node_id ORDER BY idx) AS next_time
    FROM node_data
)
SELECT
    idx AS low_voltage_idx,
    node_id,
    battery_voltage AS low_voltage,
    next_idx AS high_voltage_idx,
    next_voltage AS high_voltage,
    TIMESTAMPDIFF(MINUTE, record_time, next_time) AS downtime_minutes
FROM node_voltage_sequence
WHERE
    -- 当前记录为低电压
    battery_voltage BETWEEN 2.6 AND 2.8
    -- 下一条记录为高电压
    AND next_voltage BETWEEN 4.1 AND 4.3
ORDER BY
    node_id, idx;

关键注意事项 #

• 电压范围调整：如果你的电压采集精度更高（比如两位小数），可以把范围收窄，例如2.65 AND 2.75和4.15 AND 4.25，避免误匹配。
• 排序依据：如果idx不能严格保证时间顺序（比如存在数据延迟插入），建议将ORDER BY idx替换为ORDER BY record_time，确保按实际采集时间排序。
• 去重处理：如果同一节点短时间内多次触发低电压/高电压，可根据需求添加时间过滤条件（比如TIMESTAMPDIFF(HOUR, low.record_time, high.record_time) > 1），排除误触发。

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