MySQL 高级SQL进阶真题讲解(五)

一、题目描述

昨天有一朋友问我一个SQL.
场景是这样的，有一个客服对话记录表。记录了顾客人员与客服人员的对话记录。需求是统计出每一次顾客提问后客服人员的响应时间。
举例。假如有如下对话记录

顾客：你好
顾客：有个问题咨询一下 1
客服：请你描述一下你的问题 2
顾客：XXXXXXXXX
顾客：YYYYYYYY 3
客服：aaaaaaa 4
客服: bbbbbb

顾客多次连发，计最后一条时间；客服回复多条，计第一条时间。以上对话就统计 1与2的时间间隔， 3与4的时间间隔。

二、数据构建

create table diag_log
( sessid int,  -- 对话id
  msg_type varchar(10),  -- 0问 1答
  reqtime datetime  -- 对话时间
);

insert into diag_log(sessid,msg_type,reqtime)
select 1,0,'2023-08-25 18:53:52' union all
select 1,0,'2023-08-25 18:53:54' union all
select 1,1,'2023-08-25 18:53:59' union all
select 1,1,'2023-08-25 18:54:54' union all
select 1,0,'2023-08-25 18:55:52' union all
select 1,0,'2023-08-25 18:57:54' union all
select 1,1,'2023-08-25 18:59:52' union all
select 1,1,'2023-08-25 18:59:54' union all
select 1,0,'2023-08-25 19:01:54' ;

三、题目答案

select *, TIMESTAMPDIFF(SECOND,reqtime,next_time) as diff
from
(
select *,lead(msg_type) over w as next_type,lag(msg_type) over w as pre_type,
lead(reqtime) over w as next_time
from  diag_log 
window w  as (partition by sessid order by reqtime)
order by reqtime
) as t
where (msg_type = 0 and next_type = 1) 

四、答案讲解

定义窗口

window w  as (partition by sessid order by reqtime)

想了解窗口函数的，请移步我的窗口函数合辑

lead(msg_type) over w as next_type 取下一行对话类型
lag(msg_type) over w as pre_type 取上一行对话类型
lead(reqtime) over w as next_time 取下一行对话时间

where (msg_type = 0 and next_type = 1)

where 条件取出 当前行是顾客对话记录，下一行是客服对话记录的行，再用 TIMESTAMPDIFF 算出间隔时间，到此，此问题完成

这题重点就是要对窗口函数比较熟练应用。