如何在SQL中获得平均响应时间 [英] How to get average response time in SQL

问题描述

我写信是为了让Avg回复聊天时间。

例如： -



visitor 2018-02- 19 04:32:17

visitor 2018-02-19 04:32:36

visitor 2018-02-19 04:32:49 />
visitor 2018-02-19 04:38:08

visitor 2018-02-19 04:38:34

agent 2018- 02-19 04:38:37

代理商2018-02-19 04:44:27

访客 2018-02-19 04： 46:59

代理商 2018-02-19 04:50:39

代理商2018-02-19 04:56 ：28

agent 2018-02-19 04:57:51

agent 2018-02-19 05:00:19

visitor 2018 -02-19 05:01:18

visitor 2018-02-19 05:01:23



所以我需要Agent First回复-Visitor每次聊天的第一个回复

1.2018-02-19 04:38:37-2018-02-19 04:32:17

2.2018-02-19 04 ：50：39-2018-02-19 04:46:59



我尝试过：



LEAD（user_type，1）OVER（由chat_id ORDER BY时间戳分区）为Resp_User，

min（CASE WHEN user_type ='agent'THEN timestamp ELSE NULL end）AS agent_time

from（

select a.chat_id，user_type，timestamp'epopoch' +timestamp* interval'1秒'作为timestamp

，ROW_NUMBER（）结束（由a.chat_id按timestamp分区）作为Rnk

来自livechat.chat_events加入livechat.chats ch on ch.chat_id = a.chat_id

加入livechat.group_info g on g.chat_id = a.chat_id

其中g.group_id in（1,4,7）

和a.type ='message'

- and cast（timestamp'epoch'+ ch.started_timestamp * interval'1秒'截止日期''2018-03-01'和'2018-03-31'

和a.chat_id in（'P4VDURT7MQ'）

按时间戳排序

）其中rnk> 1

group by 1,2,3

order by 3

I am writing to get Avg response time of chat.
For Example :-

visitor 2018-02-19 04:32:17
visitor 2018-02-19 04:32:36
visitor 2018-02-19 04:32:49
visitor 2018-02-19 04:38:08
visitor 2018-02-19 04:38:34
agent 2018-02-19 04:38:37
agent 2018-02-19 04:44:27
visitor 2018-02-19 04:46:59
agent 2018-02-19 04:50:39
agent 2018-02-19 04:56:28
agent 2018-02-19 04:57:51
agent 2018-02-19 05:00:19
visitor 2018-02-19 05:01:18
visitor 2018-02-19 05:01:23

So i need Agent First reply-Visitor first reply for every chat
1.2018-02-19 04:38:37-2018-02-19 04:32:17
2.2018-02-19 04:50:39-2018-02-19 04:46:59

What I have tried:

LEAD(user_type,1) OVER (PARTITION BY chat_id ORDER BY timestamp) as Resp_User,
min(CASE WHEN user_type = 'agent' THEN timestamp ELSE NULL end) AS agent_time
from (
select a.chat_id,user_type,timestamp 'epoch' + "timestamp" * interval '1 second' as "timestamp"
,ROW_NUMBER() over(partition by a.chat_id order by "timestamp") as Rnk
from livechat.chat_events a join livechat.chats ch on ch.chat_id=a.chat_id
join livechat.group_info g on g.chat_id=a.chat_id
where g.group_id in (1,4,7)
and a.type = 'message'
--and cast(timestamp 'epoch' + ch.started_timestamp * interval '1 second' as date) between '2018-03-01' and '2018-03-31'
and a.chat_id in ('P4VDURT7MQ')
order by timestamp
)where rnk>1
group by 1,2,3
order by 3

推荐答案

我建​​议你稍微更改你的数据库并为每对通信添加一个conversationID：打开一个新的ID访问者启动，并将其用于代理商帖子。如果访问者在代理之后响应，则会启动一个新的conversationID - 我会使用GUID值。 （我还会为每个主题添加一个ID - 以便相关的会话保持在一起）。



鉴于SQL，它不仅会使这更准确服务器是一个多用户系统，您可能有多个代理，但代理也可能正在处理多个访问者，因此识别单个会话非常简单：使用GROUP BY，并获取代理和服务器的第一个消息时间戳访客也非常简单。



您拥有的结构不允许任何这种结构，并使数据处理变得相当复杂。

I'd suggest that you change your DB a little and add a "conversationID" to each pair of communications: Open a new ID when the visitor starts, and use that for the agent posts. If the visitor responds after the agent, that starts a new conversationID - I'd use GUID values for this. (I'd also add an ID for each "topic" - so that related conversations are kept together).

Not only would it make this more accurate given that SQL Server is a multiuser system and you are likely to have multiple agents, but an agent is also likely to be processing multiple visitors as well, so identifying individual conversations is then pretty trivial: with GROUP BY, and getting the first message timestamp for agent and visitor is also pretty simple.

The structure you have doesn't allow for any of that, and complicates the data processing considerably.

这篇关于如何在SQL中获得平均响应时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！