如何将device_events表中的设备离线与在线事件配对？

问题背景 #

现有device_events表存储设备的Online和Offline事件，需将每个设备的离线事件与对应的在线事件配对（假设在线事件始终晚于对应离线事件）。

原表数据 #

期望输出 #

解决方案 #

使用CTE（公共表表达式）拆分离线和在线事件数据集，分别按设备分组并按时间倒序添加行号，最后通过设备ID和行号左连接实现配对：

WITH offline_events AS (
    SELECT 
        device_id,
        created_at AS device_offline,
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY created_at DESC) AS rn
    FROM device_events
    WHERE trigger = 'Offline'
),
online_events AS (
    SELECT 
        device_id,
        created_at AS device_online,
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY created_at DESC) AS rn
    FROM device_events
    WHERE trigger = 'Online'
)
SELECT 
    oe.device_id,
    oe.device_offline,
    oe2.device_online
FROM offline_events oe
LEFT JOIN online_events oe2 
    ON oe.device_id = oe2.device_id 
    AND oe.rn = oe2.rn
ORDER BY oe.device_id, oe.device_offline DESC;

代码说明 #

1. offline_events：筛选所有离线事件，按device_id分组，按created_at倒序生成行号，确保最新的离线事件排在最前。
2. online_events：筛选所有在线事件，按相同规则生成行号，保证同一设备的在线事件与离线事件排序一致。
3. 左连接配对：通过device_id和行号rn关联两个数据集，左连接确保无对应在线事件的离线事件（如设备3）会显示NULL。
4. 排序：最终结果按设备ID和离线时间倒序排列，与期望输出一致。

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