資料統計指令碼（彙總）

1. 註冊數量；提交借款申請

select count(*) from  agentCore.custInfo where channelid=393 and datediff(registertime,'2018-07-22')<=0; #貸款平臺
select count(*) from  agentCore.loanCase where channelid=393 and datediff(createtime,'2018-07-22')<=0;

2. 填寫補充資料人數

select 
count(*) from
(
select 
a.id,
case when b.custid is not null then 1 else 0 end submitstatus
 from agentCore.loanCase a
left join
agentCore.custCompleteInfo b on a.id=b.loancaseid where a.channelid=393 and datediff(a.createtime,'2018-07-22')<=0
) t
where submitstatus=1;

3. 工作資訊及人行報告

select 
count(institution),
count(incomeMonthly),
count(reserveFundTime),
count(policyPeriod),
count(houseProperty),
sum(case when PBCReport=0 then 1 end),
sum(case when PBCReport=1 then 1 end),
sum(case when PBCReport=2 then 1 end)
 from
(
select 
a.id,
a.brwid,
a.brwtelenc,
a.brwidcardcodeenc,
a.brwidcardnameenc,
a.createtime,
a.channelid,
b.custid,
b.loancaseid,
b.idcardnameenc,
b.custtelnoenc,
b.idcardcodeenc,
b.institution,
b.incomeMonthly,
b.reserveFundTime,
b.policyPeriod,
b.premiumYear,
b.houseProperty,
b.houseValuation,
b.repayMonthly,
b.PBCReport,
case when b.custid is not null then 1 else 0 end submitstatus
 from agentCore.loanCase a
left join
agentCore.custCompleteInfo b on a.id=b.loancaseid where a.channelid=393 and datediff(a.createtime,'2018-07-22')<=0
) t where submitstatus=1;

4. 電核資料

# {3:通過,4:拒絕,6:跟進,2:稽核中}
select 
auditstatus,
count(*),
count(distinct id) 
from 
agentCore.loanCase where datediff(audittime,'2018-07-22')<=0 group by 1 order by 1;

5. 風控稽核資料

#｛1000:拒絕,2000:通過｝
select 
b.resultcode,
count(distinct caseid) 
from 
agentCore.loanCase a
join
agentCore.loanCaseAutoAuditLog b
on a.id=b.caseid
where datediff(b.createtime,'2018-07-22')<=0 group by 1 order by 1;

6. 檢視三個平臺放款通過與沒通過的名單

select
a.channelid, 
date(a.createtime) createtime,
a.id,
a.brwid,
a.brwtelenc,
a.brwidcardcodeenc,
a.brwidcardnameenc,
case when b.custid is not null then 1 else 0 end submitstatus
 from agentCore.loanCase a
left join
agentCore.custCompleteInfo b on a.id=b.loancaseid where a.channelid in (393,393,397);