SQL Lag/Lead带条件应用：可编程控制器传感器状态数据匹配查询需求

嘿，我明白你的需求了——要从PLC的时间序列数据里，给每个传感器的每一次ON状态，找到它对应的第一个OFF状态时间戳，而且要跳过那些连续重复的状态记录对吧？你之前尝试用LEAD()没成功，大概率是没先过滤掉连续的相同状态，导致函数取到的不是真正的状态切换点。

下面给你一个可行的SQL解决方案，我会一步步解释逻辑：

思路拆解 #

1. 先过滤状态变化记录：原始数据里有很多连续的相同状态（比如S1在10:05、10:06、10:11都是OFF），这些重复记录对我们找状态切换没有意义，先把它们去掉，只保留状态发生变化的行。
2. 匹配ON对应的首个OFF：在过滤后的状态切换记录里，给每个ON状态匹配它之后同传感器的第一个OFF状态。

具体SQL代码 #

WITH state_transitions AS (
    -- 第一步：过滤出状态发生变化的记录
    SELECT 
        Timestamp,
        Sensor,
        State,
        -- 用LAG获取上一条同传感器的状态，判断是否发生变化
        LAG(State) OVER (PARTITION BY Sensor ORDER BY Timestamp) AS previous_state
    FROM plc_data
    QUALIFY 
        -- 保留第一次出现的记录，或者状态和上一条不同的记录
        previous_state IS NULL OR previous_state != State
)
SELECT 
    st_on.Timestamp AS "ON",
    st_off.Timestamp AS "OFF",
    st_on.Sensor,
    st_on.State
FROM state_transitions st_on
-- 自连接找到当前ON之后的第一个OFF
LEFT JOIN state_transitions st_off
    ON st_on.Sensor = st_off.Sensor
    AND st_off.Timestamp > st_on.Timestamp
    AND st_off.State = 'OFF'
WHERE st_on.State = 'ON'
-- 确保每个ON只匹配最早的那个OFF
QUALIFY ROW_NUMBER() OVER (PARTITION BY st_on.Sensor, st_on.Timestamp ORDER BY st_off.Timestamp) = 1;

代码解释 #

• state_transitions CTE：用LAG()窗口函数对比当前行和上一行的状态，只保留状态变化的记录，这样就去掉了连续重复的ON/OFF，只留下真正的状态切换点。
• 自连接+ROW_NUMBER()：把每个ON状态的记录和之后同传感器的OFF记录关联，再用ROW_NUMBER()给每个ON对应的OFF按时间排序，取第一个（也就是最早的那个OFF）。

针对你的样本数据的执行结果 #

用你提供的样本数据运行这段SQL，会得到符合预期的结果（注：你给出的期望结果里第二条的S1应该是笔误，实际样本中10:06是S2的ON，对应的OFF是10:10，所以结果里会包含这条S2的记录，而S1的有效ON/OFF对是10:00→10:05、10:15→10:18）。

如果你用的是支持LEAD()的SQL方言（比如BigQuery、PostgreSQL），也可以用更简洁的写法：

WITH state_transitions AS (
    SELECT 
        Timestamp,
        Sensor,
        State,
        LEAD(Timestamp) OVER (PARTITION BY Sensor ORDER BY Timestamp) AS next_timestamp,
        LEAD(State) OVER (PARTITION BY Sensor ORDER BY Timestamp) AS next_state
    FROM plc_data
    QUALIFY LAG(State) OVER (PARTITION BY Sensor ORDER BY Timestamp) != State OR LAG(State) IS NULL
)
SELECT 
    Timestamp AS "ON",
    next_timestamp AS "OFF",
    Sensor,
    State
FROM state_transitions
WHERE State = 'ON' AND next_state = 'OFF';

这个写法直接在状态切换的CTE里用LEAD()获取下一条状态的时间和类型，然后只筛选出ON之后跟着OFF的记录，结果是一样的。

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