产品线的时效问题
这两天一直受制于各产品的时效问题没有好的方案，起因是需求啊！！还是需求！！老大对于整个产品线的优化问题到了一个新的高度，同行业的审批流程一直在下降，但是对于风控的要求还是不能有过多的取舍，谁让鱼和熊掌不能够兼得，即想有好的MOB数据，又要减少审批时间，那么需求来了，到底应该优化哪个阶段的时效呢？
于是有了以下的代码
‘’ select ‘
‘’ 2016-06-12 ‘ as 周期, kx.产品线, kx.初审, sum(kx.初审等待) 初审等待, sum（kx.初审处理）初审处理, sum(时效3) 时效3, count(transport_id) 初审时效处理量
‘’ from (
‘’ select
‘’ transport_id, cs 初审, product as 产品, round(cs_dd,2) as 初审等待 , round(cs_cl,2) as 初审处理, round(cs_dd + cs_cl ,2) as 时效3 ,
‘’ (case
‘’ when substr(product,0,3) in （’新薪贷’,’精英贷’,’新薪宜’,’助业贷’,’线下金’,’线下信’,’新薪（’,’助业宜’,’MSE’） then ‘城市信贷’
‘’ when substr(product,0,3) in （’网商贷’,’乐购分’,’宜学贷’,’pos贷’,’供应链’,’汽车金’,’宜车购’,’企合消费金融’） then ‘渠道’
‘’ when substr(product,0,3) in （’线上精’,’线上码’,’新线上’） then ‘宜人线上’
‘’ when substr(product,0,3) in （’线上瞬’,’瞬时贷’,’公积金’） then ‘k计划’
‘’ else product end) 产品线
‘’ from (select transport_id, sum(csdd) as cs_dd , sum(cscl) as cs_cl
‘’ from (select transport_id, process_node,
‘’ (case when process_node in (‘2-1’,’2-2’,’2-3’,’2-4’) then all_time else 0 end ) as csdd,(case when process_node in (‘2-0’) then all_time else 0 end ) as cscl
‘’
‘’ from (select transport_id, process_node, s_time, e_time, 24(time1+time2+time3 ) as all_time
‘’ from (select transport_id, process_node, s_time, e_time,
‘’ (case when to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-30 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-30 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-30 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-30 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0.375
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-30 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-30 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) - to_date(s_time, ‘yyyy-mm-dd hh24:mi:ss’)
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-30 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-30 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) - to_date(‘ 2016-03-30 09:30:00’,’yyyy-mm-dd hh24:mi:ss’）
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-30 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-30 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(‘ 2016-03-30 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) - to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) else 0 end ) time1 ,
‘’
‘’ (case when to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-31 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-31 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-31 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-31 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0.375
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-31 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-31 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) - to_date(s_time, ‘yyyy-mm-dd hh24:mi:ss’)
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-31 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-03-31 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) - to_date(‘ 2016-03-31 09:30:00’,’yyyy-mm-dd hh24:mi:ss’）
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-31 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-03-31 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(‘ 2016-03-31 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) - to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) else 0 end ) time2 ,
‘’
‘’ (case when to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-04-01 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-04-01 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-04-01 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-04-01 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then 0.375
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-04-01 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-04-01 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) - to_date(s_time, ‘yyyy-mm-dd hh24:mi:ss’)
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-04-01 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) <= to_date(‘ 2016-04-01 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) - to_date(‘ 2016-04-01 09:30:00’,’yyyy-mm-dd hh24:mi:ss’）
‘’ when to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-04-01 09:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(e_time,’yyyy-mm-dd hh24:mi:ss’) >= to_date(‘ 2016-04-01 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) then to_date(‘ 2016-04-01 18:30:00’,’yyyy-mm-dd hh24:mi:ss’) - to_date(s_time,’yyyy-mm-dd hh24:mi:ss’) else 0 end ) time3
‘’
‘’ from (select transport_id from
‘’ ( select transport_id,max(decesion_id) as decision_id from clic_sele_v.v_xg_tc_busi_decision
‘’ where to_date(inspection_time,’yyyy-mm-dd hh24:mi:ss’)>to_date(‘ 2016-06-08 17:30:01’,’yyyy-mm-dd hh24:mi:ss’) and to_date(inspection_time,’yyyy-mm-dd hh24:mi:ss’)
‘’ natural left join (select flow_log_id as n_log_id, process_node from clic_sele_v.v_xg_tc_flow_log ) )
‘’ natural left join ( select transport_id, min(flow_log_id) as flow_2 from clic_sele_v.v_xg_tc_flow_log where process_node in (‘5-1’,’6-1’,’1-2’,’15-3’) group by transport_id)
‘’ where process_node in (‘11-1’,’11-2’,’20-1’) and flow_2 is null )
‘’ natural left join
‘’
‘’ (select transport_id, process_node, s_time1 as s_time,
‘’ case when s_time1>=e_time1 and e_time1 is not null then s_time2
‘’ else e_time1 end e_time
‘’ from ( select rownum+1 rn, transport_id, process_node, s_time1, e_time1
‘’ from ( select
‘’ transport_id, process_node,
‘’ nvl(start_time, create_date) s_time1,
‘’ nvl(end_time,assign_time) e_time1
‘’ FROM clic_sele_v.v_xg_tc_flow_log
‘’ where to_date(create_date,’yyyy-mm-dd hh24:mi:ss’)> to_date(‘2015-10-01 00:00:01’,’yyyy-mm-dd hh24:mi:ss’)
‘’ order by transport_id , flow_log_id )
‘’ )
‘’ natural left join ( select
‘’ from ( select rownum rn, s_time2
‘’ from
‘’ ( select
‘’ nvl(start_time, create_date) s_time2
‘’ from clic_sele_v.v_xg_tc_flow_log
‘’ where to_date(create_date,’yyyy-mm-dd hh24:mi:ss’)> to_date(‘2015-10-01 00:00:01’,’yyyy-mm-dd hh24:mi:ss’)
‘’ order by transport_id , flow_log_id )
‘’ )
‘’ where rn!=1
‘’ )
‘’ )
‘’ ))) group by transport_id )
‘’ natural left join ( select transport_id, product_type,submit_dept_no as dept_id from clic_sele_v.v_xg_tc_bs_transport )
‘’ natural left join ( select dept_id ,dept_name from clic_sele_v.v_xg_TC_BS_DEPARTMENT )
‘’ natural left join ( select system_id as product_type , remark as product from clic_sele_v.v_xg_s_data_dic where system_type=’COMMON_PRODUCT_TYPE’)
‘’
‘’ natural left join
‘’ (select transport_id,beg_jude_name as cs
‘’ from
‘’ (SELECT transport_id, inspection_man from clic_sele_v.v_xg_tc_busi_decision
‘’ where decesion_id in
‘’ ( select max(decesion_id) as decision_id from clic_sele_v.v_xg_tc_busi_decision
‘’ where data_source=’2’ and is_decision=’1’
‘’ group by transport_id ))
‘’ natural left join
‘’ (select distinct user_code as inspection_man,user_name as beg_jude_name
‘’ from clic_sele_v.v_xg_tc_user))
‘’ ) kx
‘’ group by kx.产品线, kx.初审
‘’ order by kx.产品线
不得不说虽然很丑很长，但是，我是说但是，考虑了所有的流程的情况，神马提交的反欺诈、二次协商问题、回退问题等等….统统考虑进去，效率在三分钟以内，对于全线产品来说是通用的！感觉自己的逻辑又有了新的境界！毕竟真的是和一线的审核和系统的技术人员擦出了绳命的火花！