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

with a as
(select category,avg(t_usage) avg_usage
from (select device_id,sum(usage_kwh) t_usage from energy_logs  where date_format(log_timestamp,'%Y%m')='202501' group by device_id) b
join smart_devices sd using(device_id)
group by category)
select device_name,location_code,total_usage,efficiency_level
from
(select sd.device_id,sd.device_name,replace(upper(location),' ','_') location_code,round(sum(usage_kwh),2) total_usage,if(round(sum(usage_kwh),2)>=50.00,'High Load','Normal') efficiency_level
from energy_logs el
join smart_devices sd using(device_id)
join a using(category)
where date_format(log_timestamp,'%Y%m')='202501' 
group by sd.device_id,sd.device_name,location_code
having sum(usage_kwh)>avg(avg_usage)) c
order by total_usage desc,device_id