--更新客户标签信息 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.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); --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 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); UPDATE shanglifeecif.Individual si1 SET si1.Label69 = ( SELECT CASE si2.CustClass WHEN 0 THEN '无' WHEN 1 THEN '钻石' WHEN 2 THEN '白金' WHEN 3 THEN '黄金' WHEN 4 THEN '普通' END 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); UPDATE shanglifeecif.Individual si1 SET si1.Label98 = (SELECT CASE WHEN si2.occupation like '%记者%' OR si2.occupation like '%律师%' OR si2.occupation like '%公务员%' OR si2.occupation like '%媒体%' OR si2.occupation like '%金融行业%' 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.Label101 = ( SELECT '长通话' FROM ( SELECT t.contnos, row_number()OVER(PARTITION BY p.customerno) rn, p.customerno FROM ( SELECT (sum(lo.call_length)/count(*)) AS avgcalltime,m.contnos from cc_record_main m LEFT JOIN ucc_rms_recorderlog lo ON m.call_id = lo.call_id WHERE m.contnos IS NOT NULL AND lo.call_length IS NOT NULL GROUP BY m.contnos ) t LEFT JOIN policy_information p ON t.contnos = p.contno WHERE t.avgcalltime>600 ) temp WHERE temp.customerno IS NOT NULL AND temp.rn = 1 AND si1.scustid = temp.customerno ); --要求较多，每次来电均下转办单 UPDATE shanglifeecif.Individual si1 SET si1.Label103 = (SELECT "要求较多" FROM ( SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno) rn FROM ( SELECT max(t.mnum) AS mnum,f.policyno,count(*) AS fnum FROM ( SELECT count(*) AS mnum,m.contnos FROM cc_record_main m WHERE m.contnos IS NOT null GROUP BY m.contnos ) t LEFT JOIN cc_swfflowmain f ON t.contnos = f.policyno WHERE f.flowid LIKE 'ZX%' AND f.policyno IS NOT NULL GROUP BY f.policyno ) temp LEFT JOIN policy_information p ON temp.policyno = p.contno WHERE temp.fnum = temp.mnum ) tt WHERE tt.rn = 1 AND tt.customerno IS NOT NULL AND si1.scustid = tt.customerno) --易投诉升级 UPDATE shanglifeecif.Individual si1 SET si1.Label104 = (SELECT "易投诉升级" FROM ( SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno) rn FROM ( SELECT max(t.mnum) AS mnum,f.policyno,count(*) AS fnum FROM ( SELECT count(*) AS mnum,m.contnos FROM cc_record_main m WHERE m.contnos IS NOT null GROUP BY m.contnos ) t LEFT JOIN cc_swfflowmain f ON t.contnos = f.policyno LEFT JOIN cc_swf_sort r ON r.id=f.complaintProject WHERE f.flowid LIKE 'ZX%' AND r.idname='投诉' and f.policyno IS NOT NULL GROUP BY f.policyno ) temp LEFT JOIN policy_information p ON temp.policyno = p.contno WHERE temp.fnum = temp.mnum ) tt WHERE tt.rn = 1 AND tt.customerno IS NOT NULL AND si1.scustid = tt.customerno) --高频投诉 UPDATE shanglifeecif.Individual si1 SET si1.Label105 = '高频投诉' WHERE si1.scustid in (SELECT p.customerno FROM policy_information p WHERE p.contno IN (SELECT temp.productno FROM ( SELECT t.productno,t.sinserttime, (SELECT tt.inserttime FROM (SELECT cad.inserttime,row_number()OVER(PARTITION BY cad.productno ORDER BY cad.inserttime) rownum,cad.productno FROM cc_action_complaints ca,cc_action_data_complaints cad WHERE cad.complaintsid=ca.complaintsid AND cad.productno IS NOT NULL) tt WHERE tt.productno = t.productno AND tt.rownum = t.rn+1) AS esinserttime FROM ( select cd.productno,row_number()OVER(PARTITION BY cd.productno ORDER BY cd.inserttime) rn,cd.inserttime AS sinserttime from cc_action_complaints c,cc_action_data_complaints cd WHERE cd.complaintsid=c.complaintsid AND cd.productno IS NOT NULL ) t ) temp WHERE temp.esinserttime IS NOT NULL AND DATEDIFF(temp.esinserttime,temp.sinserttime) < 180)) 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 WHEN tt.complaintsSource LIKE '%保监会%' THEN '重大投诉' WHEN tt.complaintsSource LIKE '%保监局%' THEN '重大投诉' WHEN tt.complaintsSource LIKE '%媒体转办%' THEN '重大投诉' END FROM ( SELECT pi.customerno,max(complaintsSource) AS complaintsSource FROM ( select group_concat((case c.complaintsSource when '911' then '来电' when '912' then '来访' when '913' then '来函' when '914' then '保监会转办' when '919' then '保监局转办' when '915' then '媒体转办' when '916' then '同业公会' when '917' then '其他转办' when '918' then '呼出' else c.complaintsSource END),',') as complaintsSource,p.contno from cc_action_complaints c,cc_action_data_complaints cd,policy_information p where cd.complaintsid=c.complaintsid AND p.contno = cd.productno GROUP BY p.contno ) t LEFT JOIN policy_information pi ON t.contno = pi.contno GROUP BY pi.customerno ) tt WHERE tt.customerno = si1.scustid ); --高保额 UPDATE shanglifeecif.Individual si1 SET si1.Label107 = '高保额' WHERE si1.scustid IN (SELECT p.customerno FROM policy_information p WHERE (p.salecom = '03' AND p.prem>30000 AND p.appflag = '有效') OR (p.salecom <> '03' AND p.prem>60000 AND p.appflag = '有效')) --理赔报案追踪 UPDATE shanglifeecif.Individual si1 SET si1.Label109 = '理赔报案追踪' WHERE si1.scustid IN (SELECT p.customerno FROM policy_information p WHERE p.contno IN (SELECT c.CONTNO FROM insurance_claim c WHERE c.LLCLAIMSTATE = '报案' AND DATEDIFF(sysdate(), c.RPTDATE) <30) ) --理赔处理中 UPDATE shanglifeecif.Individual si1 SET si1.Label110 = '理赔处理中' WHERE si1.scustid IN (SELECT p.customerno FROM policy_information p WHERE p.contno IN (SELECT c.CONTNO FROM insurance_claim c WHERE c.LLCLAIMSTATE = '受理' or c.LLCLAIMSTATE = '立案') ) --重点银行 UPDATE shanglifeecif.Individual si1 SET si1.Label120 = '重点银行' WHERE si1.scustid IN (SELECT p.customerno FROM policy_information p WHERE p.salechnl LIKE '%招商%') --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 ;