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

with monthly_usage as (
    -- 第一步：算出每个设备在 2025年1月 的总用电量，并准备好需要的展示字段
    select 
        sd.device_id,
        sd.device_name,
        sd.category,
        upper(replace(sd.location, ' ', '_')) as location_code,
        round(sum(el.usage_kwh), 2) as total_usage
    from smart_devices sd
    inner join energy_logs el on sd.device_id = el.device_id
    where el.log_timestamp >= '2025-01-01' 
      and el.log_timestamp < '2025-02-01'
    group by 
        sd.device_id, 
        sd.device_name, 
        sd.category, 
        sd.location
),
category_avg as (
    select
    category,
    avg(total_usage) as avg_usage
    from
    monthly_usage
    group by category
)

select 
m.device_name,
m.location_code,
m.total_usage,
case 
when m.total_usage >= 50.00 then 'High Load'
else 'Normal' end efficiency_level
from monthly_usage m 
inner join category_avg ca 
on ca.category = m.category
where m.total_usage > ca.avg_usage
order by total_usage desc , device_id asc