electricity_mock_new.sql 45 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794
  1. -- =============================================================================
  2. -- Electricity service mock data script (PostgreSQL)
  3. --
  4. -- Execute after electricity_init_new.sql. station_id values are BIGINT ids from
  5. -- config_station.id; STA001~STA004 are kept only as business entity_id codes.
  6. -- =============================================================================
  7. -- 电力业务前端 16 个新增页面 Mock 数据脚本(PostgreSQL)
  8. --
  9. -- 使用方法:
  10. -- psql -h <host> -U <user> -d electricity -f electricity_mock_new.sql
  11. -- 或在 IDE 数据源里整文件执行。
  12. --
  13. -- 设计:
  14. -- - 4 个场站:2 风电 (BHWP01/HHWP02) + 2 光伏 (YMPV03/GBPV04),entity_id 保留 STA001~STA004 作为业务实体编码
  15. -- - 时间窗口:当前时刻向前 24 小时 + 向后 24 小时,每小时一个采样点
  16. -- - 实时类表 (data_*_real) 只写历史 24h;预测类表 (forecast_*) 写整 49 点
  17. -- - 风电功率随风速近似 ws^3 缩放,光伏功率随辐照度+昼夜余弦缩放
  18. -- - 脚本幂等:每张表执行前先 DELETE 命中行
  19. --
  20. -- 涉及的 16 个页面 → 主要表:
  21. -- 主看板 / 数据监视 / 预测总览 -> config_station, data_short_term_power_*
  22. -- 气象地图 / 灾害预警地图 -> weather_area, weather_alert_*, typhoon_warning_*
  23. -- 多源/理论功率/发电量预测 -> data_short_term_power_forecast_*, model_prediction_tasks
  24. -- 电量统计 / 风光/多源/气象指标 -> stats_assessment_indicator, stats_report_indicator,
  25. -- stats_weather_indicator
  26. -- 上报数据查询 / 上报记录查询 -> data_short_term_power_forecast_report, data_source_grab,
  27. -- monitoring_send_record
  28. -- 设备状态监控 / 风机设备信息 -> data_booster_station_real, data_weather_station_real,
  29. -- data_station_device_power_real,
  30. -- data_station_device_weather_real
  31. -- =============================================================================
  32. BEGIN;
  33. -- -----------------------------------------------------------------------------
  34. -- 0. 清理旧的 mock 数据(按 STA001..STA004 / EAST01 命名空间过滤,避免误删真实数据)
  35. -- -----------------------------------------------------------------------------
  36. DELETE FROM data_booster_station_real WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  37. DELETE FROM data_weather_station_real WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  38. DELETE FROM data_station_device_power_real WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  39. DELETE FROM data_station_device_weather_real WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  40. DELETE FROM data_short_term_power_forecast_raw WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  41. DELETE FROM data_short_term_power_forecast_correct WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  42. DELETE FROM data_short_term_power_forecast_issued WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  43. DELETE FROM data_short_term_power_forecast_back WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  44. DELETE FROM data_short_term_power_forecast_report WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  45. DELETE FROM data_super_short_term_forecast_report WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  46. DELETE FROM data_short_term_weather_forecast_raw WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  47. DELETE FROM data_short_term_weather_forecast_report WHERE entity_id IN ('STA001','STA002','STA003','STA004');
  48. DELETE FROM stats_assessment_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  49. DELETE FROM stats_report_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  50. DELETE FROM stats_weather_indicator WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  51. DELETE FROM config_assessment_task WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  52. DELETE FROM config_assessment_indicator WHERE rule_id IN ('RULE001','RULE002');
  53. DELETE FROM config_assessment_rule WHERE id IN ('RULE001','RULE002');
  54. DELETE FROM model_prediction_tasks WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  55. DELETE FROM monitoring_send_record WHERE recipient_email = 'demo@electricity.local';
  56. DELETE FROM data_source_grab WHERE pattern_name LIKE 'MOCK-%';
  57. DELETE FROM config_station_point WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  58. DELETE FROM config_station_channel WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  59. DELETE FROM config_station_device WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  60. DELETE FROM config_station_power_limit WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  61. DELETE FROM config_station_pv_module WHERE station_id IN (SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04'));
  62. DELETE FROM weather_alert_area WHERE record_id IN (90001, 90002);
  63. DELETE FROM weather_alert_record WHERE id IN (90001, 90002);
  64. DELETE FROM typhoon_warning_path WHERE record_id = 90101;
  65. DELETE FROM typhoon_warning_record WHERE id = 90101;
  66. DELETE FROM typhoon WHERE id = 90101;
  67. DELETE FROM weather_warning_type WHERE id IN (9001, 9002, 9003, 9004);
  68. DELETE FROM weather_area WHERE area_id IN ('AREA-SD-QD','AREA-SD-WH','AREA-NM-BT','AREA-XJ-HM');
  69. DELETE FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04');
  70. DELETE FROM config_area WHERE id = 'EAST01';
  71. -- -----------------------------------------------------------------------------
  72. -- 1. 大区 + 4 个场站
  73. -- -----------------------------------------------------------------------------
  74. INSERT INTO config_area (id, area_code, area_name, superior_area_id, is_del,
  75. create_by, create_time, update_by, update_time)
  76. VALUES ('EAST01', 'EAST01', '华东大区', NULL, FALSE,
  77. 'admin', now(), 'admin', now());
  78. -- station_type: 0=风机, 1=光伏;is_enable 必填
  79. INSERT INTO config_station
  80. (station_name, station_code, superior_area_id, period_total,
  81. longitude, latitude, province_number, city_number, position,
  82. installed_capacity, grid_connected_capacity, fans_pv_panelquantity,
  83. elevation, operation_time, boosterstation_voltage_level,
  84. booster_station_longitude, boosterstation_latitude,
  85. responsible_person_name, responsible_person_phone, is_enable, push_status,
  86. subject_total, is_full_power, is_del,
  87. create_by, create_time, update_by, update_time,
  88. station_type, area_id)
  89. VALUES
  90. ('渤海一号风电场','BHWP01','EAST01',0,'121.4737','38.9087','370000','370200','山东省青岛市',
  91. 150000,148000,75,12.5,'2021-06-01','110kV','121.4737','38.9087','张工','13800000001',TRUE,1,
  92. 2,TRUE,FALSE,'admin',now(),'admin',now(),0,'EAST01'),
  93. ('黄海二号风电场','HHWP02','EAST01',0,'122.1108','37.5128','370000','371000','山东省威海市',
  94. 120000,118500,60,18.0,'2022-03-15','110kV','122.1108','37.5128','李工','13800000002',TRUE,1,
  95. 1,TRUE,FALSE,'admin',now(),'admin',now(),0,'EAST01'),
  96. ('沂蒙光伏电站','YMPV03','EAST01',0,'118.3262','35.0654','370000','371300','山东省临沂市',
  97. 80000,79200,160000,250.0,'2020-09-20','35kV','118.3262','35.0654','王工','13800000003',TRUE,1,
  98. 1,TRUE,FALSE,'admin',now(),'admin',now(),1,'EAST01'),
  99. ('戈壁光伏电站','GBPV04','EAST01',0,'93.5145','42.8194','650000','650500','新疆哈密',
  100. 200000,196000,400000,1100.0,'2019-12-01','220kV','93.5145','42.8194','陈工','13800000004',TRUE,1,
  101. 1,TRUE,FALSE,'admin',now(),'admin',now(),1,'EAST01');
  102. -- -----------------------------------------------------------------------------
  103. -- 2. 实时数据(升压站 / 气象站 / 设备功率 / 设备气象)
  104. -- 时间窗:过去 24 小时,1 小时一个点
  105. -- -----------------------------------------------------------------------------
  106. DO $$
  107. DECLARE
  108. v_station RECORD;
  109. v_offset_hour INTEGER;
  110. v_record_time TIMESTAMP;
  111. v_epoch INTEGER;
  112. v_create_epoch INTEGER;
  113. v_wind_speed NUMERIC;
  114. v_tirr NUMERIC;
  115. v_power NUMERIC;
  116. v_hour_of_day INTEGER;
  117. v_solar_factor NUMERIC;
  118. v_device_idx INTEGER;
  119. v_device_code VARCHAR(32);
  120. v_device_name VARCHAR(64);
  121. v_device_type INTEGER;
  122. BEGIN
  123. FOR v_station IN
  124. SELECT id, station_name, station_type, installed_capacity
  125. FROM config_station
  126. WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')
  127. LOOP
  128. FOR v_offset_hour IN 0..23 LOOP
  129. v_record_time := date_trunc('hour', now()) - make_interval(hours => v_offset_hour);
  130. v_epoch := EXTRACT(EPOCH FROM v_record_time)::INTEGER;
  131. v_create_epoch := v_epoch + 60;
  132. v_hour_of_day := EXTRACT(HOUR FROM v_record_time)::INTEGER;
  133. -- 风速:白天 5~12 m/s,夜里偏低;辐照:白天最大,夜里 0
  134. v_wind_speed := 6 + 4 * sin((v_hour_of_day::NUMERIC) / 24 * 2 * pi())
  135. + (random() - 0.5) * 1.5;
  136. IF v_wind_speed < 1 THEN v_wind_speed := 1; END IF;
  137. v_solar_factor := GREATEST(0, sin((v_hour_of_day - 6)::NUMERIC / 12 * pi()));
  138. v_tirr := v_solar_factor * (850 + (random() - 0.5) * 100);
  139. IF v_station.station_type = 0 THEN
  140. -- 风电 ≈ capacity * (ws/12)^3,截顶 95%
  141. v_power := v_station.installed_capacity
  142. * LEAST(0.95, POWER(LEAST(v_wind_speed, 12) / 12, 3))
  143. * (0.9 + random() * 0.2);
  144. ELSE
  145. v_power := v_station.installed_capacity * v_solar_factor * (0.8 + random() * 0.2);
  146. END IF;
  147. INSERT INTO data_booster_station_real
  148. (entity_id, entity_type, entity_name, create_time, record_time, power)
  149. VALUES (v_station.id, 1, v_station.station_name, v_create_epoch, v_epoch,
  150. ROUND(v_power::NUMERIC, 2)::TEXT);
  151. INSERT INTO data_weather_station_real
  152. (entity_id, entity_type, entity_name, create_time, record_time, wind_speed, tirr)
  153. VALUES (v_station.id, 1, v_station.station_name, v_create_epoch, v_epoch,
  154. ROUND(v_wind_speed::NUMERIC, 2)::TEXT, ROUND(v_tirr::NUMERIC, 2)::TEXT);
  155. -- 每个场站 3 台设备
  156. FOR v_device_idx IN 1..3 LOOP
  157. IF v_station.station_type = 0 THEN
  158. v_device_type := 1; -- 风机
  159. v_device_code := v_station.id || '-WTG' || LPAD(v_device_idx::TEXT, 2, '0');
  160. v_device_name := '风机 #' || v_device_idx;
  161. ELSE
  162. v_device_type := 2; -- 光伏板组
  163. v_device_code := v_station.id || '-INV' || LPAD(v_device_idx::TEXT, 2, '0');
  164. v_device_name := '逆变器 #' || v_device_idx;
  165. END IF;
  166. INSERT INTO data_station_device_power_real
  167. (entity_id, entity_type, entity_name, device_id, device_type, device_name,
  168. create_time, record_time, power)
  169. VALUES (v_station.id, 1, v_station.station_name,
  170. v_device_code, v_device_type, v_device_name,
  171. v_create_epoch, v_epoch,
  172. ROUND((v_power / 3 * (0.85 + random() * 0.3))::NUMERIC, 2)::TEXT);
  173. INSERT INTO data_station_device_weather_real
  174. (entity_id, entity_type, entity_name, device_id, device_type, device_name,
  175. create_time, record_time, wind_speed, tirr)
  176. VALUES (v_station.id, 1, v_station.station_name,
  177. v_device_code, v_device_type, v_device_name,
  178. v_create_epoch, v_epoch,
  179. ROUND((v_wind_speed + (random() - 0.5) * 0.5)::NUMERIC, 2)::TEXT,
  180. ROUND((v_tirr + (random() - 0.5) * 30)::NUMERIC, 2)::TEXT);
  181. END LOOP;
  182. END LOOP;
  183. END LOOP;
  184. END $$;
  185. -- -----------------------------------------------------------------------------
  186. -- 3. 短期功率预测(raw / correct / issued / back / report)
  187. -- 时间窗:过去 24h + 未来 24h(共 49 点),统一起报时间 = 今天 00:00
  188. -- -----------------------------------------------------------------------------
  189. DO $$
  190. DECLARE
  191. v_station RECORD;
  192. v_offset_hour INTEGER;
  193. v_forecast_time TIMESTAMP;
  194. v_start_time TIMESTAMP;
  195. v_create_time TIMESTAMP;
  196. v_fc_epoch INTEGER;
  197. v_st_epoch INTEGER;
  198. v_ct_epoch INTEGER;
  199. v_hour_of_day INTEGER;
  200. v_wind_speed NUMERIC;
  201. v_solar_factor NUMERIC;
  202. v_base_power NUMERIC;
  203. BEGIN
  204. v_start_time := date_trunc('day', now());
  205. v_create_time := v_start_time;
  206. v_st_epoch := EXTRACT(EPOCH FROM v_start_time)::INTEGER;
  207. v_ct_epoch := EXTRACT(EPOCH FROM v_create_time)::INTEGER;
  208. FOR v_station IN
  209. SELECT id, station_name, station_type, installed_capacity
  210. FROM config_station
  211. WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')
  212. LOOP
  213. FOR v_offset_hour IN -24..24 LOOP
  214. v_forecast_time := v_start_time + make_interval(hours => v_offset_hour);
  215. v_fc_epoch := EXTRACT(EPOCH FROM v_forecast_time)::INTEGER;
  216. v_hour_of_day := EXTRACT(HOUR FROM v_forecast_time)::INTEGER;
  217. v_wind_speed := 6 + 4 * sin((v_hour_of_day::NUMERIC) / 24 * 2 * pi());
  218. v_solar_factor := GREATEST(0, sin((v_hour_of_day - 6)::NUMERIC / 12 * pi()));
  219. IF v_station.station_type = 0 THEN
  220. v_base_power := v_station.installed_capacity
  221. * LEAST(0.95, POWER(LEAST(v_wind_speed, 12) / 12, 3));
  222. ELSE
  223. v_base_power := v_station.installed_capacity * v_solar_factor;
  224. END IF;
  225. -- 原始(带噪声)
  226. INSERT INTO data_short_term_power_forecast_raw
  227. (latitude, longitude, data_org_type, data_type, business_process_type,
  228. data_source, data_sourcegrab_id, data_sourcegrab_pattern_name,
  229. data_model_id, data_model_name, source_id, source_name,
  230. entity_id, entity_type, create_time, start_time, forecast_time,
  231. pre_days, lead_time, station_id, station_name, power)
  232. VALUES ('38.9087','121.4737','TIME_SERIES',1,1,
  233. 1, 1001, 'MOCK-RAW-PATTERN',
  234. 1, 'EC-Model', 1, 'EC',
  235. v_station.id, '1', v_ct_epoch, v_st_epoch, v_fc_epoch,
  236. 1, v_offset_hour, v_station.id, v_station.station_name,
  237. ROUND((v_base_power * (0.85 + random() * 0.3))::NUMERIC, 2)::TEXT);
  238. -- 修正
  239. INSERT INTO data_short_term_power_forecast_correct
  240. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  241. entity_name, create_time, start_time, forecast_time, predays, power)
  242. VALUES (1001, 'MOCK-CORRECT', v_station.id, 1, v_station.station_name,
  243. v_ct_epoch, v_st_epoch, v_fc_epoch, 1,
  244. ROUND((v_base_power * (0.92 + random() * 0.16))::NUMERIC, 2)::TEXT);
  245. -- 下发
  246. INSERT INTO data_short_term_power_forecast_issued
  247. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  248. entity_name, create_time, start_time, forecast_time, predays, power)
  249. VALUES (1001, 'MOCK-ISSUED', v_station.id, 1, v_station.station_name,
  250. v_ct_epoch, v_st_epoch, v_fc_epoch, 1,
  251. ROUND((v_base_power * (0.95 + random() * 0.10))::NUMERIC, 2)::TEXT);
  252. -- 回传
  253. INSERT INTO data_short_term_power_forecast_back
  254. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  255. entity_name, create_time, start_time, forecast_time, predays, power)
  256. VALUES (1001, 'MOCK-BACK', v_station.id, 1, v_station.station_name,
  257. v_ct_epoch, v_st_epoch, v_fc_epoch, 1,
  258. ROUND((v_base_power * (0.96 + random() * 0.08))::NUMERIC, 2)::TEXT);
  259. -- 上报(最终值,靠近实际)
  260. INSERT INTO data_short_term_power_forecast_report
  261. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  262. entity_name, create_time, start_time, forecast_time, predays, power)
  263. VALUES (1001, 'MOCK-REPORT', v_station.id, 1, v_station.station_name,
  264. v_ct_epoch, v_st_epoch, v_fc_epoch, 1,
  265. ROUND((v_base_power * (0.97 + random() * 0.06))::NUMERIC, 2)::TEXT);
  266. END LOOP;
  267. END LOOP;
  268. END $$;
  269. -- -----------------------------------------------------------------------------
  270. -- 4. 短期气象预测(raw / report) —— 字段较多,只填核心
  271. -- -----------------------------------------------------------------------------
  272. DO $$
  273. DECLARE
  274. v_station RECORD;
  275. v_offset_hour INTEGER;
  276. v_forecast_time TIMESTAMP;
  277. v_start_time TIMESTAMP;
  278. v_fc_epoch INTEGER;
  279. v_st_epoch INTEGER;
  280. v_ct_epoch INTEGER;
  281. v_hour_of_day INTEGER;
  282. v_wind_speed NUMERIC;
  283. v_solar_factor NUMERIC;
  284. v_tirr NUMERIC;
  285. v_tsfc NUMERIC;
  286. BEGIN
  287. v_start_time := date_trunc('day', now());
  288. v_st_epoch := EXTRACT(EPOCH FROM v_start_time)::INTEGER;
  289. v_ct_epoch := v_st_epoch;
  290. FOR v_station IN
  291. SELECT id, station_name FROM config_station
  292. WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')
  293. LOOP
  294. FOR v_offset_hour IN -24..24 LOOP
  295. v_forecast_time := v_start_time + make_interval(hours => v_offset_hour);
  296. v_fc_epoch := EXTRACT(EPOCH FROM v_forecast_time)::INTEGER;
  297. v_hour_of_day := EXTRACT(HOUR FROM v_forecast_time)::INTEGER;
  298. v_wind_speed := 6 + 4 * sin((v_hour_of_day::NUMERIC) / 24 * 2 * pi());
  299. v_solar_factor := GREATEST(0, sin((v_hour_of_day - 6)::NUMERIC / 12 * pi()));
  300. v_tirr := v_solar_factor * 850;
  301. v_tsfc := 18 + 8 * sin((v_hour_of_day - 6)::NUMERIC / 12 * pi())
  302. + (random() - 0.5) * 2;
  303. INSERT INTO data_short_term_weather_forecast_raw
  304. (latitude, longitude, data_org_type, data_type, business_process_type,
  305. data_source, data_sourcegrab_id, data_sourcegrab_pattern_name,
  306. data_model_id, data_model_name, source_id, source_name,
  307. entity_id, entity_type, create_time, start_time, forecast_time,
  308. pre_days, lead_time, station_id, station_name,
  309. tcc, tsfc, psfc, rhsfc, tirr, ws_hub, wd_hub, ws)
  310. VALUES ('38.9087','121.4737','TIME_SERIES',2,1,
  311. 1, 1002, 'MOCK-WEATHER-RAW',
  312. 2, 'WRF-Model', 2, 'WRF',
  313. v_station.id, '1', v_ct_epoch, v_st_epoch, v_fc_epoch,
  314. 1, v_offset_hour, v_station.id, v_station.station_name,
  315. ROUND((0.3 + random() * 0.5)::NUMERIC, 2)::TEXT,
  316. ROUND(v_tsfc::NUMERIC, 2)::TEXT,
  317. ROUND((1010 + (random() - 0.5) * 5)::NUMERIC, 2)::TEXT,
  318. ROUND((60 + (random() - 0.5) * 20)::NUMERIC, 1)::TEXT,
  319. ROUND(v_tirr::NUMERIC, 1)::TEXT,
  320. ROUND(v_wind_speed::NUMERIC, 2)::TEXT,
  321. ROUND((180 + (random() - 0.5) * 90)::NUMERIC, 1)::TEXT,
  322. ROUND((v_wind_speed * 0.9)::NUMERIC, 2)::TEXT);
  323. INSERT INTO data_short_term_weather_forecast_report
  324. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  325. entity_name, create_time, start_time, forecast_time, predays,
  326. tcc, tsfc, psfc, rhsfc, tirr, ws_hub, wd_hub, ws)
  327. VALUES (1002, 'MOCK-WEATHER-REPORT', v_station.id, 1, v_station.station_name,
  328. v_ct_epoch, v_st_epoch, v_fc_epoch, 1,
  329. ROUND((0.25 + random() * 0.45)::NUMERIC, 2)::TEXT,
  330. ROUND((v_tsfc + 0.3)::NUMERIC, 2)::TEXT,
  331. ROUND((1010 + (random() - 0.5) * 4)::NUMERIC, 2)::TEXT,
  332. ROUND((58 + (random() - 0.5) * 18)::NUMERIC, 1)::TEXT,
  333. ROUND((v_tirr * 0.97)::NUMERIC, 1)::TEXT,
  334. ROUND((v_wind_speed * 0.97)::NUMERIC, 2)::TEXT,
  335. ROUND((180 + (random() - 0.5) * 80)::NUMERIC, 1)::TEXT,
  336. ROUND((v_wind_speed * 0.88)::NUMERIC, 2)::TEXT);
  337. END LOOP;
  338. END LOOP;
  339. END $$;
  340. -- -----------------------------------------------------------------------------
  341. -- 5. 气象区域 + 灾害预警 + 台风
  342. -- -----------------------------------------------------------------------------
  343. INSERT INTO weather_warning_type (id, warning_type, data_source, remark)
  344. VALUES (9001,'台风',2,'热带气旋类预警'),
  345. (9002,'暴雨',1,'强降水预警'),
  346. (9003,'大风',1,'内陆/海面大风预警'),
  347. (9004,'雷电',1,'雷电活动预警');
  348. INSERT INTO weather_area
  349. (area_id, name_cn, district_cn, provcn_cn, nation_cn, continent_cn,
  350. city_id, city_name, parent_name, longitude, latitude, area_name, area_number)
  351. VALUES
  352. ('AREA-SD-QD','青岛站','市南区','山东','中国','亚洲',
  353. 370200,'青岛','山东','120.3826','36.0671','华东','EAST01'),
  354. ('AREA-SD-WH','威海站','环翠区','山东','中国','亚洲',
  355. 371000,'威海','山东','122.1201','37.5128','华东','EAST01'),
  356. ('AREA-NM-BT','包头站','昆都仑区','内蒙古','中国','亚洲',
  357. 150200,'包头','内蒙古','109.8403','40.6577','华北','NORTH01'),
  358. ('AREA-XJ-HM','哈密站','伊州区','新疆','中国','亚洲',
  359. 650500,'哈密','新疆','93.5145','42.8194','西北','WEST01');
  360. INSERT INTO weather_alert_record
  361. (id, alert_id, head_line, publish_date, description, longitude, latitude,
  362. type_code, title, effect_area, type_id, warning_type, level, level_name,
  363. create_date, update_date, end_date, publish_status, province, city, county,
  364. area_id, alert_info)
  365. VALUES
  366. (90001,'AL-2024-001','山东省气象台发布大风蓝色预警',
  367. now() - interval '2 hours','预计未来 12 小时山东半岛沿海有 7~8 级大风',
  368. '120.3826','36.0671','11B06','大风蓝色预警','青岛 威海 烟台',9003,'大风',1,'蓝色',
  369. now() - interval '2 hours', now() - interval '1 hours', now() + interval '12 hours',
  370. '发布','山东','青岛','市南区','AREA-SD-QD','请加强海上风电运维安全'),
  371. (90002,'AL-2024-002','新疆气象台发布雷电黄色预警',
  372. now() - interval '4 hours','预计未来 6 小时哈密东部出现雷阵雨',
  373. '93.5145','42.8194','11B07','雷电黄色预警','哈密',9004,'雷电',2,'黄色',
  374. now() - interval '4 hours', now() - interval '3 hours', now() + interval '6 hours',
  375. '发布','新疆','哈密','伊州区','AREA-XJ-HM','光伏板请做好防雷');
  376. INSERT INTO weather_alert_area
  377. (id, record_id, xh, area_id, area_name)
  378. VALUES (90011,90001,1,370200,'青岛市'),
  379. (90012,90001,2,371000,'威海市'),
  380. (90013,90002,1,650500,'哈密市');
  381. INSERT INTO typhoon (id, typhoon_number, typhoon_en, typhoon_cn, status,
  382. begin_date, end_date)
  383. VALUES (90101,'TY2418','PRAPIROON','派比安','active',
  384. now() - interval '3 days', NULL);
  385. INSERT INTO typhoon_warning_record
  386. (id, status, typhoon_cn, typhoon_en, typhoon_number, arrival_date, typhoon_level,
  387. longitude, latitude, center_pressure, max_wind_speed, future_move_speed,
  388. futuredirection, create_date, update_date, pub_time)
  389. VALUES (90101,'active','派比安','PRAPIROON','TY2418',
  390. to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), 'TY',
  391. '120.5','22.8',965,38.5,15.0,'西北',
  392. now() - interval '1 hours', now(), to_char(now(), 'YYYYMMDDHH24MI'));
  393. INSERT INTO typhoon_warning_path
  394. (id, record_id, xh, arrival_date, typhoon_level, latitude, longitude,
  395. center_pressure, max_wind_speed, future_move_speed, futuredirection,
  396. create_date, pub_time, status, typhoon_number)
  397. VALUES
  398. (90201,90101,1,to_char(now() - interval '12 hours','YYYY-MM-DD HH24:MI'),
  399. 'TS','19.5','118.6',985,25.0,20.0,'西北偏北',
  400. now() - interval '12 hours', to_char(now() - interval '12 hours','YYYYMMDDHH24MI'),'history','TY2418'),
  401. (90202,90101,2,to_char(now() - interval '6 hours','YYYY-MM-DD HH24:MI'),
  402. 'STS','21.0','119.5',975,32.0,18.0,'西北',
  403. now() - interval '6 hours', to_char(now() - interval '6 hours','YYYYMMDDHH24MI'),'history','TY2418'),
  404. (90203,90101,3,to_char(now(),'YYYY-MM-DD HH24:MI'),
  405. 'TY','22.8','120.5',965,38.5,15.0,'西北',
  406. now(), to_char(now(),'YYYYMMDDHH24MI'),'current','TY2418'),
  407. (90204,90101,4,to_char(now() + interval '12 hours','YYYY-MM-DD HH24:MI'),
  408. 'TY','24.5','121.6',960,40.0,15.0,'西北',
  409. now(), to_char(now() + interval '12 hours','YYYYMMDDHH24MI'),'forecast','TY2418'),
  410. (90205,90101,5,to_char(now() + interval '24 hours','YYYY-MM-DD HH24:MI'),
  411. 'STS','26.7','122.8',975,32.0,14.0,'北',
  412. now(), to_char(now() + interval '24 hours','YYYYMMDDHH24MI'),'forecast','TY2418');
  413. -- -----------------------------------------------------------------------------
  414. -- 6. 数据源抓取配置 + 预测任务 + 告警发送记录
  415. -- -----------------------------------------------------------------------------
  416. INSERT INTO data_source_grab
  417. (data_source_id, data_organization_type, data_type, data_content_type,
  418. remote_directory, earliest_start_time, longest_forecast_period, push_time,
  419. file_match_pattern, is_del, create_by, create_time, update_by, update_time,
  420. pattern_name, batch_of_day, threshold, standard_quantity,
  421. shortest_parsing_time, data_model_id, data_model_name)
  422. VALUES
  423. (1,1,1,1,'/data/ec/short_term/power', now() - interval '30 days', 24,
  424. now(),'POW_*.txt',FALSE,'admin',now(),'admin',now(),
  425. 'MOCK-EC-POWER',4,2000,2000,60,1,'EC-Model'),
  426. (1,1,2,1,'/data/ec/short_term/weather', now() - interval '30 days', 72,
  427. now(),'WTH_*.grb',FALSE,'admin',now(),'admin',now(),
  428. 'MOCK-EC-WEATHER',4,2000,2000,60,2,'EC-Weather-Model'),
  429. (2,1,8,1,'/data/wrf/ultra/power', now() - interval '30 days', 4,
  430. now(),'USP_*.csv',FALSE,'admin',now(),'admin',now(),
  431. 'MOCK-WRF-ULTRA',96,500,500,15,3,'WRF-Ultra');
  432. INSERT INTO model_prediction_tasks
  433. (task_name, station_id, model_version_id, version_source, prediction_type,
  434. target_time, status, result_path, created_at, executed_at)
  435. VALUES
  436. ('渤海一号-短期-T+24',(SELECT id FROM config_station WHERE station_code = 'BHWP01'),1001,'station','short_term',
  437. date_trunc('day', now()) + interval '1 day','SUCCESS','/result/STA001/short.csv',
  438. now() - interval '2 hours', now() - interval '1 hours'),
  439. ('黄海二号-短期-T+24',(SELECT id FROM config_station WHERE station_code = 'HHWP02'),1001,'station','short_term',
  440. date_trunc('day', now()) + interval '1 day','SUCCESS','/result/STA002/short.csv',
  441. now() - interval '2 hours', now() - interval '1 hours'),
  442. ('沂蒙光伏-超短期-T+15min',(SELECT id FROM config_station WHERE station_code = 'YMPV03'),1002,'global','ultra_short',
  443. now() + interval '15 minutes','RUNNING',NULL,
  444. now() - interval '5 minutes', now() - interval '4 minutes'),
  445. ('戈壁光伏-短期-T+24',(SELECT id FROM config_station WHERE station_code = 'GBPV04'),1001,'area','short_term',
  446. date_trunc('day', now()) + interval '1 day','PENDING',NULL,
  447. now() - interval '10 minutes',NULL);
  448. INSERT INTO monitoring_send_record
  449. (check_record_id, send_status, recipient_email, email_info, create_by, create_time)
  450. VALUES
  451. (5001, 1,'demo@electricity.local','短期功率上报成功,行数 96/96','system', now() - interval '50 minutes'),
  452. (5002,-1,'demo@electricity.local','超短期功率上报失败:连接超时','system', now() - interval '20 minutes'),
  453. (5003, 1,'demo@electricity.local','短期气象上报成功,行数 96/96','system', now() - interval '5 minutes');
  454. -- -----------------------------------------------------------------------------
  455. -- 7. 考核规则 / 任务 / 指标统计
  456. -- -----------------------------------------------------------------------------
  457. INSERT INTO config_assessment_rule (id, rule_name, province_number,
  458. del_flag, create_by, create_time, update_by, update_time)
  459. VALUES ('RULE001','短期功率预测准确率细则','370000',FALSE,'admin',now(),'admin',now()),
  460. ('RULE002','超短期功率预测准确率细则','370000',FALSE,'admin',now(),'admin',now());
  461. INSERT INTO config_assessment_indicator
  462. (id, rule_id, indicator_key, formula, limit_value, assess_description, enable_flag,
  463. del_flag, create_by, create_time, update_by, update_time)
  464. VALUES
  465. ('IND001','RULE001','accuracy','1 - sqrt(sum((actual-pred)^2)/n) / capacity', 0.85,
  466. '日准确率达标值', TRUE, FALSE, 'admin', now(), 'admin', now()),
  467. ('IND002','RULE001','rmse','sqrt(sum((actual-pred)^2)/n)', 0.20,
  468. '均方根误差', TRUE, FALSE, 'admin', now(), 'admin', now()),
  469. ('IND003','RULE002','accuracy','1 - sqrt(sum((actual-pred)^2)/n) / capacity', 0.90,
  470. '超短期日准确率', TRUE, FALSE, 'admin', now(), 'admin', now());
  471. INSERT INTO config_assessment_task (id, station_id, rule_id, enable_flag, del_flag,
  472. create_by, create_time, update_by, update_time)
  473. VALUES ('TSK001',(SELECT id FROM config_station WHERE station_code = 'BHWP01'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()),
  474. ('TSK002',(SELECT id FROM config_station WHERE station_code = 'HHWP02'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()),
  475. ('TSK003',(SELECT id FROM config_station WHERE station_code = 'YMPV03'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()),
  476. ('TSK004',(SELECT id FROM config_station WHERE station_code = 'GBPV04'),'RULE001',TRUE,FALSE,'admin',now(),'admin',now()),
  477. ('TSK005',(SELECT id FROM config_station WHERE station_code = 'BHWP01'),'RULE002',TRUE,FALSE,'admin',now(),'admin',now()),
  478. ('TSK006',(SELECT id FROM config_station WHERE station_code = 'HHWP02'),'RULE002',TRUE,FALSE,'admin',now(),'admin',now());
  479. -- 每个场站近 7 天的考核指标统计
  480. DO $$
  481. DECLARE
  482. v_station_id BIGINT;
  483. v_rule_id VARCHAR(32);
  484. v_offset_day INTEGER;
  485. v_accuracy NUMERIC;
  486. v_rmse NUMERIC;
  487. BEGIN
  488. FOR v_station_id IN SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP
  489. FOR v_rule_id IN SELECT unnest(ARRAY['RULE001','RULE002']) LOOP
  490. FOR v_offset_day IN 0..6 LOOP
  491. v_accuracy := 0.85 + random() * 0.10;
  492. v_rmse := 0.05 + random() * 0.10;
  493. INSERT INTO stats_assessment_indicator
  494. (id, station_id, rule_id, time_type, data_source, indicator_value,
  495. create_by, create_time, update_by, update_time, record_time,
  496. business_process_time, model_id)
  497. VALUES (md5(v_station_id || v_rule_id || v_offset_day::TEXT),
  498. v_station_id, v_rule_id, 1, '1',
  499. jsonb_build_object('accuracy', ROUND(v_accuracy::NUMERIC, 4),
  500. 'rmse', ROUND(v_rmse::NUMERIC, 4),
  501. 'mae', ROUND((v_rmse * 0.8)::NUMERIC, 4),
  502. 'qualified', v_accuracy >= 0.85),
  503. 'system', now(),'system', now(),
  504. (CURRENT_DATE - v_offset_day)::DATE, 1, 1);
  505. END LOOP;
  506. END LOOP;
  507. END LOOP;
  508. END $$;
  509. -- 报表指标(每场站 7 天日指标)
  510. DO $$
  511. DECLARE
  512. v_station_id BIGINT;
  513. v_offset_day INTEGER;
  514. BEGIN
  515. FOR v_station_id IN SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP
  516. FOR v_offset_day IN 0..6 LOOP
  517. INSERT INTO stats_report_indicator
  518. (id, station_id, site_id, time_type, indicator_value,
  519. create_by, create_time, update_by, update_time, record_time)
  520. VALUES (md5('rep' || v_station_id || v_offset_day::TEXT),
  521. v_station_id,'SITE-DEMO',1,
  522. jsonb_build_object('total_generation', ROUND((300000 + random() * 800000)::NUMERIC, 2),
  523. 'theoretical_power', ROUND((350000 + random() * 800000)::NUMERIC, 2),
  524. 'utilization_hours', ROUND((4 + random() * 6)::NUMERIC, 2),
  525. 'curtailment', ROUND((random() * 20000)::NUMERIC, 2)),
  526. 'system', now(),'system', now(),
  527. (CURRENT_DATE - v_offset_day)::DATE);
  528. END LOOP;
  529. END LOOP;
  530. END $$;
  531. -- 气象指标(每场站 7 天)
  532. DO $$
  533. DECLARE
  534. v_station_id BIGINT;
  535. v_offset_day INTEGER;
  536. BEGIN
  537. FOR v_station_id IN SELECT id FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04') LOOP
  538. FOR v_offset_day IN 0..6 LOOP
  539. INSERT INTO stats_weather_indicator
  540. (station_id, time_type, data_source, indicator_val,
  541. create_by, create_time, update_by, update_time, record_time,
  542. business_process_times, model_id)
  543. VALUES (v_station_id, 1, '1',
  544. jsonb_build_object('wind_speed_mae', ROUND((0.8 + random() * 0.6)::NUMERIC, 3),
  545. 'wind_speed_rmse', ROUND((1.0 + random() * 0.8)::NUMERIC, 3),
  546. 'tirr_mae', ROUND((40 + random() * 30)::NUMERIC, 2),
  547. 'temperature_mae', ROUND((0.5 + random() * 0.8)::NUMERIC, 3),
  548. 'forecast_qualified', random() > 0.2),
  549. 'system', now(),'system', now(),
  550. (CURRENT_DATE - v_offset_day)::DATE, 1, 1);
  551. END LOOP;
  552. END LOOP;
  553. END $$;
  554. -- -----------------------------------------------------------------------------
  555. -- 8. 上报数据查询专用 mock:15 分钟粒度,覆盖 2026-01-13 全天
  556. -- -----------------------------------------------------------------------------
  557. DELETE FROM data_short_term_power_forecast_report
  558. WHERE entity_id IN ('STA003','STA004')
  559. AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  560. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  561. DELETE FROM data_short_term_power_forecast_raw
  562. WHERE entity_id IN ('STA003','STA004')
  563. AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  564. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  565. DELETE FROM data_short_term_power_forecast_issued
  566. WHERE entity_id IN ('STA003','STA004')
  567. AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  568. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  569. DELETE FROM data_super_short_term_forecast_report
  570. WHERE entity_id IN ('STA003','STA004')
  571. AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  572. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  573. DELETE FROM data_short_term_weather_forecast_report
  574. WHERE entity_id IN ('STA003','STA004')
  575. AND forecast_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  576. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  577. DELETE FROM data_booster_station_real
  578. WHERE entity_id IN ('STA003','STA004')
  579. AND record_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  580. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  581. DELETE FROM data_station_device_power_real
  582. WHERE entity_id IN ('STA003','STA004')
  583. AND record_time BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER
  584. AND EXTRACT(EPOCH FROM TIMESTAMP '2026-01-14 00:00:00')::INTEGER;
  585. DO $$
  586. DECLARE
  587. v_station RECORD;
  588. v_idx INTEGER;
  589. v_point_time TIMESTAMP;
  590. v_start_time TIMESTAMP := TIMESTAMP '2026-01-13 00:00:00';
  591. v_epoch INTEGER;
  592. v_start_epoch INTEGER := EXTRACT(EPOCH FROM TIMESTAMP '2026-01-13 00:00:00')::INTEGER;
  593. v_create_epoch INTEGER;
  594. v_hour NUMERIC;
  595. v_solar_factor NUMERIC;
  596. v_capacity_mw NUMERIC;
  597. v_raw_power NUMERIC;
  598. v_short_power NUMERIC;
  599. v_super_power NUMERIC;
  600. v_actual_power NUMERIC;
  601. v_tirr NUMERIC;
  602. v_ws NUMERIC;
  603. BEGIN
  604. FOR v_station IN
  605. SELECT id, station_name, installed_capacity
  606. FROM config_station
  607. WHERE station_code IN ('YMPV03','GBPV04')
  608. LOOP
  609. v_capacity_mw := ROUND((v_station.installed_capacity / 1000.0)::NUMERIC, 2);
  610. FOR v_idx IN 0..96 LOOP
  611. v_point_time := v_start_time + make_interval(mins => v_idx * 15);
  612. v_epoch := EXTRACT(EPOCH FROM v_point_time)::INTEGER;
  613. v_create_epoch := v_epoch + 120;
  614. v_hour := EXTRACT(HOUR FROM v_point_time)::NUMERIC + EXTRACT(MINUTE FROM v_point_time)::NUMERIC / 60;
  615. v_solar_factor := GREATEST(0, sin((v_hour - 7.0) / 11.0 * pi()));
  616. v_raw_power := v_capacity_mw * v_solar_factor * (0.86 + 0.06 * cos(v_idx / 8.0));
  617. v_short_power := v_capacity_mw * v_solar_factor * (0.82 + 0.08 * sin(v_idx / 7.0));
  618. v_super_power := v_capacity_mw * v_solar_factor * (0.78 + 0.10 * cos(v_idx / 6.0));
  619. v_actual_power := v_capacity_mw * v_solar_factor * (0.74 + 0.07 * sin(v_idx / 5.0));
  620. v_tirr := 900 * v_solar_factor;
  621. v_ws := 2.5 + 1.6 * sin(v_idx / 12.0);
  622. INSERT INTO data_short_term_power_forecast_raw
  623. (latitude, longitude, data_org_type, data_type, business_process_type,
  624. data_source, data_sourcegrab_id, data_sourcegrab_pattern_name,
  625. data_model_id, data_model_name, source_id, source_name,
  626. entity_id, entity_type, create_time, start_time, forecast_time,
  627. pre_days, lead_time, station_id, station_name, power)
  628. VALUES ('35.0654','118.3262','TIME_SERIES',1,1,
  629. 1, 1001, 'MOCK-RAW-15MIN-Q4',
  630. 1, 'EC-Model', 1, 'EC',
  631. v_station.id, '1', v_create_epoch, v_start_epoch, v_epoch,
  632. 1, v_idx, v_station.id, v_station.station_name,
  633. ROUND(v_raw_power::NUMERIC, 3)::TEXT);
  634. INSERT INTO data_short_term_power_forecast_report
  635. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  636. entity_name, create_time, start_time, forecast_time, predays, power)
  637. VALUES (1001, 'MOCK-REPORT-15MIN-Q4', v_station.id, 1,
  638. v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 1,
  639. ROUND(v_short_power::NUMERIC, 3)::TEXT);
  640. INSERT INTO data_short_term_power_forecast_issued
  641. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  642. entity_name, create_time, start_time, forecast_time, predays, power)
  643. VALUES (1001, 'MOCK-ISSUED-15MIN-Q4', v_station.id, 1,
  644. v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 1,
  645. ROUND((v_short_power * 1.015)::NUMERIC, 3)::TEXT);
  646. INSERT INTO data_super_short_term_forecast_report
  647. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  648. entity_name, create_time, start_time, forecast_time, pre_qrts, power)
  649. VALUES (1003, 'MOCK-SUPER-REPORT-15MIN-Q4', v_station.id, 1,
  650. v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 4,
  651. ROUND(v_super_power::NUMERIC, 3)::TEXT);
  652. INSERT INTO data_short_term_weather_forecast_report
  653. (data_sourcegrab_id, data_sourcegrab_pattern_name, entity_id, entity_type,
  654. entity_name, create_time, start_time, forecast_time, predays,
  655. tcc, tsfc, psfc, rhsfc, tirr, ws_hub, wd_hub, ws)
  656. VALUES (1002, 'MOCK-WEATHER-REPORT-15MIN-Q4', v_station.id, 1,
  657. v_station.station_name, v_create_epoch, v_start_epoch, v_epoch, 1,
  658. ROUND((0.25 + 0.35 * v_solar_factor)::NUMERIC, 3)::TEXT,
  659. ROUND((18 + 8 * v_solar_factor)::NUMERIC, 2)::TEXT,
  660. '893.0',
  661. ROUND((42 + 8 * (1 - v_solar_factor))::NUMERIC, 2)::TEXT,
  662. ROUND(v_tirr::NUMERIC, 2)::TEXT,
  663. ROUND(v_ws::NUMERIC, 2)::TEXT,
  664. ROUND((270 + 25 * sin(v_idx / 8.0))::NUMERIC, 2)::TEXT,
  665. ROUND((v_ws * 0.88)::NUMERIC, 2)::TEXT);
  666. INSERT INTO data_booster_station_real
  667. (entity_id, entity_type, entity_name, create_time, record_time, power)
  668. VALUES (v_station.id, 1, v_station.station_name, v_create_epoch, v_epoch,
  669. ROUND(v_actual_power::NUMERIC, 3)::TEXT);
  670. INSERT INTO data_station_device_power_real
  671. (entity_id, entity_type, entity_name, device_id, device_type, device_name,
  672. create_time, record_time, power)
  673. VALUES (v_station.id, 1, v_station.station_name,
  674. v_station.id || '-INV-TOTAL', 2, '逆变器汇总',
  675. v_create_epoch, v_epoch,
  676. ROUND((v_actual_power * 0.985)::NUMERIC, 3)::TEXT);
  677. END LOOP;
  678. END LOOP;
  679. END $$;
  680. -- -----------------------------------------------------
  681. -- Station configuration mock data
  682. -- -----------------------------------------------------
  683. 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)
  684. VALUES
  685. ((SELECT id FROM config_station WHERE station_code='BHWP01'),'JKM580N-72HL4','晶科能源',580,'双面',240000,'22.5%','-0.30%/℃','2023-04-01','admin',now()),
  686. ((SELECT id FROM config_station WHERE station_code='YMPV03'),'LR5-72HTH-590M','隆基绿能',590,'单面',180000,'22.8%','-0.29%/℃','2024-06-15','admin',now()),
  687. ((SELECT id FROM config_station WHERE station_code='GBPV04'),'Hi-MO 7 620W','隆基绿能',620,'双面',192000,'23.0%','-0.28%/℃','2024-02-01','admin',now());
  688. INSERT INTO config_station_power_limit (station_id, limit_type, start_date, end_date, limit_percent, reason, approver, status, create_by, create_time)
  689. VALUES
  690. ((SELECT id FROM config_station WHERE station_code='BHWP01'),'调度限电','2026-06-01','2026-09-30','20%','电网调峰','省调','生效中','admin',now()),
  691. ((SELECT id FROM config_station WHERE station_code='HHWP02'),'检修限电','2026-07-15','2026-07-20','100%','年度检修','场站经理','待执行','admin',now()),
  692. ((SELECT id FROM config_station WHERE station_code='GBPV04'),'调度限电','2026-05-01','2026-10-31','15%','新能源消纳','省调','生效中','admin',now());
  693. INSERT INTO config_station_device (station_id, device_name, device_type, brand, model, serial_number, status, install_date, create_by, create_time)
  694. VALUES
  695. ((SELECT id FROM config_station WHERE station_code='BHWP01'),'逆变器A1-001','组串式逆变器','华为','SUN2000-300KTL','HWT202304001','在线','2023-04-01','admin',now()),
  696. ((SELECT id FROM config_station WHERE station_code='BHWP01'),'箱变A-01','箱式变压器','特变电工','ZGS11-ZG-3150','TB202304001','在线','2023-04-01','admin',now()),
  697. ((SELECT id FROM config_station WHERE station_code='HHWP02'),'风机WT-01','直驱永磁','金风科技','GW175-8.0MW','JF202209001','在线','2022-09-15','admin',now()),
  698. ((SELECT id FROM config_station WHERE station_code='YMPV03'),'气象站-WS1','自动气象站','Campbell','CR1000X','CP202311001','在线','2023-11-15','admin',now());
  699. INSERT INTO config_station_channel (station_id, channel_name, protocol, host, port, collect_interval, data_type, status, last_sync_time, create_by, create_time)
  700. VALUES
  701. ((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()),
  702. ((SELECT id FROM config_station WHERE station_code='HHWP02'),'风机OPC通道','OPC UA','opc.internal',4840,'1s','时序','正常','2026-07-21 14:03','admin',now()),
  703. ((SELECT id FROM config_station WHERE station_code='YMPV03'),'气象站数据通道','MQTT','mqtt-broker.internal',1883,'1min','JSON','正常','2026-07-21 14:05','admin',now()),
  704. ((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());
  705. 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)
  706. VALUES
  707. ((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()),
  708. ((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()),
  709. ((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()),
  710. ((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());
  711. COMMIT;
  712. -- 完成提示
  713. SELECT '=== Mock 数据写入完成 ===' AS status,
  714. (SELECT COUNT(*) FROM config_station WHERE station_code IN ('BHWP01','HHWP02','YMPV03','GBPV04')) AS stations,
  715. (SELECT COUNT(*) FROM data_booster_station_real WHERE entity_id LIKE 'STA00%') AS booster_rows,
  716. (SELECT COUNT(*) FROM data_short_term_power_forecast_report WHERE entity_id LIKE 'STA00%') AS forecast_rows,
  717. (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,
  718. (SELECT COUNT(*) FROM weather_alert_record WHERE id IN (90001,90002)) AS alert_rows,
  719. (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,
  720. (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;