ユーザー別 月別 解決済み質問と担当トピック

データとレポート
,
1

:discourse: このレポートには 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, 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の説明

  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 (文字列): ユーザーをフィルタリングするグループの名前。デフォルトは「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: ユーザーが属するすべてのグループの名前を含む文字列。

: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