-- ============================================================================= -- Electricity service mock data script (PostgreSQL) -- -- Execute after electricity_init_new.sql. station_id values are BIGINT ids from -- config_station.id; STA001~STA004 are kept only as business entity_id codes. -- ============================================================================= -- 电力业务前端 16 个新增页面 Mock 数据脚本(PostgreSQL) -- -- 使用方法: -- psql -h -U -d electricity -f electricity_mock_new.sql -- 或在 IDE 数据源里整文件执行。 -- -- 设计: -- - 4 个场站:2 风电 (BHWP01/HHWP02) + 2 光伏 (YMPV03/GBPV04),entity_id 保留 STA001~STA004 作为业务实体编码 -- - 时间窗口:当前时刻向前 24 小时 + 向后 24 小时,每小时一个采样点 -- - 实时类表 (data_*_real) 只写历史 24h;预测类表 (forecast_*) 写整 49 点 -- - 风电功率随风速近似 ws^3 缩放,光伏功率随辐照度+昼夜余弦缩放 -- - 脚本幂等:每张表执行前先 DELETE 命中行 -- -- 涉及的 16 个页面 → 主要表: -- 主看板 / 数据监视 / 预测总览 -> config_station, data_short_term_power_* -- 气象地图 / 灾害预警地图 -> weather_area, weather_alert_*, typhoon_warning_* -- 多源/理论功率/发电量预测 -> data_short_term_power_forecast_*, model_prediction_tasks -- 电量统计 / 风光/多源/气象指标 -> stats_assessment_indicator, stats_report_indicator, -- stats_weather_indicator -- 上报数据查询 / 上报记录查询 -> data_short_term_power_forecast_report, data_source_grab, -- monitoring_send_record -- 设备状态监控 / 风机设备信息 -> data_booster_station_real, data_weather_station_real, -- data_station_device_power_real, -- data_station_device_weather_real -- ============================================================================= BEGIN; -- ----------------------------------------------------------------------------- -- 0. 清理旧的 mock 数据(按 STA001..STA004 / EAST01 命名空间过滤,避免误删真实数据) -- ----------------------------------------------------------------------------- DELETE FROM data_booster_station_real WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_weather_station_real WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_station_device_power_real WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_station_device_weather_real WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_power_forecast_raw WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_power_forecast_correct WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_power_forecast_issued WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_power_forecast_back WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_power_forecast_report WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_super_short_term_forecast_report WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_weather_forecast_raw WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM data_short_term_weather_forecast_report WHERE entity_id IN ('STA001','STA002','STA003','STA004'); DELETE FROM stats_assessment_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM stats_report_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM stats_weather_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM config_assessment_task WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM config_assessment_indicator WHERE rule_id IN ('RULE001','RULE002'); DELETE FROM config_assessment_rule WHERE id IN ('RULE001','RULE002'); DELETE FROM model_prediction_tasks WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM monitoring_send_record WHERE recipient_email = 'demo@electricity.local'; DELETE FROM data_source_grab WHERE pattern_name LIKE 'MOCK-%'; DELETE FROM config_station_point WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM config_station_channel WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM config_station_device WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM config_station_power_limit WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM config_station_pv_module WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')); DELETE FROM weather_alert_area WHERE record_id IN (90001, 90002); DELETE FROM weather_alert_record WHERE id IN (90001, 90002); DELETE FROM typhoon_warning_path WHERE record_id = 90101; DELETE FROM typhoon_warning_record WHERE id = 90101; DELETE FROM typhoon WHERE id = 90101; DELETE FROM weather_warning_type WHERE id IN (9001, 9002, 9003, 9004); DELETE FROM weather_area WHERE area_id IN ('AREA-SD-QD','AREA-SD-WH','AREA-NM-BT','AREA-XJ-HM'); DELETE FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'); DELETE FROM config_area WHERE id = 'EAST01'; -- ----------------------------------------------------------------------------- -- 1. 大区 + 4 个场站 -- ----------------------------------------------------------------------------- INSERT INTO config_area (id, area_code, area_name, superior_area_id, is_del, create_by, create_time, update_by, update_time) VALUES ('EAST01', 'EAST01', '华东大区', NULL, FALSE, 'admin', now(), 'admin', now()); -- station_type: 0=风机, 1=光伏;is_enable 必填 INSERT INTO config_station (station_name, station_code, superior_area_id, period_total, longitude, latitude, province_number, city_number, position, installed_capacity, grid_connected_capacity, fans_pv_panelquantity, elevation, operation_time, boosterstation_voltage_level, booster_station_longitude, boosterstation_latitude, responsible_person_name, responsible_person_phone, is_enable, push_status, subject_total, is_full_power, is_del, create_by, create_time, update_by, update_time, station_type, area_id) VALUES ('渤海一号风电场','BHWP01','EAST01',0,'121.4737','38.9087','370000','370200','山东省青岛市', 150000,148000,75,12.5,'2021-06-01','110kV','121.4737','38.9087','张工','13800000001',TRUE,1, 2,TRUE,FALSE,'admin',now(),'admin',now(),0,'EAST01'), ('黄海二号风电场','HHWP02','EAST01',0,'122.1108','37.5128','370000','371000','山东省威海市', 120000,118500,60,18.0,'2022-03-15','110kV','122.1108','37.5128','李工','13800000002',TRUE,1, 1,TRUE,FALSE,'admin',now(),'admin',now(),0,'EAST01'), ('沂蒙光伏电站','YMPV03','EAST01',0,'118.3262','35.0654','370000','371300','山东省临沂市', 80000,79200,160000,250.0,'2020-09-20','35kV','118.3262','35.0654','王工','13800000003',TRUE,1, 1,TRUE,FALSE,'admin',now(),'admin',now(),1,'EAST01'), ('戈壁光伏电站','GBPV04','EAST01',0,'93.5145','42.8194','650000','650500','新疆哈密', 200000,196000,400000,1100.0,'2019-12-01','220kV','93.5145','42.8194','陈工','13800000004',TRUE,1, 1,TRUE,FALSE,'admin',now(),'admin',now(),1,'EAST01'); -- ----------------------------------------------------------------------------- -- 2. 实时数据(升压站 / 气象站 / 设备功率 / 设备气象) -- 时间窗:过去 24 小时,1 小时一个点 -- ----------------------------------------------------------------------------- DO $$ DECLARE v_station RECORD; v_offset_hour INTEGER; v_record_time TIMESTAMP; v_epoch INTEGER; v_create_epoch INTEGER; v_wind_speed NUMERIC; v_tirr NUMERIC; v_power NUMERIC; v_hour_of_day INTEGER; v_solar_factor NUMERIC; v_device_idx INTEGER; v_device_code VARCHAR(32); v_device_name VARCHAR(64); v_device_type INTEGER; BEGIN FOR v_station IN SELECT id, station_name, station_type, installed_capacity FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP FOR v_offset_hour IN 0..23 LOOP v_record_time := date_trunc('hour', now()) - make_interval(hours => v_offset_hour); v_epoch := EXTRACT(EPOCH FROM v_record_time)::INTEGER; v_create_epoch := v_epoch + 60; v_hour_of_day := EXTRACT(HOUR FROM v_record_time)::INTEGER; -- 风速:白天 5~12 m/s,夜里偏低;辐照:白天最大,夜里 0 v_wind_speed := 6 + 4 * sin((v_hour_of_day::NUMERIC) / 24 * 2 * pi()) + (random() - 0.5) * 1.5; IF v_wind_speed < 1 THEN v_wind_speed := 1; END IF; v_solar_factor := GREATEST(0, sin((v_hour_of_day - 6)::NUMERIC / 12 * pi())); v_tirr := v_solar_factor * (850 + (random() - 0.5) * 100); IF v_station.station_type = 0 THEN -- 风电 ≈ capacity * (ws/12)^3,截顶 95% v_power := v_station.installed_capacity * LEAST(0.95, POWER(LEAST(v_wind_speed, 12) / 12, 3)) * (0.9 + random() * 0.2); ELSE v_power := v_station.installed_capacity * v_solar_factor * (0.8 + random() * 0.2); END IF; INSERT INTO data_booster_station_real (entity_id, entity_type, entity_name, create_time, record_time, power) VALUES (v_station.id, 1, v_station.station_name, v_create_epoch, v_epoch, ROUND(v_power::NUMERIC, 2)::TEXT); INSERT INTO data_weather_station_real (entity_id, entity_type, entity_name, create_time, record_time, wind_speed, tirr) VALUES (v_station.id, 1, v_station.station_name, v_create_epoch, v_epoch, ROUND(v_wind_speed::NUMERIC, 2)::TEXT, ROUND(v_tirr::NUMERIC, 2)::TEXT); -- 每个场站 3 台设备 FOR v_device_idx IN 1..3 LOOP IF v_station.station_type = 0 THEN v_device_type := 1; -- 风机 v_device_code := v_station.id || '-WTG' || LPAD(v_device_idx::TEXT, 2, '0'); v_device_name := '风机 #' || v_device_idx; ELSE v_device_type := 2; -- 光伏板组 v_device_code := v_station.id || '-INV' || LPAD(v_device_idx::TEXT, 2, '0'); v_device_name := '逆变器 #' || v_device_idx; END IF; INSERT INTO data_station_device_power_real (entity_id, entity_type, entity_name, device_id, device_type, device_name, create_time, record_time, power) VALUES (v_station.id, 1, v_station.station_name, v_device_code, v_device_type, v_device_name, v_create_epoch, v_epoch, ROUND((v_power / 3 * (0.85 + random() * 0.3))::NUMERIC, 2)::TEXT); INSERT INTO data_station_device_weather_real (entity_id, entity_type, entity_name, device_id, device_type, device_name, create_time, record_time, wind_speed, tirr) VALUES (v_station.id, 1, v_station.station_name, v_device_code, v_device_type, v_device_name, v_create_epoch, v_epoch, ROUND((v_wind_speed + (random() - 0.5) * 0.5)::NUMERIC, 2)::TEXT, ROUND((v_tirr + (random() - 0.5) * 30)::NUMERIC, 2)::TEXT); END LOOP; END LOOP; END LOOP; END $$; -- ----------------------------------------------------------------------------- -- 3. 短期功率预测(raw / correct / issued / back / report) -- 时间窗:过去 24h + 未来 24h(共 49 点),统一起报时间 = 今天 00:00 -- ----------------------------------------------------------------------------- DO $$ DECLARE v_station RECORD; v_offset_hour INTEGER; v_forecast_time TIMESTAMP; v_start_time TIMESTAMP; v_create_time TIMESTAMP; v_fc_epoch INTEGER; v_st_epoch INTEGER; v_ct_epoch INTEGER; v_hour_of_day INTEGER; v_wind_speed NUMERIC; v_solar_factor NUMERIC; v_base_power NUMERIC; BEGIN v_start_time := date_trunc('day', now()); v_create_time := v_start_time; v_st_epoch := EXTRACT(EPOCH FROM v_start_time)::INTEGER; v_ct_epoch := EXTRACT(EPOCH FROM v_create_time)::INTEGER; FOR v_station IN SELECT id, station_name, station_type, installed_capacity FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP FOR v_offset_hour IN -24..24 LOOP v_forecast_time := v_start_time + make_interval(hours => v_offset_hour); v_fc_epoch := EXTRACT(EPOCH FROM v_forecast_time)::INTEGER; v_hour_of_day := EXTRACT(HOUR FROM v_forecast_time)::INTEGER; v_wind_speed := 6 + 4 * sin((v_hour_of_day::NUMERIC) / 24 * 2 * pi()); v_solar_factor := GREATEST(0, sin((v_hour_of_day - 6)::NUMERIC / 12 * pi())); IF v_station.station_type = 0 THEN v_base_power := v_station.installed_capacity * LEAST(0.95, POWER(LEAST(v_wind_speed, 12) / 12, 3)); ELSE v_base_power := v_station.installed_capacity * v_solar_factor; END IF; -- 原始(带噪声) INSERT INTO data_short_term_power_forecast_raw (latitude, longitude, data_org_type, data_type, business_process_type, data_source, data_sourcegrab_id, data_sourcegrab_pattern_name, data_model_id, data_model_name, source_id, source_name, entity_id, entity_type, create_time, start_time, forecast_time, pre_days, lead_time, station_id, station_name, power) VALUES ('38.9087','121.4737','TIME_SERIES',1,1, 1, 1001, 'MOCK-RAW-PATTERN', 1, 'EC-Model', 1, 'EC', v_station.id, '1', v_ct_epoch, v_st_epoch, v_fc_epoch, 1, v_offset_hour, v_station.id, v_station.station_name, ROUND((v_base_power * (0.85 + random() * 0.3))::NUMERIC, 2)::TEXT); -- 修正 INSERT INTO data_short_term_power_forecast_correct (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, power) VALUES (1001, 'MOCK-CORRECT', v_station.id, 1, v_station.station_name, v_ct_epoch, v_st_epoch, v_fc_epoch, 1, ROUND((v_base_power * (0.92 + random() * 0.16))::NUMERIC, 2)::TEXT); -- 下发 INSERT INTO data_short_term_power_forecast_issued (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, power) VALUES (1001, 'MOCK-ISSUED', v_station.id, 1, v_station.station_name, v_ct_epoch, v_st_epoch, v_fc_epoch, 1, ROUND((v_base_power * (0.95 + random() * 0.10))::NUMERIC, 2)::TEXT); -- 回传 INSERT INTO data_short_term_power_forecast_back (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, power) VALUES (1001, 'MOCK-BACK', v_station.id, 1, v_station.station_name, v_ct_epoch, v_st_epoch, v_fc_epoch, 1, ROUND((v_base_power * (0.96 + random() * 0.08))::NUMERIC, 2)::TEXT); -- 上报(最终值,靠近实际) INSERT INTO data_short_term_power_forecast_report (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, power) VALUES (1001, 'MOCK-REPORT', v_station.id, 1, v_station.station_name, v_ct_epoch, v_st_epoch, v_fc_epoch, 1, ROUND((v_base_power * (0.97 + random() * 0.06))::NUMERIC, 2)::TEXT); END LOOP; END LOOP; END $$; -- ----------------------------------------------------------------------------- -- 4. 短期气象预测(raw / report) —— 字段较多,只填核心 -- ----------------------------------------------------------------------------- DO $$ DECLARE v_station RECORD; v_offset_hour INTEGER; v_forecast_time TIMESTAMP; v_start_time TIMESTAMP; v_fc_epoch INTEGER; v_st_epoch INTEGER; v_ct_epoch INTEGER; v_hour_of_day INTEGER; v_wind_speed NUMERIC; v_solar_factor NUMERIC; v_tirr NUMERIC; v_tsfc NUMERIC; BEGIN v_start_time := date_trunc('day', now()); v_st_epoch := EXTRACT(EPOCH FROM v_start_time)::INTEGER; v_ct_epoch := v_st_epoch; FOR v_station IN SELECT id, station_name FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP FOR v_offset_hour IN -24..24 LOOP v_forecast_time := v_start_time + make_interval(hours => v_offset_hour); v_fc_epoch := EXTRACT(EPOCH FROM v_forecast_time)::INTEGER; v_hour_of_day := EXTRACT(HOUR FROM v_forecast_time)::INTEGER; v_wind_speed := 6 + 4 * sin((v_hour_of_day::NUMERIC) / 24 * 2 * pi()); v_solar_factor := GREATEST(0, sin((v_hour_of_day - 6)::NUMERIC / 12 * pi())); v_tirr := v_solar_factor * 850; v_tsfc := 18 + 8 * sin((v_hour_of_day - 6)::NUMERIC / 12 * pi()) + (random() - 0.5) * 2; INSERT INTO data_short_term_weather_forecast_raw (latitude, longitude, data_org_type, data_type, business_process_type, data_source, data_sourcegrab_id, data_sourcegrab_pattern_name, data_model_id, data_model_name, source_id, source_name, entity_id, entity_type, create_time, start_time, forecast_time, pre_days, lead_time, station_id, station_name, tcc, tsfc, psfc, rhsfc, tirr, ws_hub, wd_hub, ws) VALUES ('38.9087','121.4737','TIME_SERIES',2,1, 1, 1002, 'MOCK-WEATHER-RAW', 2, 'WRF-Model', 2, 'WRF', v_station.id, '1', v_ct_epoch, v_st_epoch, v_fc_epoch, 1, v_offset_hour, v_station.id, v_station.station_name, ROUND((0.3 + random() * 0.5)::NUMERIC, 2)::TEXT, ROUND(v_tsfc::NUMERIC, 2)::TEXT, ROUND((1010 + (random() - 0.5) * 5)::NUMERIC, 2)::TEXT, ROUND((60 + (random() - 0.5) * 20)::NUMERIC, 1)::TEXT, ROUND(v_tirr::NUMERIC, 1)::TEXT, ROUND(v_wind_speed::NUMERIC, 2)::TEXT, ROUND((180 + (random() - 0.5) * 90)::NUMERIC, 1)::TEXT, ROUND((v_wind_speed * 0.9)::NUMERIC, 2)::TEXT); INSERT INTO data_short_term_weather_forecast_report (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, tcc, tsfc, psfc, rhsfc, tirr, ws_hub, wd_hub, ws) VALUES (1002, 'MOCK-WEATHER-REPORT', v_station.id, 1, v_station.station_name, v_ct_epoch, v_st_epoch, v_fc_epoch, 1, ROUND((0.25 + random() * 0.45)::NUMERIC, 2)::TEXT, ROUND((v_tsfc + 0.3)::NUMERIC, 2)::TEXT, ROUND((1010 + (random() - 0.5) * 4)::NUMERIC, 2)::TEXT, ROUND((58 + (random() - 0.5) * 18)::NUMERIC, 1)::TEXT, ROUND((v_tirr * 0.97)::NUMERIC, 1)::TEXT, ROUND((v_wind_speed * 0.97)::NUMERIC, 2)::TEXT, ROUND((180 + (random() - 0.5) * 80)::NUMERIC, 1)::TEXT, ROUND((v_wind_speed * 0.88)::NUMERIC, 2)::TEXT); END LOOP; END LOOP; END $$; -- ----------------------------------------------------------------------------- -- 5. 气象区域 + 灾害预警 + 台风 -- ----------------------------------------------------------------------------- INSERT INTO weather_warning_type (id, warning_type, data_source, remark) VALUES (9001,'台风',2,'热带气旋类预警'), (9002,'暴雨',1,'强降水预警'), (9003,'大风',1,'内陆/海面大风预警'), (9004,'雷电',1,'雷电活动预警'); INSERT INTO weather_area (area_id, name_cn, district_cn, provcn_cn, nation_cn, continent_cn, city_id, city_name, parent_name, longitude, latitude, area_name, area_number) VALUES ('AREA-SD-QD','青岛站','市南区','山东','中国','亚洲', 370200,'青岛','山东','120.3826','36.0671','华东','EAST01'), ('AREA-SD-WH','威海站','环翠区','山东','中国','亚洲', 371000,'威海','山东','122.1201','37.5128','华东','EAST01'), ('AREA-NM-BT','包头站','昆都仑区','内蒙古','中国','亚洲', 150200,'包头','内蒙古','109.8403','40.6577','华北','NORTH01'), ('AREA-XJ-HM','哈密站','伊州区','新疆','中国','亚洲', 650500,'哈密','新疆','93.5145','42.8194','西北','WEST01'); INSERT INTO weather_alert_record (id, alert_id, head_line, publish_date, description, longitude, latitude, type_code, title, effect_area, type_id, warning_type, level, level_name, create_date, update_date, end_date, publish_status, province, city, county, area_id, alert_info) VALUES (90001,'AL-2024-001','山东省气象台发布大风蓝色预警', now() - interval '2 hours','预计未来 12 小时山东半岛沿海有 7~8 级大风', '120.3826','36.0671','11B06','大风蓝色预警','青岛 威海 烟台',9003,'大风',1,'蓝色', now() - interval '2 hours', now() - interval '1 hours', now() + interval '12 hours', '发布','山东','青岛','市南区','AREA-SD-QD','请加强海上风电运维安全'), (90002,'AL-2024-002','新疆气象台发布雷电黄色预警', now() - interval '4 hours','预计未来 6 小时哈密东部出现雷阵雨', '93.5145','42.8194','11B07','雷电黄色预警','哈密',9004,'雷电',2,'黄色', now() - interval '4 hours', now() - interval '3 hours', now() + interval '6 hours', '发布','新疆','哈密','伊州区','AREA-XJ-HM','光伏板请做好防雷'); INSERT INTO weather_alert_area (id, record_id, xh, area_id, area_name) VALUES (90011,90001,1,370200,'青岛市'), (90012,90001,2,371000,'威海市'), (90013,90002,1,650500,'哈密市'); INSERT INTO typhoon (id, typhoon_number, typhoon_en, typhoon_cn, status, begin_date, end_date) VALUES (90101,'TY2418','PRAPIROON','派比安','active', now() - interval '3 days', NULL); INSERT INTO typhoon_warning_record (id, status, typhoon_cn, typhoon_en, typhoon_number, arrival_date, typhoon_level, longitude, latitude, center_pressure, max_wind_speed, future_move_speed, futuredirection, create_date, update_date, pub_time) VALUES (90101,'active','派比安','PRAPIROON','TY2418', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), 'TY', '120.5','22.8',965,38.5,15.0,'西北', now() - interval '1 hours', now(), to_char(now(), 'YYYYMMDDHH24MI')); INSERT INTO typhoon_warning_path (id, record_id, xh, arrival_date, typhoon_level, latitude, longitude, center_pressure, max_wind_speed, future_move_speed, futuredirection, create_date, pub_time, status, typhoon_number) VALUES (90201,90101,1,to_char(now() - interval '12 hours','YYYY-MM-DD HH24:MI'), 'TS','19.5','118.6',985,25.0,20.0,'西北偏北', now() - interval '12 hours', to_char(now() - interval '12 hours','YYYYMMDDHH24MI'),'history','TY2418'), (90202,90101,2,to_char(now() - interval '6 hours','YYYY-MM-DD HH24:MI'), 'STS','21.0','119.5',975,32.0,18.0,'西北', now() - interval '6 hours', to_char(now() - interval '6 hours','YYYYMMDDHH24MI'),'history','TY2418'), (90203,90101,3,to_char(now(),'YYYY-MM-DD HH24:MI'), 'TY','22.8','120.5',965,38.5,15.0,'西北', now(), to_char(now(),'YYYYMMDDHH24MI'),'current','TY2418'), (90204,90101,4,to_char(now() + interval '12 hours','YYYY-MM-DD HH24:MI'), 'TY','24.5','121.6',960,40.0,15.0,'西北', now(), to_char(now() + interval '12 hours','YYYYMMDDHH24MI'),'forecast','TY2418'), (90205,90101,5,to_char(now() + interval '24 hours','YYYY-MM-DD HH24:MI'), 'STS','26.7','122.8',975,32.0,14.0,'北', now(), to_char(now() + interval '24 hours','YYYYMMDDHH24MI'),'forecast','TY2418'); -- ----------------------------------------------------------------------------- -- 6. 数据源抓取配置 + 预测任务 + 告警发送记录 -- ----------------------------------------------------------------------------- INSERT INTO data_source_grab (data_source_id, data_organization_type, data_type, data_content_type, remote_directory, earliest_start_time, longest_forecast_period, push_time, file_match_pattern, is_del, create_by, create_time, update_by, update_time, pattern_name, batch_of_day, threshold, standard_quantity, shortest_parsing_time, data_model_id, data_model_name) VALUES (1,1,1,1,'/data/ec/short_term/power', now() - interval '30 days', 24, now(),'POW_*.txt',FALSE,'admin',now(),'admin',now(), 'MOCK-EC-POWER',4,2000,2000,60,1,'EC-Model'), (1,1,2,1,'/data/ec/short_term/weather', now() - interval '30 days', 72, now(),'WTH_*.grb',FALSE,'admin',now(),'admin',now(), 'MOCK-EC-WEATHER',4,2000,2000,60,2,'EC-Weather-Model'), (2,1,8,1,'/data/wrf/ultra/power', now() - interval '30 days', 4, now(),'USP_*.csv',FALSE,'admin',now(),'admin',now(), 'MOCK-WRF-ULTRA',96,500,500,15,3,'WRF-Ultra'); INSERT INTO model_prediction_tasks (task_name, station_id, model_version_id, version_source, prediction_type, target_time, status, result_path, created_at, executed_at) VALUES ('渤海一号-短期-T+24',(SELECT id FROM config_station WHERE station_code = 'BHWP01'),1001,'station','short_term', date_trunc('day', now()) + interval '1 day','SUCCESS','/result/STA001/short.csv', now() - interval '2 hours', now() - interval '1 hours'), ('黄海二号-短期-T+24',(SELECT id FROM config_station WHERE station_code = 'HHWP02'),1001,'station','short_term', date_trunc('day', now()) + interval '1 day','SUCCESS','/result/STA002/short.csv', now() - interval '2 hours', now() - interval '1 hours'), ('沂蒙光伏-超短期-T+15min',(SELECT id FROM config_station WHERE station_code = 'YMPV03'),1002,'global','ultra_short', now() + interval '15 minutes','RUNNING',NULL, now() - interval '5 minutes', now() - interval '4 minutes'), ('戈壁光伏-短期-T+24',(SELECT id FROM config_station WHERE station_code = 'GBPV04'),1001,'area','short_term', date_trunc('day', now()) + interval '1 day','PENDING',NULL, now() - interval '10 minutes',NULL); INSERT INTO monitoring_send_record (check_record_id, send_status, recipient_email, email_info, create_by, create_time) VALUES (5001, 1,'demo@electricity.local','短期功率上报成功,行数 96/96','system', now() - interval '50 minutes'), (5002,-1,'demo@electricity.local','超短期功率上报失败:连接超时','system', now() - interval '20 minutes'), (5003, 1,'demo@electricity.local','短期气象上报成功,行数 96/96','system', now() - interval '5 minutes'); -- ----------------------------------------------------------------------------- -- 7. 考核规则 / 任务 / 指标统计 -- ----------------------------------------------------------------------------- INSERT INTO config_assessment_rule (id, rule_name, province_number, del_flag, create_by, create_time, update_by, update_time) VALUES ('RULE001','短期功率预测准确率细则','370000',FALSE,'admin',now(),'admin',now()), ('RULE002','超短期功率预测准确率细则','370000',FALSE,'admin',now(),'admin',now()); INSERT INTO config_assessment_indicator (id, rule_id, indicator_key, formula, limit_value, assess_description, enable_flag, del_flag, create_by, create_time, update_by, update_time) VALUES ('IND001','RULE001','accuracy','1 - sqrt(sum((actual-pred)^2)/n) / capacity', 0.85, '日准确率达标值', TRUE, FALSE, 'admin', now(), 'admin', now()), ('IND002','RULE001','rmse','sqrt(sum((actual-pred)^2)/n)', 0.20, '均方根误差', TRUE, FALSE, 'admin', now(), 'admin', now()), ('IND003','RULE002','accuracy','1 - sqrt(sum((actual-pred)^2)/n) / capacity', 0.90, '超短期日准确率', TRUE, FALSE, 'admin', now(), 'admin', now()); INSERT INTO config_assessment_task (id, station_id, rule_id, enable_flag, del_flag, create_by, create_time, update_by, update_time) VALUES ('TSK001',(SELECT id FROM config_station WHERE station_code = 'BHWP01'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()), ('TSK002',(SELECT id FROM config_station WHERE station_code = 'HHWP02'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()), ('TSK003',(SELECT id FROM config_station WHERE station_code = 'YMPV03'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()), ('TSK004',(SELECT id FROM config_station WHERE station_code = 'GBPV04'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()), ('TSK005',(SELECT id FROM config_station WHERE station_code = 'BHWP01'),'RULE002',TRUE,FALSE,'admin',now(),'admin',now()), ('TSK006',(SELECT id FROM config_station WHERE station_code = 'HHWP02'),'RULE002',TRUE,FALSE,'admin',now(),'admin',now()); -- 每个场站近 7 天的考核指标统计 DO $$ DECLARE v_station_id BIGINT; v_rule_id VARCHAR(32); v_offset_day INTEGER; v_accuracy NUMERIC; v_rmse NUMERIC; BEGIN FOR v_station_id IN SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP FOR v_rule_id IN SELECT unnest(ARRAY['RULE001','RULE002']) LOOP FOR v_offset_day IN 0..6 LOOP v_accuracy := 0.85 + random() * 0.10; v_rmse := 0.05 + random() * 0.10; INSERT INTO stats_assessment_indicator (id, station_id, rule_id, time_type, data_source, indicator_value, create_by, create_time, update_by, update_time, record_time, business_process_time, model_id) VALUES (md5(v_station_id || v_rule_id || v_offset_day::TEXT), v_station_id, v_rule_id, 1, '1', jsonb_build_object('accuracy', ROUND(v_accuracy::NUMERIC, 4), 'rmse', ROUND(v_rmse::NUMERIC, 4), 'mae', ROUND((v_rmse * 0.8)::NUMERIC, 4), 'qualified', v_accuracy >= 0.85), 'system', now(),'system', now(), (CURRENT_DATE - v_offset_day)::DATE, 1, 1); END LOOP; END LOOP; END LOOP; END $$; -- 报表指标(每场站 7 天日指标) DO $$ DECLARE v_station_id BIGINT; v_offset_day INTEGER; BEGIN FOR v_station_id IN SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP FOR v_offset_day IN 0..6 LOOP INSERT INTO stats_report_indicator (id, station_id, site_id, time_type, indicator_value, create_by, create_time, update_by, update_time, record_time) VALUES (md5('rep' || v_station_id || v_offset_day::TEXT), v_station_id,'SITE-DEMO',1, jsonb_build_object('total_generation', ROUND((300000 + random() * 800000)::NUMERIC, 2), 'theoretical_power', ROUND((350000 + random() * 800000)::NUMERIC, 2), 'utilization_hours', ROUND((4 + random() * 6)::NUMERIC, 2), 'curtailment', ROUND((random() * 20000)::NUMERIC, 2)), 'system', now(),'system', now(), (CURRENT_DATE - v_offset_day)::DATE); END LOOP; END LOOP; END $$; -- 气象指标(每场站 7 天) DO $$ DECLARE v_station_id BIGINT; v_offset_day INTEGER; BEGIN FOR v_station_id IN SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP FOR v_offset_day IN 0..6 LOOP INSERT INTO stats_weather_indicator (station_id, time_type, data_source, indicator_val, create_by, create_time, update_by, update_time, record_time, business_process_times, model_id) VALUES (v_station_id, 1, '1', jsonb_build_object('wind_speed_mae', ROUND((0.8 + random() * 0.6)::NUMERIC, 3), 'wind_speed_rmse', ROUND((1.0 + random() * 0.8)::NUMERIC, 3), 'tirr_mae', ROUND((40 + random() * 30)::NUMERIC, 2), 'temperature_mae', ROUND((0.5 + random() * 0.8)::NUMERIC, 3), 'forecast_qualified', random() > 0.2), 'system', now(),'system', now(), (CURRENT_DATE - v_offset_day)::DATE, 1, 1); END LOOP; END LOOP; END $$; -- ----------------------------------------------------------------------------- -- 8. 上报数据查询专用 mock:15 分钟粒度,覆盖 2026-01-13 全天 -- ----------------------------------------------------------------------------- DELETE FROM data_short_term_power_forecast_report WHERE entity_id IN ('STA003','STA004') AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DELETE FROM data_short_term_power_forecast_raw WHERE entity_id IN ('STA003','STA004') AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DELETE FROM data_short_term_power_forecast_issued WHERE entity_id IN ('STA003','STA004') AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DELETE FROM data_super_short_term_forecast_report WHERE entity_id IN ('STA003','STA004') AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DELETE FROM data_short_term_weather_forecast_report WHERE entity_id IN ('STA003','STA004') AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DELETE FROM data_booster_station_real WHERE entity_id IN ('STA003','STA004') AND record_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DELETE FROM data_station_device_power_real WHERE entity_id IN ('STA003','STA004') AND record_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER; DO $$ DECLARE v_station RECORD; v_idx INTEGER; v_point_time TIMESTAMP; v_start_time TIMESTAMP := TIMESTAMP '2026-01-13 00:00:00'; v_epoch INTEGER; v_start_epoch INTEGER := EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER; v_create_epoch INTEGER; v_hour NUMERIC; v_solar_factor NUMERIC; v_capacity_mw NUMERIC; v_raw_power NUMERIC; v_short_power NUMERIC; v_super_power NUMERIC; v_actual_power NUMERIC; v_tirr NUMERIC; v_ws NUMERIC; BEGIN FOR v_station IN SELECT id, station_name, installed_capacity FROM config_station WHERE station_code IN ('YMPV03','GBPV04') LOOP v_capacity_mw := ROUND((v_station.installed_capacity / 1000.0)::NUMERIC, 2); FOR v_idx IN 0..96 LOOP v_point_time := v_start_time + make_interval(mins => v_idx * 15); v_epoch := EXTRACT(EPOCH FROM v_point_time)::INTEGER; v_create_epoch := v_epoch + 120; v_hour := EXTRACT(HOUR FROM v_point_time)::NUMERIC + EXTRACT(MINUTE FROM v_point_time)::NUMERIC / 60; v_solar_factor := GREATEST(0, sin((v_hour - 7.0) / 11.0 * pi())); v_raw_power := v_capacity_mw * v_solar_factor * (0.86 + 0.06 * cos(v_idx / 8.0)); v_short_power := v_capacity_mw * v_solar_factor * (0.82 + 0.08 * sin(v_idx / 7.0)); v_super_power := v_capacity_mw * v_solar_factor * (0.78 + 0.10 * cos(v_idx / 6.0)); v_actual_power := v_capacity_mw * v_solar_factor * (0.74 + 0.07 * sin(v_idx / 5.0)); v_tirr := 900 * v_solar_factor; v_ws := 2.5 + 1.6 * sin(v_idx / 12.0); INSERT INTO data_short_term_power_forecast_raw (latitude, longitude, data_org_type, data_type, business_process_type, data_source, data_sourcegrab_id, data_sourcegrab_pattern_name, data_model_id, data_model_name, source_id, source_name, entity_id, entity_type, create_time, start_time, forecast_time, pre_days, lead_time, station_id, station_name, power) VALUES ('35.0654','118.3262','TIME_SERIES',1,1, 1, 1001, 'MOCK-RAW-15MIN-Q4', 1, 'EC-Model', 1, 'EC', v_station.id, '1', v_create_epoch, v_start_epoch, v_epoch, 1, v_idx, v_station.id, v_station.station_name, ROUND(v_raw_power::NUMERIC, 3)::TEXT); INSERT INTO data_short_term_power_forecast_report (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, power) VALUES (1001, 'MOCK-REPORT-15MIN-Q4', v_station.id, 1, v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 1, ROUND(v_short_power::NUMERIC, 3)::TEXT); INSERT INTO data_short_term_power_forecast_issued (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, power) VALUES (1001, 'MOCK-ISSUED-15MIN-Q4', v_station.id, 1, v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 1, ROUND((v_short_power * 1.015)::NUMERIC, 3)::TEXT); INSERT INTO data_super_short_term_forecast_report (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, pre_qrts, power) VALUES (1003, 'MOCK-SUPER-REPORT-15MIN-Q4', v_station.id, 1, v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 4, ROUND(v_super_power::NUMERIC, 3)::TEXT); INSERT INTO data_short_term_weather_forecast_report (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type, entity_name, create_time, start_time, forecast_time, predays, tcc, tsfc, psfc, rhsfc, tirr, ws_hub, wd_hub, ws) VALUES (1002, 'MOCK-WEATHER-REPORT-15MIN-Q4', v_station.id, 1, v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 1, ROUND((0.25 + 0.35 * v_solar_factor)::NUMERIC, 3)::TEXT, ROUND((18 + 8 * v_solar_factor)::NUMERIC, 2)::TEXT, '893.0', ROUND((42 + 8 * (1 - v_solar_factor))::NUMERIC, 2)::TEXT, ROUND(v_tirr::NUMERIC, 2)::TEXT, ROUND(v_ws::NUMERIC, 2)::TEXT, ROUND((270 + 25 * sin(v_idx / 8.0))::NUMERIC, 2)::TEXT, ROUND((v_ws * 0.88)::NUMERIC, 2)::TEXT); INSERT INTO data_booster_station_real (entity_id, entity_type, entity_name, create_time, record_time, power) VALUES (v_station.id, 1, v_station.station_name, v_create_epoch, v_epoch, ROUND(v_actual_power::NUMERIC, 3)::TEXT); INSERT INTO data_station_device_power_real (entity_id, entity_type, entity_name, device_id, device_type, device_name, create_time, record_time, power) VALUES (v_station.id, 1, v_station.station_name, v_station.id || '-INV-TOTAL', 2, '逆变器汇总', v_create_epoch, v_epoch, ROUND((v_actual_power * 0.985)::NUMERIC, 3)::TEXT); END LOOP; END LOOP; END $$; -- ----------------------------------------------------- -- Station configuration mock data -- ----------------------------------------------------- INSERT INTO config_station_pv_module (station_id, model, brand, rated_power, module_type, module_count, conversion_efficiency, temperature_coefficient, install_date, create_by, create_time) VALUES ((SELECT id FROM config_station WHERE station_code='BHWP01'),'JKM580N-72HL4','晶科能源',580,'双面',240000,'22.5%','-0.30%/℃','2023-04-01','admin',now()), ((SELECT id FROM config_station WHERE station_code='YMPV03'),'LR5-72HTH-590M','隆基绿能',590,'单面',180000,'22.8%','-0.29%/℃','2024-06-15','admin',now()), ((SELECT id FROM config_station WHERE station_code='GBPV04'),'Hi-MO 7 620W','隆基绿能',620,'双面',192000,'23.0%','-0.28%/℃','2024-02-01','admin',now()); INSERT INTO config_station_power_limit (station_id, limit_type, start_date, end_date, limit_percent, reason, approver, status, create_by, create_time) VALUES ((SELECT id FROM config_station WHERE station_code='BHWP01'),'调度限电','2026-06-01','2026-09-30','20%','电网调峰','省调','生效中','admin',now()), ((SELECT id FROM config_station WHERE station_code='HHWP02'),'检修限电','2026-07-15','2026-07-20','100%','年度检修','场站经理','待执行','admin',now()), ((SELECT id FROM config_station WHERE station_code='GBPV04'),'调度限电','2026-05-01','2026-10-31','15%','新能源消纳','省调','生效中','admin',now()); INSERT INTO config_station_device (station_id, device_name, device_type, brand, model, serial_number, status, install_date, create_by, create_time) VALUES ((SELECT id FROM config_station WHERE station_code='BHWP01'),'逆变器A1-001','组串式逆变器','华为','SUN2000-300KTL','HWT202304001','在线','2023-04-01','admin',now()), ((SELECT id FROM config_station WHERE station_code='BHWP01'),'箱变A-01','箱式变压器','特变电工','ZGS11-ZG-3150','TB202304001','在线','2023-04-01','admin',now()), ((SELECT id FROM config_station WHERE station_code='HHWP02'),'风机WT-01','直驱永磁','金风科技','GW175-8.0MW','JF202209001','在线','2022-09-15','admin',now()), ((SELECT id FROM config_station WHERE station_code='YMPV03'),'气象站-WS1','自动气象站','Campbell','CR1000X','CP202311001','在线','2023-11-15','admin',now()); INSERT INTO config_station_channel (station_id, channel_name, protocol, host, port, collect_interval, data_type, status, last_sync_time, create_by, create_time) VALUES ((SELECT id FROM config_station WHERE station_code='BHWP01'),'SCADA实时通道','Modbus TCP','192.168.100.10',502,'1s','时序','正常','2026-07-21 14:05','admin',now()), ((SELECT id FROM config_station WHERE station_code='HHWP02'),'风机OPC通道','OPC UA','opc.internal',4840,'1s','时序','正常','2026-07-21 14:03','admin',now()), ((SELECT id FROM config_station WHERE station_code='YMPV03'),'气象站数据通道','MQTT','mqtt-broker.internal',1883,'1min','JSON','正常','2026-07-21 14:05','admin',now()), ((SELECT id FROM config_station WHERE station_code='GBPV04'),'NWP-GFS通道','FTP','nwp-server.internal',21,'6h','GRIB2','延迟','2026-07-21 13:30','admin',now()); INSERT INTO config_station_point (station_id, device_id, device_name, point_name, unit, precision_value, measure_range, protocol, address, collect_interval, create_by, create_time) VALUES ((SELECT id FROM config_station WHERE station_code='BHWP01'),(SELECT id FROM config_station_device WHERE serial_number='HWT202304001'),'逆变器A1-001','直流功率','kW',2,'0-300','Modbus','40001','1s','admin',now()), ((SELECT id FROM config_station WHERE station_code='BHWP01'),(SELECT id FROM config_station_device WHERE serial_number='HWT202304001'),'逆变器A1-001','交流功率','kW',2,'0-300','Modbus','40003','1s','admin',now()), ((SELECT id FROM config_station WHERE station_code='YMPV03'),(SELECT id FROM config_station_device WHERE serial_number='CP202311001'),'气象站-WS1','环境温度','℃',1,'-40~80','Modbus','40201','1min','admin',now()), ((SELECT id FROM config_station WHERE station_code='HHWP02'),(SELECT id FROM config_station_device WHERE serial_number='JF202209001'),'风机WT-01','有功功率','kW',1,'0-8000','OPC UA','ns=2;s=WT.P','1s','admin',now()); COMMIT; -- 完成提示 SELECT '=== Mock 数据写入完成 ===' AS status, (SELECT COUNT(*) FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')) AS stations, (SELECT COUNT(*) FROM data_booster_station_real WHERE entity_id LIKE 'STA00%') AS booster_rows, (SELECT COUNT(*) FROM data_short_term_power_forecast_report WHERE entity_id LIKE 'STA00%') AS forecast_rows, (SELECT COUNT(*) FROM stats_assessment_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'))) AS stats_rows, (SELECT COUNT(*) FROM weather_alert_record WHERE id IN (90001,90002)) AS alert_rows, (SELECT COUNT(*) FROM config_station_device WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'))) AS station_devices, (SELECT COUNT(*) FROM config_station_point WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'))) AS station_points;