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

# smart_devices、energy_logs
# device_id
# 找出在 2025年1月（2025-01-01 至 2025-01-31） 期间，总用电量 严格大于 其所属设备类别（category）下所有设备该月平均总用电量的设备
# \1 计算 设备类别（category）下所有设备的月平均总用电量 和 总用电量
with avg_category as (
select s.category,
       sum(e.usage_kwh) as total_usage,
       round(avg(usage_kwh),2) as avg_usage
from smart_devices s  
join energy_logs e on s.device_id = e.device_id and date(e.log_timestamp) between '2025-01-01' and '2025-01-31'
group by s.category),
total_category as (
select s.device_id,
       sum(e.usage_kwh) as total_usage
from smart_devices s  
join energy_logs e on s.device_id = e.device_id and date(e.log_timestamp) between '2025-01-01' and '2025-01-31'
group by s.device_id)

select s.device_name,
       upper(replace(s.location,' ','_')) as location_code,
       t.total_usage,
       (case when t.total_usage >= 50 then 'High Load' else  'Normal' end)  as efficiency_level
from smart_devices s 
join avg_category a on a.category = s.category
join total_category t on t.device_id = s.device_id
where t.total_usage > a.avg_usage
order by t.total_usage desc,s.device_id asc