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

# 筛选汇比同类设备平均用电量更高的高耗能设备并调整顺序
select device_name,location_code,total_usage,efficiency_level
from(
# 计算每个类别设备当月的所有设备平均总用电量
select device_id,device_name,location_code,category,total_usage,cast(avg(total_usage) over(partition by category) as decimal(10,2)) as category_mean,case when total_usage>=50 then 'High Load' when total_usage<50 then 'Normal' end as efficiency_level
from(
# 计算当月各设备总用电量，去重
select distinct device_id,device_name,location_code,category,round(sum(usage_kwh) over(partition by device_id),2) as total_usage
from(
# 连接表，替换字符，大写转换、限定时间条件
select el.log_id,sd.device_id ,sd.device_name,upper(replace(sd.location,' ','_')) as location_code,sd.category,el.usage_kwh,el.log_timestamp,sd.install_date,el.status
from energy_logs as el 
left join smart_devices as sd on el.device_id = sd.device_id
# 此处需要注意between固定00:00:00,用year和month搭配筛选时间更适合
where year(el.log_timestamp) = '2025' and month(el.log_timestamp) = 1
) as temp
) as temp2
) as temp3
where total_usage>category_mean
order by total_usage desc,device_id asc