| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794 |
- -- =============================================================================
- -- 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 <host> -U <user> -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;
|