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.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;			
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	;
*/