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

WITH monthly_usage AS (
    -- 计算每个设备在2025年1月的总用电量
    SELECT 
        d.device_id,
        d.device_name,
        d.category,
        d.location,
        COALESCE(SUM(e.usage_kwh), 0) AS total_usage_raw
    FROM smart_devices d
    LEFT JOIN energy_logs e ON d.device_id = e.device_id
        AND e.log_timestamp >= '2025-01-01 00:00:00'
        AND e.log_timestamp <= '2025-01-31 23:59:59'
        AND e.status = 'active'  -- 只考虑active状态的记录
    GROUP BY d.device_id, d.device_name, d.category, d.location
),
category_stats AS (
    -- 计算每个设备类别的平均用电量
    SELECT 
        device_id,
        device_name,
        category,
        location,
        total_usage_raw,
        -- 窗口函数：计算每个类别下所有设备的平均用电量
        AVG(total_usage_raw) OVER(PARTITION BY category) AS category_avg_usage
    FROM monthly_usage
),
filtered_devices AS (
    -- 筛选出用电量大于类别平均值的设备
    SELECT 
        device_id,
        device_name,
        category,
        location,
        ROUND(total_usage_raw, 2) AS total_usage,
        category_avg_usage
    FROM category_stats
    WHERE total_usage_raw > category_avg_usage
        AND total_usage_raw > 0  -- 确保设备有实际用电量
)
SELECT 
    fd.device_name,
    UPPER(REPLACE(fd.location, ' ', '_')) AS location_code,
    fd.total_usage,
    CASE 
        WHEN fd.total_usage >= 50.00 THEN 'High Load'
        ELSE 'Normal'
    END AS efficiency_level
FROM filtered_devices fd
ORDER BY 
    fd.total_usage DESC,
    fd.device_id ASC;

-- 1对多对多  一个类别多个设备，一个设备多个记录

-- 每个设备的总用电量
with total_tb as (
    select
        device_id
        ,round(sum(usage_kwh), 2) as total_usage  -- 每个设备的总电量
    from
        energy_logs 
    where
        log_timestamp >= '2025-01-01' and log_timestamp < '2025-02-01'
    group by 
        device_id
),

-- 每个类别的平均用电量
avg_tb as (
    select
        d.category
        ,avg(t.total_usage) as avg_usage       
    from
        smart_devices as d
        inner join total_tb as t on d.device_id=t.device_id
    group by 
        d.category
)

-- 输出
select
    d.device_name
    ,upper(replace(d.location,' ', '_'))  as location_code
    ,t.total_usage    
    ,case when total_usage >= 50.00 then 'High Load' else 'Normal' end  as efficiency_level      
from 
    smart_devices as d
    inner join total_tb  as t on d.device_id=t.device_id
    inner join avg_tb as a on d.category =a.category
where
    t.total_usage > a.avg_usage
order by 
    t.total_usage desc ,d.device_id asc ;

select
    device_name,
    location_code,
    total_usage,
    efficiency_level
from
    (
        select
            s.device_name,
            upper(
                replace
                    (s.location, ' ', '_')
            ) as location_code,
            round(sum(usage_kwh), 2) as total_usage,
            case
                when round(sum(usage_kwh), 2) >= 50.00 then 'High Load'
                else 'Normal'
            end as efficiency_level,
            case
                when round(sum(usage_kwh), 2) > avg(round(sum(usage_kwh), 2)) over (
                    partition by
                        s.category
                ) then 1
                else 0
            end as avg_or_not
        from
            smart_devices as s
            join energy_logs as e on s.device_id = e.device_id
        where
            date_format(e.log_timestamp, '%Y-%m') = '2025-01'
        group by
            s.device_id,
            s.device_name,
            s.location,
            s.category
        order by
            total_usage desc,
            s.device_id
    ) as tmp
where
    avg_or_not = 1

avg() over()的使用

select device_name,upper(replace(location,' ','_')) location_code,total_usage,
    case when total_usage >=50 then 'High Load' else 'Normal' end efficiency_level
from ( 
    # 1、类别平均用电
    select device_name,device_id,category,location,total_usage,
        avg(total_usage)over(partition by category) category_avg_usage
    
    from (
        # 2、设备总用电
        select 
            distinct device_name,device_id,category,location,
            sum(usage_kwh)over(partition by device_name) total_usage
        from energy_logs join smart_devices using(device_id)
        where log_timestamp like '2025-01%'
    )a
)b
# 3、总用电量 > 类别平均用电
where total_usage>category_avg_usage
order by total_usage desc ,device_id

with cnt_device as (
    select category,count(device_id) as cnt_device
    from
        (select distinct el.device_id,category
        from smart_devices sd
        inner join energy_logs el on sd.device_id = el.device_id
        where date(log_timestamp) between '2025-01-01' and '2025-01-31') device_new
    group by category
)

select device_name,
       upper(replace(location,' ','_')) as location_code,
       sum_usage_kwh as total_usage,
       case when sum_usage_kwh>=50 then 'High Load' else 'Normal' end as efficiency_level
from
    (select distinct
        sd.device_id,sd.category,device_name,
        location,
        sum(usage_kwh) over(partition by el.device_id) as sum_usage_kwh,
        sum(usage_kwh) over(partition by category) as sum_all_usage_kwh
    from smart_devices sd
    inner join energy_logs el on sd.device_id = el.device_id
    where date(log_timestamp) between '2025-01-01' and '2025-01-31') new
inner join cnt_device cd on cd.category = new.category
where sum_usage_kwh>sum_all_usage_kwh/cnt_device
order by total_usage desc ,device_id