练习 |委托方在每个月重复委案率

 

• 数据：

案件清单表（vi_cs_rpt_cse_dtl）是汇总了每个甲方案件的一些案件信息——按照委托方、合同号、批次号、委案开始日期这4要素为唯一标示。
包含的主要字段（委托方、合同号、批次号、委案开始日期、委案金额、委案结束日期，导案操作时间）

• 题目：

若本月甲方给到案件导入系统之后，对于同个委托方，若本月导入系统的案件的合同号在之前月份导入过，即在系统中能够查到该合同号在之前月份出现过，则认为该案件重复委案【案件清单表的历史数据是从2018年1月1日开始截止今日】

• 求解：

每个委托方在每个月重复委案率？【重复委案率=本月重复委案的次数/本月导入案件数】

• >>思路：

1、数据筛选

　　筛选出需要用到的有效字段，这里是委托人、合同号、导案操作时间 即可

2、排序

　　利用row_number() over() 标出同一委托人的合同号在不同月份是第几次导入，标非1的就是和历史月份重复导入了　

3、 计算每月各委托人下面的重复导入合同数和总导入合同数

4、计算每月各委托人下的重复委案率

 

• >>脚本：

--stp1 计算月份对应的重复导入合同数

select 导入月份
,委托方
,count(distinct case when row_num <>1 then 合同号 end) as '重复导入合同数'
into #A

from (
select *
,row_number()over(partition by 委托方,合同号 order by 导入月份 asc) as row_num
from (
select 委托方
,合同号
,date_format(导案操作时间,'%y-%m') as '导入月份'
from vi_cs_rpt_cse_dtl
group by 委托方
,合同号
,date_format(导案操作时间,'%y-%m') 
) a1
) a2
group by 导入月份
,委托方

 

--stp2 计算月份对应的总导入合同数
select 导入月份
,委托方
,count(distinct 合同号) as '导入合同数'
into #B
from (
select 委托方
,合同号
,date_format(导案操作时间,'%y-%m') as '导入月份'
from vi_cs_rpt_cse_dtl
group by 委托方
,合同号
,date_format(导案操作时间,'%y-%m') 
) b1
group by 导入月份
,委托方

 


--stp3计算重复委案率

select a.导入月份
,a.委托方
,a.重复导入合同数*100.0/b.导入合同数 as '重复委案率%'
from #A a
left join #B b 
on a. 导入月份 =b.导入月份
and a.委托方 =b.委托方