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

WITH 
device_monthly_usage AS (
    SELECT 
        device_id,
        SUM(usage_kwh) AS total_usage 
    FROM energy_logs
    WHERE log_timestamp BETWEEN '2025-01-01 00:00:00' AND '2025-01-31 23:59:59'
    GROUP BY device_id
),
category_avg_usage AS (
    SELECT 
        sd.category,
        AVG(dmu.total_usage) AS category_avg_usage  
    FROM smart_devices sd
    JOIN device_monthly_usage dmu ON sd.device_id = dmu.device_id
    GROUP BY sd.category
)
SELECT 
    sd.device_name,
    UPPER(REPLACE(sd.location, ' ', '_')) AS location_code,
    ROUND(dmu.total_usage, 2) AS total_usage,
    CASE 
        WHEN ROUND(dmu.total_usage, 2) >= 50.00 THEN 'High Load'
        ELSE 'Normal'
    END AS efficiency_level
FROM smart_devices sd
JOIN device_monthly_usage dmu ON sd.device_id = dmu.device_id
JOIN category_avg_usage cau ON sd.category = cau.category
WHERE dmu.total_usage > cau.category_avg_usage
ORDER BY dmu.total_usage DESC, sd.device_id ASC;