SQL实现按设备、电表分组取最新记录统计null读数数量

业务表energy_readings用于存储各设备绑定电表的历史读数数据，表结构如下：

• equipment_id：设备唯一标识
• meter_id：电表唯一标识
• readings：电表读数值
• reading_date：读数生成时间

需要通过SQL实现两层统计逻辑：

1. 先按equipment_id、meter_id两个维度分组，筛选出每个分组内reading_date最晚（最新生成）的1条记录
2. 基于第一步筛选出的所有最新记录，按equipment_id维度聚合，统计该设备下readings值为NULL的电表记录总数量

统计规则匹配样例：
样例数据中equipment_id=1绑定的meter_id=1、meter_id=2最新记录的readings均为NULL，对应统计值Count=2；equipment_id=2绑定的电表中，仅meter_id=2的最新记录readings为NULL（meter_id=1最新记录readings为399，非NULL），对应统计值Count=1。最终输出结果仅包含equipment_id、Count两个字段。

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

用窗口函数ROW_NUMBER()标记组内记录排序，逻辑清晰，执行性能更优：

WITH meter_latest_record AS (
    SELECT
        equipment_id,
        meter_id,
        readings,
        ROW_NUMBER() OVER (
            PARTITION BY equipment_id, meter_id
            ORDER BY reading_date DESC
        ) AS group_sort_no
    FROM energy_readings
)
SELECT
    equipment_id,
    COUNT(CASE WHEN readings IS NULL THEN 1 END) AS `Count`
FROM meter_latest_record
WHERE group_sort_no = 1 -- 仅保留每个设备+电表分组下的最新记录
GROUP BY equipment_id;

不支持窗口函数的老版本数据库（如MySQL 5.x） #

通过子查询先关联取到每个分组的最新记录时间，再做聚合统计：

SELECT
    base.equipment_id,
    COUNT(CASE WHEN base.readings IS NULL THEN 1 END) AS `Count`
FROM energy_readings base
INNER JOIN (
    SELECT
        equipment_id,
        meter_id,
        MAX(reading_date) AS latest_read_time
    FROM energy_readings
    GROUP BY equipment_id, meter_id
) group_latest
ON base.equipment_id = group_latest.equipment_id
AND base.meter_id = group_latest.meter_id
AND base.reading_date = group_latest.latest_read_time
GROUP BY base.equipment_id;

• 如果同一equipment_id+meter_id分组下存在多条reading_date完全相同的最新记录，ROW_NUMBER()写法会随机取其中1条，若需要保留所有同时刻最新记录，可以把ROW_NUMBER()替换为RANK()
• 统计NULL数量时不要直接用COUNT(readings)，该写法会自动忽略NULL值，和需求逻辑不符，上述写法中COUNT(CASE WHEN ... THEN 1 END)只会统计符合判断条件的记录，结果准确

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