题解 | 智能家居设备高能耗异常监控分析

WITH device_usage AS (
    -- ① 每台设备 2025年1月总用电量
    SELECT 
        t2.device_id,
        t2.device_name,
        t2.category,
        t2.location,
        ROUND(SUM(t1.usage_kwh), 2) AS total_usage
    FROM energy_logs t1
    LEFT JOIN smart_devices t2 USING(device_id)
    WHERE log_timestamp >= '2025-01-01'
      AND log_timestamp <  '2025-02-01'
    GROUP BY t2.device_id, t2.device_name, t2.category, t2.location
),

category_avg AS (
    -- ② 每个类别下设备的“月总用电平均值”
    SELECT 
        category,
        AVG(total_usage) AS avg_usage
    FROM device_usage
    GROUP BY category
)

-- ③ 筛选设备总用电 > 类别平均
SELECT 
    d.device_name,
    UPPER(REPLACE(d.location, ' ', '_')) AS location_code,
    d.total_usage,
    CASE 
        WHEN d.total_usage >= 50.00 THEN 'High Load'
        ELSE 'Normal'
    END AS efficiency_level
FROM device_usage d
JOIN category_avg c
    ON d.category = c.category
WHERE d.total_usage > c.avg_usage
ORDER BY d.total_usage DESC, d.device_id ASC;