ShangHaiLifeECIF/sql-achievement/存储过程/洛阳测试环境使用sql整理/个体关系存储过程.sql

DROP TABLE IF EXISTS shanglifeecif.indrelationship_tmp;
CREATE  TABLE shanglifeecif.indrelationship_tmp(
  id string DEFAULT NULL COMMENT 'id', 
  rstype string DEFAULT NULL COMMENT '关系类型 其他、配偶、祖父母、外祖父母、祖孙、外祖孙、本人、父母子女', 
  indid1 string DEFAULT NULL COMMENT '个体1', 
  scustid1 string DEFAULT NULL COMMENT '个体1上游客户号', 
  name1 string DEFAULT NULL COMMENT '个体1名称', 
  sex1 string DEFAULT NULL COMMENT '个体性别1', 
  mphone1 string DEFAULT NULL COMMENT '个体1手机', 
  idcard1 string DEFAULT NULL COMMENT '个体1证件号码', 
  role1 string DEFAULT NULL COMMENT '个体1角色 父亲、母亲、儿子、女儿、妻子、丈夫', 
  indid2 string DEFAULT NULL COMMENT '个体2', 
  scustid2 string DEFAULT NULL COMMENT '个体2上游客户号', 
  name2 string DEFAULT NULL COMMENT '个体2名称', 
  sex2 string DEFAULT NULL COMMENT '个体性别2', 
  mphone2 string DEFAULT NULL COMMENT '个体2手机', 
  idcard2 string DEFAULT NULL COMMENT '个体2证件号码', 
  role2 string DEFAULT NULL COMMENT '个体2角色 父亲、母亲、儿子、女儿、妻子、丈夫', 
  rsstime timestamp DEFAULT NULL COMMENT '开始时间', 
  rsetime timestamp DEFAULT NULL COMMENT '结束时间'
)
COMMENT '个体关系临时表'
stored as ES
with shard number 10
replication 1;

CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_tmp( ) 
IS
DECLARE
	strsql string 
BEGIN
	--删除数据
	DELETE shanglifeecif.indrelationship_tmp;
	--插入数据
	INSERT INTO shanglifeecif.indrelationship_tmp(
		id,
		RSType,
		SCustID1,
		Name1,
		sex1,
		IDCard1,
		Role1,
		SCustID2,
		Name2,
		sex2,
		IDCard2,
		RSSTime,
		Role2
		)
	SELECT 
		row_number()over() AS IRSID,
		max(RELATIONTOAPPNT) AS RSType,
		CUSTOMERNO AS IndID1,
		max(NAME) AS Name1,
		max(sex) AS sex1,
		max(IDNO) AS IDCard1,
		CASE max(RELATIONTOAPPNT) 
			WHEN '子女' 
				THEN CASE max(sex) 
					WHEN 1 THEN '母亲' 
					WHEN 0 THEN '父亲' 
				END
		    WHEN '父母' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '女儿' 
		    		WHEN 0 THEN '儿子' 
		    	END
		    WHEN '配偶' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '妻子' 
		    		WHEN 0 THEN '丈夫' 
		    	END
		    WHEN '祖父母、外祖父母' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '(外)孙女' 
		    		WHEN 0 THEN '(外)祖孙' 
		    	END
		    WHEN '祖孙、外祖孙' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '(外)祖母' 
		    		WHEN 0 THEN '(外)祖父' 
		    	END
			ELSE max(RELATIONTOAPPNT) 
		END AS Role1,
		INSUREDNO AS IndID2,
		max(INSUREDNAME) AS Name2,
		max(insuredsex) AS sex2,
		max(INSUREDIDNO) AS IDCard2,
		min(CUSTOMGETPOLDATE) AS RSSTime,
		CASE max(RELATIONTOAPPNT) 
			WHEN '子女' 
				THEN CASE max(INSUREDSEX) 
					WHEN 1 THEN '女儿' 
					WHEN 0 THEN '儿子' 
				END
		    WHEN '父母' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '母亲' 
		    		WHEN 0 THEN '父亲' 
		    	END
		    WHEN '配偶' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '妻子' 
		    		WHEN 0 THEN '丈夫' 
		    	END
		    WHEN '祖父母、外祖父母' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '(外)祖母' 
		    		WHEN 0 THEN '(外)祖父' 
		    	END
		    WHEN '祖孙、外祖孙' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '(外)孙女' 
		    		WHEN 0 THEN '(外)祖孙' 
		    	END
			ELSE max(RELATIONTOAPPNT) 
		END AS Role2
	FROM shanghailifeecif.POLICY_INFORMATION 
	WHERE RELATIONTOAPPNT<>'本人' AND RELATIONTOAPPNT IS NOT NULL
	GROUP BY CUSTOMERNO,INSUREDNO;
	

	--更新临时表的indid1 indid2
	UPDATE shanglifeecif.indrelationship_tmp a set indid1 = 
		(SELECT b.indid FROM (select 
			indid,row_number() over(partition by scustid) rn ,SCustID
		from shanglifeecif.individual) b where a.SCustID1 = b.SCustID AND b.rn = 1);
	UPDATE shanglifeecif.indrelationship_tmp a set indid2 = 
		(SELECT b.indid FROM (select 
			indid,row_number() over(partition by scustid) rn ,SCustID
		from shanglifeecif.individual) b where a.SCustID2 = b.SCustID AND b.rn = 1);

END;

/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship( ) 
IS
DECLARE
BEGIN
	
	DELETE FROM shanglifeecif.indrelationship;
	--插入数据
	INSERT INTO shanglifeecif.indrelationship(
		IRSID, --'个体关系ID' 
		RSType, --'关系类型 其他、配偶、祖父母、外祖父母、祖孙、外祖孙、本人、父母子女' ,
		IndID1, --'个体1' ,
		Name1, --个体1名称
	
		IDCard1, --个体1证件号码
		Role1,--个体1角色 父亲、母亲、儿子、女儿、妻子、丈夫
		IndID2,--个体2
		Name2,--个体2名称	sex2,--个体2手机
		IDCard2,--个体2证件号码
		Role2,--个体2角色 父亲、母亲、儿子、女儿、妻子、丈夫
		RSSTime,--开始时间
		CREATED_BY,--创建人
		CREATED_TIME--
		)
	SELECT 
		id,
		RSType,
		indid1,
		Name1,
		IDCard1,
		Role1,
		indid2,
		Name2,
		IDCard2,
		Role2,
		RSSTime,
		'admin',
		sysdate
	FROM shanglifeecif.indrelationship_tmp where indid1 is not null and indid2 is not null

	
END;