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.riskcode_statistics() IS
DECLARE
BEGIN
	delete from shanglifeecif.riskcode_statistics;
	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 shanghailifeecif.lmriskapp sl 
	LEFT JOIN shanghailifeecif.policy_information spi ON spi.riskcode = sl.riskcode
	GROUP BY sl.kindcode
	) temp
	
	
	/*SELECT row_number()over(),tt.riskcode,tt.riskname,tt.khnum,tt.totalPrem FROM (
	SELECT sl.riskcode,sl.riskname,count(spi.idno) AS khnum,sum(spi.prem) AS totalPrem FROM shanghailifeecif.lmriskapp sl 
	LEFT JOIN shanghailifeecif.policy_information spi ON spi.riskcode = sl.riskcode
	GROUP BY sl.riskcode,sl.riskname
	) tt WHERE tt.khnum > 0 AND tt.totalPrem >0*/
	
	/*SELECT slk.kindcode,sum(temp.khnum),sum(totalPrem) FROM (
		SELECT sl.riskcode,sl.riskname,count(spi.idno) AS khnum,sum(spi.prem) AS totalPrem FROM shanghailifeecif.lmriskapp sl 
	LEFT JOIN shanghailifeecif.policy_information spi ON spi.riskcode = sl.riskcode
	GROUP BY sl.riskcode,sl.riskname
	) temp LEFT JOIN shanghailifeecif.lmriskapp slk ON slk.riskcode =  temp.riskcode
	GROUP BY slk.kindcode*/
	
			
END	;