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

with avg_t as (
    select category,sum(usage_kwh)/count(distinct energy_logs.device_id) avg_usage
    from smart_devices
    join energy_logs using(device_id)
    where date(log_timestamp) between '2025-01-01' and '2025-01-31'
    group by category
),### 平均用电量表
total_t as(
    select smart_devices.device_name,avg_usage,device_id,
    upper(replace(location,' ','_')) location_code,
    round(sum(usage_kwh),2) total_usage,
    case when round(sum(usage_kwh),2)>=50 then 'High Load' else 'Normal' end efficiency_level
    from smart_devices join energy_logs using(device_id)
    join avg_t using(category)
    where date(log_timestamp) between '2025-01-01' and '2025-01-31'
    group by device_name,location,device_id,avg_usage
) ### 每个设备总用电量表

select device_name,location_code,total_usage,efficiency_level
from total_t 
where total_usage > avg_usage
order by total_usage desc,device_id
### 注意不能直接在where里比较sum与avg，因为先执行where后执行groupby，where后面不支持聚合语句