Discourseの一般的なデータヒント

データとレポート
1

これは、Discourse のデータに関するいくつかの特殊な点を理解するのに役立つ良い例です。これにより、クエリの構築時に、より正確な結果を得ることができます。

ほとんどの投稿やトピックは削除時に「ソフト削除」されるため、データベースには残っており、カスタムレポートの統計に含めることができます。そのため、たとえばユーザーまたはカテゴリの統計情報を取得したい場合に、結果から除外すると役立ちます。同様に、統計情報から個人メッセージやシステムユーザーを除外したい場合もあります。

これらを除外する方法をいくつかご紹介します。

このSQLクエリは、削除された投稿やトピックの投稿を除外し、システムユーザーを除外した、最も多くの公開投稿を行った上位 10 名のユーザーを取得するために使用されます。

-- posts テーブルから user_id を選択し、各ユーザーが行った投稿数をカウントします
SELECT 
    p.user_id,
    COUNT(p.id) AS "投稿数"
FROM posts p
  -- topics テーブルと topic_id で左結合します
  LEFT JOIN topics t ON t.id = p.topic_id
WHERE 
  -- プライベートメッセージのトピックを除外します
  t.archetype <> 'private_message'
  -- 削除されたトピックを除外します
  AND t.deleted_at ISNULL
  -- 削除された投稿を除外します
  AND p.deleted_at ISNULL
  -- ウィスパー、小さなアクション投稿、モデレーター投稿を除外します
  AND p.post_type = 1
  -- システムユーザーと discobot (user_id 0 および -1) によって行われた投稿を除外します
  AND p.user_id > 0
-- 結果を user_id でグループ化します
GROUP BY 1
-- 結果を投稿数で降順に並べ替えます
ORDER BY 2 DESC
-- 結果を投稿数が最も多い上位 10 名のユーザーに制限します
LIMIT 10

そして、インラインコメントなしのバージョンは次のとおりです。

SELECT 
    p.user_id,
    COUNT(p.id) AS "投稿数"
FROM posts p
  LEFT JOIN topics t ON t.id = p.topic_id
WHERE t.archetype <> 'private_message'
  AND t.deleted_at ISNULL
  AND p.deleted_at ISNULL
  AND p.post_type = 1
  AND p.user_id > 0
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

これは例を示すための簡単なクエリですが、データエクスプローラーの利用を開始するのに役立つことを願っています。:+1: ご質問があれば、以下でお尋ねください。:slight_smile:

「いいね！」 10
2

メッセージのことですか？

削除された投稿やトピックを除き[…]

私には奇妙に聞こえます。

3

そうは思いません。削除されたトピック内の投稿が含まれていないことを伝えるためのものです。メッセージを使用すると、PM（プライベートメッセージ）を指しているように聞こえます。しかし、より明確に説明する方法をもう一度確認します。:+1:

別の考えですが、

AND p.post_type = 1 をこれも含めるのは良いかもしれません。:thinking:

「いいね！」 1
4

PMも除外されるのかと思ったのですが、

メッセージ、削除された投稿、および削除されたトピックからの投稿を除外します。

削除された投稿、またはトピックからの投稿を除外します。

よりも、こちらの方が理にかなっています。

もう一度読み直すと、元の意図は理解できましたが、期待していたほど明確ではありませんでした。:person_shrugging:

「いいね！」 1