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

with device_sum as (
    select
        category,
        device_name,
        t1.device_id,
        location,
        round(sum(usage_kwh), 2) as total_usage
    from energy_logs t1
    left join smart_devices t2 on t1.device_id=t2.device_id
    where log_timestamp between '2025-01-01 00:00:00' and '2025-01-31 23:59:59'
    group by category, device_name, device_id, location
),
category_avg as (
    select
        category,
        avg(total_usage) as usage_kwh_avg
    from device_sum
    group by category
)
select
    device_name,
    UPPER(replace(location, ' ', '_')) as location_code,
    total_usage,
    (
        case
            when total_usage >= 50.00 then 'High Load'
            else 'Normal'
        end
    ) as efficiency_level
from device_sum t1
left join category_avg t2 on t1.category=t2.category
where total_usage > usage_kwh_avg
order by total_usage desc, device_id

注意时间范围为between '2025-01-01 00:00:00' and '2025-01-31 23:59:59'，如果直接写between '2025-01-01' and '2025-01-31'代表的是between '2025-01-01 00:00:00' and '2025-01-31 00:00:00'， 不包含2025-01-31 00:00:00之后的时间