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

with temp0 as (
    select category,round(sum(usage_kwh)/count(*),2) as avg_usage_kwh
    from smart_devices sd inner join energy_logs el 
    on sd.device_id = el.device_id
    where date_format(log_timestamp,"%Y-%m") = "2025-01"
    group by category
), temp1 as (
    select sd.device_id,round(sum(usage_kwh),2) as total_usage
    from smart_devices sd inner join energy_logs el 
    on sd.device_id = el.device_id
    where date_format(log_timestamp,"%Y-%m") = "2025-01"
    group by sd.device_id
)


select sd.device_name,upper(replace(location," ","_")) as location_code,total_usage,
case
    when total_usage >= 50.00 then "High Load"
    else "Normal"
end as efficiency_level
from temp0 t0 inner join smart_devices sd on t0.category = sd.category
inner join temp1 t1 on t1.device_id = sd.device_id
where t1.total_usage > t0.avg_usage_kwh
order by total_usage desc,t1.device_id asc;