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

select
    d.device_name,
    replace(upper(d.location), ' ', '_') as location_code,
    d.total_usage,
    case
        when d.total_usage >= 50 then 'High Load'
        else 'Normal'
    end as efficiency_level
from (
    select
        s.device_id,
        s.device_name,
        s.category,
        s.location,
        round(sum(e.usage_kwh), 2) as total_usage
    from smart_devices s
    join energy_logs e
        on s.device_id = e.device_id
    where e.log_timestamp >= '2025-01-01'
      and e.log_timestamp < '2025-02-01'
    group by
        s.device_id,
        s.device_name,
        s.category,
        s.location
) d
join (
    select
        category,
        avg(total_usage) as avg_kwh
    from (
        select
            s.category,
            sum(e.usage_kwh) as total_usage
        from smart_devices s
        join energy_logs e
            on s.device_id = e.device_id
        where e.log_timestamp >= '2025-01-01'
          and e.log_timestamp < '2025-02-01'
        group by
            s.category,
            s.device_id
    ) t
    group by category
) c
    on d.category = c.category
where d.total_usage > c.avg_kwh
order by
    d.total_usage desc,
    d.device_id asc;