如何从传感器事件表中筛选各传感器的连续事件对

嘿，这个需求其实用SQL窗口函数就能轻松搞定！我帮你梳理下具体的实现思路和代码示例，完全贴合你的场景：

核心思路 #

你的场景是要给每个传感器的事件按时间戳排序后，把相邻的事件配对——说白了就是拿到每个事件的"上一个事件"信息。这里最适合用LAG()窗口函数，它能在同一个传感器的分组里，按指定顺序获取当前行的前一行数据，正好满足我们配对的需求。

基础SQL实现 #

假设你的表名叫sensor_events，字段分别是sensor_id（传感器ID）、event_seq（事件序列号）、event_timestamp（事件时间戳）、event_data（相关数据）。基础的查询代码如下：

SELECT
    sensor_id,
    -- 当前事件的各项信息
    event_seq AS current_seq,
    event_timestamp AS current_timestamp,
    event_data AS current_data,
    -- 通过LAG()获取同一传感器的前一个事件信息
    LAG(event_seq) OVER (
        PARTITION BY sensor_id 
        ORDER BY event_timestamp, event_seq
    ) AS previous_seq,
    LAG(event_timestamp) OVER (
        PARTITION BY sensor_id 
        ORDER BY event_timestamp, event_seq
    ) AS previous_timestamp,
    LAG(event_data) OVER (
        PARTITION BY sensor_id 
        ORDER BY event_timestamp, event_seq
    ) AS previous_data
FROM
    sensor_events
ORDER BY
    sensor_id,
    event_timestamp,
    event_seq;

关键细节说明 #

• PARTITION BY sensor_id：确保我们只在同一个传感器的事件里找前序事件，不会跨传感器配对。
• ORDER BY event_timestamp, event_seq：先按时间戳排序，同时把event_seq作为第二排序条件——毕竟业务里说序列号是持续递增的，万一出现同一时间戳下多个事件的情况，序列号能帮我们保证排序的准确性。

过滤无配对的行 #

如果你只想保留真正有配对的行（也就是每个传感器从第二行开始的事件，因为第一行没有前序事件），可以用CTE（公共表表达式）封装后过滤：

WITH sensor_event_pairs AS (
    SELECT
        sensor_id,
        event_seq AS current_seq,
        event_timestamp AS current_timestamp,
        event_data AS current_data,
        LAG(event_seq) OVER (
            PARTITION BY sensor_id 
            ORDER BY event_timestamp, event_seq
        ) AS previous_seq,
        LAG(event_timestamp) OVER (
            PARTITION BY sensor_id 
            ORDER BY event_timestamp, event_seq
        ) AS previous_timestamp,
        LAG(event_data) OVER (
            PARTITION BY sensor_id 
            ORDER BY event_timestamp, event_seq
        ) AS previous_data
    FROM
        sensor_events
)
SELECT *
FROM sensor_event_pairs
WHERE previous_seq IS NOT NULL  -- 过滤掉没有前序事件的行
ORDER BY sensor_id, current_timestamp, current_seq;

扩展：计算事件间隔 #

如果还需要计算连续事件之间的时间差或者序列号差，直接在SELECT里加计算逻辑就行，比如：

• MySQL里计算时间差（秒）：TIMESTAMPDIFF(SECOND, previous_timestamp, current_timestamp) AS time_interval
• PostgreSQL里计算时间差（秒）：EXTRACT(EPOCH FROM current_timestamp - previous_timestamp) AS time_interval
• 序列号差：current_seq - previous_seq AS seq_interval

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