如何编写SQL查询获取设备状态表各日期下所有设备每日最新状态

需求说明 #

需要统计指定日期范围内，每天每台设备截止当日的最后一次状态变更记录，若设备当日无新变更，则沿用之前的最新状态，用于Dashboard展示。

现有可用方案逻辑说明 #

你提供的自测可行方案核心逻辑是通过自关联匹配每个时间点之前的所有状态记录，分组后取最新值，可正常输出预期结果，适合小数据量场景使用，代码如下：

With
    Query1 As (
        Select
            *
        From
            devices
    )
select 
    first.deviceid as Device,
    first.changedon as Date,
    first.changedon1 as SubDate,
    first.prevalue,
    first.postvalue
from (
Select
    Concat(Date(devices.changedon), " 23:59:59") As changedon,
    Query1.deviceid,
    Query1.changedon As changedon1,
    Query1.prevalue,
    Query1.postvalue
From
    devices Left Join
    Query1 On Query1.changedon <= devices.changedon
Group By
    devices.changedon,
    Query1.deviceid
order by devices.changedon DESC, changedon1 DESC
) as first
group by Date,Device
order by Date ASC

优化方案（支持自定义日期范围、性能更好） #

如果数据量较大或者需要灵活调整统计日期范围，推荐使用窗口函数实现，逻辑更清晰，执行效率更高：

WITH date_range AS (
    -- 自定义统计日期范围，示例为2021-05-04至2021-05-06，可按需修改起止日期和天数
    SELECT DATE '2021-05-04' + INTERVAL (t.n) DAY AS stat_date
    FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2
    ) t
),
device_list AS (
    -- 获取所有设备ID
    SELECT DISTINCT DEVICEID FROM devices
),
device_date_map AS (
    -- 生成设备+统计日期的全量组合，避免当日无变更记录的设备/日期被遗漏
    SELECT dl.DEVICEID, dr.stat_date
    FROM device_list dl
    CROSS JOIN date_range dr
),
status_rank AS (
    -- 按日期+设备分组，给所有符合时间要求的状态记录按变更时间倒序排序
    SELECT 
        dm.stat_date AS LATESTSTATPERDATE,
        dm.DEVICEID,
        d.CHANGEDON,
        d.PREVALUE,
        d.POSTVALUE,
        ROW_NUMBER() OVER (PARTITION BY dm.stat_date, dm.DEVICEID ORDER BY d.CHANGEDON DESC) AS rnk
    FROM device_date_map dm
    LEFT JOIN devices d 
        ON dm.DEVICEID = d.DEVICEID 
        AND DATE(d.CHANGEDON) <= dm.stat_date
)
-- 取每组排序第一的记录即为当日最新状态
SELECT LATESTSTATPERDATE, DEVICEID, CHANGEDON, PREVALUE, POSTVALUE
FROM status_rank
WHERE rnk = 1
ORDER BY LATESTSTATPERDATE ASC, DEVICEID ASC

优化点说明 #

• 可灵活调整统计日期范围，无需依赖原表的变更记录存在对应日期
• 使用窗口函数替代嵌套分组排序，数据库执行效率提升明显，适合十万级以上数据量场景
• 逻辑分层清晰，后续修改需求调整成本更低

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