SQL时间序列查询：如何获取设备生命周期各阶段的状态变更日期

解决方案 #

方案1：基于原有查询最小改动 #

直接用COALESCE函数处理NULL值，当原状态变更日期子查询返回空时，取当前设备的最早记录日期作为默认值：

SELECT
    s.*,
    COALESCE(
        (SELECT MAX(ss.statusDate) 
         FROM #machineStatus ss 
         WHERE ss.machineId = s.machineId 
           AND ss.machineStatus <> s.machineStatus 
           AND ss.statusDate < s.statusDate),
        (SELECT MIN(ss2.statusDate) FROM #machineStatus ss2 WHERE ss2.machineId = s.machineId)
    ) AS statusChangeDate
FROM #machineStatus s
ORDER BY s.statusDate

方案2：窗口函数优化版（适合大数据量场景） #

用窗口函数替代嵌套子查询，减少表扫描次数，性能更优：

WITH StatusMark AS (
    SELECT 
        *,
        -- 标记和上一条记录状态是否发生变化
        CASE WHEN LAG(machineStatus) OVER (PARTITION BY machineID ORDER BY statusDate) <> machineStatus THEN 1 ELSE 0 END AS change_flag,
        -- 提前计算每个设备的最早记录日期
        MIN(statusDate) OVER (PARTITION BY machineID) AS first_date
    FROM #machineStatus
),
StatusGroup AS (
    SELECT 
        *,
        -- 对同一连续状态的记录分配相同分组ID
        SUM(change_flag) OVER (PARTITION BY machineID ORDER BY statusDate ROWS UNBOUNDED PRECEDING) AS group_id
    FROM StatusMark
)
SELECT 
    machineID,
    machineStatus,
    statusDate,
    COALESCE(LAG(MAX(statusDate)) OVER (PARTITION BY machineID ORDER BY group_id), first_date) AS statusChangeDate
FROM StatusGroup
GROUP BY machineID, machineStatus, statusDate, group_id, first_date
ORDER BY statusDate

两种方案都可以完全匹配你给出的期望输出结果。

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