计算客服操作的净处理时长

  • 1.去掉连续类型计算时长

操作记录只剩下两种类型,客户操作U,客服操作T。计算UT之间的时间差。
例如U1->U2->T1->T2,只算U1和T1之间的时间差。
例如T1->T2->U1-U2-T3,只算U1和T3之间的时间差。
例如T1->U1-T2->U2->T3,计算U1和T2,U2和T3的时间差。
如果最后一条记录不是客服操作,也不是8和52,那么处理时长还需要加上(当前时间减去最后一条记录的时间点。

 tmp_jsc as 
(
    
select      ticket_id,
            o_type,
            operation,
            gmt_create,
            row_number()over(partition by ticket_id,o_type order by gmt_create asc) as result_rank,
            is_last,
            rank_diff
from 
    (select 
            ticket_id,
            o_type,
           ranks1,
           operation,
           gmt_create,
           ranks2,
           row_number()over(partition by ticket_id,o_type, ranks2-ranks1 order by gmt_create asc ) as rank_diff,
           case when ranks3=1 and operation not in (8,52) and o_type='U' then 1 else 0 end as is_last
    from 
        (
            select case when operation in (42,12) then 'T' WHEN operation in (5,1,8,52) then 'U' end as o_type,
                   operation,
                   gmt_create,
                   ticket_id,
                  row_number() over(partition by ticket_id, case when operation in (42,12) then 'T' WHEN operation in (5,1,8,52) then 'U' end  order by gmt_create asc ) as ranks1 ,
                  
                  row_number() over(partition by ticket_id    order by gmt_create asc ) as ranks2 , 
                  
                   row_number() over(partition by ticket_id    order by gmt_create desc ) as ranks3 ---判断是否最后一条
            from bi_ods.ods_council_ticket_operation_log
            where dt='${yyyymmdd}'
             and operation  in (42,12,5,1,8,52)
         )p
    ) p 
where p.rank_diff=1  or is_last=1
),
jcl_diff
(select p1.ticket_id,
       (nvl(sum(p2.gmt_create-p1.gmt_create),0) 
       +nvl(sum(case when p1.is_last=1 then unix_timestamp(concat('${last1day_date}',' 00:00:00'))*1000 -p1.gmt_create else 0 end ) ,0)
       +nvl(sum(case when p2.is_last=1 then unix_timestamp(concat('${last1day_date}', ' 00:00:00'))*1000 -p2.gmt_create else 0 end ),0))/1000/3600  as cs_handle_due
        
from (select   ticket_id,
            o_type,
            operation,
            gmt_create,
            result_rank,
            is_last
      from tmp_jsc
      where o_type='U' )p1 
full  join 
     (select  o_type,  
              operation,
              gmt_create,
              ticket_id, 
              result_rank,
              is_last
      from tmp_jsc
      where o_type='T' ) p2 
    on p1.ticket_id=P2.ticket_id
    and p1.result_rank=p2.result_rank
group by  p1.ticket_id
 )   


  • 2.presto:json解析:
json_extract('{"name":"王二","sex":"男","age":"25"}', '$.age')

– 3.date_add

date_add('day', -1, cast(p3.date_code as timestamp))=cast(p4.date_code as timestamp)
select  date_format(date_add('week', -7, now()),'%x%v')
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页