ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/年龄段性别分布统计.sql

CREATE  TABLE shanglifeecif.age_sex_distribution(
  id string DEFAULT NULL, 
  labelname string DEFAULT NULL COMMENT '阶段名称', 
  gender string DEFAULT NULL COMMENT '性别', 
  cusnum string DEFAULT NULL COMMENT '客户数量'
)
COMMENT '年龄段性别分布'
STORED AS ES
with shard number 10
replication 1;

CREATE OR REPLACE PROCEDURE shanglifeecif.age_sex_distribution() IS
DECLARE
BEGIN

	DELETE FROM shanglifeecif.age_sex_distribution;
	insert into shanglifeecif.age_sex_distribution (
		id,
		labelName,
		gender,
		cusNum
	) SELECT row_number()over(),t.labelName,t.gender,t.cusNum FROM (
	SELECT si.label4 labelName,si.gender,count(1) cusNum FROM shanglifeecif.individual si GROUP BY si.label4,si.gender
	) 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	;