此报告需要启用 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, dsta.created_at) AS month,
COUNT(*) AS total_solved
FROM discourse_solved_solved_topics dsst
JOIN discourse_solved_topic_answer dsta on dsta.solved_id = dsst.id
JOIN posts p ON p.id = dsta.answer_post_id
JOIN group_users_filtered guf ON guf.user_id = p.user_id
WHERE dsta.created_at BETWEEN :start_date AND :end_date
GROUP BY p.user_id, DATE_TRUNC(:date_trunc, dsta.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 详解
- group_users_filtered：此 CTE 识别属于指定组（
:group_name_filter）的用户。它根据用户是否属于目标组进行过滤。 - user_groups：此 CTE 将用户所属的所有组聚合为单个字符串。这有助于识别与用户关联的所有组，为其活动提供背景信息。
- questions_solved：此 CTE 计算每个用户在指定日期范围（
:start_date至:end_date）内解决的问答总数。 - assigns_per_user：与
questions_solved类似，此 CTE 统计每个用户在指定日期范围内被分配的任务数量。它确保仅统计分配给用户的任务（而非组或其他实体），并排除已删除的话题。
最终查询将这些 CTE 组合在一起，生成一份包含日期（或日期范围）、用户 ID、已解决问答总数、已分配话题总数以及用户所属组名的报告。如果提供了特定用户列表（:user_list），它可以按该列表进行过滤，并可根据 :date_trunc 参数（例如：月、年）调整日期粒度。
参数
group_name_filter（字符串）：用于过滤用户的组名，默认为“staff”。此参数用于选择属于特定组的用户。date_trunc（字符串）：决定报告时间聚合的粒度（例如：“month”、“year”、“week”、“day”）。这影响输出中数据按时间分组的方式。user_list（null user_list）：可选的用户 ID 列表，用于进一步过滤结果。如果提供，查询将仅包含这些用户的数据。userlist_filter（布尔值）：一个标志，指示是否应用user_list过滤。如果设置为false，则忽略user_list参数，并包含指定组中所有用户的数据。start_date（日期）：要报告活动的开始日期。end_date（日期）：要报告活动的结束日期。
结果
该报告提供以下列：
date：数据聚合的日期或日期范围的开始日期。date_range：日期范围的粒度（例如：月、年）。user_id：用户 ID。total_solved_questions：用户已解决的问答总数。total_assigned_topics：分配给用户的任务总数。group_names：包含用户所属所有组名的字符串。
请注意，由于 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 |
| … | … | … | … | … | … |
