一次复杂场景处理

背景 :工单日志记录了客服的每次操作,需要计算再 原因分析中,客服组的处理次数,和处理时间。

已知条件:

1.客户操作当前工单结束时间,为工单下一条日志的起始时间。

2. 工单在 连续客服组内流转,只能算客服组处理一次,及 两条连续的日志客服组相同,只能算作一次处理。

数据如下:

create table ticket_log  
(
        ticket_id BIGINT  COMMENT "工单id",
        schedule_id BIGINT  COMMENT  "工单流转节点id",
        schedule   STRING   COMMENT  "工单流转节点名称",
        cust_user_name STRING  COMMENT  "客服名称" ,
        group_name     STRING  COMMENT  "客服组名称" ,
        create_time     STRING  COMMENT  "日志创建时间" 
) COMMENT "工单日志表";

23442532    4    原因分析    张三    品质部    2022-12-27 14:38:14
23442532    4    原因分析    李四    品质部    2022-12-27 15:38:14
23442532    4    原因分析    王五    硬件研发中心    2022-12-27 16:38:14
23442532    4    原因分析    李四    品质部    2022-12-27 18:38:14
23442532    4    原因分析    张三    品质部    2022-12-27 19:38:14
23442532    5    临时对策    李四    品质部    2022-12-27 23:38:14
23442533    4    原因分析    李四    品质部    2022-12-27 11:38:14
23442533    4    原因分析    王五    硬件研发中心    2022-12-27 12:38:14
23442533    5    临时对策    李四    品质部    2022-12-27 21:38:14

思路分析

正常可以直接用开窗函数,获取下一条的时间, 之后得到本条记录的时间,次数,复杂在于基于组计算, 连续的同一组操作,只能计算一次。 可以将同一个组连续操作看作一个周期,之后将组内数据汇总成一条,之后再计算。

sql 代码:

select 
     group_name,
     count(*) ,--处理次数
     sum(datediff(cast(schedule_period_end_time as DATETIME ),cast(start_time as DATETIME ),"hh")) -- 计算时间,用datediff 计算不精准,准确可以用时间戳
from (
    select
        t1.*
        ,ROW_NUMBER() over(PARTITION  by  ticket_id,schedule_id,schedule_period order by start_time asc)   schedule_period_rank_number  -- 周期内排序
        ,LAST_VALUE(end_time) OVER(PARTITION by  ticket_id,schedule_id,schedule_period  ) as  schedule_period_end_time  -- 获取当前周期的结束时间    
    from(--  对数据进行周期划分
        select 
            t1.*
            ,count(is_schedule_period_start)  OVER(PARTITION  by   ticket_id,schedule_id order by   start_time asc)   schedule_period   -- 同部门连续操作,归为一个周期
        from (
            select 
                    t1.*
                    ,if(last_group_name!=group_name  or period_schedule_rank_number=1,1,null)    is_schedule_period_start  -- 判断是不是新部门操作,(起始操作,这里也可以直接日期,不用 rank_number)
            from (
                select 
                    ticket_id
                    ,schedule_id
                    ,group_name
                    ,create_time as  start_time -- 起始时间
                    ,lead(create_time,1)  OVER(PARTITION by ticket_id  order by  create_time ) as end_time
                    ,row_number() over(PARTITION  by ticket_id ) period_schedule_rank_number
                    ,lag(group_name,1)  OVER(PARTITION by ticket_id,schedule_id  order by  create_time ) as last_group_name -- 上一个部门
                from   ticket_log
            )t1 where   schedule_id!=5  -- 剔除非原因分析的数据
        )t1
    )t1    
)t1 where  schedule_period_rank_number=1  -- 每个连续的操作周期中 只取一条  
group by group_name

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值