ETL详细设计

待沟通

目前上游无法提供增量数据，所以每次批处理必须全量，效率低
受益人只有三要素证件号码、证件类型、姓名，上游客户号为null
客户等级是否有效的判断规则（有效标志IS_DELETE、失效日期END_DATE全为null，生效日期是否有作用），客户ID（CUSTOMER_ID）是否与核心客户号一致
数据字典中不同表之间存在大量的重复字段，且从测试环境来看，这些表存在差异，如保单数量:HEALTH_INSURANCE_LISTING << POLICY_INFORMATION < INSURANCEINFO，但没有文档说明，无法保证所取字段的正确性。
需提供结案日期

个人信息（Individual）
从保单主表（POLICY_INFORMATION）中获取投保人的上游客户号和五要素，得到 $投保人列表$

code

SELECT CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO;

从个险承保清单自助分析表（POLICY_INFORMATION）中获取被保人的上游客户号和五要素，得到 $被保人列表$

code

SELECT INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO;

从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）中获取受益人的上游客户号和三要素，得到 $受益人列表$ ，实际数据上游客户全为null，同时五要素不齐，故暂时不考虑

code

SELECT BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO FROM HEALTH_INSURANCE_LISTING GROUP BY BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO;

将 $投保人列表$ ， $被保人列表$ 合并去重，得到 $投被保人列表$ ，并按保单生效日期（CVALIDATE）从早到晚排序（客户号生成规则之一）
遍历 $投被保人列表$
标识及证件信息：根据五要素生成CPID，并生成代理主键IndID同时将CPID以及列表中其他字段分别插入个人信息表（Individual）。注意：要根据证件类型，将证件号码插入不同的字段。（如：证件类型为0，则证件号码插入身份证字段）
todo 等级信息：根据上游客户号，从客户等级表（T_CUSTOMER_CLASS）获取客户等级、贡献度分、家庭加分、续期加分、保单加分、总分值。

code

select CLASS_VALUE,CONTRIBUTION_VALUE,AWARDED3,AWARDED2,AWARDED1,TOTAL_VALUE from T_CUSTOMER_CLASS where CUSTOMER_ID = 上游客户号

籍贯、职业：根据上游客户号，从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）获取投保人籍贯，投保人职业代码，投保人职业名称。注意，此表投保人和被保人都有职业代码名称，取最近的一条记录

code

select APPNTHOMEADDRESS,APPNTOCCUPATIONCODE,APPNTOCCUPATIONNAME from HEALTH_INSURANCE_LISTING where APPNTNO = 上游客户号 order by POLAPPLYDATE desc

保单（InsuranceArrangement）

从新契约签单明细清单（INSURANCEINFO）表中获取相关字段插入到InsuranceArrangement表，由于INSURANCEINFO表中是以保单号唯一的，所以直接插入。语法见下，字段映射详见模型文档

code

INSERT INTO InsuranceArrangement(PolicyNo,PANo) 
SELECT contno,prtno
FROM  INSURANCEINFO

用保单信息表（POLICY_INFORMATION）去更新InsuranceArrangement，由于POLICY_INFORMATION不是以保单号唯一的，所以先要去重。由于星环inceptor不支持如下在update中使用group by的语法,所以可以对POLICY_INFORMATION按保单号去重后存入临时表POLICY_INFORMATION_tmp

code

insert into POLICY_INFORMATION_tmp(contno,CVALIDATE,ENDDATE)
SELECT contno,max(CVALIDATE),max(ENDDATE)   
ROM POLICY_INFORMATION
GROUP BY contno

更新临时表POLICY_INFORMATION_tmp中投保人（ApplicantID），主被保险人（InsuredID）为individual表的个体ID(IndID),可根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号（SCustID），得到IndID，支持update，临时表设置为ORC事务表

code

UPDATE POLICY_INFORMATION_tmp
SET ApplicantID =
(SELECT ApplicantID
FROM individual
WHERE (POLICY_INFORMATION_tmp.CUSTOMERNO = individual.SCustID))
WHERE 1=1;

最后用临时表更新InsuranceArrangement（code2）,为了支持update，InsuranceArrangement表设置为ORC事务表。语法见下，字段映射详见模型文档

code2

UPDATE InsuranceArrangement
SET (PINDate,PMDate) =
(SELECT CVALIDATE,ENDDATE
FROM POLICY_INFORMATION_tmp
WHERE (POLICY_INFORMATION_tmp.contno = InsuranceArrangement.PolicyNo))
WHERE 1=1;

理赔（InsuranceClaimThread）

首先将个单理赔表（INSURANCE_CLAIM）按保单和赔案号去重并存入临时表INSURANCE_CLAIM_tmp

code

insert into INSURANCE_CLAIM_tmp(ontno,clmno,RISKDATE,INSUREDNO,RPTDATE,LLOCCURREASON,RGTDATE,ACCIDENTDETAIL,LLCLAIMSTATE)
SELECT contno,clmno,max(RISKDATE),max(INSUREDNO),max(RPTDATE),max(LLOCCURREASON),max(RGTDATE),max(ACCIDENTDETAIL),max(LLCLAIMSTATE)
FROM INSURANCE_CLAIM GROUP BY contno,clmno

更新INSURANCE_CLAIM_tmp中出险人为individual表的个体ID(IndID)
将INSURANCE_CLAIM_tmp相应字段直接插入到InsuranceClaimThread表中，字段映射详见模型文档。

接触(Communication)
事件(PartyTimeLine)
目前主要有以下事件，一个事件一条记录，包括开始时间和结束时间
投保：参与方为投保人，Scenario='0001',SDate=InsuranceArrangement.PADate,EndDate=InsuranceArrangement.PISDate
保全:todo 等待上游提供数据字典
报案:参与方为报案人，Scenario='0003',SDate=InsuranceClaimThread.CNDate,EndDate= todo 等待上游提供结案日期字段
投诉:
咨询:

main.md 5.8 KB Histórico Raw

ETL详细设计

待沟通

个人信息（Individual）

保单（InsuranceArrangement）

理赔（InsuranceClaimThread）

接触(Communication)

事件(PartyTimeLine)

main.md 5.8 KB

Histórico Raw