SQL查询：获取组件有效启停状态对及筛选有效事件

看起来你在处理存储组件启停事件表的时候，碰到了旧接口遗留的重复激活记录问题——就是同一个组件连续出现多条激活（A）记录，没法直接配对启停时间对吧？我整理了两个需求的可行SQL方案，咱们一步步来：

先明确核心思路 #

问题的关键是过滤掉连续重复的激活（A）记录，只保留能和停用（I）形成有效配对的事件。我们可以用窗口函数LAG()来识别连续重复的状态，再通过分组或关联来实现配对。

需求2：筛选构成有效启停对的事件行（Result2） #

先搞定这个需求，因为需求1的启停时间对是基于这些有效事件行来生成的。

WITH ranked_events AS (
    SELECT 
        id,
        component_number,
        timestamp,
        status,
        -- 获取当前事件的上一条同组件事件的状态
        LAG(status) OVER (PARTITION BY component_number ORDER BY timestamp) AS prev_status
    FROM your_table_name -- 替换成你的实际表名
),
filtered_events AS (
    SELECT 
        id,
        component_number,
        timestamp,
        status
    FROM ranked_events
    -- 保留第一条记录，或者状态和上一条不同的记录（过滤连续重复的A）
    WHERE prev_status IS NULL OR status != prev_status
)
SELECT * FROM filtered_events
WHERE 
    -- 保留能找到后续停用记录的激活事件
    (status = 'A' AND LEAD(status) OVER (PARTITION BY component_number ORDER BY timestamp) = 'I')
    -- 同时保留能找到前置激活记录的停用事件
    OR (status = 'I' AND LAG(status) OVER (PARTITION BY component_number ORDER BY timestamp) = 'A')
ORDER BY component_number, timestamp;

逻辑解释： #

1. ranked_events：用LAG()窗口函数标记每一条事件的上一条同组件事件状态，帮我们识别连续重复的A记录。
2. filtered_events：过滤掉连续重复的状态（比如组件1的id=2、组件2的id=7这类连续A记录），只保留状态变化的节点或初始记录。
3. 最后一步：只保留那些能形成A-I配对的事件——也就是A后面跟着I，或者I前面是A的记录，完美匹配你要的Result2。

需求1：获取各组件的有效启停时间对（Result1） #

基于上面过滤后的有效事件，我们可以给A和I配对生成时间对：

WITH ranked_events AS (
    SELECT 
        id,
        component_number,
        timestamp,
        status,
        LAG(status) OVER (PARTITION BY component_number ORDER BY timestamp) AS prev_status
    FROM your_table_name -- 替换成你的实际表名
),
filtered_events AS (
    SELECT 
        component_number,
        timestamp,
        status,
        -- 给每个组件的A/I分别按时间排序，生成配对ID
        ROW_NUMBER() OVER (PARTITION BY component_number, status ORDER BY timestamp) AS pair_id
    FROM ranked_events
    WHERE prev_status IS NULL OR status != prev_status
    AND status IN ('A', 'I')
)
SELECT 
    a.component_number,
    a.timestamp AS start,
    i.timestamp AS end
FROM filtered_events a
JOIN filtered_events i 
    ON a.component_number = i.component_number
    AND a.pair_id = i.pair_id
    AND a.status = 'A'
    AND i.status = 'I'
ORDER BY a.component_number, a.timestamp;

逻辑解释： #

1. 前两个CTE和需求2完全一致，先过滤掉连续重复的无效记录。
2. filtered_events里新增了pair_id：给每个组件的激活事件按时间排号，停用事件也按时间排号，这样同一个pair_id的A和I就是一对有效启停。
3. 最后通过JOIN把同组件、同pair_id的A和I配对，直接得到启停时间对，就是你要的Result1。

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