forecast/release_note/20261015/20261015_dml.sql

-- -----------------------------------------------------
-- 1. 上报批次表 data_power_forecast_report_batch
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS data_power_forecast_report_batch (
    id SERIAL NOT NULL,
    station_code VARCHAR(64) NOT NULL,
    station_name VARCHAR(255),
    batch_no VARCHAR(128) NOT NULL,
    type VARCHAR(16) NOT NULL,
    report_day INTEGER NOT NULL,
    est_report_time VARCHAR(16) NOT NULL,
    status INTEGER NOT NULL DEFAULT 1,
    generate_source VARCHAR(16) NOT NULL DEFAULT 'auto',
    create_time TIMESTAMP NOT NULL DEFAULT now(),
    update_time TIMESTAMP NOT NULL DEFAULT now(),
    remark VARCHAR(512),
    CONSTRAINT pk_data_power_forecast_report_batch PRIMARY KEY (id)
);

-- 单场站幂等保险(任务按 station_code + report_day + est_report_time + type 查重)
CREATE INDEX IF NOT EXISTS uk_data_power_forecast_report_batch
    ON data_power_forecast_report_batch (station_code, report_day, est_report_time, type);

COMMENT ON TABLE data_power_forecast_report_batch IS '功率预测上报批次';
COMMENT ON COLUMN data_power_forecast_report_batch.id IS '唯一标识';
COMMENT ON COLUMN data_power_forecast_report_batch.station_code IS '场站编码';
COMMENT ON COLUMN data_power_forecast_report_batch.station_name IS '场站名称';
COMMENT ON COLUMN data_power_forecast_report_batch.batch_no IS '上报批次号';
COMMENT ON COLUMN data_power_forecast_report_batch.type IS '类型(short短期,ultra超短期)';
COMMENT ON COLUMN data_power_forecast_report_batch.report_day IS '上报日(yyyyMMdd,北京时间)';
COMMENT ON COLUMN data_power_forecast_report_batch.est_report_time IS '预计上报时间(HH:mm:ss,北京时间)';
COMMENT ON COLUMN data_power_forecast_report_batch.status IS '状态(1待确认,2已确认)';
COMMENT ON COLUMN data_power_forecast_report_batch.generate_source IS '生成来源(auto自动,manually手动)';
COMMENT ON COLUMN data_power_forecast_report_batch.create_time IS '创建时间';
COMMENT ON COLUMN data_power_forecast_report_batch.update_time IS '更新时间';
COMMENT ON COLUMN data_power_forecast_report_batch.remark IS '备注';

-- -----------------------------------------------------
-- 2. report 表加 batch_no 列(101901 统计查询按 batch_no is null 隔离)
-- -----------------------------------------------------
-- 字段说明：batch_no VARCHAR(128) 上报批次号

DO $$
BEGIN
    -- 1. 添加字段（若不存在）
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name   = 'data_short_term_power_forecast_report'
          AND column_name  = 'batch_no'
    ) THEN
        ALTER TABLE data_short_term_power_forecast_report
            ADD COLUMN batch_no VARCHAR(128);

        COMMENT ON COLUMN data_short_term_power_forecast_report.batch_no IS '上报批次号';
    END IF;

    -- 2. 创建索引（若不存在）
    IF NOT EXISTS (
        SELECT 1
        FROM pg_indexes
        WHERE schemaname = current_schema()
          AND tablename  = 'data_short_term_power_forecast_report'
          AND indexname  = 'idx_dstpfr_batch_no'
    ) THEN
        CREATE INDEX idx_dstpfr_batch_no
            ON data_short_term_power_forecast_report (batch_no);
    END IF;
END
$$;

DO $$
BEGIN
    -- 1. 添加字段（若不存在）
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = current_schema()
          AND table_name   = 'data_super_short_term_forecast_report'
          AND column_name  = 'batch_no'
    ) THEN
        ALTER TABLE data_super_short_term_forecast_report
            ADD COLUMN batch_no VARCHAR(128);

        COMMENT ON COLUMN data_super_short_term_forecast_report.batch_no IS '上报批次号';
    END IF;

    -- 2. 创建索引（若不存在）
    IF NOT EXISTS (
        SELECT 1
        FROM pg_indexes
        WHERE schemaname = current_schema()
          AND tablename  = 'data_super_short_term_forecast_report'
          AND indexname  = 'idx_dssstfr_batch_no'
    ) THEN
        CREATE INDEX idx_dssstfr_batch_no
            ON data_super_short_term_forecast_report (batch_no);
    END IF;
END
$$;