まず、新年おめでとうございます!! 
ユーザー（管理者を除く）がトピックごとに平均返信数を月ごとに取得するために使用するSQLクエリを知りたいです。
または、メンバーが投稿した投稿の総数とスタッフが投稿した投稿の総数の比率を月ごとに取得する方法があれば教えてください。
よろしくお願いします！
明けましておめでとうございます (少し遅れましたが 
)\n\n[quote="Eloïse Barrège, post:1, topic:290618, username:Eloïse_Barrège"]\nユーザー（管理者を除く）が月ごとにトピックあたり平均返信数を取得するためのSQLクエリを知りたいです。\n[/quote]\n\n平均についてですが、スタッフと非スタッフの両方が作成したトピックで、返信数は非スタッフの投稿のみを対象としていますか？また、管理者のみを除外したいのか、それとも管理者とモデレーターの両方を除外したいのか、どちらでしょうか？
こんにちは、Jammy！
その通りです。クエリは（スタッフと非スタッフの両方が作成した）すべてのトピックを検索できますが、返信数は非スタッフの投稿のみになります。
現時点では管理者のみを除外できます（コミュニティを立ち上げたばかりなので、管理者とモデレーターは同じです）。
しかし、スタッフのトピックと非スタッフのトピック（管理者を除く）の比率を簡単に把握できると、さらに良いでしょう。
「いいね！」 1
これならお探しの数値が得られると思います。
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Month",
ms.total_topics AS "All Topics",
ms.total_posts AS "All Posts",
ms.non_staff_posts AS "Non-staff posts",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Non-staff posts (% of total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Average non-staff posts per topic",
ms.non_staff_users AS "Non-staff users who posted",
ms.staff_posts AS "Staff posts",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Staff posts (% of total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Average staff posts per topic",
ms.staff_users AS "Staff users who posted"
FROM month_stats ms
ORDER BY "Month"
これにより、次のような結果が得られます。
おまけとして、少し解説を付け加えます。
このクエリは、指定された期間における「スタッフ」と「非スタッフ」ユーザーの貢献を区別することに焦点を当てた、月次統計サマリーを提供するように設計されています。計算される指標には、作成されたトピックの総数、投稿されたすべての投稿数、投稿したユニークな非スタッフユーザー数、非スタッフによって投稿された投稿の数と割合、トピックあたりの平均非スタッフ投稿数、およびスタッフメンバーに関する同様の数値が含まれます。この情報は、ユーザーエンゲージメント、コンテンツ生成、およびフォーラムのディスカッションにおけるスタッフと非スタッフメンバーの参加率に関する洞察を提供することを目的としています。クエリは、「通常の」（PMではない）トピックのみを考慮し、指定された日付範囲内で削除された投稿やトピック、ささやき/短い投稿/モデレーターアクション、およびシステムユーザーによる投稿を除外することで精度を保証します。
この場合、「スタッフ」の基準は、管理者とモデレーターの両方を含む自動化された @staff グループに属していることですが、管理者にのみターゲットを絞ったり、技術的には「サイトスタッフ」ではない従業員のカスタムグループをターゲットにしたりすることも調整可能です。モデレーターを確実に除外したい場合は、先頭の group_id を ‘1’ に切り替えることができます。 
お探しのものに近いでしょうか？
「いいね！」 3
なぜかスタッフのIDは3だとわかっているのですが、どうすればそのIDを見つけられるのでしょうか？ 最初はURLに他のIDと同じように埋め込まれていると確信していましたが、そうではありませんでした。使用されているのは名前だけです。
SQLはほとんど知らないので、できないと言っても差し支えありませんが、これはすべてのグループIDを表示します。
select
id,
name
from
groups
しかし、もっと一般的な方法で見つける方法があるはずですよね？
個人的には、user_id と同じように group_id パラメータの検索機能が欲しいです 
- Param dropdown for group_id in data explorer query
しかし、その夢が叶うまでは、グループページのJSONを利用して調べています。例: https://meta.discourse.org/g.json
クエリ内でグループ検索を行うことができるため、グループ名で機能し、よりユーザーフレンドリーな方法になるかもしれません。例えば以下のようになります。
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
（パラメータを使いたくない場合のハードコードバージョン:）
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
「いいね！」 2
ああ、それは言及すべきだった私の癖ですね。ページを更新すると、パラメータ入力ボックスが表示されるはずです。
「いいね！」 1
そのスニペットは即興で作ったに違いない。あまりうまくいっていないからね 
希望するグループの全ユーザーが表示され、全員が is_staff であると主張しているよ 
でもありがとう！基本的なレベルの管理者にとって、JSONとSQLの使い方について貴重な情報（本当に、でもAIレポートがどう見ているかを見るのは好きだよ…）を得ることができた。
この例では、is_staff ビットは、この特定のクエリの機能の一部です。データベース自体から取得されるのではなく、SELECT user_id, true as is_staff のように具体的に追加されています。これにより、指定したグループのユーザーが「スタッフ」として設定され、2つの結果セット（スタッフ投稿と非スタッフ投稿）に分割できるようになります。
したがって、データベースが設定する技術的なサイトスタッフではない「従業員」グループがあったとしても、それらを追加でき、それらは「非スタッフ」バケットではなく「スタッフ」バケットに入ります。
「いいね！」 1
OMG、まさに求めていたものでした、本当にありがとうございます！
念のため確認ですが、「投稿」はトピック＋返信をまとめたものですか、それとも返信のみをカウントするものですか？
重ねてお礼申し上げます！
「いいね！」 1
この場合、「投稿」にはトピックの最初の投稿は含まれず、返信のみが含まれます。
「いいね！」 1
JammyDodgerさん、こんにちは。
トピック（新しいスレッド作成）のみで同様のことは可能でしょうか？
どうぞよろしくお願いいたします！
「いいね！」 1
スタッフが作成したトピックと、このクエリに追加された非スタッフが作成したトピックの比率ということですか？
はい、同じク
「いいね！」 1
これらの列を追加すれば完了すると思います。
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Month",
ms.total_topics AS "All Topics",
ms.non_staff_topics AS "Non-staff topics",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Non-staff topics (% of total)",
ms.staff_topics AS "Staff topics",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Staff topics (% of total)",
ms.total_posts AS "All Posts",
ms.non_staff_posts AS "Non-staff posts",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Non-staff posts (% of total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Average non-staff posts per topic",
ms.non_staff_users AS "Non-staff users who posted",
ms.staff_posts AS "Staff posts",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Staff posts (% of total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Average staff posts per topic",
ms.staff_users AS "Staff users who posted"
FROM month_stats ms
ORDER BY "Month"
ありがとうございます、完璧です！
「いいね！」 1
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.