一次复杂场景处理

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

已知条件：

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