用sql統計信貸業務審批表、逾期表(即期)
報表1:審批表
#DROP VIEW IF EXISTS approve_channel_grp ;
#CREATE VIEW approve_channel_grp as
SELECT
date as ‘日期’,
channel ‘渠道’,
#count(apply_id) ‘進件量’,
#sum(case when approve_status = -6 then 1 else 0 end) ‘報告沒選’,
#concat(TRUNCATE(sum(case when approve_status = -6 then 1 else 0 end)/count(apply_id)*100, 2),‘%’) ‘報告沒選%’,
count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end) ‘審批次’,
#sum(case when tab。loan_n = 1 then 1 else 0 end) ‘續貸審批’,
#concat(TRUNCATE(sum(case when tab。loan_n = 1 then 1 else 0 end)/count(apply_id)*100, 2),‘%’) ‘續貸%’,
#sum(case when approve_status = 1 then 1 else 0 end) ‘待稽核’,
sum(case when approve_status = -1 then 1 else 0 end) ‘規則拒絕’,
concat(TRUNCATE(sum(case when approve_status = -1 then 1 else 0 end)/(count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end))*100, 2),‘%’) ‘規則拒絕%’,
#sum(case when approve_status = 3 then 1 else 0 end) ‘自動放款’,
concat(TRUNCATE(sum(case when approve_status = 3 then 1 else 0 end)/(count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end))*100, 2),‘%’) ‘自動放款%’,
concat(TRUNCATE(sum(case when approve_status in( 6 ,-3) then 1 else 0 end)/(count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end))*100, 2),‘%’) ‘人工審批%’,
sum(case when approve_status = 4 then 1 else 0 end) ‘人工待審批’,
sum(case when approve_status = -3 then 1 else 0 end) ‘人工拒絕’,
sum(case when approve_status = 6 then 1 else 0 end) ‘人工透過’,
concat(TRUNCATE(sum(case when approve_status = 6 then 1 else 0 end)/sum(case when approve_status in( 6 ,-3) then 1 else 0 end)*100, 2),‘%’) ‘人工透過率%’,
#concat(TRUNCATE(sum(case when approve_status = 4 then 1 else 0 end)/(count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end))*100, 2),‘%’) ‘人工待審批%’,
sum(case when approve_status in (3,6) then 1 else 0 end) ‘放款成功’,
concat(TRUNCATE(sum(case when approve_status in (3,6) then 1 else 0 end)/(count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end))*100, 2),‘%’) ‘核撥率%’
#sum(case when tab。loan_n = 1 and approve_status = 6 then 1 else 0 end) ‘續貸放款’,
#concat(TRUNCATE(sum(case when tab。loan_n = 1 and approve_status = 6 then 1 else 0 end)/sum(case when approve_status = 6 then 1 else 0 end)*100, 2),‘%’) ‘續貸佔比%’
FROM
approve_list
#where date > ‘2019-06-21’ and date< ‘2019-06-23’
GROUP BY date,channel
ORDER BY date desc,sum(case when approve_status in (3,6) then 1 else 0 end)/(count(apply_id)-sum(case when approve_status = -6 then 1 else 0 end))*100 desc
#limit 30
報表2:金融業務貸後-逾期表(即期)
程式碼
SELECT
DATE_FORMAT
(
a
。
endtime
,
‘%Y-%m-%d’
)
as
‘日期’
,
TRUNCATE
(
sum
(
b
。
money
),
0
)
‘正常應還$’
,
TRUNCATE
(
sum
(
if
(
a
。
`
status
`
!=
2
or
a
。
success_status
=
3
,
b
。
money
,
0
)),
0
)
‘首次逾期$’
,
concat
(
TRUNCATE
(
sum
(
if
(
a
。
`
status
`
!=
2
or
a
。
success_status
=
3
,
b
。
money
,
0
))
/
sum
(
b
。
money
)
*
100
,
2
),
‘%’
)
‘首次逾期率$%’
,
TRUNCATE
(
sum
(
if
(
a
。
`
status
`
!=
2
,
b
。
money
,
0
)),
0
)
‘當前逾期$’
,
concat
(
TRUNCATE
(
sum
(
if
(
a
。
`
status
`
!=
2
,
b
。
money
,
0
))
/
sum
(
b
。
money
)
*
100
,
2
),
‘%’
)
‘當前逾期率$%’
,
count
(
a
。
apply_id
)
‘正常應還#’
,
sum
(
if
(
a
。
`
status
`
!=
2
or
a
。
success_status
=
3
,
1
,
0
))
‘首次逾期#’
,
concat
(
TRUNCATE
(
sum
(
if
(
a
。
`
status
`
!=
2
or
a
。
success_status
=
3
,
1
,
0
))
/
count
(
a
。
apply_id
)
*
100
,
2
),
‘%’
)
‘首次逾期率#%’
,
sum
(
if
(
a
。
`
status
`
!=
2
,
1
,
0
))
‘當前逾期#’
,
concat
(
TRUNCATE
(
sum
(
if
(
a
。
`
status
`
!=
2
,
1
,
0
))
/
count
(
a
。
apply_id
)
*
100
,
2
),
‘%’
)
‘當前逾期率#%’
,
concat
(
TRUNCATE
(
sum
(
if
(
l
。
count
>
1
,
b
。
money
,
0
))
/
sum
(
b
。
money
)
*
100
,
2
),
‘%’
)
‘續貸佔比$% ’
from
app_repayment
a
left
join
app_A
b
on
b
。
apply_id
=
a
。
apply_id
left
join
app_B
c
on
c
。
id
=
a
。
apply_id
left
join
app_C
d
on
d
。
id
=
c
。
user_id
left
join
app_D
e
on
e
。
id
=
d
。
channel_id
left
join
(
SELECT
a
。
id
user_id
,
count
(
b
。
id
)
count
from
app_A
a
left
join
app_B
b
on
b
。
user_id
=
a
。
id
group
by
a
。
id
)
l
on
l
。
user_id
=
d
。
id
where
a
。
endtime
BETWEEN
DATE_SUB
(
CURDATE
(),
INTERVAL
7
DAY
)
and
DATE_SUB
(
CURDATE
(),
INTERVAL
-
1
DAY
)
and
e
。
channel
=
‘渠道A’
GROUP
BY
DATE_FORMAT
(
a
。
endtime
,
‘%Y-%m-%d’
)
desc
limit
7
結果展示
我是正陽, 很高興能透過文字認識你,點個關注,後會有期。
微信公眾號:正陽能量場