SQL按设备分区取数后如何新增最新设备状态变更日期字段

实现逻辑 #

核心通过单次全表扫描+窗口函数完成计算，无需额外表关联或多次扫描，性能最优：

1. 保留原有ROW_NUMBER逻辑用于筛选每个设备的最新观测记录
2. 通过LAG窗口函数比对同设备上一条观测的设备状态，识别状态变更的时间点
3. 通过MAX窗口函数取同设备所有状态变更时间点的最大值，即为最新状态变更日期

完整SQL代码 #

WITH o AS (
SELECT *,
    -- 同设备按观测时间倒序排名，取1即为最新观测
    ROW_NUMBER() OVER (PARTITION by device
                       ORDER BY date_observation DESC) AS queue,
    -- 计算最新状态变更日期
    MAX(
        CASE 
            -- 状态和上一条不一致，判定为状态变更，记录当前观测日期
            WHEN device_state != LAG(device_state) OVER (PARTITION BY device ORDER BY date_observation) 
                THEN date_observation 
            -- 设备仅有1条观测记录时，默认首次观测日期为状态变更日期
            WHEN LAG(device_state) OVER (PARTITION BY device ORDER BY date_observation) IS NULL 
                THEN date_observation
        END
    ) OVER (PARTITION BY device) AS date_state_change
FROM observations  
)
-- 仅保留每个设备的最新观测+最新状态变更日期
SELECT id, device, date_observation, device_state, reading, date_state_change
FROM o
WHERE queue = 1

方案优势 #

• 仅扫描一次observations表，所有计算都在单次窗口遍历中完成，相比嵌套子查询、表关联的方案性能提升明显，尤其适合大数据量场景
• 兼容边界场景：设备仅有1条观测记录时不会返回空值，自动填充首次观测日期作为状态变更日期
• 逻辑简洁，无需额外CTE嵌套，易维护

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