用户过去7天按电子邮件域名的帖子

数据与报告
1

查询过去7天按电子邮件域计算帖子数量，排除自动组（机器人、系统）

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- 提取电子邮件的域部分
  COUNT(p.id) AS post_count
FROM
  posts p
  JOIN users u ON p.user_id = u.id
  JOIN user_emails ue ON u.id = ue.user_id
    AND ue.primary = TRUE -- 确保我们使用的是主电子邮件
  LEFT JOIN group_users gu ON gu.user_id = u.id
  LEFT JOIN GROUPS g ON gu.group_id = g.id
    AND g.automatic = TRUE
WHERE
  p.created_at >= CURRENT_DATE - INTERVAL '7 days' -- 筛选过去7天的帖子
  AND g.id IS NULL -- 确保用户不属于任何自动组
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- 按帖子数量降序排序结果
2 个赞
2

这似乎不对。目的是什么？

按电子邮件域分组，计算上周的帖子，但排除系统用户？

是否需要计算已删除的帖子或来自已删除主题的帖子，排除小型操作帖子，或排除私信中的帖子？

3

这是对 SQL Helper 机器人用于查找各域帖子数量的快速粗略测试。用例可能是希望跟踪论坛内哪些子组参与度最高的协会或会员论坛。

当然，它还可以进行改进，例如删除已删除或小型操作帖子、PM 等，但我想将其发布，以防任何人能从中获得启发。

4

我认为它实际上会因为群组加入而给出虚高的数字（此外，每个人都属于一个自动群组以获得他们的信任级别，所以这里有点不一致）。我认为我们可以去掉这些，也许再做一些调整来聚焦主题、帖子和私信。

让我试试看。

也许是这样的：

-- [params]
-- string :interval = 7 days


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- 提取电子邮件的域名部分
  COUNT(p.id) AS "总帖子数",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "主题数",
  COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "帖子数",
  COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "私信帖子数"
FROM posts p
  JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- 确保我们使用的是主电子邮件
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- 根据参数过滤最近 x 时间内的帖子
  AND p.user_id > 0 -- 排除系统用户（系统和机器人）
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type <> 3
GROUP BY email_domain
ORDER BY "总帖子数" DESC -- 按帖子总数降序排序结果
2 个赞
5

2 篇帖子已拆分到新主题：User Stats plus email

6

此主题在上次回复后 30 天自动关闭。不再允许回复。