如何获取群组在过去一年中的提及次数和成员数量

社区 数据与报告
,
1

您好，我是 Hopscotch 论坛 的版主，想请教能否协助完成以下数据探索器查询：

  • 过去 365 天内，各群组收到的 @ 提及次数
    • 按从低到高排序
    • 且仅包含成立超过 365 天的群组（即群组创建日期在 365 天之前）
  • 过去 365 天内，各群组收到的 @ 提及次数
    • 仅包含过去 365 天内提及次数少于 5 次的群组
    • 按从低到高排序
    • 且仅包含成立超过 365 天的群组（即群组创建日期在 365 天之前）
  • 各群组的成员数量
    • 仅包含成员数少于 10 人的群组
    • 按从低到高排序

我们的目标是逐步清理不活跃的群组。（我会将查询请求提交给管理员来创建）

提前感谢，
Tracey

（我想添加 data-explorer 标签，但目前似乎无法操作）

2

您好，

为明确起见，您需要以下三个查询吗？

  1. 针对所有成立超过 365 天的群组，按提及次数升序列出它们的提及次数。
  2. 针对所有成立超过 365 天且提及次数少于 5 次的群组，按提及次数升序列出它们的提及次数。
  3. 针对所有成员数少于 10 人的群组，列出它们的成员数量。

如果是这样，我可以为您完成这些查询——您所需的所有信息都存储在 groups、group_users 和 group_mentions 表中。

3

据我所知，Discourse 并未明确记录提及次数，但它会记录添加到主题中的链接。帖子中的群组提及会被记录在 topic_links 表的 url 字段中，格式为："/groups/<group_name>"。以下查询应能为您提供接近您所需的数据。您需要为 start_date 和 end_date 参数提供具体值。日期格式应为 yyyy-mm-dd。例如，若要获取过去一年的数据，请将 2020-01-01 作为 start_date，将 2020-12-31 作为 end_date 输入：

--[params]
-- date :start_date
-- date :end_date

WITH group_mentions AS (
SELECT
split_part(url, '/', 3) AS group_name
FROM topic_links tl
JOIN topics t ON t.id = tl.topic_id
WHERE internal = true
AND url LIKE '/groups/%'
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tl.created_at::date BETWEEN :start_date AND :end_date
)

SELECT
gm.group_name,
COUNT(gm.group_name) AS mention_count
FROM group_mentions gm
JOIN groups g ON g.name = gm.group_name
WHERE g.created_at::date <= :start_date
GROUP BY gm.group_name
ORDER BY mention_count DESC

该查询仅返回添加到常规帖子中的提及（但会包含添加到私密帖子中的提及）。如果您希望将添加到私信中的提及也包含在结果中，请从第一个查询中删除 AND t.archetype = 'regular' 这一行。

如果此查询未能返回您所需的数据类型，请告诉我。如果在此之前无人回答您的问题，我明天会尽力回复您的其他问题。