ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/保单件数分布统计.sql

CREATE TABLE shanglifeecif.bdnum_distribution(
    id string,
    labelName string  DEFAULT NULL COMMENT '保单件数名称',
    bdnum string DEFAULT NULL COMMENT '保单件数'
) COMMENT '保单件数分布'
STORED AS ES
with shard number 10
replication 1;

CREATE OR REPLACE PROCEDURE shanglifeecif.bdnum_distribution() IS
DECLARE
BEGIN
	--第一步 更新 Individual.NOVPolicy 有效记录数
	UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = (SELECT nvl(t.cnum,0) FROM (
		SELECT temp.customerno,count(*) AS cnum FROM (
			SELECT max(p.customerno) customerno,p.contno FROM dsj.policy_information p WHERE p.appflag = '有效' GROUP BY p.contno 
		) temp GROUP BY temp.customerno
	) t WHERE t.customerno =  si1.scustid); 
	
	--第二步 更新 Individual.NOIVPolicy 无效记录数
	UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = (SELECT nvl(t.cnum,0) FROM (
			SELECT temp.customerno,count(*) AS cnum FROM (
			SELECT max(p.customerno) customerno,p.contno FROM dsj.policy_information p WHERE p.appflag <> '有效' GROUP BY p.contno 
		) temp GROUP BY temp.customerno
	) t WHERE t.customerno =  si1.scustid); 
	
	--第三步		
	UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (
		CASE 
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 0 THEN '无保单' 
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 1 THEN '1件保单' 
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 2 THEN '2件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 3  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 5 THEN '3-5件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 6  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 10 THEN '6-10件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 11  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0))<= 20 THEN '11-20件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 21  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 50 THEN '21-50件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) > 50 THEN '50件保单以上' 
		END 
			) where si1.custtype like '%投保人%';
	DELETE FROM shanglifeecif.bdnum_distribution;
	insert into shanglifeecif.bdnum_distribution (
		id,
		labelName,
		bdnum
	) SELECT row_number()over(),t.labelName,t.bdnum FROM (
		SELECT si.label25 labelName,count(1) bdnum FROM shanglifeecif.individual si GROUP BY si.label25 
	) t;
	
	
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());	


END	;