时间戳

首页 > 解决方案 > MySQL计算一天内第一个初始收到的消息和第一个答案之间的平均时间

mysql - MySQL计算一天内第一个初始收到的消息和第一个答案之间的平均时间

问题描述

我有三个名为chats、chat_members和的表chat_messages。我想在一天内获得第一个初始收到的消息和第一个答案之间的平均时间。

聊天成员

|chat_id|user_id|
-----------------
|   1   |   3   |
|   1   |   4   |
|   2   |   3   |
|   2   |   6   |

聊天消息

|chat_id|sender_id|      body    |     created_at     |
------------------------------------------------------|
|   1   |   4     |"initial 1   "|2019-06-30 10:31:16 |
|   1   |   3     |"answer day 1"|2019-06-30 10:52:56 |
|   2   |   6     |"initial ans" |2019-06-30 10:54:33 |
|   2   |   3     |"answer"      |2019-06-30 10:56:30 |
|   1   |   4     |"initial 2"   |2019-07-01 09:30:02 |
|   1   |   3     |"answer day 2"|2019-07-01 09:45:02 |

预期结果：特定用户的平均响应时间。假设user_id: 3有平均响应时间17.5 minutes。

|chat_id|avg_minutes|
--------------------|
|   1   |   20      |
|   2   |   15      |

将其视为 Facebook 页面的平均响应时间：

“响应时间是您的主页在一天内对新消息发送初始响应所需的平均时间。”</p>

标签： mysqlmysql-5.7

解决方案

这是一个中间答案...

DROP TABLE IF EXISTS chat_messages;
CREATE TABLE chat_messages
(message_id SERIAL PRIMARY KEY
,chat_id INT NOT NULL
,sender_id INT NOT NULL
,body VARCHAR(255) NOT NULL
,created_at DATETIME NOT NULL
);

INSERT INTO chat_messages VALUES
(1,1,4,'initial 1','2019-06-30 10:31:16'),
(2,1,3,'answer day 1','2019-06-30 10:52:56'),
(3,2,6,'initial ans','2019-06-30 10:54:33'),
(4,2,3,'answer','2019-06-30 10:56:30'),
(5,1,4,'initial 2','019-07-01 09:30:02'),
(6,1,3,'answer day 2','2019-07-01 09:45:02');

SELECT x.*
     , y.sender_id responder
     , MIN(y.created_at) response 
  FROM chat_messages x 
  JOIN chat_messages y 
    ON y.chat_id = x.chat_id 
   AND y.created_at > x.created_at 
   AND y.sender_id <> x.sender_id 
 GROUP 
    BY x.message_id,y.sender_id;
+------------+---------+-----------+-------------+---------------------+-----------+---------------------+
| message_id | chat_id | sender_id | body        | created_at          | responder | response            |
+------------+---------+-----------+-------------+---------------------+-----------+---------------------+
|          1 |       1 |         4 | initial 1   | 2019-06-30 10:31:16 |         3 | 2019-06-30 10:52:56 |
|          3 |       2 |         6 | initial ans | 2019-06-30 10:54:33 |         3 | 2019-06-30 10:56:30 |
|          5 |       1 |         4 | initial 2   | 0019-07-01 09:30:02 |         3 | 2019-06-30 10:52:56 |
+------------+---------+-----------+-------------+---------------------+-----------+---------------------+

这给了我们每个响应者最早响应的时间。

接下来，我们可以汇总 created_at 和 response 之间的差异，但我将把它作为练习留给读者。

推荐阅读