用户每月已解决问题及当前分配主题

数据与报告
,
1

:discourse: 此报告需要启用 Discourse Solved 插件。

此数据探索器报告提供了特定时间范围内特定组成员执行活动的概览。具体来说，它侧重于两项主要活动：已解决的问题和已分配的主题。

该报告旨在帮助管理员了解组成员的贡献和工作量，从而更好地分配资源并表彰活跃贡献者。

--[params]
-- string :group_name_filter = staff
-- string :date_trunc = month
-- null user_list :user_list
-- boolean :userlist_filter = false
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH group_users_filtered AS (
    SELECT
        gu.user_id
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name_filter
),
user_groups AS (
    SELECT
        gu.user_id,
        STRING_AGG(g.name, ', ') AS group_names
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    GROUP BY gu.user_id
),
questions_solved AS (
    SELECT
        p.user_id,
        DATE_TRUNC(:date_trunc, dst.created_at) AS month,
        COUNT(*) AS total_solved
    FROM discourse_solved_solved_topics dst
    JOIN posts p ON p.id = dst.answer_post_id
    JOIN group_users_filtered guf ON guf.user_id = p.user_id
    WHERE dst.created_at BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, DATE_TRUNC(:date_trunc, dst.created_at)
),
assigns_per_user AS (
    SELECT
        a.assigned_to_id AS user_id,
        DATE_TRUNC(:date_trunc, a.created_at) AS month,
        COUNT(a.topic_id) AS total_assigned
    FROM assignments a
    JOIN topics t ON t.id = a.topic_id
    JOIN group_users_filtered guf ON guf.user_id = a.assigned_to_id
    WHERE a.assigned_to_type = 'User'
      AND t.deleted_at IS NULL
      AND a.created_at BETWEEN :start_date AND :end_date
    GROUP BY a.assigned_to_id, DATE_TRUNC(:date_trunc, a.created_at)
)
SELECT
    COALESCE(qs.month, apu.month)::DATE AS date,
    :date_trunc as date_range,
    COALESCE(qs.user_id, apu.user_id, ug.user_id) AS user_id,
    COALESCE(qs.total_solved, 0) AS total_solved_questions,
    COALESCE(apu.total_assigned, 0) AS total_assigned_topics,
    COALESCE(ug.group_names, '') AS group_names
FROM questions_solved qs
FULL OUTER JOIN assigns_per_user apu ON qs.user_id = apu.user_id AND qs.month = apu.month
LEFT JOIN user_groups ug ON ug.user_id = COALESCE(qs.user_id, apu.user_id)
WHERE (qs.user_id IN(:user_list) OR :userlist_filter = FALSE)
ORDER BY date DESC

SQL 查询说明

该报告是通过一系列通用表表达式 (CTE) 生成的，这些 CTE 根据指定的参数过滤和聚合数据。以下是每个 CTE 及其在报告中的作用的细分：

CTE 说明

  1. group_users_filtered: 此 CTE 标识属于指定组 (:group_name_filter) 的用户。它根据用户是否属于感兴趣的组来过滤用户。
  2. user_groups: 此 CTE 将用户所属的所有组聚合为单个字符串。这有助于识别与用户关联的所有组，为他们的活动提供上下文。
  3. questions_solved: 此 CTE 计算在指定日期范围（从 :start_date 到 :end_date）内，每个用户已解决的问题总数。
  4. assigns_per_user: 与 questions_solved 类似，此 CTE 计算在指定日期范围内分配给每个用户的任务数量。它确保只计算分配给用户的（而不是组或其他实体的）分配，并排除已删除的主题。

最终查询结合了这些 CTE，生成一个报告，其中包含日期（或日期范围）、用户 ID、已解决问题的总数、已分配主题的总数以及用户所属的组名。如果提供了特定用户列表（:user_list），则允许按该列表进行过滤，并且可以根据 :date_trunc 参数（例如，月、年）调整日期粒度。

参数

  • group_name_filter (string)：用于过滤用户的组名，默认为“staff”。此参数用于选择属于特定组的成员。
  • date_trunc (string)：确定报告中时间聚合的粒度（例如，“month”、“year”、“week”、“day”）。这会影响数据在输出中按时间分组的方式。
  • user_list (null user_list)：一个可选的用户 ID 列表，用于进一步过滤结果。如果提供，查询将仅包含这些用户的数据。
  • userlist_filter (boolean)：一个标志，用于指示是否应应用 user_list 过滤器。如果设置为 false，则忽略 user_list 参数，并包含指定组中所有用户的数据。
  • start_date (date)：报告活动期间的开始日期。
  • end_date (date)：报告活动期间的结束日期。

结果

该报告提供以下列：

  • date：聚合数据的日期或日期范围的开始日期。
  • date_range：日期范围的粒度（例如，month、year）。
  • user_id：用户的 ID。
  • total_solved_questions：用户已解决问题的总数。
  • total_assigned_topics：分配给用户的任务总数。
  • group_names：一个包含用户所属所有组名的字符串。

:person_tipping_hand: 请注意，由于 Discourse 中的 assignments 表的工作方式，此报告只能跟踪当前分配的主题，并且将显示当前分配的主题在其分配（或重新分配）日期。

示例结果

date date_range user total_solved_questions total_assigned_topics group_names
2023-12-01 month user1 5 3 admins, customer-projects-team, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
2023-11-01 month user2 8 1 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, support, trust_level_2
2023-11-01 month user3 3 4 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
… … … … … …
3 个赞
2

这简直是太及时了！我正需要做类似的事情，**瞧！**它就出现了！ :tada:

谢谢！

可惜的是，我们无法获取过去（已关闭主题）的作业统计数据。看来不能事事如意。

1 个赞