---------------------保单数据处理 --创建保险理赔表insuranceclaimthread DROP TABLE IF EXISTS insuranceclaimthread; CREATE TABLE insuranceclaimthread( icthreadid string NOT NULL COMMENT '保险理赔Id', crno string DEFAULT NULL COMMENT '立案号', companyno string DEFAULT NULL COMMENT '公司代码', policyno string NOT NULL COMMENT '保险单号 INSURANCE_CLAIM.CONTNO', applicantid string DEFAULT NULL COMMENT '投保人ID', appname string DEFAULT NULL COMMENT '投保人名称', appphone string DEFAULT NULL COMMENT '投保人手机', appcertid string DEFAULT NULL COMMENT '投保人证件号码 INSURANCE_CLAIM.RISKDATE', cnno string DEFAULT NULL COMMENT '报案号', losstime date DEFAULT NULL COMMENT '损失发生时间', rpid string DEFAULT NULL COMMENT '报案人', rpname string DEFAULT NULL COMMENT '报案人名称', rpphone string DEFAULT NULL COMMENT '报案人电话', lpid string DEFAULT NULL COMMENT '出险人 根据INSURANCE_CLAIM.INSUREDNO获取indid', lpname string DEFAULT NULL COMMENT '出险人名称', cndate date DEFAULT NULL COMMENT '报案日期 INSURANCE_CLAIM.RPTDATE', lossdescribe string DEFAULT NULL COMMENT '损失原因 案件发生的原因:INSURANCE_CLAIM.LLOCCURREASON', crdate date DEFAULT NULL COMMENT '立案日期 INSURANCE_CLAIM.RGTDATE', cndescribe string DEFAULT NULL COMMENT '报案描述 INSURANCE_CLAIM.ACCIDENTDETAIL', claimstatus string DEFAULT NULL COMMENT '理赔状态 案件的状态,例如:录入、已立案等', csdate date DEFAULT NULL COMMENT '理赔状态日期', relationship string DEFAULT NULL COMMENT '报案人与出险人关系', ccno1 string DEFAULT NULL COMMENT '赔案号1 AUDIT_CLAIM_INSURANCE.CLMNO', ccstatus1 string DEFAULT NULL COMMENT '赔案状态1', ccsdate1 date DEFAULT NULL COMMENT '赔案状态日期1', productid1 string DEFAULT NULL COMMENT '险种代码1 INSURANCE_CLAIM_HEALTH.RISKCODE', ccamt1 double DEFAULT NULL COMMENT '赔付金额1 INSURANCE_CLAIM_HEALTH.REALPAY', ccno2 string DEFAULT NULL COMMENT '赔案号2', ccstatus2 string DEFAULT NULL COMMENT '赔案状态2', ccsdate2 date DEFAULT NULL COMMENT '赔案状态日期2', productid2 string DEFAULT NULL COMMENT '险种代码2', ccamt2 double DEFAULT NULL COMMENT '赔付金额2', branchcode string DEFAULT NULL COMMENT '机构代码', claimcasestatus string DEFAULT NULL COMMENT '案件状态INSURANCE_CLAIM_HEALTH.LLCLAIMSTATE', created_by string DEFAULT NULL COMMENT '', created_time date DEFAULT NULL COMMENT '', updated_by string DEFAULT NULL COMMENT '', updated_time date DEFAULT NULL COMMENT '' ) COMMENT '保险理赔' STORED AS ES with shard number 10 replication 1; DROP TABLE IF EXISTS insuranceclaimthread_tmp; CREATE TABLE insuranceclaimthread_tmp( contno string NOT NULL COMMENT '保单号', rownumber int not null comment '同保单号 按照出险日期倒叙排列的行号', CLMNO string NOT NULL COMMENT '赔案号', LPID string DEFAULT NULL COMMENT '出险人客户号', LPName string DEFAULT NULL COMMENT '出险人名称', scustid string NOT NULL COMMENT '出险人上游客户id', rptdate date DEFAULT NULL COMMENT '报案日期', rgtdate date DEFAULT NULL COMMENT '立案日期', riskdate date DEFAULT NULL COMMENT '出险日期', lloccurreason string DEFAULT NULL COMMENT '出险原因', -- dialect: ORACLE accidentdetail string DEFAULT NULL COMMENT '意外细节', -- dialect: ORACLE llclaimstate string DEFAULT NULL COMMENT '目前状态', -- dialect: ORACLE riskcode string DEFAULT NULL COMMENT '险种代码', realpay double DEFAULT NULL COMMENT '赔付金额' ) COMMENT 'insuranceclaimthread临时表' CLUSTERED BY (contno) INTO 1 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true"); ------------------------------------------------------------------------------------------------------------------------------------------ CREATE OR REPLACE PROCEDURE insuranceclaimthread_main() -- 创建主存储过程 IS BEGIN --1.获取主要信息插入主表 如保单号 客户名称 init_insuranceclaimthread() --2.更新 投保人相关信息 EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('insuranceclaimthread_main',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('insuranceclaimthread_main',sqlerrm(),sqlcode()) END; ----------------------利用insuranceinfo 插入或更新信息临时表 CREATE OR REPLACE PROCEDURE init_insuranceclaimthread() -- 创建存储过程 IS BEGIN delete insuranceclaimthread_tmp; --插入主要信息 insert into insuranceclaimthread_tmp( contno,-- '保单号', rownumber ,--'同保单号 按照出险日期倒叙排列的行号', CLMNO ,-- '赔案号', scustid ,--'上游客户号' LPName ,--出险人名称 rptdate ,--'报案日期', rgtdate ,--'立案日期', riskdate ,--'出险日期', lloccurreason ,-- '出险原因', -- dialect: ORACLE accidentdetail ,-- '意外细节', -- dialect: ORACLE llclaimstate ,-- '目前状态', -- dialect: ORACLE riskcode ,-- '险种代码', realpay -- '赔付金额' ) select contno, row_number()over(PARTITION BY contno ORDER BY riskdate desc), CLMNO, insuredno, insuredname, rptdate ,--'报案日期', rgtdate ,--'立案日期', riskdate ,--'出险日期', lloccurreason ,-- '出险原因', -- dialect: ORACLE accidentdetail ,-- '意外细节', -- dialect: ORACLE llclaimstate ,-- '目前状态', -- dialect: ORACLE riskcode ,-- '险种代码', realpay -- '赔付金额' from shanghailifeecif.insurance_claim --更新 临时表的客户号 UPDATE insuranceclaimthread_tmp a set LPID = (select indid from scustid_unique_tmp b where a.scustid = b.scustid ) WHERE 1=1; --插入 最近的 insert into insuranceclaimthread ( icthreadid , -- '保险理赔Id', policyno ,--'保险单号 INSURANCE_CLAIM.CONTNO', lpid ,--'出险人 根据INSURANCE_CLAIM.INSUREDNO获取indid', lpname ,--'出险人名称', losstime ,-- '损失发生时间', cndate ,-- '报案日期 INSURANCE_CLAIM.RPTDATE', crdate ,-- '立案日期 INSURANCE_CLAIM.RGTDATE', lossdescribe ,-- '损失原因 案件发生的原因:INSURANCE_CLAIM.LLOCCURREASON', cndescribe ,-- '报案描述 INSURANCE_CLAIM.ACCIDENTDETAIL', claimstatus ,--'理赔状态 案件的状态,例如:录入、已立案等', ccno1 ,--'赔案号1 AUDIT_CLAIM_INSURANCE.CLMNO', ccstatus1 ,-- '赔案状态1', productid1 ,--'险种代码1 INSURANCE_CLAIM_HEALTH.RISKCODE', ccamt1 ,--'赔付金额1 INSURANCE_CLAIM_HEALTH.REALPAY', created_by , created_time ) select row_number()over(), contno, lpid, lpname, riskdate, rptdate, rgtdate, lloccurreason ,-- '出险原因', -- dialect: ORACLE accidentdetail ,-- '意外细节', -- dialect: ORACLE llclaimstate ,-- '目前状态', -- dialect: ORACLE CLMNO , llclaimstate, riskcode ,-- '险种代码', realpay, -- '赔付金额' 'admin', sysdate() from insuranceclaimthread_tmp where rownumber =1; --更新第二近的 update insuranceclaimthread a set ( ccno2,--'赔案号1 AUDIT_CLAIM_INSURANCE.CLMNO', ccstatus2 ,-- '赔案状态1', productid2 ,--'险种代码1 INSURANCE_CLAIM_HEALTH.RISKCODE', ccamt2 --'赔付金额1 INSURANCE_CLAIM_HEALTH.REALPAY', ) = ( select CLMNO , llclaimstate, riskcode ,-- '险种代码', realpay -- '赔付金额' from insuranceclaimthread_tmp b where b.rownumber =2 and a.policyno = b.contno ) where 1=1 EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_insuranceclaimthread',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_insuranceclaimthread',sqlerrm(),sqlcode()) END; ==================================================================================================================== =============================================数据查询语句============================================================ ==================================================================================================================== --contno 保单号 SELECT count(1) FROM ( select contno from shanghailifeecif.insurance_claim group by contno ); 11521 11490 --contno 保单号 SELECT count(1) FROM ( select contno,insuredname,insuredno from shanghailifeecif.insurance_claim group by contno,insuredname,insuredno ); select contno,count(contno) a from shanghailifeecif.insurance_claim group by contno ORDER BY a DESC; SELECT * FROM shanghailifeecif.insurance_claim WHERE contno = 2019101200159088; SELECT * FROM shanghailifeecif.insurance_claim WHERE contno = 2018122000202088; select CLMNO,count(CLMNO) a from shanghailifeecif.insurance_claim group by CLMNO ORDER BY a DESC; SELECT count(1) FROM ( select contno,CLMNO from shanghailifeecif.insurance_claim group by contno,CLMNO ); SELECT * FROM shanghailifeecif.insurance_claim WHERE CLMNO = 3631815101227631;