ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/险种大类统计.sql

CREATE TABLE shanglifeecif.riskcode_statistics(
    id string,
    kindcode string  DEFAULT NULL COMMENT '险种大类代码',
    kindNAme string DEFAULT NULL COMMENT '险种大类名称',
  	khnum string DEFAULT NULL COMMENT '客户数量',--pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
	tatolprem string DEFAULT NULL COMMENT '总保额'
) COMMENT '险种大类统计表'
STORED AS ES
with shard number 10
replication 1;

CREATE OR REPLACE PROCEDURE shanglifeecif.customertotaltemp() IS
DECLARE
BEGIN
		DELETE FROM shanglifeecif.customertotaltemp;
		insert into shanglifeecif.customertotaltemp(
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	idcard,
		 	idtype
		)
		SELECT 
			scustid ,
			max(name) as name,
			max(gender) as gender ,
			to_char(max(birthday),"yyyy-MM-dd")  birthday,
			max(idcard) as idcard,
			max(idtype) AS idtype
		FROM (
			SELECT
				trim(customerno) AS scustid,--投保人
				trim(name) AS name,
				trim(sex) AS gender,
				trim(birthday) AS  birthday,
				trim(idtype) AS idtype , 
				trim(idno) AS idcard
			FROM
				dsj.policy_information 
			WHERE customerno IS NOT NULL
			UNION 
			SELECT 
				trim(insuredno) AS scustid,--被保人
				trim(insuredname) AS name,
				trim(insuredsex) AS gender,
				trim(insuredbirthday) AS  birthday,
				trim(insuredidtype) AS idtype , 
				trim(insuredidno) AS idcard
			FROM
				dsj.policy_information
			WHERE insuredno IS NOT NULL
			) tmpTable GROUP BY scustid;
			
		UPDATE shanglifeecif.customertotaltemp sct SET sct.mobile = (SELECT trim(t.MOBILE) FROM dsj.t_customer_class t WHERE sct.scustid=t.customer_id);

	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	;


CREATE OR REPLACE PROCEDURE shanglifeecif.customer_risk_temp() 
IS
DECLARE
BEGIN
	DELETE FROM shanglifeecif.customer_risk_temp;
     INSERT INTO shanglifeecif.customer_risk_temp(
	id,
	  scustid,
	  name,
	  gender,
	  idcard,
	  birthday,
	  policyno,
	  productname,
	  riskcategoriesname,
	  risk
	) 
	SELECT
		row_number()over(),
		si.scustid,
		si.name,
		CASE si.gender 
			WHEN '0' THEN '男' 
			WHEN '1' THEN '女'
			END AS gender,
			si.idcard,
			to_char(si.birthday,"yyyy-MM-dd") birthday,
			sit.policyno,
			sit.productname,
			sit.risk_categories_name,
			sit.Risk
	FROM shanglifeecif.individual si 
	LEFT JOIN shanglifeecif.insurancearrangement sit ON si.scustid = sit.applicantscustid;
		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	;

/

CREATE OR REPLACE PROCEDURE shanglifeecif.riskcode_statistics() IS
DECLARE
BEGIN
     DELETE FROM shanglifeecif.riskcode_statistics; 
	insert into shanglifeecif.riskcode_statistics (
		id,
		kindName,
		khnum,
		tatolprem
	) --险种大类统计
		SELECT 
			row_number()over(),
			temp.kindName,
			temp.khnum,
			temp.tatolprem
		FROM (
			SELECT 
				crt.riskcategoriesname AS kindName,
				count(DISTINCT scustid) AS khnum,
				sum(risk) AS tatolprem
			FROM shanglifeecif.customer_risk_temp crt 
			WHERE crt.riskcategoriesname IS NOT null
			GROUP BY crt.riskcategoriesname
	
		) temp;
	
	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	;
/*
CREATE OR REPLACE PROCEDURE shanglifeecif.riskcode_statistics() IS
DECLARE
BEGIN
     DELETE FROM shanglifeecif.riskcode_statistics; 
	insert into shanglifeecif.riskcode_statistics (
		id,
		kindName,
		khnum,
		tatolprem
	) --险种大类统计
		SELECT 
			row_number()over(),
			temp.kindName,
			temp.khnum,
			temp.tatolprem
		FROM (
			select 
				a.risk_categories_name AS kindName ,
				count(*) AS khnum,
				nvl(sum(a.prem),0) AS tatolprem  
			FROM shanglifeecif.insurancearrangement a GROUP BY a.risk_categories_name
	
		) temp;



	insert into shanglifeecif.riskcode_statistics (
		id,
		kindcode,
		kindNAme,
		khnum,
		tatolprem
	) --险种大类统计
	SELECT row_number()over(),temp.kindcode,temp.kindname,temp.khnum,temp.totalPrem FROM (
		SELECT sl.kindcode,
	(CASE sl.kindcode 
		WHEN 'A' THEN '意外伤害险' 
		WHEN 'U' THEN '万能保险' 
		WHEN 'R' THEN '年金保险' 
		WHEN 'S' THEN '重疾保险' 
		WHEN 'L' THEN '人寿保险' 
		WHEN 'H' THEN '健康险' 
	END) AS kindname,
	count(spi.idno) AS khnum,nvl(sum(spi.prem),0) AS totalPrem FROM lmriskapp sl 
	LEFT JOIN policy_information spi ON spi.riskcode = sl.riskcode
	GROUP BY sl.kindcode
	) temp;
	
	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	;
*/