ユーザーが投稿している内容の理解

データとレポート
1

ユーザーがどのようなトピックについて投稿しているかを理解するためのSQLベースのレポートを作成したいと思います。

カテゴリ、トピック、タグは、これらを提供してくれると判断したフィールドであり、投稿数（postcount）は指標となります（基本的に人気を理解するために使用したいと考えています）。

これをサポートする他のフィールドはありますか？ 他のユーザーも同様のリクエストをしたことがあると仮定すると、既存のクエリを使用できますか？

「いいね！」 2
2

SQLクエリではありませんが、おそらくお気づきかと思います。

/categories は、各カテゴリの月ごとの新しいトピックの数を示しており、非常に役立ちます。

/tags は、タグごとのトピック数を表示します。

ご存知なかった場合に備えて（しかし、ご存知だったかもしれませんが！）

「いいね！」 1
3

ここでおっしゃっていることがよくわかりません。

これらはコミュニティの管理部分に関するレポートですか？

これらは見ましたが、基本的にすべてを1つにまとめたいと思っています。たとえば、カテゴリにいくつのトピックやタグがあるかなどを確認できるようにしたいのです。

4

URLパスです。

5

カテゴリー、タグ、トピックをそれぞれ投稿数とともに同じレポート/クエリに含めることについて、どのようなことを考えているのか、私はまだよく想像できません。

おそらく、カテゴリー用とタグ用の2つに分けて、それぞれについて特定の期間内の新しいトピックと新しい投稿の数をカウントできるでしょう。投稿しているユーザー数を含めることもできるかもしれません。

そうすれば、結果のテーブルは以下のようになります。

カテゴリー 新規トピック 新規投稿 ユーザー
カテゴリー a 9 15 4
カテゴリー b 56 167 32
「いいね！」 2
6

Categories - Discourse Meta を入力しました。おそらくあなたが意図していたのはこれだと思われます。

基本的に、これらのカテゴリ/タグ/トピックを1つの出力で表示したいと考えています。その理由は以下の通りです。

  • 重複の特定 - 私の理解では、トピックとは会話/スレッドの開始に使用される単語です。ユーザーが類似のトピックを2つの異なるカテゴリに追加した可能性があります。これを理解することで、ユーザーの行動を把握したり、カテゴリをより明確にするように調整したりできる可能性があります。
  • カテゴリごとのトピックの種類を理解する - カテゴリは「車」のようなものかもしれませんが、自然に多くの異なる種類のトピックを収容できます。ユーザーが実際に何について話しているかを確認したいと考えています。
  • タグ - 私が確認したインスタンスでは、タグが複数の異なるカテゴリを横断する何らかの方法として使用されていることがわかります。したがって、このレンズを通して投稿を理解することも良いでしょう。

要するに、カテゴリ、タグ、トピックをSQLと、おそらく投稿を使用して結合する必要があるようです。この処理がすでに完了していてコードを表示できるか、または何らかのコード/クエリライブラリがあるかどうか疑問に思っています。

7
カテゴリ トピック タグ 投稿数 ユーザー数
車 車が好き ホイール 44 1
車 車が嫌い ホイール 32 3
車 車が嫌い ドア 39 4
車 車の仕組みは？ ドア 32 1
物事の仕組み 車の仕組みは？ 方法 32 3

これはビジョンの例です。これにより、「車カテゴリに投稿するユーザーの何パーセントがホイールについて話しているか」といったことを理解するためのさらなる分析が可能になります。

データエクスプローラーで、投稿、トピック、タグ、カテゴリ、おそらく別のテーブルを組み合わせることになると思いますが、理解しています。ここでは、これがすでに実行されたことがあるか、または以前に作成されたSQLクエリ（私たちの В accountsではなく、一般的なもの）が存在するかを理解するために投稿しました。

これが意味をなすことを願っています。調査を開始したのは昨日からです。

8

ああ、わかりました。概要ではなく、詳細なトピックリストが必要なのですね。それは可能だと思います。 :+1:

トピックには複数のタグを付けられるという点が少し変わっているかもしれませんが、何かを作成して状況を見てみましょう。 :slight_smile:

しかし、他の既存のクエリを閲覧するには、ダッシュボードから標準で利用できるもの（SQLバージョンはここで dashboard-sql の下にまとめて表示されています）、データエクスプローラーにバンドルされているストックレポート、そしてメタにある sql-query タグの下にまとめられたカスタムクエリがたくさんあります。

@SStrong - おそらくこのようなものになるでしょう：

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31


WITH tag_names AS (

    SELECT
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),

user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id

)

SELECT
    t.category_id,
    t.id AS topic_id,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
「いいね！」 2
9

申し訳ありません、すでに返信したと思っていました。

コードを修正して、カテゴリ名とトピック名を保持することは可能でしょうか？「投稿名」というものが存在するのか、それともそれが実際のトピック名なのか分かりません。

自分でコードを修正しようとしましたが、テーブルがどのように連携しているのか現時点では十分に理解できていないため、IDではなく単語で結果が表示されるはずが、0件の結果になってしまいました。

10

問題ありません。 :slight_smile: データエクスプローラーで表示すると、category_id や topic_id (その他多数) が自動的に使用可能なサイト内リンクに変換されますが、他の場所で分析するためにエクスポートする場合は、代わりにカテゴリ名やトピックタイトルを使用できます。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31


WITH tag_names AS (

    SELECT
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),

user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id

)

SELECT
    c.name AS category_name,
    t.title,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
  JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
「いいね！」 1