--更新客户标签信息 CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable() IS DECLARE BEGIN --label1: 无来源。Individual.Education --label2: Individual.Occupation，逻辑见上 UPDATE shanglifeecif.Individual si1 SET si1.label2 = (SELECT si2.Occupation FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --标签3 根据Individual.Birthday年代，更新为70后，80后等 UPDATE shanglifeecif.Individual si1 SET si1.label3 = (SELECT substr(si2.birthday,3,1) || '0后' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label4：根据Individual.Birthday算出年龄，儿童:[0,13),少年:[13,19),青年:[19,41),中年:[41,66),老年:[66,) UPDATE shanglifeecif.Individual si1 SET si1.label4 = (SELECT CASE WHEN temp.age>=0 AND temp.age<13 THEN '儿童' WHEN temp.age>=13 AND temp.age <19 THEN '少年' WHEN temp.age >=19 AND temp.age <41 THEN '青年' WHEN temp.age >=41 AND temp.age <66 THEN '中年' ELSE '老年' END FROM (SELECT TRUNC(months_between(sysdate, si2.birthday)/12) age,si2.indid FROM shanglifeecif.Individual si2) temp WHERE temp.indid = si1.indid); --Label5: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录 --Label6: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录 --Label7：保留暂不实现 --Label8：保留暂不实现 --标签9 已婚，未婚 无来源。Individual.MaritalStat，查询个体关系表(IndRelationShip)，RSType = '配偶'，如果有记录则更新为已婚，否则更新为null UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT CASE WHEN temp.cnum >0 THEN '已婚' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '配偶' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1); --UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT CASE WHEN cnum >0 THEN '已婚' END FROM (SELECT nvl(count(*),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE si1.indid = sirs.indid2 AND sirs.rstype = '配偶' GROUP BY sirs.indid2) temp WHERE si1.indid = temp.indid2) --Label10：查询IndRelationShip，RSType='父母'，首先将返回记录数更新Individual.COChild，若记录数为0，则更新本字段为'无子女'，若记录数为1，则更新本字段为'1孩'，若记录数大于等于2，则更新本字段为'2孩及以上' UPDATE shanglifeecif.Individual si1 SET si1.COChild = (SELECT CASE WHEN temp.cnum == 0 THEN '无子女' WHEN temp.cnum == 1 THEN '1孩' ELSE '2孩及以上' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '父母' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1); UPDATE shanglifeecif.Individual si1 SET si1.label10 = (SELECT CASE WHEN temp.cnum == 0 THEN '无子女' WHEN temp.cnum == 1 THEN '1孩' ELSE '2孩及以上' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '父母' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1); --UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT '已婚' FROM shanglifeecif.IndRelationShip sirs WHERE si1.indid = sirs.indid2 AND sirs.rstype = '配偶') --Label11：todo:无来源，逻辑需要说明清楚 --Label12：todo:无来源，逻辑需要说明清楚。Individual.Drinking UPDATE shanglifeecif.Individual si1 SET si1.label12 = (SELECT si2.Drinking FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label13：todo:无来源，逻辑需要说明清楚。Individual.Smoking UPDATE shanglifeecif.Individual si1 SET si1.label13 = (SELECT si2.Smoking FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label14：todo:无来源，逻辑需要说明清楚。Individual.Cdiseases --Label15：todo:无来源，逻辑需要说明清楚。 --Label16：todo:无来源，逻辑需要说明清楚。Individual.FMHistory --Label17：todo:无来源，逻辑需要说明清楚。Individual.Pregnancy --Label18：todo:无来源，逻辑需要说明清楚。 --Label19：保留暂不实现 --Label20：todo:无来源，逻辑需要说明清楚。 --Label21：todo:无来源，Individual.PIncome:[0,120000],'低收入';(120000,1000000],'中产';(1000000,),'富人' UPDATE shanglifeecif.Individual si1 SET si1.Label21 = (SELECT CASE WHEN si2.pincome >=0 AND si2.pincome<=120000 THEN '低收入' WHEN si2.pincome >120000 AND si2.pincome<=1000000 THEN '中产' WHEN si2.pincome > 1000000 THEN '富人' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label22：todo:无来源，Individual.COVehicle:0,'无车';1,'1辆车';(1,),'2辆车以上' UPDATE shanglifeecif.Individual si1 SET si1.Label22 = (SELECT CASE WHEN si2.COVehicle ==0 THEN '无车' WHEN si2.COVehicle == 1 THEN '1辆车' WHEN si2.COVehicle > 1 THEN '2辆车以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label23：todo:无来源，Individual.CORP:0,'无房';1,'1套房';(1,),'2套房以上' UPDATE shanglifeecif.Individual si1 SET si1.Label23 = (SELECT CASE WHEN si2.CORP ==0 THEN '无房' WHEN si2.CORP == 1 THEN '1套房' WHEN si2.CORP > 1 THEN '2套房以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label24：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG = '有效'，若无记录则更新Individual.NOVPolicy为0，否则更新NOVPolicy为记录数。若NOVPolicy>0，则更新Individual.Label24为'持有寿险有效保单'，否则更新为'未持有寿险有效保单' --第一步 更新Individual.NOVPolicy有效记录数 UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = (SELECT temp.cuscount FROM (SELECT nvl(count(spi.customerno),0) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno WHERE spi.appflag = '有效' GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --第二步 更新 Individual.NOIVPolicy 无效记录数 UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = (SELECT temp.cuscount FROM (SELECT nvl(count(spi.customerno),0) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno WHERE spi.appflag <> '有效' GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --最后一步 更新标签 24 UPDATE shanglifeecif.Individual si1 SET si1.label24 = (SELECT CASE WHEN nvl(si2.novpolicy,0)>0 THEN '持有寿险有效保单' ELSE '未持有寿险有效保单' END FROM shanglifeecif.individual si2 WHERE si2.indid = si1.indid); --Label25：将Individual.NOVPolicy+Individual.NOIVPolicy的值更新ndividual.Label25 UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (SELECT (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (SELECT CASE WHEN si2.label25 == 0 THEN '无保单' WHEN si2.label25 == 1 THEN '1件' WHEN si2.label25 == 2 THEN '2件' WHEN si2.label25 >= 3 AND si2.label25 <= 5 THEN '3-5件' WHEN si2.label25 >= 6 AND si2.label25 <= 10 THEN '6-10件' WHEN si2.label25 >= 11 AND si2.label25 <= 20 THEN '11-20件' WHEN si2.label25 >= 21 AND si2.label25 <= 50 THEN '21-50件' WHEN si2.label25 > 50 THEN '50件以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label26：InsuranceArrangement.PolicyNo = AUDIT_LN_LIST.CONTNO，以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney，以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，若无记录更新Individual.Label26为Null,若LoanMoney>0则更新Individual.Label26为'有贷款'，否则更新为'无贷款' --第一步 以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney UPDATE shanglifeecif.insurancearrangement sig SET sig.LoanMoney =(SELECT temp.lnmoney FROM (SELECT sall.contno,sum(sall.lnmoney) lnmoney FROM shanghailifeecif.audit_ln_list sall GROUP BY sall.contno) temp WHERE temp.contno = sig.policyno); --第二步 以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID) --SELECT sig.applicantid,sum(sig.loanmoney) loanmoney FROM shanglifeecif.insurancearrangement sig GROUP BY sig.applicantid UPDATE shanglifeecif.Individual si1 SET si1.Label26 =(SELECT CASE WHEN temp.loanmoney > 0 THEN '有贷款' WHEN temp.loanmoney = 0 THEN '无贷款' ELSE NULL END FROM (SELECT sig.applicantid,sum(sig.loanmoney) loanmoney FROM shanglifeecif.insurancearrangement sig GROUP BY sig.applicantid) temp WHERE si1.indid = temp.applicantid); --Label27：todo:无来源，逻辑需要说明清楚。 --Label28：保留暂不实现 --Label29：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO），POLICY_INFORMATION.APPFLAG='有效' and POLICY_INFORMATION.PAYINTV='趸缴'，若有记录则更新Individual.NOSPInsurance和本标签为'有趸交保单' UPDATE shanglifeecif.Individual si1 SET si1.label29 = (SELECT CASE WHEN temp.cnum>0 THEN '有趸交保单' END FROM (SELECT count(1) cnum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.APPFLAG = '有效' AND spi.PAYINTV = '趸缴' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.NOSPInsurance = (SELECT CASE WHEN temp.cnum>0 THEN '有趸交保单' END FROM (SELECT count(1) cnum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.APPFLAG = '有效' AND spi.PAYINTV = '趸缴' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label30：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.ISelf为NULL，若有记录且RELATIONTOAPPNT仅为'本人'则若无记录则更新Individual.ISelf为'仅本人投保'，若有记录且RELATIONTOAPPNT不为'本人'则若无记录则更新Individual.ISelf为'本人未投保'，否则更新Individual.ISelf为'本人已投保' UPDATE shanglifeecif.Individual si1 SET si1.iself = (SELECT CASE WHEN temp.snum>0 THEN '本人已投保' WHEN temp.snum=0 THEN '本人未投保' END FROM (SELECT nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '本人' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.iself = (SELECT CASE WHEN temp.snum=0 AND si1.iself ='本人已投保' THEN '仅本人投保' END FROM (SELECT nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '本人' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --label31 以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Iparent为NULL，若有记录且RELATIONTOAPPNT仅为'父母'则若无记录则更新Individual.Iparent为'仅父母投保'，若有记录且RELATIONTOAPPNT不为'父母'则若无记录则更新Individual.Iparent为'父母未投保'，否则更新Individual.Iparent为'父母已投保' UPDATE shanglifeecif.Individual si1 SET si1.Iparent = (SELECT CASE WHEN temp.snum > 0 THEN '父母已投保' WHEN temp.snum=0 THEN '父母未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '父母' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.Iparent = (SELECT CASE WHEN temp.snum=0 AND si1.Iparent = '父母已投保' THEN '仅父母投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '父母' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label32：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.IChildren为NULL，若有记录且RELATIONTOAPPNT仅为'子女'则若无记录则更新Individual.IChildren为'仅子女投保'，若有记录且RELATIONTOAPPNT不为'子女'则若无记录则更新Individual.IChildren为'子女未投保'，否则更新Individual.IChildren为'子女已投保' UPDATE shanglifeecif.Individual si1 SET si1.IChildren = (SELECT CASE WHEN temp.snum>0 THEN '子女已投保' WHEN temp.snum=0 THEN '子女未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.IChildren = (SELECT CASE WHEN temp.snum=0 AND si1.IChildren = '子女已投保' THEN '仅子女投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label33：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Imate为NULL，若有记录且RELATIONTOAPPNT仅为'配偶'则若无记录则更新Individual.Imate为'仅配偶投保'，若有记录且RELATIONTOAPPNT不为'配偶'则若无记录则更新Individual.Imate为'配偶未投保'，否则更新Individual.Imate为'配偶已投保' UPDATE shanglifeecif.Individual si1 SET si1.Imate = (SELECT CASE WHEN temp.snum>0 THEN '配偶已投保' WHEN temp.snum=0 THEN '配偶未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.Imate = (SELECT CASE WHEN temp.snum=0 AND si1.Imate = '配偶已投保' THEN '仅配偶投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid) --Label34：保留暂不实现 --Label35：保留暂不实现 --Label36：保留暂不实现 --Label37：保留暂不实现 --Label38：保留暂不实现 --Label39：保留暂不实现 --Label40：保留暂不实现 --Label41：保留暂不实现 --Label42：保留暂不实现 --Label43：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label44：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG<>'有效'，若无记录则更新Individual.NOIVPolicy为0，否则更新NOIVPolicy为记录数。若NOIVPolicy>0，则更新Individual.Label44为'有失效保单'，否则更新为'无失效保单' UPDATE shanglifeecif.Individual si1 SET si1.label44 = (SELECT CASE WHEN si2.noivpolicy>0 THEN '有失效保单' END FROM shanglifeecif.individual si2 WHERE si2.indid = si1.indid); --LABEL45:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且riskperiod='长期险'，若无记录则更新Individual.Label45为NULL，若有记录且paycount= payendyear则更新Individual.Label45为'有缴费期满长险保单'，否则更新Individual.Label45为'无缴费期满长险保单' UPDATE shanglifeecif.Individual si1 SET si1.label45 = (SELECT CASE WHEN temp.cuscount IS NULL THEN NULL WHEN temp.cuscount>0 THEN '有缴费期满长险保单' ELSE '无缴费期满长险保单' END FROM (SELECT count(spi.customerno) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno WHERE spi.riskperiod='长期险' AND spi.paycount = spi.payendyear GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --Label46：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label46为NULL，若有记录且paycount= payendyear则更新Individual.Label46为'有续期缴费的保单'，否则更新Individual.Label46为'无有续期缴费的保单' UPDATE shanglifeecif.Individual si1 SET si1.label46 = (SELECT CASE WHEN temp.cuscount IS NULL THEN NULL WHEN temp.cuscount>0 THEN '有续期缴费的保单' ELSE '无有续期缴费的保单' END FROM (SELECT count(spi.customerno) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno WHERE spi.paycount = spi.payendyear GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --Label47：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label48：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最早日期(PADate)更新Individual.FADate UPDATE shanglifeecif.Individual si1 SET si1.FADate = (SELECT '首次投保日期' || temp.padate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid); --Label49：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最近日期(PADate)更新Individual.LAPPDate，最后用当前日期减去Individual.LAPPDate更新Label49 UPDATE shanglifeecif.Individual si1 SET si1.LAPPDate = (SELECT temp.PADate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid); UPDATE shanglifeecif.Individual si1 SET si1.Label49 = (SELECT '最后一次投保距今' || TRUNC(months_between(sysdate(), si2.LAPPDate)/12) || '天' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel --Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel，并更新此标签为：'最近保单'+Individual.LPChannel UPDATE shanglifeecif.Individual si1 SET si1.LPChannel = (SELECT temp.agentchannel FROM (SELECT si.agentchannel,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate DESC) rd FROM shanglifeecif.InsuranceArrangement si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label50 = (SELECT '最近保单' || si1.LPChannel FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label51：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentOrg更新Individual.LPOrg，并更新此标签为：'最近'+Individual.LPOrg+'机构' UPDATE shanglifeecif.Individual si1 SET si1.LPOrg = (SELECT temp.AgentOrg FROM (SELECT si.AgentOrg,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate DESC) rd FROM shanglifeecif.InsuranceArrangement si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label51 = (SELECT '最近' || si2.LPOrg || '机构' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid) ; --Label52：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentChannel更新Individual.FAChannel UPDATE shanglifeecif.Individual si1 SET si1.FAChannel = (SELECT tt.agentchannel FROM (SELECT si.agentchannel,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si ) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label52 = (SELECT '首单' || si2.FAChannel FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label53：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentOrg更新Individual.FAOrg UPDATE shanglifeecif.Individual si1 SET si1.FAOrg = (SELECT tt.AgentOrg FROM (SELECT si.AgentOrg,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label53 = (SELECT '首单' || si2.FAOrg || '机构' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label54：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的PolicyState更新Individual.LPState UPDATE shanglifeecif.Individual si1 SET si1.LPState = (SELECT tt.PolicyState FROM (SELECT si.PolicyState,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1); --Label55 :Label55：由于上游保全类型信息分散在AUDIT_EDORLIST.EDORNAME和edorinfo.edortype 中，同时edorinfo.edortype为code需要转为string（具体映射见下）。以上游客户号(Individual.SCustID)分别查询AUDIT_EDORLIST表和edorinfo表，取保全生效日期最近的记录的更新的保全类型更新Individual.LPOSType。todo:edorinfo.edortype为code，需要转为string，edorinfo中需要增加上游客户号，否则需要多一次关联，建议大数据平台处理完成。 --AUDIT_EDORLIST表里没有上游客户号，UPDATE shanglifeecif.Individual si1 SET si1.LPOSType =(SELECT ae.edorname FROM shanghailifeecif.audit_edorlist ae ) UPDATE shanglifeecif.Individual si1 SET si1.lpostype = (SELECT tt.edortype FROM ( SELECT temp.scustid,temp.edortype,temp.edorvalidate,row_number()OVER(PARTITION BY temp.scustid ORDER BY temp.edorvalidate DESC) rd FROM ((SELECT si2.scustid,sae.edorname edortype,sae.edorvalidate FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.audit_edorlist sae ON sae.contno = spi.contno) UNION ( SELECT si2.scustid,se.edortype,se.edorappdate edorvalidate FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.edorinfo se ON se.contno = spi.contno)) temp) tt WHERE tt.scustid = si1.scustid AND tt.rd = 1); --Label56：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询INSURANCEINFO表 and INSURANCEINFO.APPFLAG = '已退保',若有记录则更新此标签为'有退保保单' UPDATE shanglifeecif.Individual si1 SET si1.Label56 = (SELECT CASE WHEN temp.cunm > 0 THEN '有退保保单' END FROM ( SELECT count(1) cunm,si2.scustid FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.INSURANCEINFO sio ON spi.CONTNO = sio.CONTNO WHERE sio.APPFLAG = '已退保' GROUP BY si2.scustid ) temp WHERE temp.scustid = si1.scustid); --Label57：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询AUDIT_CLAIM_INSURANCE表，取AUDIT_CLAIM_INSURANCE.RGTDATE最近对应的AUDIT_CLAIM_INSURANCE.RISKTYPE更新此标签。todo:实际数据仅有寿险、意外险、健康险，取值是否正确？ UPDATE shanglifeecif.Individual si1 SET si1.Label57 = (SELECT temp.RISKTYPE FROM ( SELECT si2.scustid,saci.RISKTYPE,row_number()OVER(PARTITION BY si2.scustid ORDER BY saci.RGTDATE DESC) rd FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.audit_claim_insurance saci ON saci.CONTNO = spi.CONTNO ) temp WHERE si1.scustid = temp.scustid AND temp.rd = 1); --Label59 :以个体ID(Individual.IndID)查询InsuranceClaimThread表(Individual.IndID=InsuranceClaimThread.ApplicantID)， 以最近日期(CNDate)的ClaimCaseStatus更新Individual.CNStatus UPDATE shanglifeecif.Individual si1 SET si1.CNStatus =(SELECT temp.ClaimCaseStatus FROM (SELECT si.ClaimCaseStatus,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.CNDate DESC) rd FROM shanglifeecif.insuranceclaimthread si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); --Label60：保留暂不实现 --Label61：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label62：保留暂不实现 --Label63：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label64：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label65：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label65为NULL，若仅一条记录则更新Individual.Label65为'一单寿险客户' UPDATE shanglifeecif.Individual si1 SET si1.Label65 = (SELECT CASE WHEN tt.cnum = 1 THEN '一单寿险客户' END FROM (SELECT nvl(count(spi.customerno),0) cnum,max(si2.indid) indid FROM shanghailifeecif.policy_information spi LEFT JOIN shanglifeecif.Individual si2 ON si2.scustid = spi.customerno GROUP BY spi.customerno) tt WHERE si1.indid = tt.indid); --Label66：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label66为NULL，若 paycount= payendyear更新Individual.Label66为'缴费期满客户'，否则更新为'非缴费期满客户 UPDATE shanglifeecif.Individual si1 SET si1.Label66 = (SELECT CASE WHEN tt.cnum >0 THEN '缴费期满客户' END FROM (SELECT nvl(count(spi.customerno),0) cnum,max(si2.indid) indid FROM shanghailifeecif.policy_information spi LEFT JOIN shanglifeecif.Individual si2 ON si2.scustid = spi.customerno and spi.paycount= spi.payendyear GROUP BY spi.customerno) tt WHERE si1.indid = tt.indid); --Label67：若Individual.Birthday的月日与上海人寿司庆日月日相同，则设置此标签为：'生日与司庆日同天'。todo:需要确定司庆日是几月几号 UPDATE shanglifeecif.Individual si1 SET si1.Label67 = (SELECT CASE WHEN substr(si2.birthday,6,10) = '02-15' THEN '生日与司庆日同天' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label68:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表, and POLICY_INFORMATION.APPFLAG='有效'，若有记录则设置此标签为'持有有效保单'，否则则设置为'未持有有效保单' UPDATE shanglifeecif.Individual si1 SET si1.Label68 = (SELECT CASE WHEN si2.NOVPolicy>0 THEN '持有有效保单' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label69：Individual.CustClass，逻辑见上(一.7) UPDATE shanglifeecif.Individual si1 SET si1.Label69 = (SELECT si2.CustClass FROM shanglifeecif.Individual si2 WHERE si1.IndID = si2.IndID); --Label70:Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Scenario更新此标签 UPDATE shanglifeecif.Individual si1 SET si1.Label70 = (SELECT temp.Scenario FROM (SELECT sp.Scenario,sp.PartyID,row_number()OVER(PARTITION BY sp.PartyID ORDER BY sp.SDate DESC) rd FROM shanglifeecif.PartyTimeLine sp) temp WHERE si1.indid = temp.PartyID AND temp.rd = 1); --Label71：Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Channel更新此标签 UPDATE shanglifeecif.Individual si1 SET si1.Label71 = (SELECT temp.Channel FROM (SELECT sp.Channel,sp.PartyID,row_number()OVER(PARTITION BY sp.PartyID ORDER BY sp.SDate DESC) rd FROM shanglifeecif.PartyTimeLine sp) temp WHERE si1.indid = temp.PartyID AND temp.rd = 1); --Label72：保留暂不实现 --Label73 --Label74 --Label75 --Label76 --Label77：Individual.Birthday 距离当前日期5天内，更新Individual.Label77为'本人生日临近'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label77 = (SELECT CASE WHEN DATEDIFF(sysdate(), si2.birthday)<=5 THEN '本人生日临近' END FROM shanglifeecif.Individual si2 WHERE si1.IndID = si2.IndID); --Label78:以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'父亲'或'母亲'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label78为'父母生日临近'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label78 = (SELECT CASE WHEN DATEDIFF(sysdate(), si1.birthday)<=5 THEN '父母生日临近' END FROM ( SELECT sis.indid2 FROM shanglifeecif.Individual si2 LEFT JOIN shanglifeecif.IndRelationShip sis ON si2.indid = sis.indid1 WHERE (sis.role2 = '父亲' or sis.role2 = '母亲') GROUP BY sis.indid2) tt WHERE si1.indid = tt.indid2); --Label79：以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'儿子'或'女儿'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label79为'子女生日临近'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label79 = (SELECT CASE WHEN DATEDIFF(sysdate(), si1.birthday)<=5 THEN '子女生日临近' END FROM ( SELECT sis.indid2 FROM shanglifeecif.Individual si2 LEFT JOIN shanglifeecif.IndRelationShip sis ON si2.indid = sis.indid1 WHERE (sis.role2 = '儿子' or sis.role2 = '女儿') GROUP BY sis.indid2) tt WHERE si1.indid = tt.indid2); --Label96：根据Individual.Birthday算出当前年龄，如果50<=年龄<60，则更新Individual.Label96为'大龄'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label96 = (SELECT CASE WHEN TRUNC(months_between(sysdate, si2.birthday)/12)>=50 AND TRUNC(months_between(sysdate, si2.birthday)/12)<60 THEN '大龄' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label97：根据Individual.Birthday算出当前年龄，如果60<=年龄，则更新Individual.Label97为'高龄'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label97 = (SELECT CASE WHEN TRUNC(months_between(sysdate, si2.birthday)/12)>=60 THEN '高龄' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label98：根据Individual.Occupation，若为记者、律师、公务员、媒体、金融行业，则更新Individual.Label98为'敏感职业'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label98 = (SELECT CASE WHEN si2.occupation = '记者' OR si2.occupation = '律师' OR si2.occupation = '公务员' OR si2.occupation = '媒体' OR si2.occupation = '金融行业' THEN '敏感职业' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label109：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='报案'且 InsuranceClaimThread.CNDate距当前日期超过7天，则更新Individual.Label109为'理赔报案追踪' UPDATE shanglifeecif.Individual si1 SET si1.Label109 = (SELECT CASE WHEN count(1)>0 THEN '理赔报案追踪' END FROM shanglifeecif.insuranceclaimthread sit WHERE si1.indid = sit.applicantid AND sit.claimstatus = '报案' AND DATEDIFF(sysdate(), sit.CNDate)>7); --Label110：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='受理' or '立案'，则更新Individual.Label110为'理赔处理中' UPDATE shanglifeecif.Individual si1 SET si1.Label110 = (SELECT CASE WHEN count(1)>0 THEN '理赔处理中' END FROM shanglifeecif.insuranceclaimthread sit WHERE si1.indid = sit.applicantid AND (sit.claimstatus = '受理' OR sit.claimstatus = '立案')); --Label112：先以个体ID(Individual.IndID)得到InsuranceArrangement.PolicyNo，再以此关联AUDIT_EDORLIST，若有记录且保全状态EDORSTATE<>'确认生效' and '复核终止' and '强制终止'，则更新Individual.Label111为'保全处理中' UPDATE shanglifeecif.Individual si1 SET si1.Label112 = (SELECT CASE WHEN nvl(count(sae.contno),0) > 0 THEN '保全处理中' END FROM (SELECT si1.indid indid,max(sia.policyno) policyno FROM shanglifeecif.Individual si1 LEFT JOIN shanglifeecif.InsuranceArrangement sia ON si1.indid = sia.applicantid GROUP BY si1.indid) tt LEFT JOIN shanghailifeecif.audit_edorlist sae ON sae.contno = tt.policyno AND sae.edorstate <> '确认生效' AND sae.edorstate <> '复核终止' AND sae.edorstate <> '强制终止' WHERE si1.indid = tt.indid); UPDATE shanglifeecif.Individual si1 SET si1.Label70 = (SELECT temp.scenario FROM ( SELECT qi.applicantid,qp.threadid,qp.scenario,row_number()OVER(PARTITION BY qp.threadid ORDER BY qp.created_time DESC) rd FROM qxp.partytimeline qp LEFT JOIN qxp.insurancearrangement qi ON qp.threadid = qi.policyno WHERE rd = 1 ) temp WHERE temp.applicantid = si1.indid) --注册官微 UPDATE shanglifeecif.Individual si1 SET si1.Label74 = (SELECT CASE WHEN COUNT(1)>0 THEN '注册官微' END FROM shanghailifeecif.wx_list wl WHERE wl.certificate_no = si1.idcard) --长通话逻辑contnos为客户的保单号select (select lo.call_length from ucc_rms_recorderlog lo where lo.call_id=m.call_id) as lenth from cc_record_main mwhere m.contnos='2019082000110188' UPDATE shanglifeecif.Individual si1 SET si1.label10 = (SELECT CASE WHEN tt.avgCallLenth>600 THEN '长通话' END FROM ( SELECT temp.contnos,(temp.lenth/temp.callNum) as avgCallLenth,qi.applicantid FROM( select (select lo.call_length from shanghailifeecif.ucc_rms_recorderlog lo where lo.call_id=m.call_id) as lenth,count(1) AS callNum,m.contnos from shanghailifeecif.cc_record_main m GROUP BY m.contnos ) temp LEFT JOIN qxp.insurancearrangement qi ON temp.contnos = qi.policyno) tt WHERE tt.applicantid = si1.indid) --要求较多，每次来电均下转办单 UPDATE shanglifeecif.Individual si1 SET si1.label103 = -- (SELECT '要求多' FROM shanghailifeecif.cc_swfflowmain swf -- LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno -- WHERE swf.flowid LIKE '%ZX%' AND si1.indid = qi.applicantid) ( SELECT CASE WHEN t.callNum==t.swf_num THEN '要求较多' END FROM ( SELECT count(*) AS callNum,srm.contnos,(SELECT count(*) FROM shanghailifeecif.cc_swfflowmain swf WHERE swf.flowid LIKE '%ZX%' AND srm.contnos = swf.policyno ) AS swf_num FROM shanghailifeecif.cc_record_main srm GROUP BY srm.contnos ) t LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno WHERE si1.indid = qi.applicantid ) UPDATE shanglifeecif.Individual si1 SET si1.label104 = --(SELECT '易投诉升级' FROM shanghailifeecif.cc_swfflowmain swf --LEFT JOIN shanghailifeecif.cc_swf_sort scc ON scc.complaintProject = swf.id --LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno -- WHERE swf.flowid LIKE '%ZX%' AND scc.idname='投诉' AND si1.indid = qi.applicantid) ( SELECT CASE WHEN t.callNum==t.swf_num THEN '易投诉升级' END FROM ( SELECT count(*) AS callNum,srm.contnos, (SELECT count(*) FROM shanghailifeecif.cc_swfflowmain swf LEFT JOIN shanghailifeecif.cc_swf_sort scs ON swf.complaintproject = scs.id WHERE swf.flowid LIKE '%ZX%' AND srm.contnos = swf.policyno ) AS swf_num FROM shanghailifeecif.cc_record_main srm GROUP BY srm.contnos ) t LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno WHERE si1.indid = qi.applicantid ) UPDATE shanglifeecif.Individual si1 SET si1.label105 = (SELECT '高频投诉' FROM ( select count(1) as comnum,c.productno,DATEDIFF(max(c.inserttime),min(c.inserttime)) dayNum from shanghailifeecif.cc_action_data_complaints c WHERE c.productno IS NOT NULL GROUP BY c.productno ) temp LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = temp.productno WHERE temp.comnum >= 2 AND temp.dayNum <180 AND si1.indid = qi.applicantid ) SELECT c.productno,c.inserttime FROM shanghailifeecif.cc_action_data_complaints c WHERE c.productno IS NOT NULL GROUP BY c.productno UPDATE shanglifeecif.Individual si1 SET si1.label106 = /*(SELECT CASE temp.complaintsSource when '914' then '重大投诉' when '919' then '重大投诉' when '915' then '重大投诉' END,qi.applicantid FROM ( select c.complaintsSource,cd.productno from shanghailifeecif.cc_action_complaints c,shanghailifeecif.cc_action_data_complaints cd where cd.complaintsid=c.complaintsid ) temp LEFT JOIN qxp.insurancearrangement qi ON temp.productno = qi.policyno WHERE qi.applicantid = si1.indid) */ (SELECT CASE WHEN tt.complaintsSource LIKE '%914%' THEN '重大投诉' WHEN tt.complaintsSource LIKE '%915%' THEN '重大投诉' WHEN tt.complaintsSource LIKE '%919%' THEN '重大投诉' END FROM ( select group_concat(c.complaintsSource,',') AS complaintsSource,qi.applicantid from shanghailifeecif.cc_action_complaints c,shanghailifeecif.cc_action_data_complaints cd,qxp.insurancearrangement qi where cd.complaintsid=c.complaintsid AND qi.policyno = cd.productno GROUP BY qi.applicantid ) tt WHERE tt.applicantid = si1.indid) --lable_27 志广说只用判断有极短意外险不用判断仅有 UPDATE shanglifeecif.Individual si1 SET si1.Label26 = ( SELECT CASE WHEN temp.cnt >0 THEN '有极短意外险' END FROM ( SELECT count(1) cnt, policy.customerno FROM shanghailifeecif.policy_information policy where policy.SECURITY in('7天','15天','30天','90天','1月','3月') and policy.riskperiod='短期险' and policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003') GROUP BY policy.customerno ) temp WHERE temp.customerno = si1.scustid ); --2233条记录 --lable_43 UPDATE shanglifeecif.Individual si1 SET si1.Label43 = ( SELECT CASE WHEN temp.cnt >0 THEN '有1年期短险保单' END FROM ( SELECT count(1) cnt, policy.customerno FROM shanghailifeecif.policy_information policy where policy.SECURITY in('1年','6月','180天') and policy.riskperiod='短期险' and policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003') GROUP BY policy.customerno ) temp WHERE temp.customerno = si1.scustid ); --529354条记录 --lable_67 司庆日 UPDATE shanglifeecif.Individual si1 SET si1.Label67 = '司庆日' where birthday like '%-02-05'; --3615 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.updateIndividualLable() IS DECLARE BEGIN --label1: 无来源。Individual.Education --label2: Individual.Occupation，逻辑见上 UPDATE shanglifeecif.Individual si1 SET si1.label2 = (SELECT si2.Occupation FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --标签3 根据Individual.Birthday年代，更新为70后，80后等 UPDATE shanglifeecif.Individual si1 SET si1.label3 = (SELECT substr(si2.birthday,3,1) || '0后' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label4：根据Individual.Birthday算出年龄，儿童:[0,13),少年:[13,19),青年:[19,41),中年:[41,66),老年:[66,) UPDATE shanglifeecif.Individual si1 SET si1.label4 = (SELECT CASE WHEN temp.age>=0 AND temp.age<13 THEN '儿童' WHEN temp.age>=13 AND temp.age <19 THEN '少年' WHEN temp.age >=19 AND temp.age <41 THEN '青年' WHEN temp.age >=41 AND temp.age <66 THEN '中年' ELSE '老年' END FROM (SELECT TRUNC(months_between(sysdate, si2.birthday)/12) age,si2.indid FROM shanglifeecif.Individual si2) temp WHERE temp.indid = si1.indid); --Label5: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录 --Label6: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录 --Label7：保留暂不实现 --Label8：保留暂不实现 --标签9 已婚，未婚 无来源。Individual.MaritalStat，查询个体关系表(IndRelationShip)，RSType = '配偶'，如果有记录则更新为已婚，否则更新为null UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT CASE WHEN temp.cnum >0 THEN '已婚' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '配偶' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1); --UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT CASE WHEN cnum >0 THEN '已婚' END FROM (SELECT nvl(count(*),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE si1.indid = sirs.indid2 AND sirs.rstype = '配偶' GROUP BY sirs.indid2) temp WHERE si1.indid = temp.indid2) --Label10：查询IndRelationShip，RSType='父母'，首先将返回记录数更新Individual.COChild，若记录数为0，则更新本字段为'无子女'，若记录数为1，则更新本字段为'1孩'，若记录数大于等于2，则更新本字段为'2孩及以上' --UPDATE shanglifeecif.Individual si1 SET si1.COChild = (SELECT CASE WHEN temp.cnum == 0 THEN '无子女' WHEN temp.cnum == 1 THEN '1孩' ELSE '2孩及以上' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '父母' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1); UPDATE shanglifeecif.Individual si1 SET si1.label10 = (SELECT CASE WHEN temp.cnum == 0 THEN '无子女' WHEN temp.cnum == 1 THEN '1孩' ELSE '2孩及以上' END FROM (SELECT nvl(count(1),0) cnum,sirs.indid1 FROM shanglifeecif.IndRelationShip sirs WHERE sirs.rstype = '父母' GROUP BY sirs.indid1) temp WHERE si1.indid = temp.indid1); --UPDATE shanglifeecif.Individual si1 SET si1.label9 = (SELECT '已婚' FROM shanglifeecif.IndRelationShip sirs WHERE si1.indid = sirs.indid2 AND sirs.rstype = '配偶') --Label11：todo:无来源，逻辑需要说明清楚 --Label12：todo:无来源，逻辑需要说明清楚。Individual.Drinking --UPDATE shanglifeecif.Individual si1 SET si1.label12 = (SELECT si2.Drinking FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label13：todo:无来源，逻辑需要说明清楚。Individual.Smoking --UPDATE shanglifeecif.Individual si1 SET si1.label13 = (SELECT si2.Smoking FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label14：todo:无来源，逻辑需要说明清楚。Individual.Cdiseases --Label15：todo:无来源，逻辑需要说明清楚。 --Label16：todo:无来源，逻辑需要说明清楚。Individual.FMHistory --Label17：todo:无来源，逻辑需要说明清楚。Individual.Pregnancy --Label18：todo:无来源，逻辑需要说明清楚。 --Label19：保留暂不实现 --Label20：todo:无来源，逻辑需要说明清楚。 --Label21：todo:无来源，Individual.PIncome:[0,120000],'低收入';(120000,1000000],'中产';(1000000,),'富人' --UPDATE shanglifeecif.Individual si1 SET si1.Label21 = (SELECT CASE WHEN si2.pincome >=0 AND si2.pincome<=120000 THEN '低收入' WHEN si2.pincome >120000 AND si2.pincome<=1000000 THEN '中产' WHEN si2.pincome > 1000000 THEN '富人' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label22：todo:无来源，Individual.COVehicle:0,'无车';1,'1辆车';(1,),'2辆车以上' --UPDATE shanglifeecif.Individual si1 SET si1.Label22 = (SELECT CASE WHEN si2.COVehicle ==0 THEN '无车' WHEN si2.COVehicle == 1 THEN '1辆车' WHEN si2.COVehicle > 1 THEN '2辆车以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label23：todo:无来源，Individual.CORP:0,'无房';1,'1套房';(1,),'2套房以上' --UPDATE shanglifeecif.Individual si1 SET si1.Label23 = (SELECT CASE WHEN si2.CORP ==0 THEN '无房' WHEN si2.CORP == 1 THEN '1套房' WHEN si2.CORP > 1 THEN '2套房以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label24：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG = '有效'，若无记录则更新Individual.NOVPolicy为0，否则更新NOVPolicy为记录数。若NOVPolicy>0，则更新Individual.Label24为'持有寿险有效保单'，否则更新为'未持有寿险有效保单' --第一步 更新Individual.NOVPolicy有效记录数 UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = (SELECT temp.cuscount FROM (SELECT nvl(count(spi.customerno),0) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno WHERE spi.appflag = '有效' GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = 0 WHERE si1.NOVPolicy IS null; UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = 0 WHERE si1.noivpolicy IS null; --第二步 更新 Individual.NOIVPolicy 无效记录数 UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = (SELECT temp.cuscount FROM (SELECT nvl(count(spi.customerno),0) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno WHERE spi.appflag <> '有效' GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --最后一步 更新标签 24 UPDATE shanglifeecif.Individual si1 SET si1.label24 = (SELECT CASE WHEN nvl(si2.novpolicy,0)>0 THEN '持有寿险有效保单' ELSE '未持有寿险有效保单' END FROM shanglifeecif.individual si2 WHERE si2.indid = si1.indid); --Label25：将Individual.NOVPolicy+Individual.NOIVPolicy的值更新ndividual.Label25 --UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (SELECT (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (SELECT CASE WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) == 0 THEN '无保单' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) == 1 THEN '1件' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) == 2 THEN '2件' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) >= 3 AND (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) <= 5 THEN '3-5件' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) >= 6 AND (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) <= 10 THEN '6-10件' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) >= 11 AND (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0))<= 20 THEN '11-20件' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) >= 21 AND (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) <= 50 THEN '21-50件' WHEN (nvl(si2.NOVPolicy,0) + nvl(si2.NOIVPolicy,0)) > 50 THEN '50件以上' END FROM shanglifeecif.individual si2 WHERE si1.indid = si2.indid); --Label26：InsuranceArrangement.PolicyNo = AUDIT_LN_LIST.CONTNO，以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney，以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，若无记录更新Individual.Label26为Null,若LoanMoney>0则更新Individual.Label26为'有贷款'，否则更新为'无贷款' --第一步 以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney UPDATE shanglifeecif.insurancearrangement sig SET sig.LoanMoney =(SELECT temp.lnmoney FROM (SELECT sall.contno,sum(sall.lnmoney) lnmoney FROM audit_ln_list sall GROUP BY sall.contno) temp WHERE temp.contno = sig.policyno); --第二步 以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID) --SELECT sig.applicantid,sum(sig.loanmoney) loanmoney FROM shanglifeecif.insurancearrangement sig GROUP BY sig.applicantid UPDATE shanglifeecif.Individual si1 SET si1.Label26 =(SELECT CASE WHEN temp.loanmoney > 0 THEN '有贷款' WHEN temp.loanmoney = 0 THEN '无贷款' ELSE NULL END FROM (SELECT sig.applicantid,sum(sig.loanmoney) loanmoney FROM shanglifeecif.insurancearrangement sig GROUP BY sig.applicantid) temp WHERE si1.indid = temp.applicantid); --Label27：todo:无来源，逻辑需要说明清楚。 --lable_27 志广说只用判断有极短意外险不用判断仅有 UPDATE shanglifeecif.Individual si1 SET si1.Label27 = ( SELECT CASE WHEN temp.cnt >0 THEN '有极短意外险' END FROM ( SELECT count(1) cnt, policy.customerno FROM policy_information policy where policy.SECURITY in('7天','15天','30天','90天','1月','3月') and policy.riskperiod='短期险' and policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003') GROUP BY policy.customerno ) temp WHERE temp.customerno = si1.scustid ); --2233条记录 --Label28：保留暂不实现 --Label29：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO），POLICY_INFORMATION.APPFLAG='有效' and POLICY_INFORMATION.PAYINTV='趸缴'，若有记录则更新Individual.NOSPInsurance和本标签为'有趸交保单' UPDATE shanglifeecif.Individual si1 SET si1.label29 = (SELECT CASE WHEN temp.cnum>0 THEN '有趸交保单' END FROM (SELECT count(1) cnum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.APPFLAG = '有效' AND spi.PAYINTV = '趸缴' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.NOSPInsurance = (SELECT CASE WHEN temp.cnum>0 THEN '有趸交保单' END FROM (SELECT count(1) cnum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.APPFLAG = '有效' AND spi.PAYINTV = '趸缴' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label30：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.ISelf为NULL，若有记录且RELATIONTOAPPNT仅为'本人'则若无记录则更新Individual.ISelf为'仅本人投保'，若有记录且RELATIONTOAPPNT不为'本人'则若无记录则更新Individual.ISelf为'本人未投保'，否则更新Individual.ISelf为'本人已投保' --UPDATE shanglifeecif.Individual si1 SET si1.iself = (SELECT CASE WHEN temp.snum>0 THEN '本人已投保' WHEN temp.snum=0 THEN '本人未投保' END FROM (SELECT nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '本人' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --UPDATE shanglifeecif.Individual si1 SET si1.iself = (SELECT CASE WHEN temp.snum=0 AND si1.iself ='本人已投保' THEN '仅本人投保' END FROM (SELECT nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '本人' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label30 = (SELECT CASE WHEN temp.snum>0 THEN '本人已投保' WHEN temp.snum=0 THEN '本人未投保' END FROM (SELECT nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '本人' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label30 = (SELECT CASE WHEN temp.snum=0 AND si1.label30 ='本人已投保' THEN '仅本人投保' END FROM (SELECT nvl(count(spi.customerno),null) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '本人' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --label31 以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Iparent为NULL，若有记录且RELATIONTOAPPNT仅为'父母'则若无记录则更新Individual.Iparent为'仅父母投保'，若有记录且RELATIONTOAPPNT不为'父母'则若无记录则更新Individual.Iparent为'父母未投保'，否则更新Individual.Iparent为'父母已投保' --UPDATE shanglifeecif.Individual si1 SET si1.Iparent = (SELECT CASE WHEN temp.snum > 0 THEN '父母已投保' WHEN temp.snum=0 THEN '父母未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN shanghailifeecif.policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '父母' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --UPDATE shanglifeecif.Individual si1 SET si1.Iparent = (SELECT CASE WHEN temp.snum=0 AND si1.Iparent = '父母已投保' THEN '仅父母投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '父母' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label31 = (SELECT CASE WHEN temp.snum > 0 THEN '父母已投保' WHEN temp.snum=0 THEN '父母未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '父母' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label31 = (SELECT CASE WHEN temp.snum=0 AND si1.label31 = '父母已投保' THEN '仅父母投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '父母' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label32：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.IChildren为NULL，若有记录且RELATIONTOAPPNT仅为'子女'则若无记录则更新Individual.IChildren为'仅子女投保'，若有记录且RELATIONTOAPPNT不为'子女'则若无记录则更新Individual.IChildren为'子女未投保'，否则更新Individual.IChildren为'子女已投保' --UPDATE shanglifeecif.Individual si1 SET si1.IChildren = (SELECT CASE WHEN temp.snum>0 THEN '子女已投保' WHEN temp.snum=0 THEN '子女未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --UPDATE shanglifeecif.Individual si1 SET si1.IChildren = (SELECT CASE WHEN temp.snum=0 AND si1.IChildren = '子女已投保' THEN '仅子女投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label32 = (SELECT CASE WHEN temp.snum>0 THEN '子女已投保' WHEN temp.snum=0 THEN '子女未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label32 = (SELECT CASE WHEN temp.snum=0 AND si1.label32 = '子女已投保' THEN '仅子女投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label33：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Imate为NULL，若有记录且RELATIONTOAPPNT仅为'配偶'则若无记录则更新Individual.Imate为'仅配偶投保'，若有记录且RELATIONTOAPPNT不为'配偶'则若无记录则更新Individual.Imate为'配偶未投保'，否则更新Individual.Imate为'配偶已投保' --UPDATE shanglifeecif.Individual si1 SET si1.Imate = (SELECT CASE WHEN temp.snum>0 THEN '配偶已投保' WHEN temp.snum=0 THEN '配偶未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --UPDATE shanglifeecif.Individual si1 SET si1.Imate = (SELECT CASE WHEN temp.snum=0 AND si1.Imate = '配偶已投保' THEN '仅配偶投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label33 = (SELECT CASE WHEN temp.snum>0 THEN '配偶已投保' WHEN temp.snum=0 THEN '配偶未投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt = '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); UPDATE shanglifeecif.Individual si1 SET si1.label33 = (SELECT CASE WHEN temp.snum=0 AND si1.label33 = '配偶已投保' THEN '仅配偶投保' END FROM (SELECT count(spi.customerno) snum,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno AND spi.relationtoappnt <> '子女' GROUP BY spi.customerno) temp WHERE temp.customerno =si1.scustid); --Label34：保留暂不实现 --Label35：保留暂不实现 --Label36：保留暂不实现 --Label37：保留暂不实现 --Label38：保留暂不实现 --Label39：保留暂不实现 --Label40：保留暂不实现 --Label41：保留暂不实现 --Label42：保留暂不实现 --Label43：todo:逻辑需要说明清楚（到上游源表和字段）。 UPDATE shanglifeecif.Individual si1 SET si1.Label43 = ( SELECT CASE WHEN temp.cnt >0 THEN '有1年期短险保单' END FROM ( SELECT count(1) cnt, policy.customerno FROM policy_information policy where policy.SECURITY in('1年','6月','180天') and policy.riskperiod='短期险' and policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003') GROUP BY policy.customerno ) temp WHERE temp.customerno = si1.scustid ); --Label44：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG<>'有效'，若无记录则更新Individual.NOIVPolicy为0，否则更新NOIVPolicy为记录数。若NOIVPolicy>0，则更新Individual.Label44为'有失效保单'，否则更新为'无失效保单' UPDATE shanglifeecif.Individual si1 SET si1.label44 = (SELECT CASE WHEN si2.noivpolicy>0 THEN '有失效保单' END FROM shanglifeecif.individual si2 WHERE si2.indid = si1.indid); --LABEL45:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且riskperiod='长期险'，若无记录则更新Individual.Label45为NULL，若有记录且paycount= payendyear则更新Individual.Label45为'有缴费期满长险保单'，否则更新Individual.Label45为'无缴费期满长险保单' UPDATE shanglifeecif.Individual si1 SET si1.label45 = (SELECT CASE WHEN temp.cuscount IS NULL THEN NULL WHEN temp.cuscount>0 THEN '有缴费期满长险保单' ELSE '无缴费期满长险保单' END FROM (SELECT count(spi.customerno) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno WHERE spi.riskperiod='长期险' AND spi.paycount = spi.payendyear GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --Label46：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label46为NULL，若有记录且paycount= payendyear则更新Individual.Label46为'有续期缴费的保单'，否则更新Individual.Label46为'无有续期缴费的保单' UPDATE shanglifeecif.Individual si1 SET si1.label46 = (SELECT CASE WHEN temp.cuscount IS NULL THEN NULL WHEN temp.cuscount>0 THEN '有续期缴费的保单' ELSE '无有续期缴费的保单' END FROM (SELECT count(spi.customerno) cuscount,spi.customerno FROM shanglifeecif.Individual si1 LEFT JOIN policy_information spi ON si1.scustid = spi.customerno WHERE spi.paycount = spi.payendyear GROUP BY spi.customerno) temp WHERE temp.customerno = si1.scustid); --Label47：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label48：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最早日期(PADate)更新Individual.FADate -- UPDATE shanglifeecif.Individual si1 SET si1.FADate = (SELECT '首次投保日期' || temp.padate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid); UPDATE shanglifeecif.Individual si1 SET si1.label48 = (SELECT '首次投保日期' || temp.padate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid); --Label49：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最近日期(PADate)更新Individual.LAPPDate，最后用当前日期减去Individual.LAPPDate更新Label49 UPDATE shanglifeecif.Individual si1 SET si1.LAPPDate = (SELECT temp.PADate FROM (SELECT min(si.PADate) padate,si.applicantid FROM shanglifeecif.insurancearrangement si GROUP BY si.applicantid) temp WHERE temp.applicantid = si1.indid); UPDATE shanglifeecif.Individual si1 SET si1.Label49 = (SELECT '最后一次投保距今' || TRUNC(months_between(sysdate(), si2.LAPPDate)/12) || '天' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel --Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel，并更新此标签为：'最近保单'+Individual.LPChannel UPDATE shanglifeecif.Individual si1 SET si1.LPChannel = (SELECT temp.agentchannel FROM (SELECT si.agentchannel,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate DESC) rd FROM shanglifeecif.InsuranceArrangement si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label50 = (SELECT '最近保单' || si1.LPChannel FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label51：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentOrg更新Individual.LPOrg，并更新此标签为：'最近'+Individual.LPOrg+'机构' UPDATE shanglifeecif.Individual si1 SET si1.LPOrg = (SELECT temp.AgentOrg FROM (SELECT si.AgentOrg,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate DESC) rd FROM shanglifeecif.InsuranceArrangement si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label51 = (SELECT '最近' || si2.LPOrg || '机构' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid) ; --Label52：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentChannel更新Individual.FAChannel UPDATE shanglifeecif.Individual si1 SET si1.FAChannel = (SELECT tt.agentchannel FROM (SELECT si.agentchannel,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si ) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label52 = (SELECT '首单' || si2.FAChannel FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label53：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentOrg更新Individual.FAOrg UPDATE shanglifeecif.Individual si1 SET si1.FAOrg = (SELECT tt.AgentOrg FROM (SELECT si.AgentOrg,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1); UPDATE shanglifeecif.Individual si1 SET si1.Label53 = (SELECT '首单' || si2.FAOrg || '机构' FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label54：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的PolicyState更新Individual.LPState UPDATE shanglifeecif.Individual si1 SET si1.LPState = (SELECT tt.PolicyState FROM (SELECT si.PolicyState,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.PADate ASC) rd FROM shanglifeecif.insurancearrangement si) tt WHERE tt.applicantid = si1.indid AND tt.rd = 1); --Label55 :Label55：由于上游保全类型信息分散在AUDIT_EDORLIST.EDORNAME和edorinfo.edortype 中，同时edorinfo.edortype为code需要转为string（具体映射见下）。以上游客户号(Individual.SCustID)分别查询AUDIT_EDORLIST表和edorinfo表，取保全生效日期最近的记录的更新的保全类型更新Individual.LPOSType。todo:edorinfo.edortype为code，需要转为string，edorinfo中需要增加上游客户号，否则需要多一次关联，建议大数据平台处理完成。 --AUDIT_EDORLIST表里没有上游客户号，UPDATE shanglifeecif.Individual si1 SET si1.LPOSType =(SELECT ae.edorname FROM shanghailifeecif.audit_edorlist ae ) UPDATE shanglifeecif.Individual si1 SET si1.lpostype = (SELECT tt.edortype FROM ( SELECT temp.scustid,temp.edortype,temp.edorvalidate,row_number()OVER(PARTITION BY temp.scustid ORDER BY temp.edorvalidate DESC) rd FROM ((SELECT si2.scustid,sae.edorname edortype,sae.edorvalidate FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.audit_edorlist sae ON sae.contno = spi.contno) UNION ( SELECT si2.scustid,se.edortype,se.edorappdate edorvalidate FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.edorinfo se ON se.contno = spi.contno)) temp) tt WHERE tt.scustid = si1.scustid AND tt.rd = 1); --Label56：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询INSURANCEINFO表 and INSURANCEINFO.APPFLAG = '已退保',若有记录则更新此标签为'有退保保单' UPDATE shanglifeecif.Individual si1 SET si1.Label56 = (SELECT CASE WHEN temp.cunm > 0 THEN '有退保保单' END FROM ( SELECT count(1) cunm,si2.scustid FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.INSURANCEINFO sio ON spi.CONTNO = sio.CONTNO WHERE sio.APPFLAG = '已退保' GROUP BY si2.scustid ) temp WHERE temp.scustid = si1.scustid); --Label57：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询AUDIT_CLAIM_INSURANCE表，取AUDIT_CLAIM_INSURANCE.RGTDATE最近对应的AUDIT_CLAIM_INSURANCE.RISKTYPE更新此标签。todo:实际数据仅有寿险、意外险、健康险，取值是否正确？ UPDATE shanglifeecif.Individual si1 SET si1.Label57 = (SELECT temp.RISKTYPE FROM ( SELECT si2.scustid,saci.RISKTYPE,row_number()OVER(PARTITION BY si2.scustid ORDER BY saci.RGTDATE DESC) rd FROM shanglifeecif.Individual si2 LEFT JOIN shanghailifeecif.policy_information spi ON si2.scustid = spi.customerno LEFT JOIN shanghailifeecif.audit_claim_insurance saci ON saci.CONTNO = spi.CONTNO ) temp WHERE si1.scustid = temp.scustid AND temp.rd = 1); --Label59 :以个体ID(Individual.IndID)查询InsuranceClaimThread表(Individual.IndID=InsuranceClaimThread.ApplicantID)， 以最近日期(CNDate)的ClaimCaseStatus更新Individual.CNStatus UPDATE shanglifeecif.Individual si1 SET si1.CNStatus =(SELECT temp.ClaimCaseStatus FROM (SELECT si.ClaimCaseStatus,si.applicantid,row_number()OVER(PARTITION BY si.applicantid ORDER BY si.CNDate DESC) rd FROM shanglifeecif.insuranceclaimthread si) temp WHERE temp.applicantid = si1.indid AND temp.rd = 1); --Label60：保留暂不实现 --Label61：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label62：保留暂不实现 --Label63：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label64：todo:逻辑需要说明清楚（到上游源表和字段）。 --Label65：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label65为NULL，若仅一条记录则更新Individual.Label65为'一单寿险客户' UPDATE shanglifeecif.Individual si1 SET si1.Label65 = (SELECT CASE WHEN tt.cnum = 1 THEN '一单寿险客户' END FROM (SELECT nvl(count(spi.customerno),0) cnum,max(si2.indid) indid FROM shanghailifeecif.policy_information spi LEFT JOIN shanglifeecif.Individual si2 ON si2.scustid = spi.customerno GROUP BY spi.customerno) tt WHERE si1.indid = tt.indid); --Label66：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label66为NULL，若 paycount= payendyear更新Individual.Label66为'缴费期满客户'，否则更新为'非缴费期满客户 UPDATE shanglifeecif.Individual si1 SET si1.Label66 = (SELECT CASE WHEN tt.cnum >0 THEN '缴费期满客户' END FROM (SELECT nvl(count(spi.customerno),0) cnum,max(si2.indid) indid FROM shanghailifeecif.policy_information spi LEFT JOIN shanglifeecif.Individual si2 ON si2.scustid = spi.customerno and spi.paycount= spi.payendyear GROUP BY spi.customerno) tt WHERE si1.indid = tt.indid); --Label67：若Individual.Birthday的月日与上海人寿司庆日月日相同，则设置此标签为：'生日与司庆日同天'。todo:需要确定司庆日是几月几号 UPDATE shanglifeecif.Individual si1 SET si1.Label67 = (SELECT CASE WHEN substr(si2.birthday,6,10) = '02-15' THEN '生日与司庆日同天' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label68:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表, and POLICY_INFORMATION.APPFLAG='有效'，若有记录则设置此标签为'持有有效保单'，否则则设置为'未持有有效保单' UPDATE shanglifeecif.Individual si1 SET si1.Label68 = (SELECT CASE WHEN si2.NOVPolicy>0 THEN '持有有效保单' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label69：Individual.CustClass，逻辑见上(一.7) UPDATE shanglifeecif.Individual si1 SET si1.Label69 = (SELECT si2.CustClass FROM shanglifeecif.Individual si2 WHERE si1.IndID = si2.IndID); --Label70:Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Scenario更新此标签 UPDATE shanglifeecif.Individual si1 SET si1.Label70 = (SELECT temp.Scenario FROM (SELECT sp.Scenario,sp.PartyID,row_number()OVER(PARTITION BY sp.PartyID ORDER BY sp.SDate DESC) rd FROM shanglifeecif.PartyTimeLine sp) temp WHERE si1.indid = temp.PartyID AND temp.rd = 1); --Label71：Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Channel更新此标签 UPDATE shanglifeecif.Individual si1 SET si1.Label71 = (SELECT temp.Channel FROM (SELECT sp.Channel,sp.PartyID,row_number()OVER(PARTITION BY sp.PartyID ORDER BY sp.SDate DESC) rd FROM shanglifeecif.PartyTimeLine sp) temp WHERE si1.indid = temp.PartyID AND temp.rd = 1); --Label72：保留暂不实现 --Label73 --Label74 --Label75 --Label76 --Label77：Individual.Birthday 距离当前日期5天内，更新Individual.Label77为'本人生日临近'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label77 = (SELECT CASE WHEN DATEDIFF(sysdate(), si2.birthday)<=5 THEN '本人生日临近' END FROM shanglifeecif.Individual si2 WHERE si1.IndID = si2.IndID); --Label78:以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'父亲'或'母亲'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label78为'父母生日临近'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label78 = (SELECT CASE WHEN DATEDIFF(sysdate(), si1.birthday)<=5 THEN '父母生日临近' END FROM ( SELECT sis.indid2 FROM shanglifeecif.Individual si2 LEFT JOIN shanglifeecif.IndRelationShip sis ON si2.indid = sis.indid1 WHERE (sis.role2 = '父亲' or sis.role2 = '母亲') GROUP BY sis.indid2) tt WHERE si1.indid = tt.indid2); --Label79：以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'儿子'或'女儿'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label79为'子女生日临近'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label79 = (SELECT CASE WHEN DATEDIFF(sysdate(), si1.birthday)<=5 THEN '子女生日临近' END FROM ( SELECT sis.indid2 FROM shanglifeecif.Individual si2 LEFT JOIN shanglifeecif.IndRelationShip sis ON si2.indid = sis.indid1 WHERE (sis.role2 = '儿子' or sis.role2 = '女儿') GROUP BY sis.indid2) tt WHERE si1.indid = tt.indid2); --Label96：根据Individual.Birthday算出当前年龄，如果50<=年龄<60，则更新Individual.Label96为'大龄'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label96 = (SELECT CASE WHEN TRUNC(months_between(sysdate, si2.birthday)/12)>=50 AND TRUNC(months_between(sysdate, si2.birthday)/12)<60 THEN '大龄' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label97：根据Individual.Birthday算出当前年龄，如果60<=年龄，则更新Individual.Label97为'高龄'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label97 = (SELECT CASE WHEN TRUNC(months_between(sysdate, si2.birthday)/12)>=60 THEN '高龄' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label98：根据Individual.Occupation，若为记者、律师、公务员、媒体、金融行业，则更新Individual.Label98为'敏感职业'，否则为NULL UPDATE shanglifeecif.Individual si1 SET si1.Label98 = (SELECT CASE WHEN si2.occupation = '记者' OR si2.occupation = '律师' OR si2.occupation = '公务员' OR si2.occupation = '媒体' OR si2.occupation = '金融行业' THEN '敏感职业' END FROM shanglifeecif.Individual si2 WHERE si1.indid = si2.indid); --Label109：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='报案'且 InsuranceClaimThread.CNDate距当前日期超过7天，则更新Individual.Label109为'理赔报案追踪' UPDATE shanglifeecif.Individual si1 SET si1.Label109 = (SELECT CASE WHEN count(1)>0 THEN '理赔报案追踪' END FROM shanglifeecif.insuranceclaimthread sit WHERE si1.indid = sit.applicantid AND sit.claimstatus = '报案' AND DATEDIFF(sysdate(), sit.CNDate)>7); --Label110：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='受理' or '立案'，则更新Individual.Label110为'理赔处理中' UPDATE shanglifeecif.Individual si1 SET si1.Label110 = (SELECT CASE WHEN count(1)>0 THEN '理赔处理中' END FROM shanglifeecif.insuranceclaimthread sit WHERE si1.indid = sit.applicantid AND (sit.claimstatus = '受理' OR sit.claimstatus = '立案')); --Label112：先以个体ID(Individual.IndID)得到InsuranceArrangement.PolicyNo，再以此关联AUDIT_EDORLIST，若有记录且保全状态EDORSTATE<>'确认生效' and '复核终止' and '强制终止'，则更新Individual.Label111为'保全处理中' UPDATE shanglifeecif.Individual si1 SET si1.Label112 = (SELECT CASE WHEN nvl(count(sae.contno),0) > 0 THEN '保全处理中' END FROM (SELECT si1.indid indid,max(sia.policyno) policyno FROM shanglifeecif.Individual si1 LEFT JOIN shanglifeecif.InsuranceArrangement sia ON si1.indid = sia.applicantid GROUP BY si1.indid) tt LEFT JOIN shanghailifeecif.audit_edorlist sae ON sae.contno = tt.policyno AND sae.edorstate <> '确认生效' AND sae.edorstate <> '复核终止' AND sae.edorstate <> '强制终止' WHERE si1.indid = tt.indid); UPDATE shanglifeecif.Individual si1 SET si1.Label70 = (SELECT temp.scenario FROM ( SELECT qi.applicantid,qp.threadid,qp.scenario,row_number()OVER(PARTITION BY qp.threadid ORDER BY qp.created_time DESC) rd FROM qxp.partytimeline qp LEFT JOIN qxp.insurancearrangement qi ON qp.threadid = qi.policyno WHERE rd = 1 ) temp WHERE temp.applicantid = si1.indid) --注册官微 UPDATE shanglifeecif.Individual si1 SET si1.Label74 = (SELECT CASE WHEN COUNT(1)>0 THEN '注册官微' END FROM shanghailifeecif.wx_list wl WHERE wl.certificate_no = si1.idcard) --长通话逻辑contnos为客户的保单号select (select lo.call_length from ucc_rms_recorderlog lo where lo.call_id=m.call_id) as lenth from cc_record_main mwhere m.contnos='2019082000110188' UPDATE shanglifeecif.Individual si1 SET si1.label10 = (SELECT CASE WHEN tt.avgCallLenth>600 THEN '长通话' END FROM ( SELECT temp.contnos,(temp.lenth/temp.callNum) as avgCallLenth,qi.applicantid FROM( select (select lo.call_length from shanghailifeecif.ucc_rms_recorderlog lo where lo.call_id=m.call_id) as lenth,count(1) AS callNum,m.contnos from shanghailifeecif.cc_record_main m GROUP BY m.contnos ) temp LEFT JOIN qxp.insurancearrangement qi ON temp.contnos = qi.policyno) tt WHERE tt.applicantid = si1.indid) --要求较多，每次来电均下转办单 UPDATE shanglifeecif.Individual si1 SET si1.label103 = -- (SELECT '要求多' FROM shanghailifeecif.cc_swfflowmain swf -- LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno -- WHERE swf.flowid LIKE '%ZX%' AND si1.indid = qi.applicantid) ( SELECT CASE WHEN t.callNum==t.swf_num THEN '要求较多' END FROM ( SELECT count(*) AS callNum,srm.contnos,(SELECT count(*) FROM shanghailifeecif.cc_swfflowmain swf WHERE swf.flowid LIKE '%ZX%' AND srm.contnos = swf.policyno ) AS swf_num FROM shanghailifeecif.cc_record_main srm GROUP BY srm.contnos ) t LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno WHERE si1.indid = qi.applicantid ) UPDATE shanglifeecif.Individual si1 SET si1.label104 = --(SELECT '易投诉升级' FROM shanghailifeecif.cc_swfflowmain swf --LEFT JOIN shanghailifeecif.cc_swf_sort scc ON scc.complaintProject = swf.id --LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno -- WHERE swf.flowid LIKE '%ZX%' AND scc.idname='投诉' AND si1.indid = qi.applicantid) ( SELECT CASE WHEN t.callNum==t.swf_num THEN '易投诉升级' END FROM ( SELECT count(*) AS callNum,srm.contnos, (SELECT count(*) FROM shanghailifeecif.cc_swfflowmain swf LEFT JOIN shanghailifeecif.cc_swf_sort scs ON swf.complaintproject = scs.id WHERE swf.flowid LIKE '%ZX%' AND srm.contnos = swf.policyno ) AS swf_num FROM shanghailifeecif.cc_record_main srm GROUP BY srm.contnos ) t LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = swf.policyno WHERE si1.indid = qi.applicantid ) UPDATE shanglifeecif.Individual si1 SET si1.label105 = (SELECT '高频投诉' FROM ( select count(1) as comnum,c.productno,DATEDIFF(max(c.inserttime),min(c.inserttime)) dayNum from shanghailifeecif.cc_action_data_complaints c WHERE c.productno IS NOT NULL GROUP BY c.productno ) temp LEFT JOIN qxp.insurancearrangement qi ON qi.policyno = temp.productno WHERE temp.comnum >= 2 AND temp.dayNum <180 AND si1.indid = qi.applicantid ) SELECT c.productno,c.inserttime FROM shanghailifeecif.cc_action_data_complaints c WHERE c.productno IS NOT NULL GROUP BY c.productno UPDATE shanglifeecif.Individual si1 SET si1.label106 = /*(SELECT CASE temp.complaintsSource when '914' then '重大投诉' when '919' then '重大投诉' when '915' then '重大投诉' END,qi.applicantid FROM ( select c.complaintsSource,cd.productno from shanghailifeecif.cc_action_complaints c,shanghailifeecif.cc_action_data_complaints cd where cd.complaintsid=c.complaintsid ) temp LEFT JOIN qxp.insurancearrangement qi ON temp.productno = qi.policyno WHERE qi.applicantid = si1.indid) */ (SELECT CASE WHEN tt.complaintsSource LIKE '%914%' THEN '重大投诉' WHEN tt.complaintsSource LIKE '%915%' THEN '重大投诉' WHEN tt.complaintsSource LIKE '%919%' THEN '重大投诉' END FROM ( select group_concat(c.complaintsSource,',') AS complaintsSource,qi.applicantid from shanghailifeecif.cc_action_complaints c,shanghailifeecif.cc_action_data_complaints cd,qxp.insurancearrangement qi where cd.complaintsid=c.complaintsid AND qi.policyno = cd.productno GROUP BY qi.applicantid ) tt WHERE tt.applicantid = si1.indid) --lable_67 司庆日 UPDATE shanglifeecif.Individual si1 SET si1.Label67 = '司庆日' where birthday like '%-02-05'; END ;