我有一个mysql表来存储用户之间的消息.
我想计算特定用户等待回答他/她从其他用户收到的第一条消息的平均时间
我的表是这样的,subject_id可能是线程或帖子
|msg_id|sender_id|receiver_id| subject_id |msg |time |
--------------------------------------------------------------------------
| 1 | 123 | 456 | 3 |"yadda yadd.."|2016-01-31 00:27:16|
| 2 | 456 | 123 | 3 |"ladida .." |2016-01-31 00:37:16|
| 3 | 456 | 123 | 3 |"johndo .." |2016-01-31 01:47:04|
| 4 | 123 | 456 | 3 |"xxxxxx .." |2016-01-31 02:47:04|
| 5 | 456 | 123 | 3 |"qwerty .." |2016-01-31 03:47:04|
| 6 | 789 | 456 | 9 |"dadda kadd.."|2016-01-31 00:11:16|
| 7 | 789 | 456 | 9 |"fadda jadd.."|2016-01-31 00:12:16|
| 8 | 456 | 789 | 9 |"fadda jadd.."|2016-01-31 00:13:16|
在此特定情况下,用户456从用户123接收消息,并在10分钟后响应.
然后,他从用户789收到一条消息,并在1分钟内响应.
因此,用户456的平均响应时间平均为5分钟.
更新
让自己更清楚:想一个约会应用程序.我想计算出一个用户的平均响应时间是多少,因此其他用户将知道她/他回答消息的速度是非常快还是很慢
解决方法:
您可以尝试以下查询:
SELECT m1.sender_id, m1.receiver_id,
AVG(TIMESTAMPDIFF(MINUTE, m1.time, m2.time)) AS DiffInMinutes
FROM messages m1 INNER JOIN messages m2
ON m1.receiver_id = m2.sender_id AND m1.sender_id = m2.receiver_id AND
m2.time = (SELECT MIN(time) FROM messages WHERE time > m1.time)
GROUP BY m1.sender_id, m1.receiver_id, m2.sender_id, m2.receiver_id
输出:
╔═══════════╦═════════════╦═══════════════╗
║ sender_id ║ receiver_id ║ DiffInMinutes ║
╠═══════════╬═════════════╬═══════════════╣
║ 123 ║ 456 ║ 35 ║
║ 456 ║ 123 ║ 60 ║
║ 789 ║ 456 ║ 1 ║
╚═══════════╩═════════════╩═══════════════╝
请点击以下链接以获取正在运行的演示:
标签:mysql,php
来源: https://codeday.me/bug/20191027/1944119.html