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
	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;
	
		
	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 1 = 1;	
END	;