sql-客服绩效考核问题
题目描述
tb1表中存储客服和用户的对话,字段分别表示,发送者id,接受者id,时间戳,消息内容。
dim_server 存储客服id 和名称。
求 客服名字 客服完成用户数 未完成会话 平均响应时长
tb_msg
from_id to_id msg_time msg_content
user1 user2 t1 aa
user2 user1 t2 aa
dim_server
server_id server_name
题目分析
该题多次出现在面试中,算是一道hard难度的sql题目了。这类sql题目主要是将所描述内容用数学方式抽象出来。本题可以细分为以下三个步骤去解答。
- 将
from_id
和to_id
做归一化处理。 - 对发送的消息进行打标,分清本条消息来自于哪一方。
- 分别计算三个指标后,用
server_name
做关联获取最终结果。
代码实现
-- 2023-02-02
with temp1 as (
select
from_id
,to_id
,t2.server_name
,msg_time
,msg_content
,case when t3.server_id is not null then '1' else '2' end flag -- 1 表示客服发的 2 表示用户发的
,if(from_id>to_id,concat(from_id,'-',to_id),concat(to_id,'-',from_id)) relation1
from
tb_msg t1
left join
dim_server t2
on
t1.to_id = t2.server_id
left join
dim_server t3
on
t1.from_id = t3.server_id
)
-- 客服完成用户数
select
server_name
,sum(case when flag = 1 then 1 else 0 end) user_cnt -- 客服完成的用户数
from
(
select
server_name
,flag
,relation1
,lead(from_id,1,'-999') over(partition by relation1 order by msg_time) next_user
from
temp1 t1
)aa
where
next_user = '-999'
group by
server_name
从一名不羁的码农开始,谈风月之余谈技术