风控研发中心
…
导数sql及说明
5月6月入催出催及坏账拆分
转至元数据结尾
由
chenjian10添加，由
chenjian10最后更新于2017-06-29 (
查看更改)
转至元数据起始
jira：
JFBI-27 - 入催出催和坏账情况 (
关闭)
5月6月入催出催及坏账拆分-入催出催
-- 1
select
-- l.end_time, -- 5.7
l.statis_time as '还款日', -- 5.8
-- sum(1) as '到期应还款笔数（所有）',
sum(case when(p.start_time >= '2017-05-01') then 1 else 0 end) as '到期应还款笔数',
sum(case when(p.start_time >= '2017-05-01' and l.order_status = 0 and datediff(date(l.update_time), l.end_time) < 2) then 1 else 0 end) as '按时还款笔数',
sum(case when(l.order_status in(0,3) and datediff(date(l.update_time), l.end_time) >= 2) then 1 else 0 end) as '入催数',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0,3) and datediff(date(l.update_time), l.end_time) >= 2) then 1 else 0 end) as '入催数（5月）',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 11) then 1 else 0 end) as 'S1出催数（1-10）',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 21) then 1 else 0 end) as 'S2出催率（1-20）',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 31) then 1 else 0 end) as 'S3出催率（1-30）'
-- sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) >= 32) then 1 else 0 end) as 'S4出催率（30+）',
-- sum(case when(p.start_time >= '2017-05-01' and l.order_status in(3)) then 1 else 0 end) as '至今未还款'
from
(
select
id,start_time,end_time,date_add(end_time, interval 1 day) as statis_time,loan_type,order_status,update_time,member_id,
REVERSE(LEFT(REVERSE(EDW.queryParentLoanId (id)), INSTR(REVERSE(EDW.queryParentLoanId (id)), ',') - 1)) as origin_loan_id
from EDW.fin_vca_pda_loan
) l
inner join EDW.fin_vca_pda_loan p on p.id = l.origin_loan_id
where 1=1 and del_flag = 0
-- and l.loan_type = 0
and l.order_status not in (5,6)
and l.end_time >= '2017-05-07' and l.end_time < '2017-05-31'
group by l.statis_time
-- 2
select
-- l.end_time, -- 5.7
l.statis_time as '还款日', -- 5.8
-- sum(1) as '到期应还款笔数（所有）',
sum(case when(p.start_time >= '2017-06-01') then 1 else 0 end) as '到期应还款笔数',
sum(case when(l.order_status = 0 and p.start_time >= '2017-06-01' and datediff(date(l.update_time), l.end_time) < 2) then 1 else 0 end) as '按时还款笔数',
sum(case when(l.order_status in(0,3) and datediff(date(l.update_time), l.end_time) >= 2) then 1 else 0 end) as '入催数',
sum(case when(p.start_time >= '2017-06-01' and l.order_status in(0,3) and datediff(date(l.update_time), l.end_time) >= 2) then 1 else 0 end) as '入催数（6月）',
sum(case when(p.start_time >= '2017-06-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 11) then 1 else 0 end) as 'S1出催数（1-10）',
sum(case when(p.start_time >= '2017-06-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 21) then 1 else 0 end) as 'S2出催率（1-20）',
sum(case when(p.start_time >= '2017-06-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 31) then 1 else 0 end) as 'S3出催率（1-30）'
-- sum(case when(p.start_time >= '2017-06-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) >= 32) then 1 else 0 end) as 'S4出催率（30+）',
-- sum(case when(p.start_time >= '2017-06-01' and l.order_status in(3)) then 1 else 0 end) as '至今未还款'
from
(
select
id,start_time,end_time,date_add(end_time, interval 1 day) as statis_time,loan_type,order_status,update_time,member_id,
REVERSE(LEFT(REVERSE(EDW.queryParentLoanId (id)), INSTR(REVERSE(EDW.queryParentLoanId (id)), ',') - 1)) as origin_loan_id
from EDW.fin_vca_pda_loan
) l
inner join EDW.fin_vca_pda_loan p on p.id = l.origin_loan_id
where 1=1 and del_flag = 0
-- and l.loan_type = 0
and l.order_status not in (5,6)
and l.end_time >= '2017-06-07' and l.end_time < '2017-06-30'
group by l.statis_time
-- 3
select
-- l.end_time, -- 5.7
l.statis_time as '还款日', -- 5.8
-- sum(1) as '到期应还款笔数（所有）',
sum(case when(p.start_time >= '2017-05-01') then 1 else 0 end) as '到期应还款笔数',
sum(case when(p.start_time >= '2017-05-01' and l.order_status = 0 and datediff(date(l.update_time), l.end_time) < 2) then 1 else 0 end) as '按时还款笔数',
sum(case when(l.order_status in(0,3) and datediff(date(l.update_time), l.end_time) >= 2) then 1 else 0 end) as '入催数',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0,3) and datediff(date(l.update_time), l.end_time) >= 2) then 1 else 0 end) as '入催数（5月及以后）',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 11) then 1 else 0 end) as 'S1出催数（1-10）',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 21) then 1 else 0 end) as 'S2出催率（1-20）',
sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) between 2 and 31) then 1 else 0 end) as 'S3出催率（1-30）'
-- sum(case when(p.start_time >= '2017-05-01' and l.order_status in(0) and datediff(date(l.update_time), l.end_time) >= 32) then 1 else 0 end) as 'S4出催率（30+）',
-- sum(case when(p.start_time >= '2017-05-01' and l.order_status in(3)) then 1 else 0 end) as '至今未还款'
from
(
select
id,start_time,end_time,date_add(end_time, interval 1 day) as statis_time,loan_type,order_status,update_time,member_id,
REVERSE(LEFT(REVERSE(EDW.queryParentLoanId (id)), INSTR(REVERSE(EDW.queryParentLoanId (id)), ',') - 1)) as origin_loan_id
from EDW.fin_vca_pda_loan
) l
inner join EDW.fin_vca_pda_loan p on p.id = l.origin_loan_id
where 1=1 and del_flag = 0
-- and l.loan_type = 0
and l.order_status not in (5,6)
and l.end_time >= '2017-05-07' and l.end_time < '2017-06-30'
group by l.statis_time
5月6月入催出催及坏账拆分-坏账
SELECT
'5月' as '月份',
COUNT(DISTINCT(CASE WHEN l.order_status IN(1,2,3) THEN l.member_id ELSE NULL END)) AS '所有应还款用户数', -- 所有应还款用户数
COUNT(DISTINCT(CASE WHEN l.order_status IN(3) THEN l.member_id ELSE NULL END)) AS '到期应还未还人数', -- 到期应还未还人数
COUNT(DISTINCT(CASE WHEN l.order_status IN(3) AND DATEDIFF(DATE(NOW()), l.end_time) >= 31 THEN l.member_id ELSE NULL END)) AS 'D30+逾期人数', -- D30+逾期人数\金额
SUM(CASE WHEN l.order_status IN(3) AND DATEDIFF(DATE(NOW()), l.end_time) >= 31 THEN l.capital ELSE 0 END) AS 'D30+逾期金额', -- D30+逾期人数\金额
SUM(CASE WHEN l.order_status IN(0,1,2,3) THEN l.capital ELSE 0 END) AS '放款总金额', -- 放款总金额
SUM(CASE WHEN l.order_status IN(0) THEN l.capital ELSE 0 END) AS '还款总金额' -- 还款总金额
FROM
(
SELECT
id,start_time,end_time,DATE_ADD(end_time, INTERVAL 1 DAY) AS statis_time,loan_type,order_status,update_time,member_id,capital,
REVERSE(LEFT(REVERSE(EDW.queryParentLoanId (id)), INSTR(REVERSE(EDW.queryParentLoanId (id)), ',') - 1)) AS origin_loan_id
FROM EDW.fin_vca_pda_loan
) l
INNER JOIN EDW.fin_vca_pda_loan p ON p.id = l.origin_loan_id
WHERE 1=1
AND l.order_status IN(0,1,2,3)
AND p.start_time >= '2017-05-01' AND p.start_time < '2017-06-01'
union all
SELECT
'6月（截至当前）' as '月份',
COUNT(DISTINCT(CASE WHEN l.order_status IN(1,2,3) THEN l.member_id ELSE NULL END)) AS '所有应还款用户数', -- 所有应还款用户数
COUNT(DISTINCT(CASE WHEN l.order_status IN(3) THEN l.member_id ELSE NULL END)) AS '到期应还未还人数', -- 到期应还未还人数
COUNT(DISTINCT(CASE WHEN l.order_status IN(3) AND DATEDIFF(DATE(NOW()), l.end_time) >= 31 THEN l.member_id ELSE NULL END)) AS 'D30+逾期人数', -- D30+逾期人数\金额
SUM(CASE WHEN l.order_status IN(3) AND DATEDIFF(DATE(NOW()), l.end_time) >= 31 THEN l.capital ELSE 0 END) AS 'D30+逾期金额', -- D30+逾期人数\金额
SUM(CASE WHEN l.order_status IN(0,1,2,3) THEN l.capital ELSE 0 END) AS '放款总金额', -- 放款总金额
SUM(CASE WHEN l.order_status IN(0) THEN l.capital ELSE 0 END) AS '还款总金额' -- 还款总金额
FROM
(
SELECT
id,start_time,end_time,DATE_ADD(end_time, INTERVAL 1 DAY) AS statis_time,loan_type,order_status,update_time,member_id,capital,
REVERSE(LEFT(REVERSE(EDW.queryParentLoanId (id)), INSTR(REVERSE(EDW.queryParentLoanId (id)), ',') - 1)) AS origin_loan_id
FROM EDW.fin_vca_pda_loan
) l
INNER JOIN EDW.fin_vca_pda_loan p ON p.id = l.origin_loan_id
WHERE 1=1
AND l.order_status IN(0,1,2,3)
AND p.start_time >= '2017-06-01' AND p.start_time < '2017-07-01'
union all
SELECT
'5月以后（截至当前）' as '月份',
COUNT(DISTINCT(CASE WHEN l.order_status IN(1,2,3) THEN l.member_id ELSE NULL END)) AS '所有应还款用户数', -- 所有应还款用户数
COUNT(DISTINCT(CASE WHEN l.order_status IN(3) THEN l.member_id ELSE NULL END)) AS '到期应还未还人数', -- 到期应还未还人数
COUNT(DISTINCT(CASE WHEN l.order_status IN(3) AND DATEDIFF(DATE(NOW()), l.end_time) >= 31 THEN l.member_id ELSE NULL END)) AS 'D30+逾期人数', -- D30+逾期人数\金额
SUM(CASE WHEN l.order_status IN(3) AND DATEDIFF(DATE(NOW()), l.end_time) >= 31 THEN l.capital ELSE 0 END) AS 'D30+逾期金额', -- D30+逾期人数\金额
SUM(CASE WHEN l.order_status IN(0,1,2,3) THEN l.capital ELSE 0 END) AS '放款总金额', -- 放款总金额
SUM(CASE WHEN l.order_status IN(0) THEN l.capital ELSE 0 END) AS '还款总金额' -- 还款总金额
FROM
(
SELECT
id,start_time,end_time,DATE_ADD(end_time, INTERVAL 1 DAY) AS statis_time,loan_type,order_status,update_time,member_id,capital,
REVERSE(LEFT(REVERSE(EDW.queryParentLoanId (id)), INSTR(REVERSE(EDW.queryParentLoanId (id)), ',') - 1)) AS origin_loan_id
FROM EDW.fin_vca_pda_loan
) l
INNER JOIN EDW.fin_vca_pda_loan p ON p.id = l.origin_loan_id
WHERE 1=1
AND l.order_status IN(0,1,2,3)
AND p.start_time >= '2017-05-01' AND p.start_time < '2017-07-01'
转载于:https://www.cnblogs.com/shujuxiong/p/11257921.html
