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
	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_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
END	;