首先，新年快乐！
我想知道用于获取用户（不包括管理员）每月平均回复主题数的 SQL 查询。
或者，是否有办法获得会员每月发布的帖子总数与工作人员每月发布的帖子总数的比率。
谢谢！
新年快乐 (有点迟了 
)
对于平均值，您是在寻找由员工和非员工创建的主题，但回复数仅来自非员工帖子？您是只想排除管理员，还是排除管理员和版主？
嗨 Jammy！
没错，查询可以显示（由员工和非员工创建的）所有主题，但回复计数仅来自非员工帖子。
目前我们只能排除管理员（因为我启动我的社区时，管理员和版主是相同的 :))
但也很希望能够轻松获得员工创建的主题与非员工（排除管理员）创建的主题之间的比例。
1 个赞
我认为这样的查询可以为您提供所需的数据：
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "月份",
ms.total_topics AS "所有主题",
ms.total_posts AS "所有帖子",
ms.non_staff_posts AS "非员工帖子",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "非员工帖子（占总数百分比）",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "每个主题的平均非员工帖子数",
ms.non_staff_users AS "发帖的非员工用户",
ms.staff_posts AS "员工帖子",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "员工帖子（占总数百分比）",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "每个主题的平均员工帖子数",
ms.staff_users AS "发帖的员工用户"
FROM month_stats ms
ORDER BY "月份"
这将为您提供类似这样的结果：
另外，附带一些说明：
此查询旨在提供论坛活动的月度统计摘要，特别关注在给定时间范围内区分“员工”和“非员工”用户的贡献。计算的指标包括创建的主题总数、发布的所有帖子、发帖的独立非员工用户数量、非员工发布的帖子数量和百分比、每个主题的平均非员工帖子数，以及员工的相应数据。这些信息旨在深入了解用户参与度、内容生成以及员工与非员工在论坛讨论中的参与率。该查询通过仅考虑“常规”（非私人消息）主题，并排除指定日期范围内的任何已删除帖子或主题、悄悄话/短帖/版主操作以及系统用户的帖子来确保准确性。
在此示例中，“员工”标准是指用户属于自动化的 @staff 组，该组同时包括管理员和版主——但这可以调整为仅针对管理员，甚至是一个自定义的非技术性“网站员工”组。如果您明确要排除版主，可以将开头的 group_id 更改为“1”。 
这是否是您正在寻找的内容？
3 个赞
出于某种奇怪的原因，我知道员工的 id 是三。但如何找到那个 id 呢？起初我完全确定它会出现在 url 中，就像其他所有 id 一样，但事实并非如此。这里只使用了名字。
我懂的 SQL 很少，可以说我不会。但这个显示了每个组 id。
select
id,
name
from
groups
但肯定有更常见的方法可以找到它，不是吗？
Personally, I really want a group_id parameter lookup just like the user_id one 
- Param dropdown for group_id in data explorer query
But until that dream comes true I use the json of the groups page to find it out eg. https://meta.discourse.org/g.json
You can do a group look-up within the query itself so it works on group names, which may be a more user-friendly way of doing it. So something like:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(or the hard-coded version if you didn’t want a parameter:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
2 个赞
是的，这是我应该提到的一个怪癖。刷新页面后，参数输入框应该会显示出来。
1 个赞
我敢打赌你是临时写的那段代码。因为它根本不好用 
它显示了来自目标组的每个用户，并声称每个人都是 is_staff 
不过还是谢谢！我为初级管理员提供了关于 json 和如何使用 SQL 的宝贵信息（真的，但我仍然喜欢看看 AI 报告是如何看待它的……）
在此示例中，is_staff 位是此特定查询功能的一部分。它被专门添加在此处 SELECT user_id, true as is_staff，而不是来自数据库本身。它将您指定的任何组成员设置为“staff”，以便他们可以分为两组结果集（staff 帖子与非-staff 帖子）。
因此，如果您有一个“employees”组，但该组在数据库中并不被视为技术上的网站 staff，您仍然可以添加他们，他们将进入“staff”类别，而不是“non-staff”类别。
1 个赞
太棒了，这正是我需要的，非常感谢！\n为了确认： “帖子”是编译主题+回复，还是只计算回复？\n\n再次感谢！
1 个赞
对于这个，“posts”不包括主题的第一个帖子，所以只有回复。
1 个赞
您好 @JammyDodger
您认为是否可以实现相同的功能，但仅限于主题（=创建新帖子）？
非常感谢！
1 个赞
您是指在此查询中，由员工创建的主题与非员工添加的主题之间的比率吗？
是的，在同一个查询中，那将是太棒了！
1 个赞
我认为添加这些列应该可以做到：
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "月份",
ms.total_topics AS "所有主题",
ms.non_staff_topics AS "非员工主题",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "非员工主题（占总数的 %）",
ms.staff_topics AS "员工主题",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "员工主题（占总数的 %）",
ms.total_posts AS "所有帖子",
ms.non_staff_posts AS "非员工帖子",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "非员工帖子（占总数的 %）",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "每个主题的平均非员工帖子数",
ms.non_staff_users AS "发帖的非员工用户数",
ms.staff_posts AS "员工帖子",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "员工帖子（占总数的 %）",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "每个主题的平均员工帖子数",
ms.staff_users AS "发帖的员工用户数"
FROM month_stats ms
ORDER BY "月份"
非常感谢，这太完美了！
1 个赞
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.