ダッシュボードレポート - トピックビュー統計

データとレポート
, ,
1

これは、トピック表示統計のダッシュボードレポートのSQLバージョンです。

このレポートは、指定された期間内のトピック表示統計（匿名およびログインユーザーの表示を含む）を収集および集計します。また、カテゴリによるフィルタリングを可能にし、オプションでサブカテゴリを含めることもできます。

:discourse: このクエリと topic_view_stats データベーステーブルは、2024年5月にDiscourseに追加されたもので、それ以前のデータは表示されません。

-- [params]
-- date :start_date
-- date :end_date
-- int :category_id = 0
-- boolean :include_subcategories = false

WITH category_filter AS (
  SELECT
    id
  FROM categories
  WHERE
    (:category_id = 0 OR id = :category_id)
    OR (:include_subcategories AND parent_category_id = :category_id)
)

SELECT
  topic_view_stats.topic_id,
  topics.title AS topic_title,
  SUM(topic_view_stats.anonymous_views) AS total_anonymous_views,
  SUM(topic_view_stats.logged_in_views) AS total_logged_in_views,
  SUM(topic_view_stats.anonymous_views + topic_view_stats.logged_in_views) AS total_views
FROM topic_view_stats
INNER JOIN topics ON topics.id = topic_view_stats.topic_id
WHERE
  topic_view_stats.viewed_at BETWEEN :start_date AND :end_date
  AND (
    :category_id = 0
    OR topics.category_id IN (SELECT id FROM category_filter)
  )
GROUP BY topic_view_stats.topic_id, topics.title
ORDER BY total_views DESC
LIMIT 100

SQLクエリの説明

パラメータ

  • :start_date (日付): 分析対象期間の開始日（含む）。
  • :end_date (日付): 分析対象期間の終了日（含む）。
  • :category_id (整数、オプション): カテゴリでトピックをフィルタリングします。使用方法:
    • 0: すべてのカテゴリを含めます。
    • 特定のカテゴリIDを指定してフィルタリングします。
  • :include_subcategories (ブール値、オプション): 指定されたカテゴリのサブカテゴリを含めるかどうかを選択します。
    • false: 選択したカテゴリのみ。
    • true: サブカテゴリも含まれます。

カテゴリフィルタリング
category_filter 共通テーブル式（CTE）は、:category_id パラメータに基づいて選択されたカテゴリIDを取得します。パラメータが 0 の場合、すべてのカテゴリが含まれます。サブカテゴリを含める場合、parent_category_id が指定された :category_id と一致するカテゴリを検索します。

トピック統計の取得
トピック表示統計は topic_view_stats テーブルから集計され、topics テーブルと結合してトピックタイトルを取得します。

日付とカテゴリのフィルタリングの適用
クエリは topic_view_stats データをフィルタリングします:

  • 指定された日付範囲（:start_date および :end_date）でフィルタリングします。
  • category_filter のIDと一致するカテゴリでフィルタリングします。

合計表示回数の計算
クエリは以下を合計します:

  • anonymous_views: 匿名ユーザーからの表示回数。
  • logged_in_views: ログインユーザーからの表示回数。
  • 両方の合計としての合計表示回数。

ランキングと制限:
クエリは total_views でトピックをランク付けし、結果を上位100件に制限します。

クエリ結果
クエリは以下の列を返します:

列名 説明
topic_id トピックの一意の識別子。
topic_title トピックのタイトル。
total_anonymous_views 匿名ユーザーからの合計表示回数。
total_logged_in_views ログインユーザーからの合計表示回数。
total_views 合計表示回数（匿名 + ログイン）。

結果例

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 「例トピック1」 500 300 800
67890 「例トピック2」 450 350 800
「いいね！」 3