- 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