如何计算每天发送给不同用户的消息数？

oalqel3c 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(308)

我有一张这样的table：

// messages
+----+----------------+-----------+-------------+-------------+
| id |    content     | sender_id | receiver_id |  date_time  |
+----+----------------+-----------+-------------+-------------+
| 1  | whatever1      | 1         | 3           | 1521097240  |
| 2  | whatever2      | 3         | 1           | 1521097241  |
| 3  | whatever3      | 1         | 3           | 1521097242  |
| 4  | whatever4      | 1         | 4           | 1521097243  |
| 5  | whatever5      | 1         | 5           | 1521097244  |
| 6  | whatever6      | 5         | 1           | 1521097245  |
+----+----------------+-----------+-------------+-------------+

现在我需要计算用户收到的消息数 sender_id = 1 对不同的人。因此，假设所有这些行都在过去一天，那么结果应该是 3 . 因为 sender_id = 1 已向发送消息 receiver_ids = 3,4,5 . 我如何在mysql中进行计数？
以下是我尝试过的：

SELECT count(1) as sent_messages_num
FROM messages
WHERE sender_id = 1  
AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))

所以我只需要添加一个分组。但不知道我该怎么做？

sql mysql

来源：https://stackoverflow.com/questions/49995696/how-can-i-count-the-number-of-messages-to-different-people-per-day

2条答案

按热度按时间

rnmwe5a21#

你需要使用 distinct 在 COUNT ```
SELECT count(distinct receiver_id) as sent_messages_num
FROM users
WHERE sender_id = 1
AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))

赞(0）回复(0）举报 2021-06-24

8yparm6h2#

尝试使用以下查询：

SELECT count(*) as sent_messages_num, receiver_id
FROM users
WHERE sender_id = 1  
AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))
GROUP BY receiver_id

这将为您提供每个接收器的消息数。我再读一遍答案后，我觉得下面的可能更合适：

SELECT count(DISTINCT receiver_id) as sent_messages_num
FROM users
WHERE sender_id = 1  
AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))

赞(0）回复(0）举报 2021-06-24

我来回答

如何计算每天发送给不同用户的消息数？

2条答案

相关问题

热门标签

最新问答