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

with t1 as(
    select category,
        avg(usage_kwh) as avg_usage
    from energy_logs e
    join smart_devices s on e.device_id=s.device_id
    where date(log_timestamp) between '2025-01-01' and '2025-01-31'
    group by category
)

select device_name,
    upper(replace(location,' ','_')) as location_code,
    round(sum(usage_kwh),2) as total_usage,
    case  when round(sum(usage_kwh),2)>=50 then 'High Load'
        when round(sum(usage_kwh),2)<50 then 'Normal' end as efficiency_level
from energy_logs e
join smart_devices s on e.device_id=s.device_id
join t1 on s.category=t1.category
where date(log_timestamp) between '2025-01-01' and '2025-01-31'
group by e.device_id,device_name,location_code
having total_usage>max(avg_usage)
order by total_usage desc,e.device_id