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

WITH t1 AS (
    SELECT s.device_id,
           device_name,
           location,
           category,
           ROUND(SUM(usage_kwh),2) AS total_usage
    FROM smart_devices s 
    LEFT JOIN energy_logs e ON s.device_id=e.device_id
    WHERE substr(log_timestamp,1,7)='2025-01'
    GROUP BY device_name,location,category,s.device_id
),
t2 AS (
    SELECT category,
           AVG(total_usage) AS avg_total_usage
    FROM t1 
    GROUP BY category 
)
SELECT device_name,
       UPPER(REPLACE(location,' ','_')) AS location_code,
       total_usage,
       CASE WHEN total_usage>=50 THEN 'High Load' ELSE 'Normal' END AS efficiency_level
FROM t1 
LEFT JOIN t2 ON t1.category=t2.category
WHERE total_usage>avg_total_usage
ORDER BY total_usage DESC,t1.device_id ASC