java - 根据最近 24 小时共享 mysql 获取行

Question

我正在开发某种社交消息发布应用程序。其中，我需要获取过去24小时的数据趋势数据。我有 3 个表 - 消息、用户、共享

消息 - message_id、内容、user_id、created_at

用户 - user_id、电子邮件和其他数据

分享 - message_id、user_id、created_at

现在，我想要获取的是消息详细信息及其共享总数。我想根据过去 24 小时内的最高分享次数对这些消息进行排序，这会给我带来病毒式传播的消息。

我试过了，可能无效尝试:

SELECT msg.`message_id`, msg.`user_id`, COUNT(share.user_id) share_count
msg.created_at, category FROM 
(select * from message_shares ms where created_at>= DATE_SUB(CURDATE(), INTERVAL 24 HOUR)) trending,
 `messages` msg
left join message_shares share ON share.message_id = msg.message_id 
GROUP by msg.message_id order by msg.id desc

它给了我无效的 share_count 也没有给出有效的结果和消息。而且它没有根据过去 24 小时分享计数对消息进行排序。

我对 MySQL 查询没有太多深入的了解。因此，请向我提供查找这些详细信息的方法或查询。

Answer 1

也许您正在寻找这样的东西:

SELECT messages.message_id, messages.user_id, messages.created_at, 
COUNT(*) AS share_count 
FROM messages
INNER JOIN shares ON messages.message_id = shares.message_id
WHERE shares.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR) 
GROUP BY shares.message_id
ORDER BY share_count DESC;

直观的解释是，首先，选择来自 messages 的行，与来自 share 的行连接，并使用 created_at 的条件进行过滤分享以24小时为间隔。作为连接的结果，具有相同消息的行数与该消息所拥有的共享数一样多。然后，您可以按 share 的 message_id 进行分组，因此每条消息都有一个“其共享组”，您可以通过添加 COUNT 来获取每个组的大小(*) 列，现在按组工作。然后，您按 share_count 对行进行排序，并获取包含所需字段的查询投影。