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

# 1、计算每台设备总用电量
with t1 as (
    select
    e.device_id,
    device_name,
    location,
    category,
    sum(usage_kwh) as sum_usage
    from energy_logs e
    left 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 1,2,3,4
),
# 2、每个类别的平均用电量
t2 as (
    select 
    category,
    avg(sum_usage) as avg_usage
    from t1
    group by category
)

select
device_name,
upper(replace(location, ' ', '_')) as location_code,
sum_usage as total_usage,
case when sum_usage>=50 then 'High Load' else 'Normal' 
end as efficiency_level
from t1
left join t2
on t1.category = t2.category
where t1.sum_usage>t2.avg_usage
order by total_usage desc, t1.device_id
;