zjrs-jeecgBoot/.docs/sql/建表语句/数据统计3张表.sql

-- ============================================================
-- 湛江市智慧人社运营运维（2025-2027年）项目
-- 就业一湛通服务平台 - 数据统计 达梦数据库建表脚本
-- 
-- 依据：《需求规格说明书》第3.11节 - 数据统计
--   3.11.1 统计岗位归集数量 - 岗位数据回流明细表
--   3.11.2 农民工返乡返岗统计 - 农民工流动回流明细表
--   3.11.3 重点人群统计 - 重点人群回流明细表
-- 数据库：达梦数据库 (DM8)
-- 创建日期：2025-06-10
-- ============================================================

-- ============================================================
-- 表1：岗位数据回流明细表
-- 说明：按行业、主管部门、县（市、区）、镇街统计岗位归集数量的数据明细
-- 数据来源：省平台回流
-- ============================================================
CREATE TABLE STAT_JOB_REFLUX (
    ID                              VARCHAR(36)     NOT NULL,
    UNIT_CREDIT_CODE                VARCHAR(50),
    ENTERPRISE_NAME                 VARCHAR(200),
    DISTRICT                        VARCHAR(50),
    JOB_NAME                        VARCHAR(200),
    PLAN_HIRE_COUNT                 INTEGER,
    JOB_INDUSTRY                    VARCHAR(100),
    SALARY_RANGE                    VARCHAR(100),
    EDUCATION_REQUIRE               VARCHAR(100),
    JOB_PUBLISH_TIME                DATE,
    JOB_STATUS                      VARCHAR(50),
    DATA_REFLUX_TIME                DATE,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE STAT_JOB_REFLUX IS '岗位数据回流明细表';
COMMENT ON COLUMN STAT_JOB_REFLUX.ID IS '主键ID';
COMMENT ON COLUMN STAT_JOB_REFLUX.UNIT_CREDIT_CODE IS '统一社会信用代码';
COMMENT ON COLUMN STAT_JOB_REFLUX.ENTERPRISE_NAME IS '企业名称';
COMMENT ON COLUMN STAT_JOB_REFLUX.DISTRICT IS '所属区县';
COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_NAME IS '招聘岗位名称';
COMMENT ON COLUMN STAT_JOB_REFLUX.PLAN_HIRE_COUNT IS '计划招聘人数';
COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_INDUSTRY IS '岗位所属行业';
COMMENT ON COLUMN STAT_JOB_REFLUX.SALARY_RANGE IS '薪资区间';
COMMENT ON COLUMN STAT_JOB_REFLUX.EDUCATION_REQUIRE IS '学历要求';
COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_PUBLISH_TIME IS '岗位发布时间';
COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_STATUS IS '岗位状态';
COMMENT ON COLUMN STAT_JOB_REFLUX.DATA_REFLUX_TIME IS '数据回流时间';
COMMENT ON COLUMN STAT_JOB_REFLUX.CREATE_BY IS '创建人';
COMMENT ON COLUMN STAT_JOB_REFLUX.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN STAT_JOB_REFLUX.UPDATE_BY IS '修改人';
COMMENT ON COLUMN STAT_JOB_REFLUX.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN STAT_JOB_REFLUX.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_STAT_JOB_REFLUX_UNIT ON STAT_JOB_REFLUX(UNIT_CREDIT_CODE);
CREATE INDEX IDX_STAT_JOB_REFLUX_DISTRICT ON STAT_JOB_REFLUX(DISTRICT);
CREATE INDEX IDX_STAT_JOB_REFLUX_INDUSTRY ON STAT_JOB_REFLUX(JOB_INDUSTRY);


-- ============================================================
-- 表2：农民工流动回流明细表
-- 说明：记录农民工返乡返岗流动的明细数据，支持查询统计导出报表
-- 数据来源：省平台回流
-- ============================================================
CREATE TABLE STAT_WORKER_REFLUX (
    ID                              VARCHAR(36)     NOT NULL,
    ID_CARD                         VARCHAR(50),
    FULL_NAME                       VARCHAR(100),
    DISTRICT                        VARCHAR(50),
    RURAL_REGISTER_FLAG             VARCHAR(10),
    ORIGIN_WORK_AREA                VARCHAR(200),
    WORK_END_DATE                   DATE,
    LOCAL_EMPLOY_DATE               DATE,
    PERSON_CATEGORY_TAG             VARCHAR(100),
    DATA_REFLUX_TIME                DATE,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE STAT_WORKER_REFLUX IS '农民工流动回流明细表';
COMMENT ON COLUMN STAT_WORKER_REFLUX.ID IS '主键ID';
COMMENT ON COLUMN STAT_WORKER_REFLUX.ID_CARD IS '身份证号码';
COMMENT ON COLUMN STAT_WORKER_REFLUX.FULL_NAME IS '姓名';
COMMENT ON COLUMN STAT_WORKER_REFLUX.DISTRICT IS '所属区县';
COMMENT ON COLUMN STAT_WORKER_REFLUX.RURAL_REGISTER_FLAG IS '乡村户籍标识';
COMMENT ON COLUMN STAT_WORKER_REFLUX.ORIGIN_WORK_AREA IS '原就业外出地市/省份';
COMMENT ON COLUMN STAT_WORKER_REFLUX.WORK_END_DATE IS '外出就业结束日期';
COMMENT ON COLUMN STAT_WORKER_REFLUX.LOCAL_EMPLOY_DATE IS '本地新就业登记日期';
COMMENT ON COLUMN STAT_WORKER_REFLUX.PERSON_CATEGORY_TAG IS '人员类别标签（脱贫/普通农民工，以回流数据为准）';
COMMENT ON COLUMN STAT_WORKER_REFLUX.DATA_REFLUX_TIME IS '数据回流时间';
COMMENT ON COLUMN STAT_WORKER_REFLUX.CREATE_BY IS '创建人';
COMMENT ON COLUMN STAT_WORKER_REFLUX.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN STAT_WORKER_REFLUX.UPDATE_BY IS '修改人';
COMMENT ON COLUMN STAT_WORKER_REFLUX.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN STAT_WORKER_REFLUX.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_STAT_WORKER_REFLUX_ID_CARD ON STAT_WORKER_REFLUX(ID_CARD);
CREATE INDEX IDX_STAT_WORKER_REFLUX_DISTRICT ON STAT_WORKER_REFLUX(DISTRICT);


-- ============================================================
-- 表3：重点人群回流明细表
-- 说明：记录离校未就业大学生等重点人群的明细数据，支持查询统计导出报表
-- 数据来源：省平台回流
-- ============================================================
CREATE TABLE STAT_KEY_PERSON_REFLUX (
    ID                              VARCHAR(36)     NOT NULL,
    ID_CARD                         VARCHAR(50),
    FULL_NAME                       VARCHAR(100),
    DISTRICT                        VARCHAR(50),
    KEY_PERSON_CATEGORY             VARCHAR(100),
    CONFIRM_DATE                    DATE,
    VALID_DATE                      DATE,
    CURRENT_EMPLOY_STATUS           VARCHAR(50),
    DATA_REFLUX_TIME                DATE,
    CREATE_BY                       VARCHAR(50),
    CREATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY                       VARCHAR(50),
    UPDATE_TIME                     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SYS_ORG_CODE                    VARCHAR(50),
    PRIMARY KEY (ID)
);

COMMENT ON TABLE STAT_KEY_PERSON_REFLUX IS '重点人群回流明细表';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.ID IS '主键ID';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.ID_CARD IS '身份证号码';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.FULL_NAME IS '姓名';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.DISTRICT IS '所属区县';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.KEY_PERSON_CATEGORY IS '重点人群类别';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CONFIRM_DATE IS '认定日期';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.VALID_DATE IS '有效日期';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CURRENT_EMPLOY_STATUS IS '当前就业状态';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.DATA_REFLUX_TIME IS '数据回流时间';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CREATE_BY IS '创建人';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.UPDATE_BY IS '修改人';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.SYS_ORG_CODE IS '组织机构编号';

CREATE INDEX IDX_STAT_KEY_PERSON_REFLUX_ID_CARD ON STAT_KEY_PERSON_REFLUX(ID_CARD);
CREATE INDEX IDX_STAT_KEY_PERSON_REFLUX_DISTRICT ON STAT_KEY_PERSON_REFLUX(DISTRICT);
CREATE INDEX IDX_STAT_KEY_PERSON_REFLUX_CATEGORY ON STAT_KEY_PERSON_REFLUX(KEY_PERSON_CATEGORY);