如何查询指定日期设备读数并与前一最近日期读数对比？

问题背景 #

原查询用于获取指定日期（2020-08-19）、指定类型（type_id=289）、指定地点（place_id=1）的设备读数：

SELECT devices.name,
       devices.type_id,
       devices_readings.device_id,
       ROUND(CAST(devices_readings.reading AS numeric), 2) AS reading,
       devices_readings.reading_slot_time
FROM devices
INNER JOIN devices_readings ON devices.id = devices_readings.device_id
AND devices.type_id = 289
AND devices_readings.reading_slot_time::date = '08-19-2020'
AND devices.place_id = 1;

需求变更为：获取每个设备指定日期（2020-08-19）的读数，以及该日期之前最近一次的历史读数，计算两者的变化值（当前读数 - 历史读数），要求用单查询实现，同时适配100-200万条数据且按月份分区的devices_readings表。

解决方案：使用LATERAL JOIN关联最近历史读数 #

利用LATERAL JOIN（其他数据库可对应使用CROSS APPLY/OUTER APPLY），可以高效地为每个设备匹配指定日期前的最近一次读数，同时结合分区裁剪优化查询性能。

最终查询语句 #

SELECT 
    d.name,
    d.type_id,
    curr.device_id,
    curr.curr_reading,
    curr.curr_date,
    hist.prev_reading,
    hist.prev_date,
    ROUND(curr.curr_reading - hist.prev_reading, 2) AS reading_change
FROM (
    -- 获取指定日期的目标读数
    SELECT 
        dr.device_id,
        ROUND(CAST(dr.reading AS numeric), 2) AS curr_reading,
        dr.reading_slot_time::date AS curr_date
    FROM devices_readings dr
    JOIN devices d ON dr.device_id = d.id
    WHERE d.type_id = 289
      AND d.place_id = 1
      AND dr.reading_slot_time::date = '2020-08-19'
) curr
-- 关联每个设备的最近历史读数（指定日期之前）
LEFT JOIN LATERAL (
    SELECT 
        ROUND(CAST(dr.reading AS numeric), 2) AS prev_reading,
        dr.reading_slot_time::date AS prev_date
    FROM devices_readings dr
    WHERE dr.device_id = curr.device_id
      -- 限定日期范围：指定日期前5天（覆盖需求中的2-5天前）
      AND dr.reading_slot_time < '2020-08-19'::date
      AND dr.reading_slot_time >= '2020-08-19'::date - INTERVAL '5 days'
    -- 按时间倒序取第一条，即最近的一次读数
    ORDER BY dr.reading_slot_time DESC
    LIMIT 1
) hist ON true;

针对分区表的优化要点 #

由于devices_readings是按月份分区，需确保查询能触发分区裁剪，避免扫描全部分区：

1. 明确日期范围：在LATERAL子查询中限定reading_slot_time >= '2020-08-19'::date - INTERVAL '5 days'，数据库只会扫描2020年8月的分区（若分区键为reading_slot_time的月份），不会扫描无关月份的数据。
2. 添加复合覆盖索引：为devices_readings创建索引，让数据库快速定位每个设备的最近历史读数：

   CREATE INDEX idx_devices_readings_device_time ON devices_readings (device_id, reading_slot_time DESC) INCLUDE (reading);
   

3. 减少类型转换开销：若reading_slot_time是timestamp类型，直接用dr.reading_slot_time < '2020-08-19'::timestamp代替::date转换，避免额外计算。

补充说明 #

• 使用LEFT JOIN LATERAL可保留无历史读数的设备记录（此时prev_reading和reading_change为NULL）；若仅需保留有历史读数的设备，改为JOIN LATERAL即可。

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