91 lines
6.8 KiB
SQL
91 lines
6.8 KiB
SQL
-- =============================================
|
|
-- 智慧水务管理系统 - 在线监测列表 DDL
|
|
-- 版本: V3
|
|
-- 功能: 在线监测设备 + 实时数据 + 多维筛选
|
|
-- =============================================
|
|
|
|
-- ==================== 在线监测设备 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS prod_monitor_device (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
device_code VARCHAR(50) NOT NULL UNIQUE, -- 设备编号
|
|
device_name VARCHAR(200) NOT NULL, -- 设备名称
|
|
device_type VARCHAR(30) NOT NULL, -- 设备类型: flow/pressure/level/quality
|
|
area VARCHAR(50) NOT NULL, -- 所属区域
|
|
location VARCHAR(300), -- 安装位置描述
|
|
lng DECIMAL(12, 8), -- 经度
|
|
lat DECIMAL(12, 8), -- 纬度
|
|
status VARCHAR(20) NOT NULL DEFAULT 'offline', -- 设备状态: online/offline/fault/abnormal
|
|
last_report_time TIMESTAMP, -- 最后上报时间
|
|
brand VARCHAR(100), -- 品牌/型号
|
|
install_time TIMESTAMP, -- 安装时间
|
|
remark VARCHAR(500), -- 备注
|
|
created_time TIMESTAMP DEFAULT NOW(),
|
|
updated_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE prod_monitor_device IS '在线监测设备表';
|
|
COMMENT ON COLUMN prod_monitor_device.device_type IS '设备类型: flow(流量计)/pressure(压力计)/level(液位计)/quality(水质仪)';
|
|
COMMENT ON COLUMN prod_monitor_device.status IS '设备状态: online(在线)/offline(离线)/fault(故障)/abnormal(数据异常)';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_device_area ON prod_monitor_device(area);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_device_type ON prod_monitor_device(device_type);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_device_status ON prod_monitor_device(status);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_device_report ON prod_monitor_device(last_report_time DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_device_code_name ON prod_monitor_device(device_code, device_name);
|
|
|
|
-- ==================== 监测实时数据 ====================
|
|
|
|
CREATE TABLE IF NOT EXISTS prod_monitor_realtime_data (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
device_id BIGINT NOT NULL REFERENCES prod_monitor_device(id), -- 关联设备
|
|
device_code VARCHAR(50) NOT NULL, -- 设备编号（冗余）
|
|
metric_key VARCHAR(50) NOT NULL, -- 参数类型: flow/pressure/level/turbidity/ph/residual_chlorine/temperature
|
|
metric_value DECIMAL(14, 4) NOT NULL, -- 实时值
|
|
unit VARCHAR(20), -- 单位
|
|
threshold_high DECIMAL(14, 4), -- 阈值上限
|
|
threshold_low DECIMAL(14, 4), -- 阈值下限
|
|
is_abnormal SMALLINT DEFAULT 0, -- 是否异常: 0正常 1异常
|
|
collect_time TIMESTAMP NOT NULL, -- 采集时间
|
|
created_time TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE prod_monitor_realtime_data IS '监测实时数据表';
|
|
COMMENT ON COLUMN prod_monitor_realtime_data.metric_key IS '参数类型: flow(流量)/pressure(压力)/level(液位)/turbidity(浊度)/ph(pH值)/residual_chlorine(余氯)/temperature(温度)';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_realtime_device_id ON prod_monitor_realtime_data(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_realtime_device_code ON prod_monitor_realtime_data(device_code);
|
|
CREATE INDEX IF NOT EXISTS idx_realtime_metric_key ON prod_monitor_realtime_data(metric_key);
|
|
CREATE INDEX IF NOT EXISTS idx_realtime_collect_time ON prod_monitor_realtime_data(collect_time DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_realtime_device_metric ON prod_monitor_realtime_data(device_id, metric_key, collect_time DESC);
|
|
|
|
-- ==================== 初始化数据（示例） ====================
|
|
|
|
INSERT INTO prod_monitor_device (device_code, device_name, device_type, area, location, lng, lat, status, last_report_time, brand)
|
|
VALUES
|
|
('MON-FLOW-001', '一号泵站出口流量计', 'flow', '一体化水厂', '一号泵站出口', 82.07123456, 44.84567890, 'online', NOW(), 'E+H Promag 50'),
|
|
('MON-FLOW-002', '二号泵站出口流量计', 'flow', '一体化水厂', '二号泵站出口', 82.07234567, 44.84678901, 'online', NOW(), 'E+H Promag 50'),
|
|
('MON-PRES-001', '管网压力监测点A', 'pressure', '管网一区', '人民路DN300', 82.06890123, 44.84234567, 'online', NOW(), 'WIKA S-20'),
|
|
('MON-PRES-002', '管网压力监测点B', 'pressure', '管网一区', '建设路DN200', 82.06901234, 44.84345678, 'offline', NOW() - INTERVAL '2 hours', 'WIKA S-20'),
|
|
('MON-LEV-001', '清水池液位计', 'level', '一体化水厂', '清水池', 82.07156789, 44.84501234, 'online', NOW(), 'VEGA VEGAPULS 64'),
|
|
('MON-LEV-002', '沉淀池液位计', 'level', '一体化水厂', '沉淀池', 82.07167890, 44.84512345, 'fault', NOW() - INTERVAL '30 minutes', 'VEGA VEGAPULS 64'),
|
|
('MON-QUAL-001', '出厂水质监测仪', 'quality', '一体化水厂', '出厂水口', 82.07178901, 44.84523456, 'online', NOW(), 'HACH sc200'),
|
|
('MON-QUAL-002', '管网末梢水质仪', 'quality', '管网二区', '末梢检测点', 82.06543210, 44.83987654, 'abnormal',NOW(), 'HACH sc200'),
|
|
('MON-FLOW-003', '三号泵站流量计', 'flow', '管网二区', '三号泵站', 82.06654321, 44.84098765, 'online', NOW(), 'E+H Promag 10'),
|
|
('MON-PRES-003', '高位水池压力计', 'pressure', '管网三区', '高位水池出口', 82.07345678, 44.84789012, 'online', NOW(), 'WIKA S-20')
|
|
ON CONFLICT (device_code) DO NOTHING;
|
|
|
|
INSERT INTO prod_monitor_realtime_data (device_id, device_code, metric_key, metric_value, unit, threshold_high, threshold_low, is_abnormal, collect_time)
|
|
SELECT d.id, d.device_code, m.metric_key, m.metric_value, m.unit, m.threshold_high, m.threshold_low, m.is_abnormal, NOW()
|
|
FROM prod_monitor_device d
|
|
CROSS JOIN (VALUES
|
|
('flow', 125.50, 'm³/h', 200.0, 10.0, 0),
|
|
('pressure', 0.35, 'MPa', 0.6, 0.15, 0),
|
|
('level', 3.80, 'm', 5.0, 0.5, 0),
|
|
('turbidity', 0.45, 'NTU', 1.0, NULL, 0),
|
|
('ph', 7.20, '', 8.5, 6.5, 0),
|
|
('residual_chlorine', 0.35, 'mg/L', 0.8, 0.05, 0)
|
|
) AS m(metric_key, metric_value, unit, threshold_high, threshold_low, is_abnormal)
|
|
WHERE d.status = 'online'
|
|
ON CONFLICT DO NOTHING;
|