868 lines
39 KiB
SQL
868 lines
39 KiB
SQL
|
||
-- ----------------------------
|
||
-- 部门表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS department (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL UNIQUE, -- 部门名称
|
||
manager_id INTEGER, -- 部门经理ID
|
||
description TEXT, -- 部门描述
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (manager_id) REFERENCES user(id)
|
||
);
|
||
|
||
-- ----------------------------
|
||
-- 用户表（密码改为明文存储，不加密）
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS user (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT UNIQUE NOT NULL, -- 用户名(唯一)
|
||
password TEXT NOT NULL, -- 密码(明文存储，不加密)
|
||
real_name TEXT, -- 真实姓名
|
||
email TEXT UNIQUE, -- 邮箱(唯一)
|
||
phone TEXT, -- 联系电话
|
||
department_id INTEGER, -- 所属部门（外键）
|
||
position TEXT, -- 职位/职务
|
||
permission_level TEXT NOT NULL, -- 权限级别(Admin/Supervisor/Operator)
|
||
hire_date DATE, -- 入职日期
|
||
status TEXT DEFAULT 'Active', -- 状态(Active/Inactive)
|
||
linked_devices INTEGER DEFAULT 0, -- 关联设备数量
|
||
last_login TIMESTAMP, -- 最后登录时间
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 更新时间
|
||
CHECK (permission_level IN ('Admin', 'Supervisor', 'Operator')),
|
||
CHECK (status IN ('Active', 'Inactive')),
|
||
FOREIGN KEY (department_id) REFERENCES department(id)
|
||
);
|
||
|
||
-- 创建更新触发器自动更新updated_at字段
|
||
CREATE TRIGGER IF NOT EXISTS update_user_timestamp
|
||
AFTER UPDATE ON user
|
||
FOR EACH ROW
|
||
BEGIN
|
||
UPDATE user SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
|
||
END;
|
||
|
||
-- ----------------------------
|
||
-- 插入管理员用户（使用正确的权限级别格式）
|
||
-- ----------------------------
|
||
INSERT INTO user (username, password, real_name, email, permission_level, status)
|
||
VALUES ('root', 'root', 'root', '397088740@qq.com', 'Admin', 'Active')
|
||
ON CONFLICT(username) DO NOTHING;
|
||
|
||
-- ----------------------------
|
||
-- 用户设备关联表（新增）
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS user_device (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER NOT NULL,
|
||
device_id INTEGER NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES user(id),
|
||
FOREIGN KEY (device_id) REFERENCES device(id)
|
||
);
|
||
|
||
-- ----------------------------
|
||
-- 设备表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS device (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
device_name TEXT NOT NULL,
|
||
device_code TEXT UNIQUE NOT NULL,
|
||
status TEXT NOT NULL,
|
||
operator TEXT, -- 操作人员
|
||
fault_description TEXT, -- 故障描述
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ----------------------------
|
||
-- 温湿度数据表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS temperature_data (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
device_id INTEGER NOT NULL,
|
||
temperature REAL NOT NULL,
|
||
humidity REAL NOT NULL,
|
||
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
ph REAL, -- pH值（实数类型）
|
||
light_intensity INTEGER, -- 光照强度（整数，单位：Lux）
|
||
FOREIGN KEY (device_id) REFERENCES device (id)
|
||
);
|
||
|
||
-- ----------------------------
|
||
-- 插入部门数据
|
||
-- ----------------------------
|
||
INSERT INTO department (name, manager_id, description) VALUES
|
||
('IT部', 1, '负责公司信息技术管理'),
|
||
('销售部', 2, '负责产品销售'),
|
||
('技术部', 3, '负责技术研发'),
|
||
('生产部', NULL, '负责产品生产'),
|
||
('运维部', NULL, '负责设备维护')
|
||
ON CONFLICT(name) DO NOTHING;
|
||
|
||
-- ----------------------------
|
||
-- 操作日志表
|
||
-- ----------------------------
|
||
CREATE TABLE IF NOT EXISTS operation_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER,
|
||
type TEXT NOT NULL,
|
||
message TEXT NOT NULL,
|
||
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES user(id)
|
||
);
|
||
|
||
-- ----------------------------
|
||
-- 插入设备数据
|
||
-- ----------------------------
|
||
INSERT INTO device (device_name, device_code, status, operator, fault_description)
|
||
VALUES
|
||
('设备A', 'DEV-A', 'normal', 'root', NULL),
|
||
('设备B', 'DEV-B', 'warning', 'root', '温度过高'),
|
||
('设备C', 'DEV-C', 'fault', 'root', '传感器故障'),
|
||
('设备D', 'DEV-D', 'normal', 'root', NULL),
|
||
('设备E', 'DEV-E', 'Offline', 'root', NULL),
|
||
('中心设备', 'CENTER-DEV', 'normal', 'root', NULL),
|
||
('设备F', 'DEV-F', 'normal', 'root', NULL)
|
||
ON CONFLICT(device_code) DO NOTHING;
|
||
|
||
-- ----------------------------
|
||
-- 插入温湿度数据（设备A示例，包含ph和光照）
|
||
-- ----------------------------
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity) VALUES
|
||
(1, 22.0, 60, '2025-05-26 00:00:00', 6.5, 2000),
|
||
(1, 21.8, 59, '2025-05-26 01:00:00', 6.4, 1900),
|
||
(1, 21.5, 58, '2025-05-26 02:00:00', 6.3, 1800),
|
||
(1, 21.2, 57, '2025-05-26 03:00:00', 6.2, 1700),
|
||
(1, 21.0, 56, '2025-05-26 04:00:00', 6.1, 1600),
|
||
(1, 20.8, 55, '2025-05-26 05:00:00', 6.0, 1500),
|
||
(1, 21.2, 56, '2025-05-26 06:00:00', 6.2, 1800),
|
||
(1, 22.5, 58, '2025-05-26 07:00:00', 6.5, 2200),
|
||
(1, 24.0, 60, '2025-05-26 08:00:00', 6.8, 2500),
|
||
(1, 25.5, 62, '2025-05-26 09:00:00', 7.0, 2800),
|
||
(1, 26.8, 63, '2025-05-26 10:00:00', 7.2, 3000),
|
||
(1, 27.5, 62, '2025-05-26 11:00:00', 7.1, 3200),
|
||
(1, 28.0, 60, '2025-05-26 12:00:00', 7.0, 3500),
|
||
(1, 27.8, 58, '2025-05-26 13:00:00', 6.9, 3300),
|
||
(1, 27.2, 56, '2025-05-26 14:00:00', 6.8, 3000),
|
||
(1, 26.5, 55, '2025-05-26 15:00:00', 6.7, 2800),
|
||
(1, 25.5, 56, '2025-05-26 16:00:00', 6.6, 2500),
|
||
(1, 24.5, 58, '2025-05-26 17:00:00', 6.5, 2200),
|
||
(1, 23.5, 60, '2025-05-26 18:00:00', 6.4, 2000),
|
||
(1, 22.8, 61, '2025-05-26 19:00:00', 6.3, 1800),
|
||
(1, 22.0, 60, '2025-05-26 20:00:00', 6.2, 1700),
|
||
(1, 21.5, 59, '2025-05-26 21:00:00', 6.1, 1600),
|
||
(1, 21.2, 58, '2025-05-26 22:00:00', 6.0, 1500),
|
||
(1, 21.0, 57, '2025-05-26 23:00:00', 6.0, 1400),
|
||
(1, 21.2, 58, '2025-05-27 00:00:00', 6.2, 1800),
|
||
(1, 21.5, 59, '2025-05-27 01:00:00', 6.3, 1900),
|
||
(1, 21.8, 60, '2025-05-27 02:00:00', 6.4, 2000),
|
||
(1, 22.0, 61, '2025-05-27 03:00:00', 6.5, 2100),
|
||
(1, 22.5, 62, '2025-05-27 04:00:00', 6.6, 2200),
|
||
(1, 23.0, 63, '2025-05-27 05:00:00', 6.7, 2300),
|
||
(1, 24.0, 62, '2025-05-27 06:00:00', 6.8, 2500),
|
||
(1, 25.5, 60, '2025-05-27 07:00:00', 6.9, 2800),
|
||
(1, 27.0, 58, '2025-05-27 08:00:00', 7.0, 3000),
|
||
(1, 28.5, 55, '2025-05-27 09:00:00', 7.1, 3200),
|
||
(1, 30.0, 52, '2025-05-27 10:00:00', 7.2, 3500),
|
||
(1, 31.0, 50, '2025-05-27 11:00:00', 7.1, 3300),
|
||
(1, 31.5, 48, '2025-05-27 12:00:00', 7.0, 3000),
|
||
(1, 31.0, 47, '2025-05-27 13:00:00', 6.9, 2800),
|
||
(1, 30.0, 48, '2025-05-27 14:00:00', 6.8, 2500),
|
||
(1, 29.0, 50, '2025-05-27 15:00:00', 6.7, 2200),
|
||
(1, 28.0, 52, '2025-05-27 16:00:00', 6.6, 2000),
|
||
(1, 26.5, 55, '2025-05-27 17:00:00', 6.5, 1800),
|
||
(1, 25.0, 58, '2025-05-27 18:00:00', 6.4, 1600),
|
||
(1, 23.5, 60, '2025-05-27 19:00:00', 6.3, 1500),
|
||
(1, 22.0, 61, '2025-05-27 20:00:00', 6.2, 1400),
|
||
(1, 21.5, 60, '2025-05-27 21:00:00', 6.1, 1300),
|
||
(1, 21.2, 59, '2025-05-27 22:00:00', 6.0, 1200),
|
||
(1, 21.0, 58, '2025-05-27 23:00:00', 6.0, 1100);
|
||
|
||
-- 设备B（ID=2）警告设备（第二日15时高温异常）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity) VALUES
|
||
(2, 26.5, 55, '2025-05-26 15:00:00', 6.8, 2500),
|
||
(2, 37.0, 55, '2025-05-27 15:00:00', 5.5, 3000),
|
||
(2, 28.0, 58, '2025-05-27 16:00:00', 6.5, 2800);
|
||
|
||
-- 设备C（ID=3）故障设备（第二日15时低温+低湿异常）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity) VALUES
|
||
(3, 26.5, 55, '2025-05-26 15:00:00', 6.7, 2500),
|
||
(3, 16.0, 28, '2025-05-27 15:00:00', 5.0, 2000),
|
||
(3, 22.0, 58, '2025-05-27 16:00:00', 6.5, 2500);
|
||
|
||
-- 设备D（ID=4）复制设备A的数据（包含ph和光照）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT 4, temperature, humidity, timestamp, ph, light_intensity
|
||
FROM temperature_data
|
||
WHERE device_id = 1;
|
||
|
||
-- 中心设备（ID=6）正常数据（部分）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity) VALUES
|
||
(6, 24.0, 54, '2025-05-26 16:00:00', 6.5, 2000),
|
||
(6, 24.2, 54, '2025-05-26 17:00:00', 6.5, 2100),
|
||
(6, 23.8, 55, '2025-05-26 18:00:00', 6.4, 1900),
|
||
(6, 23.5, 55, '2025-05-26 19:00:00', 6.4, 1800),
|
||
(6, 23.2, 55, '2025-05-26 20:00:00', 6.3, 1700),
|
||
(6, 23.0, 55, '2025-05-26 21:00:00', 6.3, 1600),
|
||
(6, 22.8, 55, '2025-05-26 22:00:00', 6.2, 1500),
|
||
(6, 22.5, 55, '2025-05-26 23:00:00', 6.2, 1400),
|
||
(6, 22.8, 55, '2025-05-27 00:00:00', 6.3, 1500),
|
||
(6, 23.0, 55, '2025-05-27 01:00:00', 6.3, 1600),
|
||
(6, 23.2, 55, '2025-05-27 02:00:00', 6.4, 1700),
|
||
(6, 23.5, 55, '2025-05-27 03:00:00', 6.4, 1800),
|
||
(6, 23.8, 55, '2025-05-27 04:00:00', 6.5, 1900),
|
||
(6, 24.0, 54, '2025-05-27 05:00:00', 6.5, 2000),
|
||
(6, 24.2, 54, '2025-05-27 06:00:00', 6.6, 2100),
|
||
(6, 24.5, 54, '2025-05-27 07:00:00', 6.6, 2200),
|
||
(6, 25.0, 53, '2025-05-27 08:00:00', 6.7, 2300),
|
||
(6, 25.5, 53, '2025-05-27 09:00:00', 6.8, 2400),
|
||
(6, 26.0, 53, '2025-05-27 10:00:00', 6.8, 2500),
|
||
(6, 26.5, 52, '2025-05-27 11:00:00', 6.9, 2600),
|
||
(6, 27.0, 52, '2025-05-27 12:00:00', 7.0, 2700),
|
||
(6, 26.8, 52, '2025-05-27 13:00:00', 6.9, 2600),
|
||
(6, 26.5, 52, '2025-05-27 14:00:00', 6.9, 2500),
|
||
(6, 26.0, 52, '2025-05-27 15:00:00', 6.8, 2400),
|
||
(6, 25.5, 53, '2025-05-27 16:00:00', 6.7, 2300),
|
||
(6, 25.0, 53, '2025-05-27 17:00:00', 6.7, 2200),
|
||
(6, 24.5, 54, '2025-05-27 18:00:00', 6.6, 2100),
|
||
(6, 24.2, 54, '2025-05-27 19:00:00', 6.6, 2000),
|
||
(6, 24.0, 54, '2025-05-27 20:00:00', 6.5, 1900),
|
||
(6, 23.8, 54, '2025-05-27 21:00:00', 6.5, 1800),
|
||
(6, 23.5, 55, '2025-05-27 22:00:00', 6.4, 1700),
|
||
(6, 23.2, 55, '2025-05-27 23:00:00', 6.4, 1600);
|
||
|
||
-- 设备F（ID=7）正常数据（部分）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity) VALUES
|
||
(7, 22.0, 60, '2025-05-26 00:00:00', 6.5, 2000),
|
||
(7, 21.8, 59, '2025-05-26 01:00:00', 6.4, 1900),
|
||
(7, 21.5, 58, '2025-05-26 02:00:00', 6.3, 1800),
|
||
(7, 21.2, 57, '2025-05-26 03:00:00', 6.2, 1700),
|
||
(7, 21.0, 56, '2025-05-26 04:00:00', 6.1, 1600),
|
||
(7, 20.8, 55, '2025-05-26 05:00:00', 6.0, 1500),
|
||
(7, 21.2, 56, '2025-05-26 06:00:00', 6.2, 1800),
|
||
(7, 22.5, 58, '2025-05-26 07:00:00', 6.5, 2200),
|
||
(7, 24.0, 60, '2025-05-26 08:00:00', 6.8, 2500),
|
||
(7, 25.5, 62, '2025-05-26 09:00:00', 7.0, 2800),
|
||
(7, 26.8, 63, '2025-05-26 10:00:00', 7.2, 3000),
|
||
(7, 27.5, 62, '2025-05-26 11:00:00', 7.1, 3200),
|
||
(7, 28.0, 60, '2025-05-26 12:00:00', 7.0, 3500),
|
||
(7, 27.8, 58, '2025-05-26 13:00:00', 6.9, 3300),
|
||
(7, 27.2, 56, '2025-05-26 14:00:00', 6.8, 3000),
|
||
(7, 26.5, 55, '2025-05-26 15:00:00', 6.7, 2800),
|
||
(7, 25.5, 56, '2025-05-26 16:00:00', 6.6, 2500),
|
||
(7, 24.5, 58, '2025-05-26 17:00:00', 6.5, 2200),
|
||
(7, 23.5, 60, '2025-05-26 18:00:00', 6.4, 2000),
|
||
(7, 22.8, 61, '2025-05-26 19:00:00', 6.3, 1800),
|
||
(7, 22.0, 60, '2025-05-26 20:00:00', 6.2, 1700),
|
||
(7, 21.5, 59, '2025-05-26 21:00:00', 6.1, 1600),
|
||
(7, 21.2, 58, '2025-05-26 22:00:00', 6.0, 1500),
|
||
(7, 21.0, 57, '2025-05-26 23:00:00', 6.0, 1400),
|
||
(7, 21.2, 58, '2025-05-27 00:00:00', 6.2, 1800),
|
||
(7, 21.5, 59, '2025-05-27 01:00:00', 6.3, 1900),
|
||
(7, 21.8, 60, '2025-05-27 02:00:00', 6.4, 2000),
|
||
(7, 22.0, 61, '2025-05-27 03:00:00', 6.5, 2100),
|
||
(7, 22.5, 62, '2025-05-27 04:00:00', 6.6, 2200),
|
||
(7, 23.0, 63, '2025-05-27 05:00:00', 6.7, 2300),
|
||
(7, 24.0, 62, '2025-05-27 06:00:00', 6.8, 2500),
|
||
(7, 25.5, 60, '2025-05-27 07:00:00', 6.9, 2800),
|
||
(7, 27.0, 58, '2025-05-27 08:00:00', 7.0, 3000),
|
||
(7, 28.5, 55, '2025-05-27 09:00:00', 7.1, 3200),
|
||
(7, 30.0, 52, '2025-05-27 10:00:00', 7.2, 3500),
|
||
(7, 31.0, 50, '2025-05-27 11:00:00', 7.1, 3300),
|
||
(7, 31.5, 48, '2025-05-27 12:00:00', 7.0, 3000),
|
||
(7, 31.0, 47, '2025-05-27 13:00:00', 6.9, 2800),
|
||
(7, 30.0, 48, '2025-05-27 14:00:00', 6.8, 2500),
|
||
(7, 29.0, 50, '2025-05-27 15:00:00', 6.7, 2200),
|
||
(7, 28.0, 52, '2025-05-27 16:00:00', 6.6, 2000),
|
||
(7, 26.5, 55, '2025-05-27 17:00:00', 6.5, 1800),
|
||
(7, 25.0, 58, '2025-05-27 18:00:00', 6.4, 1600),
|
||
(7, 23.5, 60, '2025-05-27 19:00:00', 6.3, 1500),
|
||
(7, 22.0, 61, '2025-05-27 20:00:00', 6.2, 1400),
|
||
(7, 21.5, 60, '2025-05-27 21:00:00', 6.1, 1300),
|
||
(7, 21.2, 59, '2025-05-27 22:00:00', 6.0, 1200),
|
||
(7, 21.0, 58, '2025-05-27 23:00:00', 6.0, 1100);
|
||
|
||
-- ----------------------------
|
||
-- 索引优化
|
||
-- ----------------------------
|
||
CREATE INDEX IF NOT EXISTS idx_user_permission ON user(permission_level);
|
||
CREATE INDEX IF NOT EXISTS idx_user_status ON user(status);
|
||
CREATE INDEX IF NOT EXISTS idx_user_department ON user(department_id);
|
||
CREATE INDEX IF NOT EXISTS idx_user_hire_date ON user(hire_date DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_user_device_user ON user_device(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_user_device_device ON user_device(device_id);
|
||
CREATE INDEX IF NOT EXISTS idx_device_status ON device(status);
|
||
|
||
-- ----------------------------
|
||
-- 设备B、C除特殊时刻外复制设备A的数据
|
||
-- ----------------------------
|
||
-- 删除设备B的非特殊时刻数据
|
||
DELETE FROM temperature_data
|
||
WHERE device_id = 2
|
||
AND timestamp NOT IN ('2025-05-26 15:00:00', '2025-05-27 15:00:00', '2025-05-27 16:00:00');
|
||
|
||
-- 删除设备C的非特殊时刻数据
|
||
DELETE FROM temperature_data
|
||
WHERE device_id = 3
|
||
AND timestamp NOT IN ('2025-05-26 15:00:00', '2025-05-27 15:00:00', '2025-05-27 16:00:00');
|
||
|
||
-- 设备B复制设备A的数据（非特殊时刻）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT 2, temperature, humidity, timestamp, ph, light_intensity
|
||
FROM temperature_data
|
||
WHERE device_id = 1
|
||
AND timestamp NOT IN ('2025-05-26 15:00:00', '2025-05-27 15:00:00', '2025-05-27 16:00:00');
|
||
|
||
-- 设备C复制设备A的数据（非特殊时刻）
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT 3, temperature, humidity, timestamp, ph, light_intensity
|
||
FROM temperature_data
|
||
WHERE device_id = 1
|
||
AND timestamp NOT IN ('2025-05-26 15:00:00', '2025-05-27 15:00:00', '2025-05-27 16:00:00');
|
||
|
||
CREATE TABLE IF NOT EXISTS notification_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
fault_id INTEGER NOT NULL,
|
||
recipient TEXT NOT NULL,
|
||
content TEXT NOT NULL,
|
||
send_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
status TEXT DEFAULT 'success', -- success/failure
|
||
FOREIGN KEY (fault_id) REFERENCES device (id)
|
||
);
|
||
|
||
-- ----------------------------
|
||
-- 添加历史数据（前2天和后2天，每小时一个数据点）
|
||
-- ----------------------------
|
||
|
||
-- 设备A（ID=1）添加前2天和后2天数据
|
||
WITH hour_sequence AS (
|
||
-- 生成24小时序列（0-23点）
|
||
SELECT 0 AS hour UNION ALL
|
||
SELECT hour + 1 FROM hour_sequence WHERE hour < 23
|
||
), date_range AS (
|
||
-- 生成日期范围：前2天、前1天、今日、后1天、后2天
|
||
SELECT DATE('2025-05-27', '-2 days') AS date UNION ALL
|
||
SELECT DATE('2025-05-27', '-1 days') UNION ALL
|
||
SELECT DATE('2025-05-27') UNION ALL
|
||
SELECT DATE('2025-05-27', '+1 days') UNION ALL
|
||
SELECT DATE('2025-05-27', '+2 days')
|
||
)
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT
|
||
1,
|
||
25.0 + (h.hour - 12) * 0.1, -- 温度：25℃基础值，每小时波动±1.2℃
|
||
60.0 + (h.hour - 12) * 0.2, -- 湿度：60%基础值，每小时波动±2.4%
|
||
datetime(d.date, '00:00:00') + (h.hour || ':00:00') AS timestamp,
|
||
6.8 + 0.05 * (h.hour - 12), -- PH值：6.8基础值，范围6.0-7.6
|
||
500 + (h.hour - 12) * 50 -- 光照强度：500基础值，每小时波动±600
|
||
FROM date_range d
|
||
CROSS JOIN hour_sequence h
|
||
WHERE d.date BETWEEN DATE('2025-05-27', '-2 days') AND DATE('2025-05-27', '+2 days');
|
||
|
||
|
||
-- 设备B（ID=2）添加前2天和后2天数据（基础值与波动幅度调整）
|
||
WITH hour_sequence AS (
|
||
SELECT 0 AS hour UNION ALL
|
||
SELECT hour + 1 FROM hour_sequence WHERE hour < 23
|
||
), date_range AS (
|
||
SELECT DATE('2025-05-27', '-2 days') AS date UNION ALL
|
||
SELECT DATE('2025-05-27', '-1 days') UNION ALL
|
||
SELECT DATE('2025-05-27') UNION ALL
|
||
SELECT DATE('2025-05-27', '+1 days') UNION ALL
|
||
SELECT DATE('2025-05-27', '+2 days')
|
||
)
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT
|
||
2,
|
||
26.5 + (h.hour - 12) * 0.15, -- 温度：26.5℃基础值
|
||
55.0 + (h.hour - 12) * 0.25, -- 湿度：55%基础值
|
||
datetime(d.date, '00:00:00') + (h.hour || ':00:00') AS timestamp,
|
||
7.2 + 0.04 * (h.hour - 12), -- PH值：7.2基础值
|
||
400 + (h.hour - 12) * 60 -- 光照强度：400基础值
|
||
FROM date_range d
|
||
CROSS JOIN hour_sequence h
|
||
WHERE d.date BETWEEN DATE('2025-05-27', '-2 days') AND DATE('2025-05-27', '+2 days');
|
||
|
||
|
||
-- 设备C（ID=3）添加前2天和后2天数据（不同设备特性）
|
||
WITH hour_sequence AS (
|
||
SELECT 0 AS hour UNION ALL
|
||
SELECT hour + 1 FROM hour_sequence WHERE hour < 23
|
||
), date_range AS (
|
||
SELECT DATE('2025-05-27', '-2 days') AS date UNION ALL
|
||
SELECT DATE('2025-05-27', '-1 days') UNION ALL
|
||
SELECT DATE('2025-05-27') UNION ALL
|
||
SELECT DATE('2025-05-27', '+1 days') UNION ALL
|
||
SELECT DATE('2025-05-27', '+2 days')
|
||
)
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT
|
||
3,
|
||
24.0 + (h.hour - 12) * 0.08, -- 温度：24℃基础值
|
||
65.0 + (h.hour - 12) * 0.18, -- 湿度：65%基础值
|
||
datetime(d.date, '00:00:00') + (h.hour || ':00:00') AS timestamp,
|
||
6.5 + 0.06 * (h.hour - 12), -- PH值：6.5基础值
|
||
600 + (h.hour - 12) * 40 -- 光照强度：600基础值
|
||
FROM date_range d
|
||
CROSS JOIN hour_sequence h
|
||
WHERE d.date BETWEEN DATE('2025-05-27', '-2 days') AND DATE('2025-05-27', '+2 days');
|
||
|
||
|
||
-- 设备D（ID=4）添加前2天和后2天数据
|
||
WITH hour_sequence AS (
|
||
SELECT 0 AS hour UNION ALL
|
||
SELECT hour + 1 FROM hour_sequence WHERE hour < 23
|
||
), date_range AS (
|
||
SELECT DATE('2025-05-27', '-2 days') AS date UNION ALL
|
||
SELECT DATE('2025-05-27', '-1 days') UNION ALL
|
||
SELECT DATE('2025-05-27') UNION ALL
|
||
SELECT DATE('2025-05-27', '+1 days') UNION ALL
|
||
SELECT DATE('2025-05-27', '+2 days')
|
||
)
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT
|
||
4,
|
||
27.0 + (h.hour - 12) * 0.12, -- 温度：27℃基础值
|
||
58.0 + (h.hour - 12) * 0.22, -- 湿度：58%基础值
|
||
datetime(d.date, '00:00:00') + (h.hour || ':00:00') AS timestamp,
|
||
7.0 + 0.05 * (h.hour - 12), -- PH值：7.0基础值
|
||
450 + (h.hour - 12) * 55 -- 光照强度：450基础值
|
||
FROM date_range d
|
||
CROSS JOIN hour_sequence h
|
||
WHERE d.date BETWEEN DATE('2025-05-27', '-2 days') AND DATE('2025-05-27', '+2 days');
|
||
|
||
|
||
-- 中心设备（ID=6）添加前2天和后2天数据
|
||
WITH hour_sequence AS (
|
||
SELECT 0 AS hour UNION ALL
|
||
SELECT hour + 1 FROM hour_sequence WHERE hour < 23
|
||
), date_range AS (
|
||
SELECT DATE('2025-05-27', '-2 days') AS date UNION ALL
|
||
SELECT DATE('2025-05-27', '-1 days') UNION ALL
|
||
SELECT DATE('2025-05-27') UNION ALL
|
||
SELECT DATE('2025-05-27', '+1 days') UNION ALL
|
||
SELECT DATE('2025-05-27', '+2 days')
|
||
)
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT
|
||
6,
|
||
25.5 + (h.hour - 12) * 0.09, -- 温度：25.5℃基础值（中心设备）
|
||
62.0 + (h.hour - 12) * 0.19, -- 湿度：62%基础值
|
||
datetime(d.date, '00:00:00') + (h.hour || ':00:00') AS timestamp,
|
||
6.9 + 0.05 * (h.hour - 12), -- PH值：6.9基础值
|
||
550 + (h.hour - 12) * 45 -- 光照强度：550基础值
|
||
FROM date_range d
|
||
CROSS JOIN hour_sequence h
|
||
WHERE d.date BETWEEN DATE('2025-05-27', '-2 days') AND DATE('2025-05-27', '+2 days');
|
||
|
||
|
||
-- 设备F（ID=7）添加前2天和后2天数据
|
||
WITH hour_sequence AS (
|
||
SELECT 0 AS hour UNION ALL
|
||
SELECT hour + 1 FROM hour_sequence WHERE hour < 23
|
||
), date_range AS (
|
||
SELECT DATE('2025-05-27', '-2 days') AS date UNION ALL
|
||
SELECT DATE('2025-05-27', '-1 days') UNION ALL
|
||
SELECT DATE('2025-05-27') UNION ALL
|
||
SELECT DATE('2025-05-27', '+1 days') UNION ALL
|
||
SELECT DATE('2025-05-27', '+2 days')
|
||
)
|
||
INSERT INTO temperature_data (device_id, temperature, humidity, timestamp, ph, light_intensity)
|
||
SELECT
|
||
7,
|
||
23.5 + (h.hour - 12) * 0.14, -- 温度：23.5℃基础值
|
||
68.0 + (h.hour - 12) * 0.24, -- 湿度：68%基础值
|
||
datetime(d.date, '00:00:00') + (h.hour || ':00:00') AS timestamp,
|
||
6.7 + 0.07 * (h.hour - 12), -- PH值：6.7基础值
|
||
350 + (h.hour - 12) * 65 -- 光照强度：350基础值
|
||
FROM date_range d
|
||
CROSS JOIN hour_sequence h
|
||
WHERE d.date BETWEEN DATE('2025-05-27', '-2 days') AND DATE('2025-05-27', '+2 days');
|
||
|
||
-- ----------------------------
|
||
-- 操作日志数据
|
||
-- ----------------------------
|
||
-- 添加用户创建日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'USER_CREATE', '创建用户 admin'),
|
||
(1, 'USER_CREATE', '创建用户 supervisor'),
|
||
(1, 'USER_CREATE', '创建用户 operator1'),
|
||
(1, 'USER_CREATE', '创建用户 operator2');
|
||
|
||
-- 添加用户更新日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'USER_UPDATE', '更新用户 admin 的联系方式'),
|
||
(2, 'USER_UPDATE', '更新用户 supervisor 的权限级别'),
|
||
(3, 'USER_UPDATE', '更新用户 operator1 的状态为 Inactive');
|
||
|
||
-- 添加用户删除日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'USER_DELETE', '删除用户 test_user'),
|
||
(2, 'USER_DELETE', '删除用户 temp_user');
|
||
|
||
-- 添加设备操作日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'DEVICE_MANAGE', '添加新设备 环境监测仪A'),
|
||
(2, 'DEVICE_MANAGE', '修改设备 环境监测仪B 的状态为警告'),
|
||
(3, 'DEVICE_MANAGE', '删除设备 环境监测仪C');
|
||
|
||
-- 添加数据查看日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'DATA_VIEW', '查看设备A的温湿度数据'),
|
||
(2, 'DATA_VIEW', '查看设备B的温湿度数据'),
|
||
(3, 'DATA_VIEW', '查看所有设备的故障记录'),
|
||
(4, 'DATA_VIEW', '导出2025年5月的温湿度数据报表');
|
||
|
||
-- 添加权限变更日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'PERMISSION_CHANGE', '将用户 supervisor 的权限提升为管理员'),
|
||
(1, 'PERMISSION_CHANGE', '将用户 operator1 的权限调整为主管'),
|
||
(2, 'PERMISSION_CHANGE', '将用户 operator2 的权限调整为操作员');
|
||
|
||
-- 添加系统操作日志
|
||
INSERT INTO operation_log (user_id, type, message) VALUES
|
||
(1, 'SYSTEM_OPERATION', '系统备份完成'),
|
||
(1, 'SYSTEM_OPERATION', '数据库优化执行完毕'),
|
||
(2, 'SYSTEM_OPERATION', '服务器状态检查完成'),
|
||
(3, 'SYSTEM_OPERATION', '数据同步任务启动');
|
||
|
||
-- ----------------------------
|
||
-- 湿度记录表
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS soil_moisture;
|
||
|
||
CREATE TABLE soil_moisture (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
record_date TEXT NOT NULL UNIQUE,
|
||
moisture REAL NOT NULL
|
||
);
|
||
|
||
INSERT INTO soil_moisture (record_date, moisture) VALUES
|
||
('6.10', 55.0),
|
||
('6.11', 60.5),
|
||
('6.12', 80.0),
|
||
('6.13', 75.5),
|
||
('6.14', 65.0),
|
||
('6.15', 50.5),
|
||
('6.16', 90.0);
|
||
|
||
|
||
-- 删除已存在的表（如果需要）
|
||
DROP TABLE IF EXISTS sensor_data;
|
||
|
||
-- 创建新表，包含 temperature, humidity, pH 和 status 字段
|
||
CREATE TABLE sensor_data (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
record_time TEXT NOT NULL UNIQUE, -- 时间戳作为唯一记录标识
|
||
temperature REAL NOT NULL, -- 温度
|
||
humidity REAL NOT NULL, -- 湿度
|
||
ph REAL NOT NULL, -- pH 值
|
||
status TEXT NOT NULL -- 状态（normal/warning/abnormal 等）
|
||
);
|
||
|
||
-- 插入数据
|
||
INSERT INTO sensor_data (record_time, temperature, humidity, ph, status) VALUES
|
||
('2025-06-02 00:00:00', 18.1, 94.0, 6.6, 'normal'),
|
||
('2025-06-02 00:20:00', 18.7, 92.9, 6.7, 'normal'),
|
||
('2025-06-02 00:40:00', 19.3, 91.1, 6.8, 'normal'),
|
||
('2025-06-02 01:00:00', 18.6, 84.1, 6.7, 'normal'),
|
||
('2025-06-02 01:20:00', 18.6, 89.9, 6.8, 'normal'),
|
||
('2025-06-02 01:40:00', 19.7, 88.3, 6.7, 'normal'),
|
||
('2025-06-02 02:00:00', 18.5, 87.4, 6.6, 'normal'),
|
||
('2025-06-02 02:20:00', 20.8, 87.6, 6.7, 'normal'),
|
||
('2025-06-02 02:40:00', 19.2, 88.4, 6.7, 'normal'),
|
||
('2025-06-02 03:00:00', 19.5, 84.5, 6.7, 'normal'),
|
||
('2025-06-02 03:20:00', 20.7, 86.0, 6.6, 'normal'),
|
||
('2025-06-02 03:40:00', 19.4, 90.4, 6.7, 'normal'),
|
||
('2025-06-02 04:00:00', 21.6, 84.1, 6.7, 'normal'),
|
||
('2025-06-02 04:20:00', 21.4, 82.9, 6.6, 'normal'),
|
||
('2025-06-02 04:40:00', 22.6, 82.3, 6.6, 'normal'),
|
||
('2025-06-02 05:00:00', 22.7, 84.6, 6.8, 'normal'),
|
||
('2025-06-02 05:20:00', 23.9, 79.4, 6.8, 'normal'),
|
||
('2025-06-02 05:40:00', 22.9, 82.6, 6.6, 'normal'),
|
||
('2025-06-02 06:00:00', 24.7, 79.3, 6.7, 'normal'),
|
||
('2025-06-02 06:20:00', 24.5, 78.6, 6.6, 'normal'),
|
||
('2025-06-02 06:40:00', 24.5, 80.5, 6.7, 'normal'),
|
||
('2025-06-02 07:00:00', 25.5, 81.8, 6.6, 'normal'),
|
||
('2025-06-02 07:20:00', 25.2, 76.7, 6.7, 'normal'),
|
||
('2025-06-02 07:40:00', 25.7, 76.5, 6.8, 'normal'),
|
||
('2025-06-02 08:00:00', 26.1, 71.2, 6.8, 'normal'),
|
||
('2025-06-02 08:20:00', 27.1, 73.5, 6.7, 'normal'),
|
||
('2025-06-02 08:40:00', 27.2, 76.5, 6.6, 'normal'),
|
||
('2025-06-02 09:00:00', 25.9, 75.5, 6.5, 'normal'),
|
||
('2025-06-02 09:20:00', 29.0, 72.0, 6.8, 'normal'),
|
||
('2025-06-02 09:40:00', 29.5, 73.4, 6.7, 'normal'),
|
||
('2025-06-02 10:00:00', 27.9, 75.2, 6.8, 'normal'),
|
||
('2025-06-02 10:20:00', 29.3, 72.3, 6.7, 'normal'),
|
||
('2025-06-02 10:40:00', 29.2, 73.2, 6.6, 'normal'),
|
||
('2025-06-02 11:00:00', 28.6, 69.7, 6.7, 'normal'),
|
||
('2025-06-02 11:20:00', 28.8, 73.3, 6.7, 'normal'),
|
||
('2025-06-02 11:40:00', 29.9, 71.4, 6.6, 'normal'),
|
||
('2025-06-02 12:00:00', 27.8, 70.0, 6.6, 'normal'),
|
||
('2025-06-02 12:20:00', 29.7, 70.6, 6.9, 'normal'),
|
||
('2025-06-02 12:40:00', 30.0, 74.5, 6.7, 'normal'),
|
||
('2025-06-02 13:00:00', 28.4, 68.8, 6.7, 'normal'),
|
||
('2025-06-02 13:20:00', 35.5, 73.6, 6.8, 'high_temp'),
|
||
('2025-06-02 13:40:00', 29.2, 74.2, 6.5, 'normal'),
|
||
('2025-06-02 14:00:00', 29.3, 70.7, 6.7, 'normal'),
|
||
('2025-06-02 14:20:00', 28.1, 77.2, 6.5, 'normal'),
|
||
('2025-06-02 14:40:00', 27.1, 76.7, 6.9, 'normal'),
|
||
('2025-06-02 15:00:00', 27.3, 70.7, 6.6, 'normal'),
|
||
('2025-06-02 15:20:00', 25.5, 72.1, 6.5, 'normal'),
|
||
('2025-06-02 15:40:00', 26.3, 74.3, 6.5, 'normal'),
|
||
('2025-06-02 16:00:00', 27.1, 74.3, 6.7, 'normal'),
|
||
('2025-06-02 16:20:00', 25.5, 77.3, 6.7, 'normal'),
|
||
('2025-06-02 16:40:00', 26.1, 78.2, 6.6, 'normal'),
|
||
('2025-06-02 17:00:00', 27.0, 77.7, 6.6, 'normal'),
|
||
('2025-06-02 17:20:00', 24.5, 82.8, 6.9, 'normal'),
|
||
('2025-06-02 17:40:00', 24.8, 80.3, 6.7, 'normal'),
|
||
('2025-06-02 18:00:00', 22.8, 85.1, 6.9, 'normal'),
|
||
('2025-06-02 18:20:00', 24.4, 80.7, 6.5, 'normal'),
|
||
('2025-06-02 18:40:00', 23.9, 84.9, 6.7, 'normal'),
|
||
('2025-06-02 19:00:00', 22.2, 90.9, 6.8, 'normal'),
|
||
('2025-06-02 19:20:00', 21.6, 81.7, 6.8, 'normal'),
|
||
('2025-06-02 19:40:00', 21.6, 85.8, 6.6, 'normal'),
|
||
('2025-06-02 20:00:00', 20.6, 86.8, 6.8, 'normal'),
|
||
('2025-06-02 20:20:00', 21.6, 82.7, 6.6, 'normal'),
|
||
('2025-06-02 20:40:00', 21.7, 87.4, 6.8, 'normal'),
|
||
('2025-06-02 21:00:00', 20.6, 91.5, 6.7, 'normal'),
|
||
('2025-06-02 21:20:00', 18.8, 85.5, 6.7, 'normal'),
|
||
('2025-06-02 21:40:00', 21.2, 88.2, 6.6, 'normal'),
|
||
('2025-06-02 22:00:00', 20.1, 90.8, 6.7, 'normal'),
|
||
('2025-06-02 22:20:00', 19.7, 88.7, 6.8, 'normal'),
|
||
('2025-06-02 22:40:00', 18.1, 86.2, 6.8, 'normal'),
|
||
('2025-06-02 23:00:00', 19.1, 89.3, 6.7, 'normal'),
|
||
('2025-06-02 23:20:00', 19.2, 87.3, 6.6, 'normal'),
|
||
('2025-06-02 23:40:00', 19.2, 84.0, 6.8, 'normal'),
|
||
('2025-06-03 00:00:00', 19.6, 88.9, 6.9, 'normal'),
|
||
('2025-06-03 00:20:00', 18.5, 88.4, 6.7, 'normal'),
|
||
('2025-06-03 00:40:00', 18.8, 85.9, 6.4, 'normal'),
|
||
('2025-06-03 01:00:00', 18.7, 92.6, 6.5, 'normal'),
|
||
('2025-06-03 01:20:00', 19.3, 90.2, 6.5, 'normal'),
|
||
('2025-06-03 01:40:00', 18.1, 93.9, 6.6, 'normal'),
|
||
('2025-06-03 02:00:00', 20.6, 90.8, 6.5, 'normal'),
|
||
('2025-06-03 02:20:00', 20.4, 92.1, 6.7, 'normal'),
|
||
('2025-06-03 02:40:00', 20.9, 88.9, 6.5, 'normal'),
|
||
('2025-06-03 03:00:00', 20.5, 86.7, 6.7, 'normal'),
|
||
('2025-06-03 03:20:00', 21.1, 83.9, 6.5, 'normal'),
|
||
('2025-06-03 03:40:00', 20.9, 85.6, 6.6, 'normal'),
|
||
('2025-06-03 04:00:00', 22.9, 85.1, 6.5, 'normal'),
|
||
('2025-06-03 04:20:00', 21.0, 86.8, 6.4, 'normal'),
|
||
('2025-06-03 04:40:00', 21.3, 85.2, 6.6, 'normal'),
|
||
('2025-06-03 05:00:00', 22.7, 85.9, 6.5, 'normal'),
|
||
('2025-06-03 05:20:00', 23.4, 82.9, 6.5, 'normal'),
|
||
('2025-06-03 05:40:00', 25.6, 82.9, 6.4, 'normal'),
|
||
('2025-06-03 06:00:00', 25.0, 80.0, 6.6, 'normal'),
|
||
('2025-06-03 06:20:00', 25.0, 79.1, 6.6, 'normal'),
|
||
('2025-06-03 06:40:00', 24.3, 76.9, 6.5, 'normal'),
|
||
('2025-06-03 07:00:00', 24.6, 77.6, 6.5, 'normal'),
|
||
('2025-06-03 07:20:00', 25.1, 79.7, 6.5, 'normal'),
|
||
('2025-06-03 07:40:00', 26.9, 72.3, 6.6, 'normal'),
|
||
('2025-06-03 08:00:00', 27.4, 80.2, 6.4, 'normal'),
|
||
('2025-06-03 08:20:00', 27.7, 73.3, 6.6, 'normal'),
|
||
('2025-06-03 08:40:00', 26.6, 71.3, 6.5, 'normal'),
|
||
('2025-06-03 09:00:00', 26.5, 70.5, 6.6, 'normal'),
|
||
('2025-06-03 09:20:00', 28.0, 72.0, 6.6, 'normal'),
|
||
('2025-06-03 09:40:00', 28.6, 77.2, 6.5, 'normal'),
|
||
('2025-06-03 10:00:00', 28.6, 73.7, 6.5, 'normal'),
|
||
('2025-06-03 10:20:00', 29.3, 68.1, 6.5, 'normal'),
|
||
('2025-06-03 10:40:00', 29.5, 68.4, 6.5, 'normal'),
|
||
('2025-06-03 11:00:00', 30.0, 70.1, 6.4, 'normal'),
|
||
('2025-06-03 11:20:00', 29.7, 71.1, 6.4, 'normal'),
|
||
('2025-06-03 11:40:00', 27.8, 72.8, 6.5, 'normal'),
|
||
('2025-06-03 12:00:00', 28.6, 77.5, 6.6, 'normal'),
|
||
('2025-06-03 12:20:00', 28.9, 69.1, 6.6, 'normal'),
|
||
('2025-06-03 12:40:00', 30.5, 72.2, 6.6, 'normal'),
|
||
('2025-06-03 13:00:00', 28.4, 71.4, 6.5, 'normal'),
|
||
('2025-06-03 13:20:00', 28.6, 74.5, 6.5, 'normal'),
|
||
('2025-06-03 13:40:00', 28.5, 73.7, 6.5, 'normal'),
|
||
('2025-06-03 14:00:00', 29.0, 70.1, 6.7, 'normal'),
|
||
('2025-06-03 14:20:00', 28.0, 67.9, 6.6, 'normal'),
|
||
('2025-06-03 14:40:00', 28.2, 75.2, 6.6, 'normal'),
|
||
('2025-06-03 15:00:00', 26.6, 70.1, 6.6, 'normal'),
|
||
('2025-06-03 15:20:00', 27.4, 73.8, 6.4, 'normal'),
|
||
('2025-06-03 15:40:00', 27.9, 77.3, 6.4, 'normal'),
|
||
('2025-06-03 16:00:00', 27.1, 72.4, 6.6, 'normal'),
|
||
('2025-06-03 16:20:00', 26.1, 75.9, 6.6, 'normal'),
|
||
('2025-06-03 16:40:00', 26.5, 75.8, 6.6, 'normal'),
|
||
('2025-06-03 17:00:00', 26.1, 85.7, 6.4, 'normal'),
|
||
('2025-06-03 17:20:00', 25.3, 78.8, 6.5, 'normal'),
|
||
('2025-06-03 17:40:00', 25.1, 76.0, 6.3, 'normal'),
|
||
('2025-06-03 18:00:00', 25.0, 79.9, 6.5, 'normal'),
|
||
('2025-06-03 18:20:00', 23.1, 79.3, 6.7, 'normal'),
|
||
('2025-06-03 18:40:00', 22.8, 88.0, 6.6, 'normal'),
|
||
('2025-06-03 19:00:00', 23.4, 78.7, 6.6, 'normal'),
|
||
('2025-06-03 19:20:00', 23.8, 80.1, 6.6, 'normal'),
|
||
('2025-06-03 19:40:00', 21.9, 88.2, 6.5, 'normal'),
|
||
('2025-06-03 20:00:00', 20.8, 85.9, 6.4, 'normal'),
|
||
('2025-06-03 20:20:00', 22.2, 87.2, 6.5, 'normal'),
|
||
('2025-06-03 20:40:00', 20.7, 91.3, 6.5, 'normal'),
|
||
('2025-06-03 21:00:00', 20.3, 92.8, 6.6, 'normal'),
|
||
('2025-06-03 21:20:00', 18.4, 91.3, 6.6, 'normal'),
|
||
('2025-06-03 21:40:00', 19.9, 85.6, 6.4, 'normal'),
|
||
('2025-06-03 22:00:00', 20.3, 88.0, 6.6, 'normal'),
|
||
('2025-06-03 22:20:00', 18.8, 91.4, 6.5, 'normal'),
|
||
('2025-06-03 22:40:00', 20.1, 94.8, 6.5, 'normal'),
|
||
('2025-06-03 23:00:00', 18.5, 87.2, 6.6, 'normal'),
|
||
('2025-06-03 23:20:00', 18.7, 87.5, 6.9, 'normal'),
|
||
('2025-06-03 23:40:00', 18.4, 93.0, 6.8, 'normal'),
|
||
('2025-06-04 00:00:00', 18.5, 88.4, 6.3, 'normal'),
|
||
('2025-06-04 00:20:00', 18.0, 87.5, 6.1, 'normal'),
|
||
('2025-06-04 00:40:00', 19.0, 91.2, 6.2, 'normal'),
|
||
('2025-06-04 01:00:00', 19.0, 90.2, 6.1, 'normal'),
|
||
('2025-06-04 01:20:00', 19.2, 89.2, 6.0, 'normal'),
|
||
('2025-06-04 01:40:00', 19.8, 85.4, 6.1, 'normal'),
|
||
('2025-06-04 02:00:00', 18.5, 85.0, 6.0, 'normal'),
|
||
('2025-06-04 02:20:00', 20.4, 88.6, 6.2, 'normal'),
|
||
('2025-06-04 02:40:00', 20.8, 90.5, 6.1, 'normal'),
|
||
('2025-06-04 03:00:00', 20.5, 85.7, 6.0, 'normal'),
|
||
('2025-06-04 03:20:00', 21.4, 90.7, 5.9, 'normal'),
|
||
('2025-06-04 03:40:00', 21.2, 90.9, 6.0, 'normal'),
|
||
('2025-06-04 04:00:00', 23.1, 87.2, 5.9, 'normal'),
|
||
('2025-06-04 04:20:00', 22.9, 80.6, 6.0, 'normal'),
|
||
('2025-06-04 04:40:00', 23.1, 83.8, 6.4, 'normal'),
|
||
('2025-06-04 05:00:00', 21.8, 88.7, 6.3, 'normal'),
|
||
('2025-06-04 05:20:00', 23.3, 79.1, 6.0, 'normal'),
|
||
('2025-06-04 05:40:00', 24.3, 82.9, 6.1, 'normal'),
|
||
('2025-06-04 06:00:00', 24.9, 77.0,6.5, 'normal'),
|
||
('2025-06-04 06:20:00', 24.6, 80.9, 6.2, 'normal'),
|
||
('2025-06-04 06:40:00', 26.6, 82.1, 6.0, 'normal'),
|
||
('2025-06-04 07:00:00', 25.8, 76.0, 6.1, 'normal'),
|
||
('2025-06-04 07:20:00', 25.4, 74.6, 6.3, 'normal'),
|
||
('2025-06-04 07:40:00', 25.8, 75.3, 6.2, 'normal'),
|
||
('2025-06-04 08:00:00', 27.1, 75.1, 6.0, 'normal'),
|
||
('2025-06-04 08:20:00', 27.7, 74.0, 5.9, 'normal'),
|
||
('2025-06-04 08:40:00', 27.9, 79.7, 6.0, 'normal'),
|
||
('2025-06-04 09:00:00', 27.6, 73.6, 5.9, 'normal'),
|
||
('2025-06-04 09:20:00', 28.9, 72.8, 6.3, 'normal'),
|
||
('2025-06-04 09:40:00', 27.1, 69.1, 6.2, 'normal'),
|
||
('2025-06-04 10:00:00', 28.1, 69.3, 6.1, 'normal'),
|
||
('2025-06-04 10:20:00', 28.9, 71.4, 6.1, 'normal'),
|
||
('2025-06-04 10:40:00', 29.6, 66.5, 6.0, 'normal'),
|
||
('2025-06-04 11:00:00', 29.4, 73.3, 6.2, 'normal'),
|
||
('2025-06-04 11:20:00', 28.3, 72.5, 6.0, 'normal'),
|
||
('2025-06-04 11:40:00', 30.1, 71.3, 5.9, 'normal'),
|
||
('2025-06-04 12:00:00', 30.4, 72.2, 6.0, 'normal'),
|
||
('2025-06-04 12:20:00', 30.6, 67.9, 6.1, 'normal'),
|
||
('2025-06-04 12:40:00', 29.4, 71.0, 6.2, 'normal'),
|
||
('2025-06-04 13:00:00', 30.8, 66.1, 6.1, 'normal'),
|
||
('2025-06-04 13:20:00', 29.4, 70.7, 6.1, 'normal'),
|
||
('2025-06-04 13:40:00', 29.0, 69.3, 6.0, 'normal'),
|
||
('2025-06-04 14:00:00', 28.6, 74.9, 6.2, 'normal'),
|
||
('2025-06-04 14:20:00', 27.7, 74.5, 6.3, 'normal'),
|
||
('2025-06-04 14:40:00', 27.8, 73.7, 6.2, 'normal'),
|
||
('2025-06-04 15:00:00', 27.8, 74.9, 6.1, 'normal'),
|
||
('2025-06-04 15:20:00', 27.1, 73.4, 6.1, 'normal'),
|
||
('2025-06-04 15:40:00', 27.1, 76.1, 6.3, 'normal'),
|
||
('2025-06-04 16:00:00', 24.9, 79.5, 6.0, 'normal'),
|
||
('2025-06-04 16:20:00', 27.1, 77.3, 6.1, 'normal'),
|
||
('2025-06-04 16:40:00', 24.5, 78.8, 6.0, 'normal'),
|
||
('2025-06-04 17:00:00', 25.7, 78.7, 6.1, 'normal'),
|
||
('2025-06-04 17:20:00', 25.2, 75.7, 5.9, 'normal'),
|
||
('2025-06-04 17:40:00', 24.5, 89.9, 6.2, 'normal'),
|
||
('2025-06-04 18:00:00', 23.7, 82.4, 6.1, 'normal'),
|
||
('2025-06-04 18:20:00', 24.5, 81.8, 6.0, 'normal'),
|
||
('2025-06-04 18:40:00', 23.8, 83.5, 6.1, 'normal'),
|
||
('2025-06-04 19:00:00', 23.5, 80.8, 6.3, 'normal'),
|
||
('2025-06-04 19:20:00', 22.0, 82.8, 5.9, 'normal'),
|
||
('2025-06-04 19:40:00', 22.9, 83.9, 6.1, 'normal'),
|
||
('2025-06-04 20:00:00', 20.9, 83.3, 6.3, 'normal'),
|
||
('2025-06-04 20:20:00', 21.5, 87.8, 6.0, 'normal'),
|
||
('2025-06-04 20:40:00', 20.1, 80.9, 5.9, 'normal'),
|
||
('2025-06-04 21:00:00', 21.3, 86.1, 6.3, 'normal'),
|
||
('2025-06-04 21:20:00', 19.6, 86.5, 6.0, 'normal'),
|
||
('2025-06-04 21:40:00', 21.0, 89.2, 6.2, 'normal'),
|
||
('2025-06-04 22:00:00', 18.5, 91.1, 6.1, 'normal'),
|
||
('2025-06-04 22:20:00', 18.6, 85.7, 6.2, 'normal'),
|
||
('2025-06-04 22:40:00', 19.3, 94.5, 6.1, 'normal'),
|
||
('2025-06-04 23:00:00', 19.5, 88.2, 6.1, 'normal'),
|
||
('2025-06-04 23:20:00', 20.4, 89.4, 5.8, 'normal'),
|
||
('2025-06-04 23:40:00', 19.3, 90.0, 6.0, 'normal'),
|
||
('2025-06-05 00:00:00', 19.8, 90.9, 5.9, 'normal'),
|
||
('2025-06-05 00:20:00', 18.4, 95.4, 6.0, 'normal'),
|
||
('2025-06-05 00:40:00', 19.1, 92.9, 5.9, 'normal'),
|
||
('2025-06-05 01:00:00', 18.7, 86.4, 5.7, 'normal'),
|
||
('2025-06-05 01:20:00', 19.4, 88.3, 6.0, 'normal'),
|
||
('2025-06-05 01:40:00', 19.8, 85.4, 5.8, 'normal'),
|
||
('2025-06-05 02:00:00', 19.4, 91.3, 6.0, 'normal'),
|
||
('2025-06-05 02:20:00', 20.5, 89.8, 5.9, 'normal'),
|
||
('2025-06-05 02:40:00', 21.0, 91.3, 5.9, 'normal'),
|
||
('2025-06-05 03:00:00', 19.1, 90.6, 6.0, 'normal'),
|
||
('2025-06-05 03:20:00', 20.0, 88.3, 5.9, 'normal'),
|
||
('2025-06-05 03:40:00', 20.7, 89.6, 5.8, 'normal'),
|
||
('2025-06-05 04:00:00', 21.3, 83.0, 5.9, 'normal'),
|
||
('2025-06-05 04:20:00', 22.1, 88.7, 6.1, 'normal'),
|
||
('2025-06-05 04:40:00', 22.6, 84.3, 6.1, 'normal'),
|
||
('2025-06-05 05:00:00', 22.0, 86.9, 5.8, 'normal'),
|
||
('2025-06-05 05:20:00', 23.1, 78.4, 5.8, 'normal'),
|
||
('2025-06-05 05:40:00', 23.2, 78.8, 6.0, 'normal'),
|
||
('2025-06-05 06:00:00', 25.1, 74.7, 5.8, 'normal'),
|
||
('2025-06-05 06:20:00', 24.5, 83.4, 5.9, 'normal'),
|
||
('2025-06-05 06:40:00', 24.5, 81.0, 5.8, 'normal'),
|
||
('2025-06-05 07:00:00', 26.1, 77.9, 6.0, 'normal'),
|
||
('2025-06-05 07:20:00', 25.4, 77.9, 5.9, 'normal'),
|
||
('2025-06-05 07:40:00', 25.0, 76.6, 6.5, 'normal'),
|
||
('2025-06-05 08:00:00', 26.4, 74.0, 5.9, 'normal'),
|
||
('2025-06-05 08:20:00', 26.5, 79.2, 6.2, 'normal'),
|
||
('2025-06-05 08:40:00', 20.2, 68.6, 5.9, 'low_temp'),
|
||
('2025-06-05 09:00:00', 28.0, 67.9, 5.9, 'normal'),
|
||
('2025-06-05 09:20:00', 28.2, 79.6, 6.0, 'normal'),
|
||
('2025-06-05 09:40:00', 28.5, 72.5, 6.0, 'normal'),
|
||
('2025-06-05 10:00:00', 30.4, 71.4, 5.8, 'normal'),
|
||
('2025-06-05 10:20:00', 28.4, 75.3, 6.1, 'normal'),
|
||
('2025-06-05 10:40:00', 27.0, 70.7, 6.1, 'normal'),
|
||
('2025-06-05 11:00:00', 28.6, 71.8, 6.0, 'normal'),
|
||
('2025-06-05 11:20:00', 28.7, 71.6, 6.0, 'normal'),
|
||
('2025-06-05 11:40:00', 29.5, 66.9, 6.1, 'normal'),
|
||
('2025-06-05 12:00:00', 30.0, 68.7, 6.0, 'normal'),
|
||
('2025-06-05 12:20:00', 29.4, 74.6, 5.8, 'normal'),
|
||
('2025-06-05 12:40:00', 27.5, 72.8, 6.1, 'normal'),
|
||
('2025-06-05 13:00:00', 28.7, 71.9, 6.0, 'normal'),
|
||
('2025-06-05 13:20:00', 28.7, 73.3, 6.2, 'normal'),
|
||
('2025-06-05 13:40:00', 27.7, 73.4, 6.6, 'normal'),
|
||
('2025-06-05 14:00:00', 29.2, 77.1, 5.9, 'normal'),
|
||
('2025-06-05 14:20:00', 28.0, 70.3, 5.9, 'normal'),
|
||
('2025-06-05 14:40:00', 27.3, 76.9, 6.5, 'normal'),
|
||
('2025-06-05 15:00:00', 27.7, 73.6, 6.3, 'normal'),
|
||
('2025-06-05 15:20:00', 26.7, 70.4, 6.8, 'normal'),
|
||
('2025-06-05 15:40:00', 27.1, 76.4, 6.9, 'normal'),
|
||
('2025-06-05 16:00:00', 27.3, 72.6, 6.6, 'normal'),
|
||
('2025-06-05 16:20:00', 26.2, 80.9, 5.9, 'normal'),
|
||
('2025-06-05 16:40:00', 25.1, 77.0, 6.2, 'normal'),
|
||
('2025-06-05 17:00:00', 24.4, 79.6, 5.8, 'normal'),
|
||
('2025-06-05 17:20:00', 25.3, 78.0, 6.4, 'normal'),
|
||
('2025-06-05 17:40:00', 24.0, 78.0, 6.2, 'normal'),
|
||
('2025-06-05 18:00:00', 24.0, 81.5, 6.3, 'normal'),
|
||
('2025-06-05 18:20:00', 23.2, 82.9, 6.3, 'normal'),
|
||
('2025-06-05 18:40:00', 23.0, 83.6, 5.8, 'normal'),
|
||
('2025-06-05 19:00:00', 23.8, 81.0, 6.0, 'normal'),
|
||
('2025-06-05 19:20:00', 21.9, 84.2, 5.7, 'normal'),
|
||
('2025-06-05 19:40:00', 22.1, 88.0, 6.8, 'normal'),
|
||
('2025-06-05 20:00:00', 20.9, 79.5, 6.9, 'normal'),
|
||
('2025-06-05 20:20:00', 21.0, 82.6, 6.3, 'normal'),
|
||
('2025-06-05 20:40:00', 20.7, 88.5, 6.2, 'normal'),
|
||
('2025-06-05 21:00:00', 20.4, 90.6, 5.9, 'normal'),
|
||
('2025-06-05 21:20:00', 20.3, 82.6, 5.8, 'normal'),
|
||
('2025-06-05 21:40:00', 19.3, 87.0, 5.9, 'normal'),
|
||
('2025-06-05 22:00:00', 19.7, 88.6, 6.6, 'normal'),
|
||
('2025-06-05 22:20:00', 20.9, 84.1, 6.5, 'normal'),
|
||
('2025-06-05 22:40:00', 18.9, 85.7, 6.8, 'normal'),
|
||
('2025-06-05 23:00:00', 20.0, 87.4, 6.9, 'normal'),
|
||
('2025-06-05 23:20:00', 21.0, 84.4, 6.7, 'normal'),
|
||
('2025-06-05 23:40:00', 19.1, 87.9, 6.8, 'normal'),
|
||
('2025-06-06 00:00:00', 20.4, 100.0, 6.4, 'normal'),
|
||
('2025-06-06 00:20:00', 18.3, 95.1, 6.2, 'normal'),
|
||
('2025-06-06 00:40:00', 20.4, 90.6, 6.3, 'normal'),
|
||
('2025-06-06 01:00:00', 19.7, 85.8, 6.2, 'normal'),
|
||
('2025-06-06 01:20:00', 20.6, 85.0, 6.5, 'normal'),
|
||
('2025-06-06 01:40:00', 19.1, 87.1, 6.3, 'normal'),
|
||
('2025-06-06 02:00:00', 20.1, 87.6, 6.3, 'normal'),
|
||
('2025-06-06 02:20:00', 19.3, 84.9, 6.4, 'normal'),
|
||
('2025-06-06 02:40:00', 20.2, 85.1, 6.6, 'normal'),
|
||
('2025-06-06 03:00:00', 19.4, 83.8, 6.4, 'normal'),
|
||
('2025-06-06 03:20:00', 21.1, 85.9, 6.2, 'normal'),
|
||
('2025-06-06 03:40:00', 20.8, 88.5, 6.4, 'normal'),
|
||
('2025-06-06 04:00:00', 21.3, 89.7, 6.5, 'normal'),
|
||
('2025-06-06 04:20:00', 22.9, 77.1, 6.3, 'normal'),
|
||
('2025-06-06 04:40:00', 20.7, 86.3, 6.6, 'normal'); |