ShangHaiLifeECIF/sql-achievement/存储过程/理赔qxp.sql

---------------------保单数据处理

--创建保险理赔表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;