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

求助这两个cte有没有什么能缩的，感觉太长了
with temp1 as
(
    select
    t2.category,
    sum(usage_kwh),
    count(distinct device_id),
    round(sum(usage_kwh)/count(distinct device_id),2)  as avg_usage
    from energy_logs t1
    left join smart_devices t2
    using(device_id)
    where date_format(log_timestamp,'%Y-%m') = '2025-01'  
    group by 1
),
temp2 as
(
    select
    t2.category,
    device_id,
    sum(usage_kwh) as total_sum
    from energy_logs t1
    left join smart_devices t2
    using(device_id)
    where date_format(log_timestamp,'%Y-%m') = '2025-01'  
    group by 1,2
    order by 1
)
select
device_name,
upper(replace(sd.location,' ','_')) as location_code,
total_sum as total_usage,
case when total_sum >= 50 then 'High Load' else 'Normal' end as efficiency_level
from temp2 t2
left join temp1 t1
using(category)
left join smart_devices sd
using(device_id)
where t2.total_sum > t1.avg_usage
order by 3 desc ,device_id