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

with energy_all as(
select
device_id,
round(sum(usage_kwh),2) as total_kwh
from energy_logs
where date(log_timestamp) between '2025-01-01' and '2025-01-31'
group by device_id
)
select
t1.device_name as device_name,
upper(replace(location,' ','_')) as  location_code,
tk as total_usage,
case when tk>=50.00 then 'High Load'
else 'Normal' end as efficiency_level
from
(select
a.device_id as did,
a.total_kwh as tk,
b.device_name as device_name,
b.location as location,
avg(a.total_kwh) over (partition by b.category ) as avg_en
from energy_all as a
left join smart_devices as b
on a.device_id = b.device_id) as t1
where tk>avg_en
order by total_usage desc,did asc