检查已发送消息的响应时间-仅使用SQL可能吗? [英] Check response time on sent message - possible with using only SQL?

问题描述

我正在尝试确定每个人的一般响应时间.信息.因此，每次SENDER向RECEIVER发送消息时，在用户响应之前经过了几秒钟(认为转换为UNIX时间戳是合适的解决方案，然后划分).

I am trying to figure out the general response time per. message. So, every time SENDER sends a message to RECEIVER, what how many seconds (think converting to UNIX timestamp would be the suitable solution, and then dividing) went, before the user responded.

我有这张桌子:

+--------------+----------+
|    Field     |   Type   |
+--------------+----------+
| msg_id       | int(11)  |
| msg_sender   | int(11)  |
| msg_receiver | int(11)  |
| msg_date     | datetime |
| msg_message  | longtext |
+--------------+----------+

我已经尝试了大约一个小时来弄清楚如何解决这个问题，而且我所有的解决方案都冗长，复杂和/或效率低下，这让我怀疑，是否有可能/最有效地做到这一点通过MySQL.

I have tried for about an hour to figure out how to even put this up, and all my solutions are horrible long, complicated and/or inefficient, and this made me wonder, if it even possible/most efficient to do it via MySQL.

也许你们中的一些人可以指出我正确的方向.

Maybe some of you could point me in the right direction.

推荐答案

select t1.*, min(t2.msg_date) as response_date 
from mytable t1 join mytable t2 
      on t1.msg_sender=t2.msg_receiver and t2.msg_sender=t1.msg_receiver and t2.msg_date>t1.msg_date
group by t1.msg_sender,t1.msg_receiver,t1.msg_date 

对于t1中的每条消息，我们都会得到t1消息之后发送的相应答案，并将它们分组以得到最小的响应日期(第一个答案日期).

For each message in t1 we get corresponding answers sent after t1 message and group them to get minimal response date (first answer date).

将以上选择内容放在子查询中，然后找到response_date-msg_date的AVG

Place the select above in a subquery and find AVG of response_date - msg_date

更新 您使用了错误的表m2.*我替换为m1.*，结果在那里

UPDATE You used incorrect table m2.* I replaced with m1.* and results are there

SELECT
    AVG(UNIX_TIMESTAMP(response_date) - UNIX_TIMESTAMP(msg_date)),
    AVG(response_date - msg_date)
FROM
(SELECT
    m1.*,
    min(m2.msg_date) as response_date 
FROM
    edu_messages m1
JOIN
    edu_messages m2 ON m1.msg_sender = m2.msg_receiver AND m2.msg_sender = m1.msg_receiver AND m2.msg_date > m1.msg_date 
GROUP BY
    m1.msg_sender,
    m1.msg_receiver,
    m1.msg_date) AS table1

这篇关于检查已发送消息的响应时间-仅使用SQL可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！