CREATE TABLE shanghailifeecif.edorinfo( contno string DEFAULT NULL COMMENT '保单号', edortype string DEFAULT NULL COMMENT '管理机构', lppostaladdress string DEFAULT NULL COMMENT '原客户地址', lcpostaladdress string DEFAULT NULL COMMENT '现客户地址', lpmobile string DEFAULT NULL COMMENT '原手机号', lcmobile string DEFAULT NULL COMMENT '现手机号 ', lpidtype string DEFAULT NULL COMMENT '原证件类型', lcidtype string DEFAULT NULL COMMENT '现证件类型 ', lpidno string DEFAULT NULL COMMENT '原证件号码', lcidno string DEFAULT NULL COMMENT '现证件号码 ', EDORAPPDATE string DEFAULT NULL COMMENT '保全申请日期', EDORCVALIDATE string DEFAULT NULL COMMENT '保全生效日期 ' ) COMMENT '保全' STORED AS csvfile; CREATE TABLE shanghailifeecif.audit_edorlist( managecom varchar2(200) DEFAULT NULL COMMENT '管理机构', -- dialect: ORACLE managename varchar2(200) DEFAULT NULL COMMENT '管理机构名称', -- dialect: ORACLE salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道', -- dialect: ORACLE salechnlname varchar2(64) DEFAULT NULL COMMENT '销售渠道名称', -- dialect: ORACLE selltype varchar2(64) DEFAULT NULL COMMENT '合作渠道代码', -- dialect: ORACLE selltypename varchar2(64) DEFAULT NULL COMMENT '合作渠道名称', -- dialect: ORACLE contno varchar2(200) DEFAULT NULL COMMENT '保单号', -- dialect: ORACLE riskcodes varchar2(200) DEFAULT NULL COMMENT '险种代码', -- dialect: ORACLE risknames varchar2(200) DEFAULT NULL COMMENT '险种名称', -- dialect: ORACLE risktypes varchar2(64) DEFAULT NULL COMMENT '险种类型', -- dialect: ORACLE signdate timestamp DEFAULT NULL COMMENT '承保日期', appntname varchar2(200) DEFAULT NULL COMMENT '投保人', -- dialect: ORACLE prem decimal(10,2) DEFAULT NULL COMMENT '保费', edorname varchar2(256) DEFAULT NULL COMMENT '保全类型', -- dialect: ORACLE edoracceptno varchar2(256) DEFAULT NULL COMMENT '保全受理号', -- dialect: ORACLE edorno varchar2(256) DEFAULT NULL COMMENT '批单号', -- dialect: ORACLE edorvalidate timestamp DEFAULT NULL COMMENT '保全生效日期', customerhandleflag varchar2(64) DEFAULT NULL COMMENT '是否亲办', -- dialect: ORACLE operator varchar2(256) DEFAULT NULL COMMENT '操作人', -- dialect: ORACLE approveoperator varchar2(256) DEFAULT NULL COMMENT '审批人', -- dialect: ORACLE getmoney decimal(10,2) DEFAULT NULL COMMENT '保全退补费金额', cusappdate timestamp DEFAULT NULL COMMENT '客户申请日期', payyears varchar2(64) DEFAULT NULL COMMENT '缴费年期', -- dialect: ORACLE agentname varchar2(256) DEFAULT NULL COMMENT '代理人姓名', -- dialect: ORACLE edorstate varchar2(64) DEFAULT NULL COMMENT '保全状态', -- dialect: ORACLE ins_time timestamp DEFAULT NULL ) COMMENT '保全清单'; ====================================================================== =================================目的表结构================================= ====================================================================== CREATE TABLE shanglifeecif.PartyTimeLine( TripID string NOT NULL COMMENT '旅程ID' , PartyID string NOT NULL COMMENT '参与方ID' , Name string COMMENT '参与方名称' , PCertID string COMMENT '参与方证件号码' , PPhone string COMMENT '参与方手机' , PRole string COMMENT '参与方角色 投保人、被保人、受益人' , Scenario string COMMENT '场景 投保 保全 报案 咨询 投诉 其他' , SDate DATE COMMENT '发生日期' , ThreadID string COMMENT '关联ID 可关联至更详细信息，如保单ID、报案ID等' , ParentID string COMMENT '父ID 如报案对应的保单ID' , Describe string COMMENT '描述' , Channel string COMMENT '来源' , EndDate DATE COMMENT '完成日期' , CREATED_BY string COMMENT '创建人' , CREATED_TIME DATE COMMENT '创建时间' , UPDATED_BY string COMMENT '更新人' , UPDATED_TIME DATE COMMENT '更新时间' ) COMMENT '参与方事件 ' STORED AS ES with shard number 10 replication 1; ==================================================初始化数据==================================================================== DROP SEQUENCE IF EXISTS globaleTripID_sequence; CREATE OR REPLACE PROCEDURE shanglifeecif.init_partytimeline_main() -- 创建主存储过程 IS DECLARE partytimeline_count int strsql string BEGIN --1.从保全清单表中初始化 参与事件 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine; partytimeline_count:=partytimeline_count+1; DBMS_OUTPUT.PUT_LINE(partytimeline_count); init_PartyTimeLine_edorlist(partytimeline_count) --2.从保全表中初始化 参与事件 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine; partytimeline_count:=partytimeline_count+1; DBMS_OUTPUT.PUT_LINE(partytimeline_count); init_PartyTimeLine_edorinfo(partytimeline_count) -- 3.初始化理赔相关信息 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine; partytimeline_count:=partytimeline_count+1; DBMS_OUTPUT.PUT_LINE(partytimeline_count); init_PartyTimeLine_insuranceclaimthread(partytimeline_count) -- 4.初始化投保赔相关信息 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine; partytimeline_count:=partytimeline_count+1; DBMS_OUTPUT.PUT_LINE(partytimeline_count); init_PartyTimeLine_InsuranceArrangement(partytimeline_count) --5.从cc通话中心表中初始化 理赔 保全 咨询 参与事件 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine; partytimeline_count:=partytimeline_count+1; DBMS_OUTPUT.PUT_LINE(partytimeline_count); init_PartyTimeLine_cc(partytimeline_count) -- 用于以后更新时是用的序列 strsql:=' CREATE SEQUENCE globaleTripID_sequence INCREMENT BY 1 START WITH '|| partytimeline_count||' NOMAXVALUE NOMINVALUE NOCYCLE CACHE 1; ' EXECUTE IMMEDIATE strsql EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_partytimeline_main',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_partytimeline_main',sqlerrm(),sqlcode()) END; /*audit_edorlist 表 managecom varchar2(200) DEFAULT NULL COMMENT '管理机构', -- dialect: ORACLE managename varchar2(200) DEFAULT NULL COMMENT '管理机构名称', -- dialect: ORACLE salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道', -- dialect: ORACLE salechnlname varchar2(64) DEFAULT NULL COMMENT '销售渠道名称', -- dialect: ORACLE selltype varchar2(64) DEFAULT NULL COMMENT '合作渠道代码', -- dialect: ORACLE selltypename varchar2(64) DEFAULT NULL COMMENT '合作渠道名称', -- dialect: ORACLE contno varchar2(200) DEFAULT NULL COMMENT '保单号', -- dialect: ORACLE riskcodes varchar2(200) DEFAULT NULL COMMENT '险种代码', -- dialect: ORACLE risknames varchar2(200) DEFAULT NULL COMMENT '险种名称', -- dialect: ORACLE risktypes varchar2(64) DEFAULT NULL COMMENT '险种类型', -- dialect: ORACLE signdate timestamp DEFAULT NULL COMMENT '承保日期', appntname varchar2(200) DEFAULT NULL COMMENT '投保人', -- dialect: ORACLE prem decimal(10,2) DEFAULT NULL COMMENT '保费', edorname varchar2(256) DEFAULT NULL COMMENT '保全类型', -- dialect: ORACLE edoracceptno varchar2(256) DEFAULT NULL COMMENT '保全受理号', -- dialect: ORACLE edorno varchar2(256) DEFAULT NULL COMMENT '批单号', -- dialect: ORACLE edorvalidate timestamp DEFAULT NULL COMMENT '保全生效日期', customerhandleflag varchar2(64) DEFAULT NULL COMMENT '是否亲办', -- dialect: ORACLE operator varchar2(256) DEFAULT NULL COMMENT '操作人', -- dialect: ORACLE approveoperator varchar2(256) DEFAULT NULL COMMENT '审批人', -- dialect: ORACLE getmoney decimal(10,2) DEFAULT NULL COMMENT '保全退补费金额', cusappdate timestamp DEFAULT NULL COMMENT '客户申请日期', payyears varchar2(64) DEFAULT NULL COMMENT '缴费年期', -- dialect: ORACLE agentname varchar2(256) DEFAULT NULL COMMENT '代理人姓名', -- dialect: ORACLE edorstate varchar2(64) DEFAULT NULL COMMENT '保全状态', -- dialect: ORACLE ins_time timestamp DEFAULT NULL */ CREATE TABLE shanglifeecif.PartyTimeLine_edor_tmp( id string, contno string comment '保单号', PartyID string COMMENT '参与方ID' , appntname string COMMENT '参与方名称' , PCertID string COMMENT '参与方证件号码' , PPhone string COMMENT '参与方手机' , cusappdate DATE COMMENT '客户申请日期' , edoracceptno string COMMENT '保全受理号' , edorvalidate DATE COMMENT '保全生效日期' ) COMMENT '参与方保全事件临时表 ' STORED AS ES with shard number 10 replication 1; !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE init_PartyTimeLine_edorlist(partytimeline_count OUT int) IS BEGIN --初始化临时表 delete from shanglifeecif.PartyTimeLine_edor_tmp; insert into shanglifeecif.PartyTimeLine_edor_tmp( id, contno, appntname, cusappdate, edoracceptno, edorvalidate ) SELECT row_number()over(), contno, appntname, cusappdate, edoracceptno, edorvalidate from audit_edorlist; --更新临时表信息 UPDATE shanglifeecif.PartyTimeLine_edor_tmp a SET ( PartyID ,-- 参与方ID PCertID ,-- 参与方证件号码 PPhone ) = ( select ApplicantID , AppCertID , AppPhone from shanglifeecif.InsuranceArrangement b where b.PolicyNo = a.contno ) WHERE 1=1 ; --更新参与方事件表 insert into shanglifeecif.PartyTimeLine( TripID, PartyID, --参与方id Name, PCertID, PPhone, PRole, Scenario, SDate, ThreadID, ParentID, EndDate, CREATED_BY, CREATED_TIME ) SELECT row_number()over(), PartyID, appntname, PCertID, PPhone, '投保人', '保全', cusappdate, edoracceptno, contno, edorvalidate, 'admin', sysdate from shanglifeecif.PartyTimeLine_edor_tmp where PartyID is not null; --已经存在的数据行数 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_PartyTimeLine_edorlist',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_PartyTimeLine_edorlist',sqlerrm(),sqlcode()) END; ============================================================================================================================================================= ============================================================根据edorinfo更新======================================================================================== ========================================================================================================================================================== --------------------------------根据edorinfo更新 保全---------------------------------------- /* contno string DEFAULT NULL COMMENT '保单号', edortype string DEFAULT NULL COMMENT '管理机构', lppostaladdress string DEFAULT NULL COMMENT '原客户地址', lcpostaladdress string DEFAULT NULL COMMENT '现客户地址', lpmobile string DEFAULT NULL COMMENT '原手机号', lcmobile string DEFAULT NULL COMMENT '现手机号 ', lpidtype string DEFAULT NULL COMMENT '原证件类型', lcidtype string DEFAULT NULL COMMENT '现证件类型 ', lpidno string DEFAULT NULL COMMENT '原证件号码', lcidno string DEFAULT NULL COMMENT '现证件号码 ', EDORAPPDATE string DEFAULT NULL COMMENT '保全申请日期', EDORCVALIDATE string DEFAULT NULL COMMENT '保全生效日期 ' */ CREATE OR REPLACE PROCEDURE shanglifeecif.init_PartyTimeLine_edorinfo(partytimeline_count OUT int) IS BEGIN --初始化临时表 delete from shanglifeecif.PartyTimeLine_edor_tmp; insert into shanglifeecif.PartyTimeLine_edor_tmp( id, contno, PPhone, PCertID, edoracceptno , edorvalidate ) SELECT row_number()over(), contno, lcmobile, lcidno, EDORAPPDATE, EDORCVALIDATE from shanghailifeecif.edorinfo; --更新临时表信息 UPDATE shanglifeecif.PartyTimeLine_edor_tmp a SET ( PartyID ,-- 参与方ID appntname ) = ( select ApplicantID , appname--投保人名称 from shanglifeecif.InsuranceArrangement b where b.PolicyNo = a.contno ) WHERE 1=1 ; --更新参与方事件表 insert into shanglifeecif.PartyTimeLine( TripID, PartyID, --参与方id Name, PCertID, PPhone, PRole, Scenario, SDate, ThreadID, ParentID, EndDate, CREATED_BY, CREATED_TIME ) SELECT row_number()over() +partytimeline_count, PartyID, appntname, PCertID, PPhone, '投保人', '保全', cusappdate, edoracceptno, contno, edorvalidate, 'admin', sysdate from shanglifeecif.PartyTimeLine_edor_tmp where PartyID is not null; --已经存在的数据行数 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_PartyTimeLine_edorinfo',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_PartyTimeLine_edorinfo',sqlerrm(),sqlcode()) END; ============================================================================================================================================================= ============================================================根据insuranceclaimthread更新理赔======================================================================================== ========================================================================================================================================================== CREATE OR REPLACE PROCEDURE init_PartyTimeLine_insuranceclaimthread(partytimeline_count OUT int) IS BEGIN insert into shanglifeecif.PartyTimeLine( TripID, PartyID, --参与方id Name, PCertID, PPhone, PRole, Scenario, SDate, ThreadID, ParentID, EndDate, CREATED_BY, CREATED_TIME ) SELECT row_number()over() +partytimeline_count, applicantid, appname, appcertid, appphone, '投保人', '报案', crdate, icthreadid, policyno, csdate, 'admin', sysdate from shanglifeecif.insuranceclaimthread where applicantid is not null; --已经存在的数据行数 select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine; EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_PartyTimeLine_insuranceclaimthread',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_PartyTimeLine_insuranceclaimthread',sqlerrm(),sqlcode()) END; ============================================================================================================================================================= ============================================================根据InsuranceArrangement更新投保======================================================================================== ========================================================================================================================================================== CREATE OR REPLACE PROCEDURE init_PartyTimeLine_InsuranceArrangement(partytimeline_count OUT int) IS BEGIN --初始化临时表、 insert into PartyTimeLine( TripID, PartyID, --参与方id Name, PCertID, PPhone, PRole, Scenario, SDate, ThreadID, ParentID, EndDate, CREATED_BY, CREATED_TIME ) SELECT row_number()over() +partytimeline_count, applicantid, appname, appcertid, appphone, '投保人', '投保', PADate, PANo, PolicyNo, PMDate , 'admin', sysdate from shanglifeecif.InsuranceArrangement where applicantid is not null; --已经存在的数据行数 select count(0) into partytimeline_count from PartyTimeLine EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_PartyTimeLine_InsuranceArrangement',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_PartyTimeLine_InsuranceArrangement',sqlerrm(),sqlcode()) END; ================================================================================================================================================== ===========================================================根据cc_record_main表插入 保全 理赔 咨询========================================================= ================================================================================================================================================== CREATE TABLE PartyTimeLine_cc_tmp( id string, ThreadID string comment '记录id', ParentID string comment '父ID 保单号 ', PartyID string COMMENT '参与方ID' , Name string COMMENT '参与方名称' , PCertID string COMMENT '参与方证件号码' , PPhone string COMMENT '参与方手机' , PRole string COMMENT '参与方角色' , EndDate DATE COMMENT '完成日期' ) COMMENT '呼叫中心临时表' STORED AS ES with shard number 10 replication 1; CREATE OR REPLACE PROCEDURE init_PartyTimeLine_cc(partytimeline_count OUT int) IS BEGIN --清除临时表数据 delete from PartyTimeLine_cc_tmp; -- 插入临时中间表 insert into PartyTimeLine_cc_tmp( id , ThreadID,-- string comment ' 记录id', ParentID ,--string comment '保单号 ' --PartyID ,--string COMMENT '参与方ID' , Name ,-- string COMMENT '参与方名称' , --PCertID ,--string COMMENT '参与方证件号码' , PPhone ,--string COMMENT '参与方手机' , --PRole ,--string COMMENT '参与方角色' , EndDate --DATE COMMENT '完成日期' ) SELECT row_number()over(), recordinfo_id, contNos, econtactsName, econtactsPhone, startTime from shanghailifeecif.cc_record_main where contNos is not null; --更新用户id 和 角色 UPDATE PartyTimeLine_cc_tmp a SET ( PartyID ,--string COMMENT '参与方ID' , PCertID ,--string COMMENT '参与方证件号码' , PRole --string COMMENT '参与方角色' , ) = ( select b.ApplicantID , b.appcertid,--投保人名称 '投保人' from ( SELECT policyno, row_number()over(partition by policyno ) tmpfiled, ApplicantID , appname,--投保人名称 appcertid --证件号 FROM qxp.insurancearrangement )b where b.policyno = a.ParentID AND b.appname = a.name AND b.tmpfiled=1 ) WHERE a.PartyID IS null ; UPDATE PartyTimeLine_cc_tmp a SET ( PartyID ,--string COMMENT '参与方ID' , PCertID ,--string COMMENT '参与方证件号码' , PRole --string COMMENT '参与方角色' , ) = ( select b.insuredid , b.inscertid,--投保人名称 '被保人' from ( SELECT policyno, row_number()over(partition by policyno ) tmpfiled, insuredid , insname,--被保人名称 inscertid --证件号 FROM qxp.insurancearrangement )b where b.policyno = a.ParentID AND b.insname = a.name AND b.tmpfiled=1 ) WHERE a.PartyID IS null ; --插入到主表中去 insert into PartyTimeLine( TripID, PartyID, --参与方id Name, PCertID, PPhone, PRole, Scenario, SDate, ThreadID, ParentID, EndDate, CREATED_BY, CREATED_TIME ) SELECT row_number()over() +partytimeline_count, PartyID, Name, PCertID, PPhone, PRole, '咨询', EndDate, ThreadID, ParentID, EndDate , 'admin', sysdate from PartyTimeLine_cc_tmp where PartyID is not null; --已经存在的数据行数 select count(0) into partytimeline_count from PartyTimeLine; EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_PartyTimeLine_cc',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_PartyTimeLine_cc',sqlerrm(),sqlcode()) END; ================================================================================================================================================== ===========================================================更新投诉到事件表中========================================================= ================================================================================================================================================== ================================================================================================================================================== ===========================================================更新用户主表最后接触方式========================================================= ================================================================================================================================================== ===================================================================数据解析======================================================================== --保全号是唯一的 是不重复的 --从 audit_edorlist 的表中插入的数据为 619006 --有1060个数据 因为查不到保单无法进入数据库 --带edorinfo 一共插入 619685 --带理赔和保单一共插入 2086136 保单总数 1520720 SELECT count(1) FROM audit_edorlist ; --620066 SELECT count(1) FROM ( SELECT contno FROM audit_edorlist GROUP BY contno ); --根据保单 去重550805 SELECT count(1) FROM ( SELECT contno,cusappdate FROM audit_edorlist GROUP BY contno,cusappdate );--根据保单和申请时间 去重 591350 SELECT count(1) a,contno FROM audit_edorlist GROUP BY contno,cusappdate ORDER BY a DESC; --查询重复最多的数据 SELECT * FROM audit_edorlist WHERE contno = '2018020800060078'; SELECT count(1) FROM PartyTimeLine WHERE scenario = "保全" ; --619713 SELECT count(1) FROM PartyTimeLine WHERE Scenario ='投保' ; --1466423 SELECT count(1) FROM PartyTimeLine WHERE Scenario ='报案' ; --11490 SELECT count(1) FROM PartyTimeLine WHERE Scenario ='咨询' ; --14 SELECT count(1) FROM PartyTimeLine; --2097626 SELECT count(1) FROM shanglifeecif.InsuranceArrangement; --1520720 SELECT count(1) FROM shanglifeecif.insuranceclaimthread; --13957 --