如何用SQL生成电机启停状态的时间戳区间（基于连续相同状态段）

电机每10秒上报一次状态数据，需要把连续相同状态的记录合并，得到每个状态的起始时间（TS_FROM）和结束时间（TS_TO）——最后一次上报的状态，TS_TO设为NULL。

样本数据 #

期望输出 #

解决方案SQL #

WITH status_groups AS (
    SELECT 
        MOTOR_STATUS,
        TS,
        MOTOR_NAME,
        -- 给连续相同状态的记录分配同一个组ID
        SUM(CASE WHEN prev_status = MOTOR_STATUS THEN 0 ELSE 1 END) 
            OVER (PARTITION BY MOTOR_NAME ORDER BY TS) AS group_id
    FROM (
        SELECT 
            MOTOR_STATUS,
            TS,
            MOTOR_NAME,
            -- 获取当前记录的上一条状态
            LAG(MOTOR_STATUS) OVER (PARTITION BY MOTOR_NAME ORDER BY TS) AS prev_status
        FROM motor_data
    ) t
),
grouped_data AS (
    SELECT 
        MOTOR_STATUS,
        MIN(TS) AS TS_FROM,
        MOTOR_NAME,
        group_id
    FROM status_groups
    GROUP BY MOTOR_STATUS, MOTOR_NAME, group_id
)
SELECT 
    gd.MOTOR_STATUS,
    gd.TS_FROM,
    -- 取下一个状态组的起始时间作为当前状态的结束时间
    LEAD(gd.TS_FROM) OVER (PARTITION BY gd.MOTOR_NAME ORDER BY gd.TS_FROM) AS TS_TO,
    gd.MOTOR_NAME
FROM grouped_data gd
ORDER BY gd.MOTOR_NAME, gd.TS_FROM;

逻辑说明 #

1. 标记连续状态组：先用LAG函数拿到每个电机上一条记录的状态，和当前状态对比，状态变化时就生成新的组ID，这样连续相同状态的记录会被归为同一组。
2. 聚合起始时间：对每个电机的每个状态组，取组内最早的时间戳作为该状态的起始时间（TS_FROM）。
3. 生成结束时间：用LEAD函数拿到同一个电机下一个状态组的起始时间，作为当前状态的结束时间（TS_TO）；最后一个状态组没有后续状态，所以TS_TO为NULL。

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