智能家居设备高能耗异常监控分析_牛客题霸

某智能家居服务商需要监控用户家中各类设备的电力消耗情况。系统需要定期分析每台设备的月度用电量，找出那些比同类设备（如所有“空调”或所有“热水器”）平均用电量更高的“高耗能设备”，并对这些设备生成简报，以便提醒用户检查设备是否老化或存在漏电风险。表结构和字段说明：表1：smart_devices(智能设备信息表) device_id(varchar): 设备唯一标识符，主键。 device_name(varchar): 设备名称（如 "Living Room AC"）。 category(varchar): 设备类别（如 "HVAC", "Lighting", "Kitchen"）。 location(varchar): 设备所在具体位置（如 "Living Room", "Master Bedroom"）。 install_date(date): 设备安装日期。表2：energy_logs(能耗日志表) log_id(int): 日志ID，主键。 device_id(varchar): 设备ID，逻辑关联smart_devices.device_id。 usage_kwh(decimal): 该时段消耗的电量（千瓦时），保留两位小数。 log_timestamp(datetime): 记录日志的具体时间点。 status(varchar): 设备状态（"active", "standby", "error"）。问题请编写SQL查询，找出在 2025年1月（2025-01-01 至 2025-01-31）期间，总用电量严格大于其所属设备类别（category）下所有设备该月平均总用电量的设备。查询结果要求： device_name：设备名称。 location_code：位置代码。要求将location字段中的所有空格替换为下划线_，并将结果转换为全大写（例如 "Living Room" 变为 "LIVING_ROOM"）。 total_usage：该设备2025年1月的总用电量，结果保留2位小数（四舍五入）。 efficiency_level：能效评级。根据该设备的总用电量判断：如果total_usage= 50.00，显示 'High Load' 如果total_usage 结果排序：优先按total_usage从高到低排序；如果用电量相同，则按device_id升序排序。

示例1

输入

-- 创建设备表
CREATE TABLE smart_devices (
    device_id VARCHAR(20) PRIMARY KEY,
    device_name VARCHAR(50),
    category VARCHAR(30),
    location VARCHAR(50),
    install_date DATE
);

-- 创建能耗日志表
CREATE TABLE energy_logs (
    log_id INT PRIMARY KEY,
    device_id VARCHAR(20),
    usage_kwh DECIMAL(10, 2),
    log_timestamp DATETIME,
    status VARCHAR(20)
);

-- 插入设备数据
INSERT INTO smart_devices VALUES ('D001', 'Master Bedroom AC', 'HVAC', 'Master Bedroom', '2024-05-20');
INSERT INTO smart_devices VALUES ('D002', 'Living Room AC', 'HVAC', 'Living Room', '2024-06-15');
INSERT INTO smart_devices VALUES ('D003', 'Kitchen Fridge', 'Kitchen', 'Kitchen Area', '2024-01-10');
INSERT INTO smart_devices VALUES ('D004', 'Smart Oven', 'Kitchen', 'Kitchen Area', '2024-11-05');
INSERT INTO smart_devices VALUES ('D005', 'Guest Room AC', 'HVAC', 'Guest Room', '2024-12-01');

-- 插入能耗日志数据 (2025年1月数据为主，包含少量干扰数据)
-- HVAC类: D001(Total: 40+25=65), D002(Total: 80+10=90), D005(Total: 20). Avg = (65+90+20)/3 = 58.33
-- Kitchen类: D003(Total: 30), D004(Total: 10). Avg = 20.
INSERT INTO energy_logs VALUES (1, 'D001', 40.00, '2025-01-05 10:00:00', 'active');
INSERT INTO energy_logs VALUES (2, 'D001', 25.00, '2025-01-20 14:00:00', 'active');
INSERT INTO energy_logs VALUES (3, 'D002', 80.00, '2025-01-15 18:00:00', 'active');
INSERT INTO energy_logs VALUES (4, 'D002', 10.00, '2025-01-16 09:00:00', 'standby');
INSERT INTO energy_logs VALUES (5, 'D003', 30.00, '2025-01-10 12:00:00', 'active'); -- 高于Kitchen平均
INSERT INTO energy_logs VALUES (6, 'D004', 10.00, '2025-01-12 18:00:00', 'active');
INSERT INTO energy_logs VALUES (7, 'D005', 20.00, '2025-01-25 20:00:00', 'active');
INSERT INTO energy_logs VALUES (8, 'D002', 50.00, '2024-12-31 23:59:00', 'active'); -- 干扰数据：非2025年1月

输出

device_name|location_code|total_usage|efficiency_level
Living Room AC|LIVING_ROOM|90.00|High Load
Master Bedroom AC|MASTER_BEDROOM|65.00|High Load
Kitchen Fridge|KITCHEN_AREA|30.00|Normal

说明

解析逻辑：
HVAC类：D001(65), D002(90), D005(20)。平均值 = (65+90+20)/3 = 58.33。D001 (65 > 58.33) -> 选中。Location: MASTER_BEDROOM.
D002 (90 > 58.33) -> 选中。Location: LIVING_ROOM.
D005 (20 < 58.33) -> 排除。
Kitchen类：D003(30), D004(10)。平均值 = (30+10)/2 = 20.00。D003 (30 > 20) -> 选中。Location: KITCHEN_AREA.
排序：Total Usage 降序 (90 -> 65 -> 30)。
device_name location_code total_usage efficiency_level
Living Room AC LIVING_ROOM 90.00 High Load
Master Bedroom AC MASTER_BEDROOM 65.00 High Load
Kitchen Fridge KITCHEN_AREA 30.00 Normal

device_name	location_code	total_usage	efficiency_level
Living Room AC	LIVING_ROOM	90.00	High Load
Master Bedroom AC	MASTER_BEDROOM	65.00	High Load
Kitchen Fridge	KITCHEN_AREA	30.00	Normal

-- 创建设备表
CREATE TABLE smart_devices (
    device_id VARCHAR(20) PRIMARY KEY,
    device_name VARCHAR(50),
    category VARCHAR(30),
    location VARCHAR(50),
    install_date DATE
);

-- 创建能耗日志表
CREATE TABLE energy_logs (
    log_id INT PRIMARY KEY,
    device_id VARCHAR(20),
    usage_kwh DECIMAL(10, 2),
    log_timestamp DATETIME,
    status VARCHAR(20)
);

-- 插入设备数据
INSERT INTO smart_devices VALUES ('D001', 'Master Bedroom AC', 'HVAC', 'Master Bedroom', '2024-05-20');
INSERT INTO smart_devices VALUES ('D002', 'Living Room AC', 'HVAC', 'Living Room', '2024-06-15');
INSERT INTO smart_devices VALUES ('D003', 'Kitchen Fridge', 'Kitchen', 'Kitchen Area', '2024-01-10');
INSERT INTO smart_devices VALUES ('D004', 'Smart Oven', 'Kitchen', 'Kitchen Area', '2024-11-05');
INSERT INTO smart_devices VALUES ('D005', 'Guest Room AC', 'HVAC', 'Guest Room', '2024-12-01');

-- 插入能耗日志数据 (2025年1月数据为主，包含少量干扰数据)
-- HVAC类: D001(Total: 40+25=65), D002(Total: 80+10=90), D005(Total: 20). Avg = (65+90+20)/3 = 58.33
-- Kitchen类: D003(Total: 30), D004(Total: 10). Avg = 20.
INSERT INTO energy_logs VALUES (1, 'D001', 40.00, '2025-01-05 10:00:00', 'active');
INSERT INTO energy_logs VALUES (2, 'D001', 25.00, '2025-01-20 14:00:00', 'active');
INSERT INTO energy_logs VALUES (3, 'D002', 80.00, '2025-01-15 18:00:00', 'active');
INSERT INTO energy_logs VALUES (4, 'D002', 10.00, '2025-01-16 09:00:00', 'standby');
INSERT INTO energy_logs VALUES (5, 'D003', 30.00, '2025-01-10 12:00:00', 'active'); -- 高于Kitchen平均
INSERT INTO energy_logs VALUES (6, 'D004', 10.00, '2025-01-12 18:00:00', 'active');
INSERT INTO energy_logs VALUES (7, 'D005', 20.00, '2025-01-25 20:00:00', 'active');
INSERT INTO energy_logs VALUES (8, 'D002', 50.00, '2024-12-31 23:59:00', 'active'); -- 干扰数据：非2025年1月