信贷风控策略/模型监控_基于sql优化

原创

已于 2024-04-07 11:39:10 修改 · 941 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据分析

于 2022-08-25 21:02:01 首次发布

1 sql编写规范性

编写原则：有序不杂乱、直观可读性强、不降低执行效率。

换行和缩进；（union、case when、占比等串行语句；整体三段式：查询段-表关联段-条件判断段）
排列有序可循；（关联表：数据来源/业务流程分块；字段排序同理）
勤写注释；（表名comment；字段码值；注意事项说明；判断逻辑–复杂判断逻辑注释，便于理解，尤其特殊非通用性条件，如样本选择和时间段注释，方便溯源）
表命名避免使用a b c或 t1 t2 t3之类，避免混淆；
字段带表名，便于核对数据源；
逗号写在前，增加可读性，便于查错；
尽量避免使用in，尤其客户id。

【例1】信贷业务多表关联编写举例：

create table detail as 
select 
DATE_FORMAT(sqzhb.APPL_TIME,'%Y-%m')  as 申请月份
,dkjg.PRODUCT_CODE as 产品小类
,case when  pjmx.SCORE is null then '未评分'  
 when pjmx.SCORE<=20 then '  (0,20]'  when pjmx.SCORE<=40 then ' (20,40]'  when pjmx.SCORE<=60 then ' (40,60]' 
 when pjmx.SCORE<=80 then ' (60,80]'  when pjmx.SCORE<=90 then ' (80,90]' else '(90,100]' end as 评分区间 

-- 1、申请审批情况
from ( select * from (select a.* ,@num2 := IF(@str2 =  APPL_CERTIFICATE_NUMBER ,@num2 + 1, 1) as rn
 from LM_LOAN_APPLICATION_INFO a ,(SELECT @str2 := '', @num2 := 0) b
 order by APPL_CERTIFICATE_NUMBER ,APPL_TIME desc )t where rn=1
) as  sqzhb -- 【1-申请主表：LM_LOAN_APPLICATION_INFO】申请客户去重，ROW_NUMBER() OVER()在此mysql版本不支持
left join LM_LOAN_APPLICATION as sqzb on sqzhb.req_id = sqzb.original_appl_id -- 【2-申请子表：LM_LOAN_APPLICATION】
left join lm_application_result as dkjg on sqzb.req_id = dkjg.req_id -- 【3-贷款结果表：lm_application_result】
left join lm_application_audit_result as  dkjjsp on sqzb.business_id = dkjjsp.business_id and sqzb.PRODUCT_CODE=dkjjsp.PRODUCT_CODE -- 【4-最终进件表：lm_application_audit_result】

-- 2、签约情况
left join lm_loan_apply_report_list as cont on dkjg.business_id = cont.business_id and dkjg.PRODUCT_CODE = cont.PRODUCT_CODE -- 【5-合同信息表：lm_loan_apply_report_list】

-- 3、支用申请情况
left join (select  zyjg.business_id, zysq.APPROVAL_BUSINESS_ID ,dkjjsp.PRODUCT_CODE ,sum(zysq.DISBURSE_AMOUNT) as  DISBURSE_AMOUNT
  from (select * from  lm_disburse_result where DISBURSE_RESULT='Y') zyjg -- 【6-支用结果表：lm_disburse_result】
  left join (select * from  lm_disburse_application where STATUS='2') as zysq  on zysq.DISBURSE_ID = zyjg.DISBURSE_ID --【7-支用申请表：lm_disburse_application 】
  left join lm_application_audit_result as  dkjjsp on zysq.business_id = dkjjsp.business_id -- 最终进件表
  group by zyjg.business_id, zysq.APPROVAL_BUSINESS_ID, dkjjsp.PRODUCT_CODE
) as zyxx on dkjg.business_id = zyxx.business_id  and dkjg.PRODUCT_CODE = zyxx.PRODUCT_CODE  

-- 4、放款情况
left join( 
select
loan.cont_no	-- as 合同编号
,dkjjsp.business_id 
,dkjjsp.PRODUCT_CODE
,max(loan.ovdue_days) as 最长逾期天数
,sum(repayplan.should_pay_amt) as 应还金额
from  bdp_cm_inte_co_lon_info as loan -- 【8-借据信息表：bdp_cm_inte_co_lon_info】
left join （ select dubil_no ,sum(repay_amt) as	repay_amt
 from bdp_cm_inte_co_lon_repay_dtl    -- 【9-还款明细表：bdp_cm_inte_co_lon_repay_dtl】
 group by dubil_no