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

データとレポート
, ,
1

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

このレポートは、指定された期間内に毎日作成された新しいトピックの数をカウントします。レポートには個人メッセージは含まれず、フォーラムで表示される通常のトピックのみが含まれます。

--[params]
-- date :start_date
-- date :end_date

SELECT 
     p.created_at::date as day,
    COUNT(p.id) AS topics_created
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.post_number = 1
    AND p.user_id > 0 
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
GROUP BY p.created_at::date
ORDER BY p.created_at::date ASC

SQLクエリの説明

SQLクエリは次のアクションを実行します。

  • パラメータ定義: レポートの期間を定義する :start_date と :end_date の2つのパラメータを受け入れます。両方の日付パラメータは YYYY-MM-DD の形式を受け入れます。
  • データ選択: クエリは、投稿の作成日 (p.created_at) と投稿IDのカウント (COUNT(p.id)) を topics_created として選択します。
  • 結合: posts テーブルと topics テーブルをそれぞれの id フィールドで結合し、削除されていないトピック (t.deleted_at ISNULL) のみが考慮されるようにします。
  • フィルタ: クエリは、次の条件を満たす投稿のみを含めるようにフィルタリングします。
    • 指定された期間内に作成された (p.created_at::date BETWEEN :start_date AND :end_date)。
    • トピックの最初の投稿である (p.post_number = 1)。これは新しいトピックの開始を示します。
    • 登録ユーザーによって作成された (p.user_id > 0)。システム投稿は除外されます。
    • 削除されていない (p.deleted_at ISNULL)。
    • 「regular」アーキタイプのトピックに属する (t.archetype = 'regular')。個人メッセージは除外されます。
  • グループ化: 結果は投稿の作成日 (p.created_at::date) でグループ化されます。
  • 並べ替え: 最終的な出力は、作成日の昇順 (ORDER BY p.created_at::date ASC) で並べ替えられ、新しいトピック作成の時系列ビューを提供します。

結果例

day topics_created
2023-11-11 18
2023-11-12 10
2023-11-13 21
2023-11-14 19
2023-11-15 22
… …
「いいね！」 4