用于活动流的智能 MySQL GROUP BY

php mysql social-networking

2022-08-30 15:41:51

我正在为我们的网站构建一个活动流，并且已经取得了一些不错的进展，效果很好。

它由两个表提供支持：

流：

id- 唯一流项目 ID
user_id- 创建流项目的用户的 ID
object_type- 对象类型（当前为“卖方”或“产品”）
object_id- 对象的内部 ID（当前为卖家 ID 或产品 ID）
action_name- 对对象采取的操作（目前是“购买”或“心脏”）
stream_date- 创建操作的时间戳。
hidden- 用户是否选择隐藏项目的布尔值。

如下：

id- 唯一的关注 ID
user_id- 启动“关注”操作的用户的 ID。
following_user- 被关注用户的 ID。
followed- 执行后续操作的时间戳。

目前，我正在使用以下查询从数据库中提取内容：

查询：

SELECT stream.*,
   COUNT(stream.id) AS rows_in_group,
   GROUP_CONCAT(stream.id) AS in_collection
FROM stream
INNER JOIN follows ON stream.user_id = follows.following_user
WHERE follows.user_id = '1'
  AND stream.hidden = '0'
GROUP BY stream.user_id,
     stream.action_name,
     stream.object_type,
     date(stream.stream_date)
ORDER BY stream.stream_date DESC;

这个查询实际上运行得很好，并且使用一些PHP来解析MySQL返回的数据，如果操作之间的时间不是太长，我们可以创建一个不错的活动流，其中包含同一用户相同类型的操作，这些操作被分组在一起（请参阅下面的示例）。

Current Stream Output Example

我的问题是，我如何让它更聪明？目前，它按一个轴（“用户”活动）进行分组，当MySQL知道在特定时间范围内特定用户有多个项目时，它们会分组。

我怎样才能使它更智能并按另一个轴分组，例如“object_id”，因此，如果同一对象有多个操作按顺序分组，则这些项目将被分组，但保持我们当前拥有的按用户对操作/对象进行分组的分组逻辑。在没有数据重复的情况下实现这一点？

按顺序出现的多个对象的示例：

Multiple Objects Appearing in Sequence

我知道像这样的问题的解决方案可能会变得非常复杂，非常快，但我想知道MySQL中是否有一个优雅且相当简单的解决方案（希望如此）。

答案 1

关于您期望结果的一些观察结果：

一些物品是汇总的（杰克·斯普拉特（Jack Sprat）有七个卖家），而另一些则是逐项列出的（纳尔逊勋爵特许金后）。您可能需要在查询中有一个 UNION，它将这两类项目从两个单独的子查询中拉到一起。

您使用相当粗糙的时间戳接近度函数来分组您的项目... 。您可能希望使用更复杂和可调整的方案...像这样，也许DATE()

  GROUP BY TIMESTAMPDIFF(HOUR,CURRENT_TIME(),stream_date) DIV hourchunk

这将允许您按年龄块对内容进行分组。例如，如果您使用48，则会将0-48小时前的内容分组在一起。向系统添加流量和操作时，您可能希望减小该值。hourchunkhourchunk

答案 2

我的印象是，您需要像您一样按用户进行分组，但是在分组之后，还需要按操作进行分组。

在我看来，你需要一个这样的子查询：

SELECT *, -- or whatever columns
   SUM(actions_in_group) AS total_rows_in_group,
   GROUP_CONCAT(in_collection) AS complete_collection
   FROM
     ( SELECT stream.*, -- or whatever columns
          COUNT(stream.id) AS actions_in_user_group,
          GROUP_CONCAT(stream.id) AS actions_in_user_collection
       FROM stream
       INNER JOIN follows
       ON stream.user_id = follows.following_user
       WHERE follows.user_id = '1'
         AND stream.hidden = '0'
       GROUP BY stream.user_id,
            date(stream.stream_date)
     )
   GROUP BY object_id,
            date(stream.stream_date)
   ORDER BY stream.stream_date DESC;

您的初始查询（现在是内部查询）按用户分组，但随后用户组按相同的操作重新分组 - 也就是说，从一个卖家处购买的相同产品或销售将放在一起。