ShangHaiLifeECIF/sql-achievement/存储过程/保单qxp.sql

--数据源 policy_information 表结构	

-- CREATE  TABLE shanghailifeecif.policy_information(
--   contno varchar2(200) DEFAULT NULL COMMENT '保单号',  -- dialect: ORACLE
--   riskcode varchar2(200) DEFAULT NULL COMMENT '险种代码',  -- dialect: ORACLE
--   riskname varchar2(200) DEFAULT NULL COMMENT '险种名称',  -- dialect: ORACLE
--   riskperiod varchar2(200) DEFAULT NULL COMMENT '险种分类',  -- dialect: ORACLE
--   risktype varchar2(200) DEFAULT NULL COMMENT '险种设计类型',  -- dialect: ORACLE
--   payintv varchar2(200) DEFAULT NULL COMMENT '缴费方式',  -- dialect: ORACLE
--   security varchar2(200) DEFAULT NULL COMMENT '保障年期',  -- dialect: ORACLE
--   payendyear decimal(10,2) DEFAULT NULL COMMENT '缴费年期', 
--   payendyearflag varchar2(200) DEFAULT NULL COMMENT '缴费年期单位',  -- dialect: ORACLE
--   insuyear decimal(10,2) DEFAULT NULL COMMENT '保险年期', 
--   insuyearflag varchar2(200) DEFAULT NULL COMMENT '保险年期单位',  -- dialect: ORACLE
--   paycount decimal(10,2) DEFAULT NULL COMMENT '续保次数', 
--   signdate string DEFAULT NULL COMMENT '承保日期', 
--   cvalidate string DEFAULT NULL COMMENT '保单生效日期', 
--   enddate string DEFAULT NULL COMMENT '保单终止日期', 
--   modifydate string DEFAULT NULL COMMENT '修改日期', 
--   prem decimal(10,2) DEFAULT NULL COMMENT '保费', 
--   appflag varchar2(200) DEFAULT NULL COMMENT '保单状态',  -- dialect: ORACLE
--   customerno varchar2(200) DEFAULT NULL COMMENT '客户号',  -- dialect: ORACLE
--   name varchar2(200) DEFAULT NULL COMMENT '客户姓名',  -- dialect: ORACLE
--   sex varchar2(200) DEFAULT NULL COMMENT '客户性别',  -- dialect: ORACLE
--   birthday string DEFAULT NULL COMMENT '客户出生日期', 
--   idtype varchar2(200) DEFAULT NULL COMMENT '证件类型',  -- dialect: ORACLE
--   idno varchar2(200) DEFAULT NULL COMMENT '证件号码',  -- dialect: ORACLE
--   insuredno varchar2(200) DEFAULT NULL COMMENT '被保人号',  -- dialect: ORACLE
--   insuredname varchar2(200) DEFAULT NULL COMMENT '被保人姓名',  -- dialect: ORACLE
--   insuredsex varchar2(200) DEFAULT NULL COMMENT '被保人性别',  -- dialect: ORACLE
--   insuredbirthday string DEFAULT NULL COMMENT '被保人出生日期', 
--   insuredidtype varchar2(200) DEFAULT NULL COMMENT '被保人证件类型',  -- dialect: ORACLE
--   insuredidno varchar2(200) DEFAULT NULL COMMENT '被保人证件号码',  -- dialect: ORACLE
--   relationtoappnt varchar2(200) DEFAULT NULL COMMENT '与投保人关系',  -- dialect: ORACLE
--   salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道',  -- dialect: ORACLE
--   salecom varchar2(200) DEFAULT NULL COMMENT '业绩归属',  -- dialect: ORACLE
--   salechannels varchar2(200) DEFAULT NULL COMMENT '二级业绩归属',  -- dialect: ORACLE
--   customgetpoldate string DEFAULT NULL COMMENT '客户投保日期', 
--   prem_js decimal(10,2) DEFAULT NULL COMMENT ''
-- )




======================================================================================================================================================
--创建保单表insurancearrangement
DROP TABLE IF EXISTS insurancearrangement;
CREATE  TABLE insurancearrangement(
  iaid string NOT NULL COMMENT '保单ID', 
  policyno string DEFAULT NULL COMMENT '保险单号 INSURANCEINFO.CONTNO', 
  pano string DEFAULT NULL COMMENT '投保单号 INSURANCEINFO.PRTNO', 
  agrmntage string DEFAULT NULL COMMENT '保险期限', 
  pbinst string DEFAULT NULL COMMENT '受益分配方式', 
  pwcomp string DEFAULT NULL COMMENT '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
  pindate date DEFAULT NULL COMMENT '起保日期 POLICY_INFORMATION.CVALIDATE', 
  pmdate date DEFAULT NULL COMMENT '终保日期 POLICY_INFORMATION.ENDDATE', 
  pisdate date DEFAULT NULL COMMENT '签单日期 INSURANCEINFO.SIGNDATE', 
  padate date DEFAULT NULL COMMENT '投保日期 INSURANCEINFO.POLAPPLYDATE', 
  renewaldate date DEFAULT NULL COMMENT '续保日期', 
  norenewal int DEFAULT NULL COMMENT '续保次数 POLICY_INFORMATION.PAYCOUNT', 
  policytype string DEFAULT NULL COMMENT '保单类型 PERSONAL_INSURANCE.CONTTYPE,需上游给出类型枚举值', 
  schannel string DEFAULT NULL COMMENT '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
  bsource string DEFAULT NULL COMMENT '业务来源', 
  policystate string DEFAULT NULL COMMENT '保单状态 保单包括投保单的所处的状态，例如：录入、生效、批单等 INSURANCEINFO.APPFLAG code 0 1 2 4 9 B F',
  topay string DEFAULT NULL COMMENT '缴费类型', 
  payment string DEFAULT NULL COMMENT '缴费方式 POLICY_INFORMATION.PAYINTV', 
  risk double DEFAULT NULL COMMENT '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
  prem double DEFAULT NULL COMMENT '总保费 INSURANCEINFO.PREM', 
  currency string DEFAULT NULL COMMENT '币种', 
  npdate date DEFAULT NULL COMMENT '下次缴费日期', 
  soinsured int DEFAULT NULL COMMENT '被保人数 HEALTH_GROUP_LISTING.PEOPLES3,团单', 
  bsinsured double DEFAULT NULL COMMENT '基本保额 保险合同条款费率表中载明的单位保额。比如中国人寿的康宁终身保险就是这样，如果购买的基本保险金额是10万的话，那么大病的保险金额就是基本保险金额的2倍即20万；', 
  insurvalue double DEFAULT NULL COMMENT '保单价值 保单价值，即保单现金价值。是指带有储蓄性质的人身bai保险单所具有的价值。保险人为履行合同责任通常提存责任准备金，如果您中途退保，即以该保单的责任准备金作为给付解约的退还金。被保险人要求解约或退保时，寿险公司应该发还的金额。
在长期寿险契约中，保险人为履行契约责任，通常需要提存一定数额的责任准备金。当被保险人于保险有效期内因故要求解约或退保时，保险人按规定，将提存的责任准备金减去解约扣除后的余额退还给被保险人，这部分余额即解约金，亦即退保时保单所具有的现金价值。', 
  applicantid string DEFAULT NULL COMMENT '投保人 根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号，再找到individualid', 
  applicantscustid string DEFAULT NULL COMMENT '投保人上游客户号', 
  appname string DEFAULT NULL COMMENT '投保人名称 POLICY_INFORMATION.NAME', 
  appphone string DEFAULT NULL COMMENT '投保人手机', 
  appcertid string DEFAULT NULL COMMENT '投保人证件号码 POLICY_INFORMATION.IDNO', 
  insuredid string DEFAULT NULL COMMENT '主被保险人 客户号', 
  insuredscustid string DEFAULT NULL COMMENT '主被保险人 上游客户号POLICY_INFORMATION.insuredno', 
  insname string DEFAULT NULL COMMENT '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
  insphone string DEFAULT NULL COMMENT '主被保险人手机', 
  inscertid string DEFAULT NULL COMMENT '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
  productid string DEFAULT NULL COMMENT '险种代码 POLICY_INFORMATION.RISKCODE', 
  productname string DEFAULT NULL COMMENT '险种名称 INSURANCEINFO.RISKNAME', 
  salesperson string DEFAULT NULL COMMENT '业务员', 
  spname string DEFAULT NULL COMMENT '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
  iaccount string DEFAULT NULL COMMENT '所属保险账户', 
  branchcode string DEFAULT NULL COMMENT '机构代码', 
  policybelong string DEFAULT NULL COMMENT '业绩归属 POLICY_INFORMATION.SALECOM', 
  agentchannel string DEFAULT NULL COMMENT '代理渠道 INSURANCEINFO.SELLTYPE', 
  agentorg string DEFAULT NULL COMMENT '代理机构', 
  regtype string DEFAULT NULL COMMENT '户籍类型', 
  pincome string DEFAULT NULL COMMENT '个人年收入', 
  fincome double DEFAULT NULL COMMENT '家庭年收入', 
  incomesource string DEFAULT NULL COMMENT '收入来源', 
  socialinsurance string DEFAULT NULL COMMENT '是否有社保', 
  oipolicy string DEFAULT NULL COMMENT '是否投保其他保险公司', 
  oicompany string DEFAULT NULL COMMENT '其他保险公司', 
  oicproduct string DEFAULT NULL COMMENT '其他保险公司险种', 
  oiamount double DEFAULT NULL COMMENT '其他保险公司保额', 
  drinking string DEFAULT NULL COMMENT '是否饮酒', 
  dfavor string DEFAULT NULL COMMENT '饮酒喜好', 
  poservice string DEFAULT NULL COMMENT '是否保全 投保人持有保单是否在AUDIT_EDORLIST.CONTNO存在', 
  payendyear int DEFAULT NULL COMMENT '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
  loanmoney double DEFAULT NULL COMMENT '保单质押贷款金额 AUDIT_LN_LIST.LNMONEY ',
  paydate date DEFAULT NULL COMMENT '缴至日期',
  created_by string DEFAULT NULL COMMENT '创建人', 
  created_time date DEFAULT NULL COMMENT '创建时间', 
  updated_by string DEFAULT NULL COMMENT '更新人', 
  updated_time date DEFAULT NULL COMMENT '更新时间'
)
COMMENT '保单 确定参与方之间，参与方和保险公司之间的协议，使相关各方能在遵守一定的规则并履行相关义务的前提下进行产品或资源或服务的供给'
STORED AS ES
with shard number 10
replication 1;


===================================================================================================================================================
=============================================================存储主过程=============================================================================
===================================================================================================================================================

CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement() -- 创建主存储过程
IS    
BEGIN
	--根据policy_information 更新保单表
	shanglifeecif.init_insurancearrangement_policy_information();

	--根据health_insurance_listing 更新相关字段
	shanglifeecif.up_insurancearrangement_health_insurance_listing();

	--其他标的字段 更新
	shanglifeecif.up_insurancearrangement_other();

	EXCEPTION
		WHEN HIVE_EXCEPTION THEN 
			 log_exception('init_insurancearrangement',sqlerrm(),sqlcode())
		WHEN Others THEN
			 log_exception('init_insurancearrangement',sqlerrm(),sqlcode())
END;





CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_policy_information() -- 创建主存储过程
IS    
BEGIN

INSERT INTO shanglifeecif.insurancearrangement(
  iaid ,--'保单ID', 
  policyno ,-- '保险单号 INSURANCEINFO.CONTNO', 
  pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE', 
  pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE', 
  norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT', 
  payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV', 
  applicantscustid ,-- '投保人 上游客户号，再找到individualid', 
  appname ,-- '投保人名称 POLICY_INFORMATION.NAME', 
  appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO', 
  insuredscustid ,-- '主被保险人 游客户号', 
  insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
  inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
  productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE', 
  policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM',  
  payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
  
  agrmntage ,--保险期限
  
  created_by ,-- '创建人', 
  created_time -- '创建时间', 

) 
SELECT 
	row_number()over(),
	CONTNO,--policyno ,-- '保险单号 INSURANCEINFO.CONTNO', 
	CVALIDATE,--pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE', 
	ENDDATE,--pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE', 
	PAYCOUNT,--norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT', 
	PAYINTV,--payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV', 
	CUSTOMERNO,--applicantid ,-- '投保人 根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号，再找到individualid', 
	NAME,--appname ,-- '投保人名称 POLICY_INFORMATION.NAME', 
	IDNO,--appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO', 
	insuredno,--insuredid ,-- '主被保险人 insuredno', 
	INSUREDNAME,--insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME', 
	INSUREDIDNO,--inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO', 
	RISKCODE,--productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE', 
	SALECOM,--policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM', 
	PAYENDYEAR,--payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR', 
	
	case
		when security ='终身' then 42720
		when security = '至100周岁' then 36500
		when security = '至80周岁' then 29200
		when security = '70年' then 25550
		when security = '至70周岁' then 25550
		when security = '至65周岁' then 23725
		when security = '至60周岁' then 21900
		when security = '30年' then 10950
		when security = '20年' then 7300
		when security = '10年' then 3650
		when security = '6年' then 2190
		when security = '5年' then 1825
		when security = '1年' then 365
		when security = '180天' then 180
		when security = '6月' then 180
		when security = '90天' then 90
		when security = '3月' then 90
		when security = '1月' then 30
		when security = '30天' then 30
		when security = '15天' then 15
		when security = '7天' then 7
	end,
	'admin',
	sysdate() 
	FROM policy_information 

	--更新投保人和被保人id  因为 scustid有重复所以需要去下重

	UPDATE shanglifeecif.insurancearrangement a SET (
		applicantid --'投保人ID',
	) = (
	   select
	   	c.indid
	   from (
		   	select
				row_number()over(PARTITION BY b.scustid) rn,
				b.indid,
				b.scustid
			from shanglifeecif.individual b
	   ) c
	   WHERE c.scustid  = a.applicantscustid and c.rn=1
	) WHERE 1=1 ;


	UPDATE shanglifeecif.insurancearrangement a SET (
		insuredid --'投保人ID',
	) = (
	   select
	   	c.indid
	   from (
		   	select
				row_number()over(PARTITION BY b.scustid) rn,
				b.indid,
				b.scustid
			from shanglifeecif.individual b
	   ) c
	   WHERE c.scustid  = a.insuredscustid and c.rn=1
	) WHERE 1=1 ;

	--更新INSURANCEINFO 表中的信息

	UPDATE shanglifeecif.insurancearrangement a SET (
		pano ,-- '投保单号 INSURANCEINFO.PRTNO',  
		pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE', 
		padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE', 
		policystate ,--INSURANCEINFO,appflag
		prem ,-- '总保费 INSURANCEINFO.PREM', 
		productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
		agentchannel -- '代理渠道 INSURANCEINFO.SELLTYPE', 
	) = (
		select
			PRTNO,--pano ,-- '投保单号 INSURANCEINFO.PRTNO',  
			SIGNDATE,--pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE', 
			POLAPPLYDATE,--padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE', 
			appflag,--policystate ,--INSURANCEINFO,appflag
			PREM,--prem ,-- '总保费 INSURANCEINFO.PREM', 
			RISKNAME,--productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
			SELLTYPE--agentchannel ,-- '代理渠道 INSURANCEINFO.SELLTYPE', 
		from INSURANCEINFO b
		where b.contno  = a.policyno
	) WHERE 1=1 ;




	EXCEPTION
		WHEN HIVE_EXCEPTION THEN 
			 log_exception('init_insurancearrangement_policy_information',sqlerrm(),sqlcode())
		WHEN Others THEN
			 log_exception('init_insurancearrangement_policy_information',sqlerrm(),sqlcode())
END;



==============================================================================================================================================================



--临时表

CREATE TABLE shanglifeecif.insurancearrangement_health_tmp(
    id string,
    orderid string  DEFAULT NULL COMMENT '用于分组的排序号',
    contno string DEFAULT NULL COMMENT '保单号',
  	AGENTGROUP string DEFAULT NULL COMMENT '承保分公司',--pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
	SALECHANNELS string DEFAULT NULL COMMENT '销售渠道',--schannel ,-- '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
	AMNT string DEFAULT NULL COMMENT '总保额',--risk ,-- '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
	AGENTCODE  string DEFAULT NULL COMMENT'业务员名称'--spname ,-- '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
) COMMENT '保单处理个险承保清单自助分析 数据临时表 '
STORED AS ES
with shard number 10
replication 1;


CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_health_insurance_listing() -- 创建主存储过程
IS    
BEGIN
	--清空临时表数据
	delete from shanglifeecif.insurancearrangement_health_tmp;

	--初始化临时表
	insert into shanglifeecif.insurancearrangement_health_tmp (
		id,
		orderid,
		contno,
		agentgroup,
		SALECHANNELS,
		AMNT,
		AGENTCODE
	) select 
		row_number()over(),
		row_number()over(PARTITION BY contno),
		contno,
		agentgroup,
		SALECHANNELS,
		AMNT,
		AGENTCODE
	from HEALTH_INSURANCE_LISTING

	--根据临时表更新insurancearrangement 表
	UPDATE shanglifeecif.insurancearrangement a SET (
		pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
		schannel ,-- '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
		risk ,-- '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
		spname -- '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
	) = (
		select
			b.AGENTGROUP,--pwcomp ,-- '承保分公司 HEALTH_INSURANCE_LISTING.AGENTGROUPAREA 营业区HEALTH_INSURANCE_LISTING.AGENTGROUP 营业部', 
			b.SALECHANNELS,--schannel ,-- '销售渠道 HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
			b.AMNT,--risk ,-- '总保额 HEALTH_INSURANCE_LISTING.AMNT(测试环境中，此表保单数据量较其他表如：POLICY_INFORMATION，INSURANCEINFO 缺少至少一个数据量级)', 
			b.AGENTCODE--spname ,-- '业务员名称 HEALTH_INSURANCE_LISTING.AGENTCODE', 
		from shanglifeecif.insurancearrangement_health_tmp b
		where b.contno  = a.policyno
		and b.orderid = 1
	) WHERE 1=1 ;

	EXCEPTION
		WHEN HIVE_EXCEPTION THEN 
			 log_exception('up_insurancearrangement_health_insurance_listing',sqlerrm(),sqlcode())
		WHEN Others THEN
			 log_exception('up_insurancearrangement_health_insurance_listing',sqlerrm(),sqlcode())
END;




=====================================================================================================================================


==============================================================================================================================================================

CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_other() -- 创建主存储过程
IS    
BEGIN
	


	UPDATE shanglifeecif.insurancearrangement a SET (
		policytype  -- '保单类型', 
	) = (
	   select
	   	c.CONTTYPE
	   from (
		   	select
				row_number()over(PARTITION BY b.contno) rn,
				b.CONTTYPE,
				b.contno
			from PERSONAL_INSURANCE b
	   ) c
	   WHERE c.contno  = a.policyno and c.rn=1
	) WHERE 1=1 ;

	UPDATE shanglifeecif.insurancearrangement a SET (
		poservice	  -- '是否保全', 
	) = (
	   SELECT
	   	IF(count(b.CONTNO) >0,1,0)
	   FROM AUDIT_EDORLIST b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;


	UPDATE shanglifeecif.insurancearrangement a SET (
		soinsured-- '被保人数', 
	) = (
	   SELECT
	   		PEOPLES3
	   FROM HEALTH_GROUP_LISTING b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;

	EXCEPTION
		WHEN HIVE_EXCEPTION THEN 
			 log_exception('up_insurancearrangement_other',sqlerrm(),sqlcode())
		WHEN Others THEN
			 log_exception('up_insurancearrangement_other',sqlerrm(),sqlcode())
END;




=====================================================================================================================================
















--数据分析sql


SELECT count(1) FROM shanghailifeecif.POLICY_INFORMATION ;
--2185616
SELECT count(1) FROM
(
	SELECT count(1) FROM shanghailifeecif.POLICY_INFORMATION GROUP BY CONTNO
);
--1466424


SELECT count(1) FROM shanghailifeecif.INSURANCEINFO GROUP BY CONTNO;
--1520720

SELECT count(1) FROM
(
SELECT count(1) FROM shanghailifeecif.INSURANCEINFO GROUP BY CONTNO
);
--1520720

SELECT count(1) FROM shanghailifeecif.health_insurance_listing WHERE agentgrouparea IS NOT NULL;
--121
SELECT count(1) FROM shanghailifeecif.health_insurance_listing WHERE agentgroup IS NOT NULL;
--10275

SELECT count(1) FROM shanghailifeecif.health_insurance_listing WHERE agentgroup IS  NULL;
--	0

SELECT count(1) a,contno FROM shanghailifeecif.health_insurance_listing GROUP BY contno ORDER BY a DESC;
--有重复

SELECT count(1) a,prtno FROM shanghailifeecif.health_insurance_listing GROUP BY prtno ORDER BY a DESC;
--有重复

SELECT count(1) a,prtno,contno FROM shanghailifeecif.health_insurance_listing GROUP BY prtno,contno ORDER BY a DESC;
--有重复
SELECT * FROM shanghailifeecif.health_insurance_listing WHERE contno = 2018091700073388;
--


SELECT count(1) FROM shanghailifeecif.PERSONAL_INSURANCE;
--3245

SELECT count(contno) a, CONTNO,PRTNO FROM shanghailifeecif.PERSONAL_INSURANCE GROUP BY CONTNO,PRTNO ORDER BY a desc; 
--有重复

SELECT * FROM shanghailifeecif.PERSONAL_INSURANCE WHERE CONTNO = 2020012900030408;

SELECT conttype FROM shanghailifeecif.PERSONAL_INSURANCE GROUP BY conttype;
--只有首年


SELECT count(1) FROM shanghailifeecif.AUDIT_EDORLIST;
--620066
SELECT count(contno) a, CONTNO FROM shanghailifeecif.AUDIT_EDORLIST GROUP BY CONTNO ORDER BY a desc; 
--最多重复61条记录
SELECT * FROM shanghailifeecif.AUDIT_EDORLIST WHERE CONTNO = 2018020800060078;


SELECT count(1) FROM shanghailifeecif.AUDIT_LN_LIST;
--44
SELECT count(contno) a, CONTNO FROM shanghailifeecif.AUDIT_LN_LIST GROUP BY CONTNO ORDER BY a desc; 
--有重复
SELECT * FROM shanghailifeecif.AUDIT_EDORLIST WHERE CONTNO = 2018081600056398
SELECT * FROM shanghailifeecif.AUDIT_LN_LIST WHERE LNMONEY IS NOT NULL;
--LNMONEY 都为空

执行时间 2492s 插入 2185616 条数据