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

with total_avg_kwh as (
select
sd.category,
round(avg(el.usage_kwh),2) as avg_kwh
from
energy_logs el
left join
smart_devices sd on el.device_id=sd.device_id
where el.log_timestamp between'2021-01-01' and '2025-01-31'
group by sd.category
),
device_kwh as (
    select 
    sd.category,
    sd.device_id,
    sd.device_name,
    sd.location,
    round(sum(el.usage_kwh),2) as total_usage
    from
    energy_logs el
    left join
    smart_devices sd on el.device_id=sd.device_id
    where date_format(el.log_timestamp,'%Y-%m')='2025-01'
    group by sd.category,sd.device_id,sd.device_name,sd.location
),
info as (
select 
device_id,
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 
device_kwh dk
left join
total_avg_kwh tk on dk.category=tk.category
where total_usage>avg_kwh
order by total_usage desc,device_id
)

select
device_name,
location_code,
total_usage,
efficiency_level
from
info;