forecast/doc/数据库脚本/backend_ddl_2026112.sql

-- 创建序列
CREATE SEQUENCE IF NOT EXISTS data_ultra_short_term_power_forecasting_id_seq;

DROP TABLE IF EXISTS data_ultra_short_term_power_forecasting;
-- 建表语句
CREATE TABLE data_ultra_short_term_power_forecasting (
    _id BIGINT NOT NULL DEFAULT nextval('data_ultra_short_term_power_forecasting_id_seq'::regclass),
    data_sourcegrab_id INTEGER NOT NULL,
    data_sourcegrab_pattern_name VARCHAR(255) NOT NULL,
    entity_id VARCHAR(255) NOT NULL,
    entity_type INTEGER NOT NULL,
    entity_name VARCHAR(255),
    create_time INTEGER NOT NULL,
    start_time INTEGER NOT NULL,
    forecast_time INTEGER NOT NULL,
    power VARCHAR(255),
    power15 VARCHAR(255),
    power30 VARCHAR(255),
    power45 VARCHAR(255),
    power60 VARCHAR(255),
    power75 VARCHAR(255),
    power90 VARCHAR(255),
    power105 VARCHAR(255),
    power120 VARCHAR(255),
    power135 VARCHAR(255),
    power150 VARCHAR(255),
    power165 VARCHAR(255),
    power180 VARCHAR(255),
    power195 VARCHAR(255),
    power210 VARCHAR(255),
    power225 VARCHAR(255),
    power240 VARCHAR(255),
    CONSTRAINT data_ultra_short_term_power_forecasting_pkey PRIMARY KEY (_id)
);

-- 表注释
COMMENT ON TABLE "public"."data_ultra_short_term_power_forecasting" IS '超短期功率预测数据表';

-- 字段注释
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."_id" IS '唯一标识';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."data_sourcegrab_id" IS '数据源抓取ID';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."data_sourcegrab_pattern_name" IS '数据抓取模式名称';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."entity_id" IS '实体ID';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."entity_type" IS '实体类型(场站/交易主体/省域)';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."entity_name" IS '实体名称';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."start_time" IS '起报时间（日期）';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."forecast_time" IS '起报时间（日期）';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power" IS '实发功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power15" IS 't+15min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power30" IS 't+30min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power45" IS 't+45min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power60" IS 't+60min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power75" IS 't+75min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power90" IS 't+90min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power105" IS 't+105min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power120" IS 't+120min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power135" IS 't+135min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power150" IS 't+150min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power165" IS 't+165min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power180" IS 't+180min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power195" IS 't+195min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power210" IS 't+210min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power225" IS 't+225min预测功率';
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."power240" IS 't+240min预测功率';

-- 超短期功率预报时间
ALTER TABLE "public"."data_ultra_short_term_power_forecasting" ADD COLUMN IF NOT EXISTS "forecast_time" integer;
COMMENT ON COLUMN "public"."data_ultra_short_term_power_forecasting"."forecast_time" IS '预报时间';

CREATE SEQUENCE IF NOT EXISTS "public"."data_medium_term_power_forecast_raw__id_seq";

DROP TABLE IF EXISTS "public"."data_medium_term_power_forecast_raw";
CREATE TABLE "public"."data_medium_term_power_forecast_raw" (
  "_id" integer NOT NULL DEFAULT nextval(
    'data_medium_term_power_forecast_raw__id_seq'::regclass
  ),
  "latitude" character varying(255),
  "longitude" character varying(255),
  "data_org_type" character varying(255) NOT NULL,
  "data_type" integer NOT NULL,
  "business_process_type" integer NOT NULL,
  "data_source" integer NOT NULL,
  "data_sourcegrab_id" integer NOT NULL,
  "data_sourcegrab_pattern_name" character varying(255) NOT NULL,
  "data_model_id" integer NOT NULL,
  "data_model_name" character varying(255) NOT NULL,
  "source_id" integer NOT NULL,
  "source_name" character varying(255) NOT NULL,
  "entity_id" character varying(255) NOT NULL,
  "entity_type" character varying(255) NOT NULL,
  "create_time" integer NOT NULL,
  "start_time" integer NOT NULL,
  "forecast_time" integer NOT NULL,
  "pre_days" integer NOT NULL,
  "lead_time" integer NOT NULL,
  "station_id" bigint,
  "station_name" character varying(255),
  "subject_id" integer,
  "subject_name" character varying(255),
  "machine_id" character varying(255),
  "machine_name" character varying(255),
  "analyze_his_id" integer,
  "archive_record_id" integer,
  "power" character varying(255)
);

COMMENT ON TABLE "public"."data_medium_term_power_forecast_raw" IS '中期功率原始数据表';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."_id" IS '唯一标识';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."latitude" IS '经度';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."longitude" IS '纬度';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_org_type" IS '数据种类:单点时间序列';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_type" IS '数据类型:短期气象';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."business_process_type" IS '数据业务类型:原始';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_source" IS '数据来源';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_sourcegrab_id" IS '数据源抓取ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_sourcegrab_pattern_name" IS '数据抓取模式名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_model_id" IS '数据模型ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."data_model_name" IS '数据模型名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."source_id" IS '数据源ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."source_name" IS '数据源名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."entity_id" IS '实体ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."entity_type" IS '实体类型';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."start_time" IS '起报时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."forecast_time" IS '预测时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."pre_days" IS '预报天数';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."lead_time" IS '预报时效';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."station_id" IS '场站ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."station_name" IS '场站名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."subject_id" IS '交易主题ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."subject_name" IS '交易主题名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."machine_id" IS '风机ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."machine_name" IS '风机名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."analyze_his_id" IS '文件解析ID:文件解析时存在';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."archive_record_id" IS '文件归档ID:文件解析时存在';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_raw"."power" IS '预测功率';

CREATE SEQUENCE IF NOT EXISTS "public"."data_medium_term_power_forecast_issued__id_seq";

DROP TABLE IF EXISTS "public"."data_medium_term_power_forecast_issued";
CREATE TABLE "public"."data_medium_term_power_forecast_issued" (
  "_id" integer NOT NULL DEFAULT nextval(
    'data_medium_term_power_forecast_issued__id_seq'::regclass
  ),
  "data_sourcegrab_id" integer NOT NULL,
  "data_sourcegrab_pattern_name" character varying(255) NOT NULL,
  "entity_id" character varying(255) NOT NULL,
  "entity_type" integer NOT NULL,
  "entity_name" character varying(255),
  "create_time" integer NOT NULL,
  "start_time" integer NOT NULL,
  "forecast_time" integer NOT NULL,
  "predays" integer NOT NULL,
  "power" character varying(255)
);

COMMENT ON TABLE "public"."data_medium_term_power_forecast_issued" IS '中期功率下发数据表';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."_id" IS '唯一标识';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."data_sourcegrab_id" IS '数据源抓取ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."data_sourcegrab_pattern_name" IS '数据抓取模式名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."entity_id" IS '实体ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."entity_type" IS '实体类型';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."entity_name" IS '实体名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."start_time" IS '起报时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."forecast_time" IS '预测时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."predays" IS '预报天数';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_issued"."power" IS '预测功率';


CREATE SEQUENCE IF NOT EXISTS "public"."data_medium_term_power_forecast_correct__id_seq";
DROP TABLE IF EXISTS "public"."data_medium_term_power_forecast_correct";
CREATE TABLE "public"."data_medium_term_power_forecast_correct" (
  "_id" integer NOT NULL DEFAULT nextval(
    'data_medium_term_power_forecast_correct__id_seq'::regclass
  ),
  "data_sourcegrab_id" integer NOT NULL,
  "data_sourcegrab_pattern_name" character varying(255) NOT NULL,
  "entity_id" character varying(255) NOT NULL,
  "entity_type" integer NOT NULL,
  "entity_name" character varying(255),
  "create_time" integer NOT NULL,
  "start_time" integer NOT NULL,
  "forecast_time" integer NOT NULL,
  "predays" integer NOT NULL,
  "power" character varying(255)
);

COMMENT ON TABLE "public"."data_medium_term_power_forecast_correct" IS '中期功率修正数据表';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."_id" IS '唯一标识';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."data_sourcegrab_id" IS '数据源抓取ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."data_sourcegrab_pattern_name" IS '数据抓取模式名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."entity_id" IS '实体ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."entity_type" IS '实体类型';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."entity_name" IS '实体名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."start_time" IS '起报时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."forecast_time" IS '预测时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."predays" IS '预报天数';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_correct"."power" IS '预测功率';


CREATE SEQUENCE IF NOT EXISTS "public"."data_medium_term_power_forecast_report__id_seq";
DROP TABLE IF EXISTS "public"."data_medium_term_power_forecast_report";
CREATE TABLE "public"."data_medium_term_power_forecast_report" (
  "_id" integer NOT NULL DEFAULT nextval(
    'data_medium_term_power_forecast_report__id_seq'::regclass
  ),
  "data_sourcegrab_id" integer NOT NULL,
  "data_sourcegrab_pattern_name" character varying(255) NOT NULL,
  "entity_id" character varying(255) NOT NULL,
  "entity_type" integer NOT NULL,
  "entity_name" character varying(255),
  "create_time" integer NOT NULL,
  "start_time" integer NOT NULL,
  "forecast_time" integer NOT NULL,
  "predays" integer NOT NULL,
  "power" character varying(255)
);

COMMENT ON TABLE "public"."data_medium_term_power_forecast_report" IS '中期功率上报数据表';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."_id" IS '唯一标识';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."data_sourcegrab_id" IS '数据源抓取ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."data_sourcegrab_pattern_name" IS '数据抓取模式名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."entity_id" IS '实体ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."entity_type" IS '实体类型';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."entity_name" IS '实体名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."start_time" IS '起报时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."forecast_time" IS '预测时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."predays" IS '预报天数';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_report"."power" IS '预测功率';


CREATE SEQUENCE IF NOT EXISTS "public"."data_medium_term_power_forecast_back__id_seq";
DROP TABLE IF EXISTS "public"."data_medium_term_power_forecast_back";
CREATE TABLE "public"."data_medium_term_power_forecast_back" (
  "_id" integer NOT NULL DEFAULT nextval(
    'data_medium_term_power_forecast_back__id_seq'::regclass
  ),
  "data_sourcegrab_id" integer NOT NULL,
  "data_sourcegrab_pattern_name" character varying(255) NOT NULL,
  "entity_id" character varying(255) NOT NULL,
  "entity_type" integer NOT NULL,
  "entity_name" character varying(255),
  "create_time" integer NOT NULL,
  "start_time" integer NOT NULL,
  "forecast_time" integer NOT NULL,
  "predays" integer NOT NULL,
  "power" character varying(255)
);

COMMENT ON TABLE "public"."data_medium_term_power_forecast_back" IS '中期功率回传数据表';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."_id" IS '唯一标识';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."data_sourcegrab_id" IS '数据源抓取ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."data_sourcegrab_pattern_name" IS '数据抓取模式名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."entity_id" IS '实体ID';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."entity_type" IS '实体类型';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."entity_name" IS '实体名称';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."start_time" IS '起报时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."forecast_time" IS '预测时间';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."predays" IS '预报天数';

COMMENT ON COLUMN "public"."data_medium_term_power_forecast_back"."power" IS '预测功率';

-- JSON 数组配置可能超过 dict_value 原长度(VARCHAR(100)), 扩容(可重复执行)
ALTER TABLE sys_dict_data ALTER COLUMN dict_value TYPE VARCHAR(2000);

ALTER TABLE "public"."sys_dict_data" ALTER COLUMN "dict_value" DROP DEFAULT, 
ALTER COLUMN "dict_value" TYPE character varying(1000) USING "dict_value"::character varying(1000), 
ALTER COLUMN "dict_value" SET DEFAULT '';

-- -----------------------------------------------------
-- 1. 待处理文件表 data_file_sync_files
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS data_file_sync_files (
    id SERIAL NOT NULL,
    type VARCHAR(64),
    file VARCHAR(1024) NOT NULL,
    status INTEGER NOT NULL DEFAULT 0,
    handler VARCHAR(255),
    create_time TIMESTAMP NOT NULL DEFAULT now(),
    update_time TIMESTAMP NOT NULL DEFAULT now(),
    fail_reason VARCHAR(1024),
    process_times INTEGER NOT NULL DEFAULT 0,
    priority INTEGER NOT NULL DEFAULT 3,
    CONSTRAINT pk_data_file_sync_files PRIMARY KEY (id)
);

-- 文件路径查重保险(扫描任务按 file 去重插入)
CREATE UNIQUE INDEX IF NOT EXISTS uk_data_file_sync_files_file ON data_file_sync_files (file);

COMMENT ON TABLE data_file_sync_files IS '待处理数据文件同步';
COMMENT ON COLUMN data_file_sync_files.id IS '唯一标识';
COMMENT ON COLUMN data_file_sync_files.type IS '类型';
COMMENT ON COLUMN data_file_sync_files.file IS '文件(完整路径)';
COMMENT ON COLUMN data_file_sync_files.status IS '状态(0待处理,1处理中,2完成,99失败,-1取消)';
COMMENT ON COLUMN data_file_sync_files.handler IS '处理器bean名称';
COMMENT ON COLUMN data_file_sync_files.create_time IS '创建时间';
COMMENT ON COLUMN data_file_sync_files.update_time IS '更新时间';
COMMENT ON COLUMN data_file_sync_files.fail_reason IS '失败原因';
COMMENT ON COLUMN data_file_sync_files.process_times IS '处理次数';
COMMENT ON COLUMN data_file_sync_files.priority IS '优先级';

-- 增加告警类别字段（幂等）
ALTER TABLE alarm_event_record ADD COLUMN IF NOT EXISTS alarm_category VARCHAR(50);
COMMENT ON COLUMN alarm_event_record.alarm_category IS '告警类别';