平均工作人员回复主题的时间

数据与报告
1

此数据探索器报告旨在分析在指定日期范围和类别内创建的主题的员工（管理员和版主）的平均响应时间。

此报告有助于了解 Discourse 站点上员工互动的效率和响应能力，这对于社区管理至关重要。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false

WITH filtered_topics AS (
    SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
    FROM topics t
    WHERE
      (
        ':categories' = '0'
        OR t.category_id IN
          (
            SELECT id
            FROM categories
            WHERE id IN(:categories)
              OR (:include_subcategories AND parent_category_id IN(:categories))
          )
      )
      AND t.created_at >= :start_date
      AND t.created_at < :end_date
      AND t.archetype = 'regular'
      AND t.deleted_at IS NULL
),
staff_replies AS (
    SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE (u.admin = true OR u.moderator = true)
      AND p.deleted_at IS NULL
      AND p.post_type = 1
    GROUP BY p.topic_id
),
response_times AS (
    SELECT
        ft.topic_id,
        ft.created_at AS topic_created_at,
        sr.first_staff_reply,
        ft.category_id as category_id,
        EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
    FROM filtered_topics ft
    LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT
    topic_id,
    category_id,
    topic_created_at,
    response_time_hours as staff_response_time_hours
FROM response_times
ORDER BY topic_created_at ASC

SQL 查询说明

该报告使用几个公共表表达式 (CTE) 来分解查询：

  • filtered_topics：此 CTE 根据日期范围、类别以及是否包含子类别等输入参数筛选主题。它确保只考虑非删除的常规原型主题。
  • staff_replies：此 CTE 识别 filtered_topics CTE 中识别的主题的员工（管理员或版主）的首次回复。它会过滤掉已删除的帖子，并且只考虑主帖子（post_type = 1）。
  • response_times：此 CTE 通过计算主题创建时间和首次员工回复时间之间的差异来计算响应时间。结果从秒转换为小时。

来自 response_times CTE 的最终 SELECT 语句获取主题 ID、类别 ID、主题创建日期和计算出的员工响应时间（以小时为单位），并按主题创建日期对结果进行排序。

参数

  • start_date (date)：分析主题创建期间的开始日期。
  • end_date (date)：分析主题创建期间的结束日期。
  • categories (int_list)：用于筛选主题的类别 ID 列表。如果设置为 0，则包含所有类别。
  • include_subcategories (boolean)：一个标志，用于确定在分析中是否包含指定类别的子类别。

结果

  • topic_id：主题的唯一标识符。
  • category_id：主题所属的类别。
  • topic_created_at：创建主题的日期。
  • staff_response_time_hours：员工响应主题所需的时间（以小时为单位）。NULL 值表示没有员工回复该主题。

示例结果

topic_id category_id topic_created_at staff_response_time_hours
101 5 2024-01-02 1.5
102 5 2024-01-02 3.2
103 12 2024-01-03 NULL
104 12 2024-01-04 0.75
1 个赞
2

有没有简单的办法排除掉由工作人员发起的主题？

1 个赞
3

是的，您可以按如下方式修改报告以排除由员工用户创建的主题。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false

WITH staff_users AS (
    SELECT id
    FROM users
    WHERE admin = true OR moderator = true
),
filtered_topics AS (
    SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
    FROM topics t
    LEFT JOIN staff_users su ON t.user_id = su.id
    WHERE
      (
        ':categories' = '0'
        OR t.category_id IN
          (
            SELECT id
            FROM categories
            WHERE id IN(:categories)
              OR (:include_subcategories AND parent_category_id IN(:categories))
          )
      )
      AND t.created_at >= :start_date
      AND t.created_at < :end_date
      AND t.archetype = 'regular'
      AND t.deleted_at IS NULL
      AND su.id IS NULL  -- Exclude topics created by staff
),
staff_replies AS (
    SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
    FROM posts p
    JOIN staff_users su ON p.user_id = su.id
    WHERE p.deleted_at IS NULL
      AND p.post_type = 1
    GROUP BY p.topic_id
),
response_times AS (
    SELECT
        ft.topic_id,
        ft.created_at AS topic_created_at,
        sr.first_staff_reply,
        ft.category_id as category_id,
        EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
    FROM filtered_topics ft
    LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT
    topic_id,
    category_id,
    topic_created_at,
    response_time_hours as staff_response_time
FROM response_times
ORDER BY topic_created_at ASC

修改说明：

  1. staff_users CTE：添加了一个新的 CTE 来识别是管理员还是版主的用户。这有助于在后续步骤中筛选掉由员工创建的主题。
  2. filtered_topics CTE：此 CTE 现在包括与 staff_users CTE 的 LEFT JOIN，以排除 user_id 与任何员工 ID 匹配的主题。这是通过检查 su.id IS NULL 来完成的，这确保了该主题不是由员工创建的。
1 个赞
4

谢谢！现在结果平静多了。

该死，一切都很容易……当有足够的技能时。

1 个赞