求助：编写SQL查询筛选符合指定事件顺序的SER_NUMBER并计算事件时间差

嗨，我来帮你搞定这个SQL查询的需求！你想要找出那些按顺序出现了EVENT_ID 101、102、103、135的SER_NUMBER，同时还要计算对应序列里103到135的时间差对吧？结合你给的样本数据，我给你两种解决方案，分别对应不同的场景：

场景1：要求事件连续出现（101→102→103→135依次紧邻） #

如果需要这四个事件是连续发生的（中间没有其他事件插入），可以用窗口函数给每个SER_NUMBER的事件按时间排序，然后通过自连接匹配连续的序列：

WITH ordered_events AS (
    SELECT 
        SER_NUMBER,
        EVENT_ID,
        -- 先把格式异常的时间字符串转成标准datetime类型（如果你的EVENT_Date已经是datetime类型可以去掉这行）
        STR_TO_DATE(REPLACE(EVENT_Date, '.', ':'), '%Y-%m-%d %H:%i:%s.%f') AS event_datetime,
        -- 给每个SER_NUMBER的事件按时间排号
        ROW_NUMBER() OVER (PARTITION BY SER_NUMBER ORDER BY STR_TO_DATE(REPLACE(EVENT_Date, '.', ':'), '%Y-%m-%d %H:%i:%s.%f')) AS rn
    FROM your_table_name
),
sequence_match AS (
    SELECT 
        o1.SER_NUMBER,
        o3.event_datetime AS dt_103,
        o4.event_datetime AS dt_135
    FROM ordered_events o1
    -- 匹配101之后紧邻的102
    JOIN ordered_events o2 
        ON o1.SER_NUMBER = o2.SER_NUMBER 
        AND o2.rn = o1.rn + 1 
        AND o2.EVENT_ID = 102
    -- 匹配102之后紧邻的103
    JOIN ordered_events o3 
        ON o2.SER_NUMBER = o3.SER_NUMBER 
        AND o3.rn = o2.rn + 1 
        AND o3.EVENT_ID = 103
    -- 匹配103之后紧邻的135
    JOIN ordered_events o4 
        ON o3.SER_NUMBER = o4.SER_NUMBER 
        AND o4.rn = o3.rn + 1 
        AND o4.EVENT_ID = 135
    WHERE o1.EVENT_ID = 101
)
SELECT 
    SER_NUMBER,
    -- 计算小时级别的时间差，格式化为你要的"X hrs"样式
    CONCAT(TIMESTAMPDIFF(HOUR, dt_103, dt_135), ' hrs') AS TIME_DIFF
FROM sequence_match;

逻辑说明： #

1. ordered_events：给每个设备（SER_NUMBER）的事件按时间排序，同时修复了样本里用点分隔的时间格式，转成数据库能识别的datetime类型。
2. sequence_match：通过四次表连接，精准匹配连续出现的101→102→103→135事件序列。
3. 最后一步计算103和135的时间差，用TIMESTAMPDIFF获取小时数并拼接成指定格式。

场景2：允许中间穿插其他事件（只要时间顺序是101→102→103→135） #

如果不需要事件连续，只要每个SER_NUMBER都存在这四个事件，且出现的时间顺序符合要求，可以用分组聚合的方式实现：

WITH event_datetimes AS (
    SELECT 
        SER_NUMBER,
        EVENT_ID,
        STR_TO_DATE(REPLACE(EVENT_Date, '.', ':'), '%Y-%m-%d %H:%i:%s.%f') AS event_datetime
    FROM your_table_name
),
ser_event_summary AS (
    SELECT 
        SER_NUMBER,
        -- 提取每个事件对应的时间
        MAX(CASE WHEN EVENT_ID = 101 THEN event_datetime END) AS dt_101,
        MAX(CASE WHEN EVENT_ID = 102 THEN event_datetime END) AS dt_102,
        MAX(CASE WHEN EVENT_ID = 103 THEN event_datetime END) AS dt_103,
        MAX(CASE WHEN EVENT_ID = 135 THEN event_datetime END) AS dt_135
    FROM event_datetimes
    GROUP BY SER_NUMBER
    -- 过滤出四个事件都存在且时间顺序符合要求的设备
    HAVING dt_101 IS NOT NULL 
        AND dt_102 IS NOT NULL 
        AND dt_103 IS NOT NULL 
        AND dt_135 IS NOT NULL
        AND dt_101 < dt_102 
        AND dt_102 < dt_103 
        AND dt_103 < dt_135
)
SELECT 
    SER_NUMBER,
    CONCAT(TIMESTAMPDIFF(HOUR, dt_103, dt_135), ' hrs') AS TIME_DIFF
FROM ser_event_summary;

逻辑说明： #

1. event_datetimes：同样先处理时间格式，确保能正确比较时间。
2. ser_event_summary：按SER_NUMBER分组，提取每个事件的时间，然后通过HAVING子句过滤出满足条件的设备。
3. 最后计算时间差并格式化输出。

注意事项： #

• 记得把代码里的your_table_name替换成你实际使用的表名。
• 如果你的数据库里EVENT_Date已经是标准的datetime类型，直接去掉STR_TO_DATE和REPLACE部分即可。
• 时间差的单位可以根据需求调整，比如把TIMESTAMPDIFF(HOUR, ...)改成MINUTE或者SECOND来获取分钟/秒级的差值。

备注：内容来源于stack exchange，提问作者santosh