-- ============================================================ -- 湛江市智慧人社运营运维（2025-2027年）项目 -- 就业一湛通服务平台 - 失败诊断 达梦数据库建表脚本 -- -- 依据：《需求规格说明书》第3.3节 - 失败诊断 -- 3.3.1 失败原因明细 -- 3.3.2 失败诊断深度分析（含失败诊断、服务跟进、发送消息） -- 数据库：达梦数据库 (DM8) -- 创建日期：2025-06-02 -- ============================================================ -- ============================================================ -- 表1：失败原因明细表 -- 说明：记录求职人员的求职失败原因明细数据 -- ============================================================ CREATE TABLE INTERVIEW_FAIL_REASON ( ID VARCHAR(36) NOT NULL, PERSONAL_ID VARCHAR(36) NOT NULL, PROVINCE_PERSONAL_ID VARCHAR(36), ENTERPRISE_NAME VARCHAR(200), JOB_NAME VARCHAR(200), INTERVIEW_METHOD VARCHAR(50), DELIVERY_TIME DATETIME, INTERVIEW_TIME DATETIME, FAIL_REASON TEXT, DATA_SOURCE VARCHAR(50), 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 INTERVIEW_FAIL_REASON IS '失败原因明细表'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.ID IS '主键ID'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.PERSONAL_ID IS '关联本系统个人基本信息ID（PERSONAL_INFO主键）'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.PROVINCE_PERSONAL_ID IS '省系统人员表ID'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.ENTERPRISE_NAME IS '企业名称'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.JOB_NAME IS '应聘岗位名称'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.INTERVIEW_METHOD IS '面试方式'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.DELIVERY_TIME IS '投递时间'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.INTERVIEW_TIME IS '面试时间'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.FAIL_REASON IS '面试不通过原因（原始数据回流）'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.DATA_SOURCE IS '数据来源（省系统/市系统/其他）'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.CREATE_BY IS '创建人'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.UPDATE_BY IS '修改人'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN INTERVIEW_FAIL_REASON.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_INTERVIEW_FAIL_REASON_PERSONAL ON INTERVIEW_FAIL_REASON(PERSONAL_ID); CREATE INDEX IDX_INTERVIEW_FAIL_REASON_ENTERPRISE ON INTERVIEW_FAIL_REASON(ENTERPRISE_NAME); -- ============================================================ -- 表2：失败诊断分析表 -- 说明：对求职者的失败原因进行次数汇总统计分析 -- ============================================================ CREATE TABLE INTERVIEW_FAIL_DIAGNOSIS ( ID VARCHAR(36) NOT NULL, PERSONAL_ID VARCHAR(36) NOT NULL, PROVINCE_PERSONAL_ID VARCHAR(36), FAIL_COUNT INTEGER NOT NULL DEFAULT 0, LOCAL_TAGS VARCHAR(500), IS_FOLLOWED VARCHAR(10), LAST_NOTIFY_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 INTERVIEW_FAIL_DIAGNOSIS IS '失败诊断分析表'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.ID IS '主键ID'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.PERSONAL_ID IS '关联本系统个人基本信息ID（PERSONAL_INFO主键）'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.PROVINCE_PERSONAL_ID IS '省系统人员表ID'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.FAIL_COUNT IS '面试失败次数（累计次数）'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.LOCAL_TAGS IS '本地标签（可多个，支持本地修改）'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.IS_FOLLOWED IS '是否已跟进（有本地服务记录则视为已跟进）'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.LAST_NOTIFY_TIME IS '最后通知时间'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.CREATE_BY IS '创建人'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.UPDATE_BY IS '修改人'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN INTERVIEW_FAIL_DIAGNOSIS.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_INTERVIEW_FAIL_DIAGNOSIS_PERSONAL ON INTERVIEW_FAIL_DIAGNOSIS(PERSONAL_ID); CREATE INDEX IDX_INTERVIEW_FAIL_DIAGNOSIS_COUNT ON INTERVIEW_FAIL_DIAGNOSIS(FAIL_COUNT); -- ============================================================ -- 表3：服务跟进表 -- 说明：记录对求职失败人员的服务跟进情况 -- ============================================================ CREATE TABLE INTERVIEW_FAIL_FOLLOW_UP ( ID VARCHAR(36) NOT NULL, DIAGNOSIS_ID VARCHAR(36) NOT NULL, FULL_NAME VARCHAR(100), SERVICE_CONTENT TEXT NOT NULL, SERVICE_TIME DATE NOT NULL, SERVICE_PERSON VARCHAR(100), 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 INTERVIEW_FAIL_FOLLOW_UP IS '服务跟进表'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.ID IS '主键ID'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.DIAGNOSIS_ID IS '关联失败诊断分析ID（INTERVIEW_FAIL_DIAGNOSIS主键）'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.FULL_NAME IS '姓名'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SERVICE_CONTENT IS '服务内容'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SERVICE_TIME IS '服务时间（年月日，默认当天日期，支持修改）'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SERVICE_PERSON IS '服务人员（系统自动加载用户姓名）'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.CREATE_BY IS '创建人'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.UPDATE_BY IS '修改人'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN INTERVIEW_FAIL_FOLLOW_UP.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_INTERVIEW_FAIL_FOLLOW_DIAGNOSIS ON INTERVIEW_FAIL_FOLLOW_UP(DIAGNOSIS_ID); -- ============================================================ -- 表4：发送消息表 -- 说明：记录向求职失败人员发送消息的情况 -- ============================================================ CREATE TABLE INTERVIEW_FAIL_MESSAGE ( ID VARCHAR(36) NOT NULL, DIAGNOSIS_ID VARCHAR(36) NOT NULL, MESSAGE_SUBJECT VARCHAR(200) NOT NULL, MESSAGE_CONTENT TEXT NOT NULL, SENDER VARCHAR(100), SEND_TIME DATETIME, 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 INTERVIEW_FAIL_MESSAGE IS '发送消息表'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.ID IS '主键ID'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.DIAGNOSIS_ID IS '关联失败诊断分析ID（INTERVIEW_FAIL_DIAGNOSIS主键）'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.MESSAGE_SUBJECT IS '消息主题'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.MESSAGE_CONTENT IS '消息内容'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.SENDER IS '推送人（系统自动加载用户姓名）'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.SEND_TIME IS '推送时间（系统自动记录）'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.CREATE_BY IS '创建人'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.UPDATE_BY IS '修改人'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN INTERVIEW_FAIL_MESSAGE.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_INTERVIEW_FAIL_MESSAGE_DIAGNOSIS ON INTERVIEW_FAIL_MESSAGE(DIAGNOSIS_ID); -- ============================================================ -- 表5：失败标签配置表 -- 说明：失败原因关键字匹配的本地标签编码映射配置 -- ============================================================ CREATE TABLE INTERVIEW_FAIL_TAG ( ID VARCHAR(36) NOT NULL, TAG_CODE VARCHAR(20) NOT NULL, TAG_NAME VARCHAR(200) NOT NULL, TAG_DESCRIPTION VARCHAR(500), MATCH_KEYWORDS VARCHAR(1000), SORT_ORDER INTEGER, 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 INTERVIEW_FAIL_TAG IS '失败标签配置表'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.ID IS '主键ID'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.TAG_CODE IS '标签编码（FAIL001~FAIL008）'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.TAG_NAME IS '标签名称'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.TAG_DESCRIPTION IS '标签说明'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.MATCH_KEYWORDS IS '关键字匹配规则'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.SORT_ORDER IS '排序序号'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.CREATE_BY IS '创建人'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.UPDATE_BY IS '修改人'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN INTERVIEW_FAIL_TAG.SYS_ORG_CODE IS '组织机构编号'; CREATE UNIQUE INDEX IDX_INTERVIEW_FAIL_TAG_CODE ON INTERVIEW_FAIL_TAG(TAG_CODE); -- ============================================================ -- 初始数据：失败标签配置 -- ============================================================ INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000001', 'FAIL001', '硬性基础条件不符', '企业因年龄/性别/学历等硬性要求拒绝求职者', '年龄,性别,学历,超龄,超岁,岁数,文化程度,文凭', 1); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000002', 'FAIL002', '岗位技能证书不足', '企业因求职者操作不熟练/无证书/无行业经验拒绝', '证书,技能,经验,不熟练,无证,没证,不会操作,无经验,经验不足', 2); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000003', 'FAIL003', '薪资福利期望不匹配', '企业/求职者因薪资、福利、补贴等未达成一致', '薪资,工资,薪酬,福利,补贴,待遇,钱少,太低,给不起,达不到预期', 3); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000004', 'FAIL004', '地域通勤住宿不方便', '企业因求职者嫌距离远/不愿驻外/住宿差等拒绝', '距离,太远,通勤,住宿,宿舍,驻外,外派,交通不便,地点不合适', 4); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000005', 'FAIL005', '体检合规背景不通过', '企业因求职者体检不合格/征信/背景审核失败拒绝', '体检,不合格,征信,背景,背调,政审,无犯罪,证明,健康证,不通过', 5); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000006', 'FAIL006', '面试沟通形象不合适', '企业因求职者沟通能力/仪容形象等拒绝', '沟通,表达,形象,仪表,气质,谈吐,面试,不合适,不匹配,不符', 6); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000007', 'FAIL007', '求职不稳定意愿不足', '企业因求职者短期过渡/拒绝加班倒班等拒绝', '短期,过渡,不稳定,加班,倒班,夜班,两班倒,拒绝加班,不想加班', 7); INSERT INTO INTERVIEW_FAIL_TAG (ID, TAG_CODE, TAG_NAME, TAG_DESCRIPTION, MATCH_KEYWORDS, SORT_ORDER) VALUES ('FT0000000000000000000000000000008', 'FAIL008', '其他模糊不明原因', '企业录入内容模糊（如"不合适""不行"），无法归为以上7类的所有情况', '不合适,不行,其他,综合,个人原因,不符合要求', 8); -- ============================================================ -- 表6：失败诊断分析标签关联表 -- 说明：INTERVIEW_FAIL_DIAGNOSIS 与 INTERVIEW_FAIL_TAG 的多对多关联 -- ============================================================ CREATE TABLE INTERVIEW_FAIL_DIAGNOSIS_TAG ( DIAGNOSIS_ID VARCHAR(36) NOT NULL COMMENT '失败诊断分析ID', TAG_ID VARCHAR(36) NOT NULL COMMENT '失败标签配置ID', PRIMARY KEY (DIAGNOSIS_ID, TAG_ID) ); COMMENT ON TABLE INTERVIEW_FAIL_DIAGNOSIS_TAG IS '失败诊断分析标签关联表'; -- ============================================================ -- 外键约束（可选启用） -- ============================================================ -- ALTER TABLE INTERVIEW_FAIL_REASON ADD CONSTRAINT FK_INTERVIEW_FAIL_REASON_PERSONAL FOREIGN KEY (PERSONAL_ID) REFERENCES PERSONAL_INFO(ID); -- ALTER TABLE INTERVIEW_FAIL_DIAGNOSIS ADD CONSTRAINT FK_INTERVIEW_FAIL_DIAGNOSIS_PERSONAL FOREIGN KEY (PERSONAL_ID) REFERENCES PERSONAL_INFO(ID); -- ALTER TABLE INTERVIEW_FAIL_FOLLOW_UP ADD CONSTRAINT FK_INTERVIEW_FAIL_FOLLOW_DIAGNOSIS FOREIGN KEY (DIAGNOSIS_ID) REFERENCES INTERVIEW_FAIL_DIAGNOSIS(ID); -- ALTER TABLE INTERVIEW_FAIL_MESSAGE ADD CONSTRAINT FK_INTERVIEW_FAIL_MESSAGE_DIAGNOSIS FOREIGN KEY (DIAGNOSIS_ID) REFERENCES INTERVIEW_FAIL_DIAGNOSIS(ID);