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

WITH a AS(
SELECT em.device_id, device_name, category, UPPER(REPLACE(location,' ','_' )) location_code, usage_kwh
FROM smart_devices sd 
JOIN energy_logs em USING(device_id)
WHERE log_timestamp >= '2025-01-01' AND log_timestamp < '2025-02-01'
),
b AS(
    SELECT category, device_id, device_name, location_code, ROUND(SUM(usage_kwh),2) total_usage
    FROM a 
    GROUP BY 1,2, 3,4
),
c AS(
    SELECT category, ROUND(AVG(total_usage),2) avg_usage
    FROM b 
    GROUP BY 1
)
SELECT device_name, location_code,  total_usage,
    CASE WHEN total_usage >= 50.00 THEN 'High Load' ELSE 'Normal' END efficiency_level

FROM b  
JOIN c USING(category)
WHERE total_usage > avg_usage
ORDER BY 3 DESC, device_id