ダッシュボードレポート - ユーザーに通知

データとレポート
, ,
1

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

このダッシュボードレポートは、ユーザーが投稿のフラグによってプライベートに通知された回数を日ごとにカウントします。

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  DATE(created_at) AS day,
  COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'notify_user'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day

SQL クエリの説明

このクエリは、topics テーブルからデータを抽出し、指定された期間内に notify_user のサブタイプを持つユーザーへのプライベートメッセージとして適格なものを対象としています。以下に詳細を説明します。

  • 日付パラメータ:
    • このクエリは、レポートの日付範囲を定義する :start_date と :end_date の 2 つのパラメータを受け取ります。両方の日付パラメータは YYYY-MM-DD の日付形式を受け入れます。
  • 選択とカウント:
    • DATE(created_at) は created_at タイムスタンプを日付形式に変換し、day というラベルを付けます。これにより、作成日ごとにメッセージをグループ化してカウントできます。
    • COUNT(1) は、各日のエントリ数をカウントし、そのカウントを notifications_count というラベルで付けます。
  • フィルタリング基準:
    • クエリは、archetype が 'private_message' であるエントリのみをフィルタリングし、他のタイプのトピックではなく、メッセージのみを対象としていることを確認します。
    • subtype は 'notify_user' に制限されており、フラグが付けられた投稿についてユーザーが通知されたケースを分離します。
    • メッセージの created_at タイムスタンプが指定された :start_date と :end_date の間にあることを確認します。
    • 削除されたメッセージ (deleted_at IS NULL) はカウントから除外され、レポートがアクティブな通信のみを反映していることを確認します。
    • 最後に、user_id > 0 を確認することで、メッセージが有効なユーザーに関連付けられていることを確認します。
  • グループ化と並べ替え:
    • クエリは GROUP BY DATE(created_at) を使用して、指定された日付範囲内の各日で結果を集計します。
    • 次に、ORDER BY day で結果を時系列に並べ替え、レポートの読みやすさと解釈しやすさを向上させます。

結果例

day notifications_count
2023-12-17 1
2023-12-18 1
2023-12-21 1
2023-12-23 1
2023-12-26 1
「いいね！」 1