平均每个主题的会员回复数（不包括工作人员）

社区 数据与报告
5

我认为这样的查询可以为您提供所需的数据：

-- [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 and non-staff posts (monthly)
Staff and non-staff posts (monthly)1396×712 24.7 KB

另外，附带一些说明：

此查询旨在提供论坛活动的月度统计摘要，特别关注在给定时间范围内区分“员工”和“非员工”用户的贡献。计算的指标包括创建的主题总数、发布的所有帖子、发帖的独立非员工用户数量、非员工发布的帖子数量和百分比、每个主题的平均非员工帖子数，以及员工的相应数据。这些信息旨在深入了解用户参与度、内容生成以及员工与非员工在论坛讨论中的参与率。该查询通过仅考虑“常规”（非私人消息）主题，并排除指定日期范围内的任何已删除帖子或主题、悄悄话/短帖/版主操作以及系统用户的帖子来确保准确性。

在此示例中，“员工”标准是指用户属于自动化的 @staff 组，该组同时包括管理员和版主——但这可以调整为仅针对管理员，甚至是一个自定义的非技术性“网站员工”组。如果您明确要排除版主，可以将开头的 group_id 更改为“1”。 :+1:

这是否是您正在寻找的内容？