-- ============================================================ -- 供水管网 T4 运行监测 测试数据脚本（KingbaseES） -- 说明： -- 1. 测试主键均使用 WS-TEST- 前缀，可重复执行；设备编码遵循新命名规范。 -- 2. 设备编码遵循“区域-类型-YYYYMMDD+4位流水号”规范。 -- 3. 本脚本只初始化测试行，不创建监测业务表；执行前请确认以下表已存在： -- app_user.equipment_type、app_user.equipment_base、app_user.radar_data、 -- app_user.telemetry_data、app_user.firefighting_pressure、app_user.noise_info -- 如测量表 device_code 仍为 varchar(10)，请先执行 migration_monitoring_device_code_length.sql。 -- 4. 覆盖场景：雷达流量、遥测流量、压力、漏失、无数据设备、 -- 非供水设备排除、数值范围过滤、时间范围过滤与近24小时之外的旧趋势点。 -- ============================================================ -- ------------------------------------------------------------ -- 0. 清理历史测试数据（先清测量值，再清设备；不删除生产设备类型） -- ------------------------------------------------------------ DELETE FROM app_user.radar_data WHERE id LIKE 'WS-TEST-%'; DELETE FROM app_user.telemetry_data WHERE id LIKE 'WS-TEST-%'; DELETE FROM app_user.firefighting_pressure WHERE id LIKE 'WS-TEST-%'; DELETE FROM app_user.noise_info WHERE encode LIKE 'WS-TEST-%' OR encode LIKE 'CB-GS-20260902000%' OR encode LIKE 'TC-GS-20260902000%'; DELETE FROM app_user.equipment_base WHERE equipment_id LIKE 'WS-TEST-EQ-%'; -- ------------------------------------------------------------ -- 1. 复用既有设备类型：id=1/type_id=water_supply，监测子类型严格挂在 parent_type_id=1 -- ------------------------------------------------------------ DO $$ DECLARE v_type_count integer; BEGIN SELECT count(*) INTO v_type_count FROM app_user.equipment_type WHERE id = '1' AND type_id = 'water_supply' AND parent_type_id = '0'; IF v_type_count <> 1 THEN RAISE EXCEPTION '缺少规范供水顶级设备类型：id=1,type_id=water_supply,parent_type_id=0'; END IF; SELECT count(*) INTO v_type_count FROM app_user.equipment_type WHERE type_id IN ('ws_flow', 'ws_pressure', 'ws_leak') AND parent_type_id = '1'; IF v_type_count <> 3 THEN RAISE EXCEPTION '缺少规范供水监测子类型：ws_flow/ws_pressure/ws_leak 必须 parent_type_id=1'; END IF; SELECT count(*) INTO v_type_count FROM app_user.equipment_type WHERE id <> '1' AND type_id NOT IN ('water_supply', 'ws_flow', 'ws_pressure', 'ws_leak'); IF v_type_count = 0 THEN RAISE EXCEPTION '缺少非供水设备类型，无法构造边界排除样本'; END IF; END $$; INSERT INTO app_user.equipment_base (equipment_id, equipment_code, equipment_name, equipment_model, equipment_type_id, longitude, latitude, equipment_location, maintainer, maintainer_phone, ownership_unit, remark, create_time, update_time, del_flag, district) VALUES ('WS-TEST-EQ-FLOW-RADAR', 'CN-GS-202609020001', '测试雷达流量监测点', 'RADAR-FLOW-TEST', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.28000000, 23.12000000, '供水测试大道1号', '供水测试维护员', '13800000001', '供水测试公司', '供水T4运行监测测试数据', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-TELEMETRY', 'CN-GS-202609020002', '测试遥测终端流量监测点', 'TELEMETRY-FLOW-TEST', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.29000000, 23.13000000, '供水测试大道2号', '供水测试维护员', '13800000002', '供水测试公司', '供水T4运行监测测试数据', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-PRESSURE', 'CB-GS-202609020001', '测试压力监测点', 'PRESSURE-TEST', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1), 113.30000000, 23.14000000, '供水测试大道3号', '供水测试维护员', '13800000003', '供水测试公司', '供水T4运行监测测试数据', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-LEAKAGE', 'TC-GS-202609020001', '测试漏失监测点', 'LEAKAGE-TEST', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1), 113.31000000, 23.15000000, '供水测试大道4号', '供水测试维护员', '13800000004', '供水测试公司', '供水T4运行监测测试数据', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-EMPTY', 'CN-GS-202609020003', '测试无测量流量监测点', 'FLOW-EMPTY-TEST', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.32000000, 23.16000000, '供水测试大道5号', '供水测试维护员', '13800000005', '供水测试公司', '供水T4运行监测无数据测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-003', 'CN-GS-202609020004', '测试流量监测点03', 'FLOW-TEST-003', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.34000000, 23.18000000, '供水测试大道7号', '供水测试维护员', '13800000007', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-004', 'CN-GS-202609020005', '测试流量监测点04', 'FLOW-TEST-004', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.35000000, 23.19000000, '供水测试大道8号', '供水测试维护员', '13800000008', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-005', 'CN-GS-202609020006', '测试流量监测点05', 'FLOW-TEST-005', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.36000000, 23.20000000, '供水测试大道9号', '供水测试维护员', '13800000009', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-006', 'CN-GS-202609020007', '测试流量监测点06', 'FLOW-TEST-006', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.37000000, 23.21000000, '供水测试大道10号', '供水测试维护员', '13800000010', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-007', 'CN-GS-202609020008', '测试流量监测点07', 'FLOW-TEST-007', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.38000000, 23.22000000, '供水测试大道11号', '供水测试维护员', '13800000011', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-008', 'CN-GS-202609020009', '测试流量监测点08', 'FLOW-TEST-008', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.39000000, 23.23000000, '供水测试大道12号', '供水测试维护员', '13800000012', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-009', 'CN-GS-202609020010', '测试流量监测点09', 'FLOW-TEST-009', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.40000000, 23.24000000, '供水测试大道13号', '供水测试维护员', '13800000013', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-010', 'CN-GS-202609020011', '测试流量监测点10', 'FLOW-TEST-010', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.41000000, 23.25000000, '供水测试大道14号', '供水测试维护员', '13800000014', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-011', 'CN-GS-202609020012', '测试流量监测点11', 'FLOW-TEST-011', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.42000000, 23.26000000, '供水测试大道15号', '供水测试维护员', '13800000015', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-FLOW-012', 'CN-GS-202609020013', '测试流量监测点12', 'FLOW-TEST-012', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.43000000, 23.27000000, '供水测试大道16号', '供水测试维护员', '13800000016', '供水测试公司', '供水T4流量分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-PRESSURE-002', 'CB-GS-202609020002', '测试压力监测点02', 'PRESSURE-TEST-002', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1), 113.44000000, 23.28000000, '供水测试大道17号', '供水测试维护员', '13800000017', '供水测试公司', '供水T4压力分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-PRESSURE-003', 'CB-GS-202609020003', '测试压力监测点03', 'PRESSURE-TEST-003', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1), 113.45000000, 23.29000000, '供水测试大道18号', '供水测试维护员', '13800000018', '供水测试公司', '供水T4压力分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-LEAKAGE-002', 'TC-GS-202609020002', '测试漏失监测点02', 'LEAKAGE-TEST-002', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1), 113.46000000, 23.30000000, '供水测试大道19号', '供水测试维护员', '13800000019', '供水测试公司', '供水T4漏失分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-LEAKAGE-003', 'TC-GS-202609020003', '测试漏失监测点03', 'LEAKAGE-TEST-003', (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1), 113.47000000, 23.31000000, '供水测试大道20号', '供水测试维护员', '13800000020', '供水测试公司', '供水T4漏失分页测试设备', now() - interval '7 days', now(), '0', '供水测试区'), ('WS-TEST-EQ-OTHER-FLOW', 'CN-RQ-202609020001', '测试非供水流量监测点', 'OTHER-FLOW-TEST', (SELECT id FROM app_user.equipment_type WHERE id <> '1' AND type_id NOT IN ('water_supply', 'ws_flow', 'ws_pressure', 'ws_leak') LIMIT 1), 113.33000000, 23.17000000, '其他系统测试大道6号', '供水测试维护员', '13800000006', '供水测试公司', '供水T4非供水设备边界测试数据', now() - interval '7 days', now(), '0', '供水测试区'); -- ------------------------------------------------------------ -- 2. 雷达流量计数据 -- 数值范围 20-40 时：最新 120 不符合，应回退到 30。 -- 5 天前数据用于验证趋势时间范围过滤。 -- ------------------------------------------------------------ INSERT INTO app_user.radar_data (id, system_identifier, device_code, meter1_instant_flow, flow_speed, create_time) VALUES ('WS-TEST-RADAR-FLOW-001', 'WS-TEST-SYSTEM', 'CN-GS-202609020001', '30', '1.20', now() - interval '3 hours'), ('WS-TEST-RADAR-FLOW-002', 'WS-TEST-SYSTEM', 'CN-GS-202609020001', '120', '3.50', now() - interval '1 hour'), ('WS-TEST-RADAR-FLOW-003', 'WS-TEST-SYSTEM', 'CN-GS-202609020001', '18', '0.80', now() - interval '5 days'), ('WS-TEST-RADAR-FLOW-004', 'WS-TEST-SYSTEM', 'CN-GS-202609020004', '25', '1.00', now() - interval '1 hour'), ('WS-TEST-RADAR-FLOW-005', 'WS-TEST-SYSTEM', 'CN-GS-202609020005', '35', '1.10', now() - interval '2 hours'), ('WS-TEST-RADAR-FLOW-006', 'WS-TEST-SYSTEM', 'CN-GS-202609020006', '45', '1.20', now() - interval '3 hours'), ('WS-TEST-RADAR-FLOW-007', 'WS-TEST-SYSTEM', 'CN-GS-202609020007', '55', '1.30', now() - interval '4 hours'), ('WS-TEST-RADAR-FLOW-008', 'WS-TEST-SYSTEM', 'CN-GS-202609020008', '65', '1.40', now() - interval '5 hours'), ('WS-TEST-RADAR-OTHER-FLOW-001', 'WS-TEST-SYSTEM', 'CN-RQ-202609020001', '66', '2.00', now() - interval '1 hour'); -- ------------------------------------------------------------ -- 3. 遥测终端流量数据（device_code 与 equipment_base.equipment_code 关联） -- ------------------------------------------------------------ INSERT INTO app_user.telemetry_data (id, system_identifier, device_code, meter1_instant_flow, meter1_positive_total, flow_speed, temperature, create_time) VALUES ('WS-TEST-TELEMETRY-001', 'WS-TEST-SYSTEM', 'CN-GS-202609020002', '42', '12345', '1.80', '24.50', now() - interval '2 hours'), ('WS-TEST-TELEMETRY-002', 'WS-TEST-SYSTEM', 'CN-GS-202609020002', '36', '12300', '1.50', '24.00', now() - interval '4 hours'), ('WS-TEST-TELEMETRY-003', 'WS-TEST-SYSTEM', 'CN-GS-202609020002', '20', '12200', '0.90', '23.50', now() - interval '5 days'); INSERT INTO app_user.telemetry_data (id, system_identifier, device_code, meter1_instant_flow, meter1_positive_total, flow_speed, temperature, create_time) VALUES ('WS-TEST-TELEMETRY-004', 'WS-TEST-SYSTEM', 'CN-GS-202609020009', '18', '10008', '0.80', '23.80', now() - interval '1 hour'), ('WS-TEST-TELEMETRY-005', 'WS-TEST-SYSTEM', 'CN-GS-202609020010', '28', '10009', '0.90', '23.90', now() - interval '2 hours'), ('WS-TEST-TELEMETRY-006', 'WS-TEST-SYSTEM', 'CN-GS-202609020011', '38', '10010', '1.00', '24.10', now() - interval '3 hours'), ('WS-TEST-TELEMETRY-007', 'WS-TEST-SYSTEM', 'CN-GS-202609020012', '48', '10011', '1.10', '24.20', now() - interval '4 hours'), ('WS-TEST-TELEMETRY-008', 'WS-TEST-SYSTEM', 'CN-GS-202609020013', '58', '10012', '1.20', '24.30', now() - interval '5 hours'); -- ------------------------------------------------------------ -- 4. 消防压力数据（telemetering_station 与 equipment_base.equipment_code 关联） -- ------------------------------------------------------------ INSERT INTO app_user.firefighting_pressure (id, central_station, telemetering_station, sending_time, observed_time, pressure_value, create_time) VALUES ('WS-TEST-PRESSURE-001', 'WS-TEST-CENTER', 'CB-GS-202609020001', now() - interval '3 hours', now() - interval '3 hours', 0.42, now() - interval '3 hours'), ('WS-TEST-PRESSURE-002', 'WS-TEST-CENTER', 'CB-GS-202609020001', now() - interval '1 hour', now() - interval '1 hour', 0.95, now() - interval '1 hour'), ('WS-TEST-PRESSURE-003', 'WS-TEST-CENTER', 'CB-GS-202609020001', now() - interval '5 days', now() - interval '5 days', 0.20, now() - interval '5 days'); INSERT INTO app_user.firefighting_pressure (id, central_station, telemetering_station, sending_time, observed_time, pressure_value, create_time) VALUES ('WS-TEST-PRESSURE-004', 'WS-TEST-CENTER', 'CB-GS-202609020002', now() - interval '2 hours', now() - interval '2 hours', 0.55, now() - interval '2 hours'), ('WS-TEST-PRESSURE-005', 'WS-TEST-CENTER', 'CB-GS-202609020003', now() - interval '1 hour', now() - interval '1 hour', 0.75, now() - interval '1 hour'); -- ------------------------------------------------------------ -- 5. 漏失噪声数据（encode 与 equipment_base.equipment_code 关联） -- ------------------------------------------------------------ INSERT INTO app_user.noise_info (encode, power, gain, rms, center_frequency, ts, create_time, update_time) VALUES ('TC-GS-202609020001', 12.00, 2.00, 120, 800.00, to_char(now() - interval '2 hours', 'YYYY-MM-DD HH24:MI:SS'), now() - interval '2 hours', now() - interval '2 hours'), ('TC-GS-202609020001', 9.00, 2.00, 100, 780.00, to_char(now() - interval '4 hours', 'YYYY-MM-DD HH24:MI:SS'), now() - interval '4 hours', now() - interval '4 hours'), ('TC-GS-202609020001', 5.00, 1.00, 80, 700.00, to_char(now() - interval '5 days', 'YYYY-MM-DD HH24:MI:SS'), now() - interval '5 days', now() - interval '5 days'); INSERT INTO app_user.noise_info (encode, power, gain, rms, center_frequency, ts, create_time, update_time) VALUES ('TC-GS-202609020002', 15.00, 2.50, 125, 820.00, to_char(now() - interval '1 hour', 'YYYY-MM-DD HH24:MI:SS'), now() - interval '1 hour', now() - interval '1 hour'), ('TC-GS-202609020003', 18.00, 3.00, 130, 840.00, to_char(now() - interval '2 hours', 'YYYY-MM-DD HH24:MI:SS'), now() - interval '2 hours', now() - interval '2 hours'); -- ------------------------------------------------------------ -- 快速核验： -- 规范 ws_flow 流量设备共13台（其中12台有 radar/telemetry 测量记录，1台无数据）； -- 流量分页（默认最新值）：12台有测量设备各返回1条；雷达流量点最新值120，遥测流量点最新值42； -- 流量分页 flowMin=20&flowMax=40：符合范围的雷达/遥测点共7条（含回退到雷达30、遥测36）； -- 压力分页 pressureMin=0.30&pressureMax=0.60：0.42、0.55，共2条； -- 漏失分页 powerMin=8&powerMax=15：12、15，共2条； -- CN-GS-202609020003 无测量数据，不应出现在任何监测页。 -- CN-RQ-202609020001 如数据库存在其他系统类型则带入该类型；不在供水规范子类型内，不应出现在监测页。 -- ============================================================