このレポートを機能させるには、Discourse Solved プラグインの有効化が必要です。
このレポートを機能させるには、このデータエクスプローラーのレポートは、特定の期間内における特定グループのメンバーが実施した活動の概要を提供します。具体的には、解決された質問と割り当てられたトピックという 2 つの主要な活動に焦点を当てています。
このレポートは、管理者がグループメンバーの貢献度と作業負荷を理解し、リソースの適切な配分や積極的な貢献者の認知を促進するために設計されています。
--[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 |
| … | … | … | … | … | … |
