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

データとレポート
, ,
1

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

このダッシュボードレポートは、指定された日付範囲内に作成されたブックマークの日次カウントを提供します。

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

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

SQLクエリの説明

パラメータ

レポートを生成するには、:start_dateと:end_dateの2つのパラメータを指定する必要があります。これらのパラメータは、ブックマークアクティビティを分析したい日付範囲を定義します。両方のdateパラメータは、YYYY-MM-DDの日付形式を受け入れます。

bookmark_counts (CTE)

bookmark_countsという名前の共通テーブル式（CTE）は、最終的な選択の前にデータを整理するために使用されます。CTEは次のアクションを実行します。

  • 日付フィルタリング: 作成日によってブックマークをフィルタリングし、指定された開始日と終了日（:start_dateおよび:end_date）内に作成されたもののみを含めます。
  • 日付グループ化: ブックマークの作成日ごとに結果をグループ化し、カウントが日次で集計されるようにします。
  • ブックマークのカウント: CASEステートメントを使用した条件付き集計を使用して、トピックと投稿のブックマークの数をそれぞれカウントします。DISTINCTを使用して、各ブックマークが1回だけカウントされるようにします。
    • topic_bookmarksは、bookmarkable_typeが「Topic」であるブックマークをカウントします。
    • post_bookmarksは、bookmarkable_typeが「Post」であるブックマークをカウントします。

メインクエリ

CTEがデータを整理した後、最終的なSELECTステートメントは次の列を取得します。

  1. bookmark_date: カウントが集計される日付。
  2. total_bookmarks: 各日のトピックと投稿のブックマークの合計。
  3. topic_bookmarks: 各日のトピックのブックマークのカウント。例：
    image
    image1249×198 17.3 KB
  4. post_bookmarks: 各日の投稿のブックマークのカウント。例：
    image
    image1121×125 5.07 KB

その後、結果はbookmark_dateで並べ替えられ、データを時系列順に表示します。

結果例

bookmark_date total_bookmarks topic_bookmarks post_bookmarks
2023-12-01 16 1 15
2023-12-02 4 1 3
2023-12-03 8 0 8
2023-12-04 19 9 10
2023-12-05 18 3 15
… … … …
「いいね！」 3