ダッシュボードレポート - システム

データとレポート
, ,
1

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

このダッシュボードレポートは、システムによって自動的に送信された個人メッセージの数を日次でカウントします。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  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テーブルからデータを抽出し、指定された期間内のsystem_messageサブタイプのユーザーへの個人メッセージとして適格なエントリを対象とします。以下に詳細を説明します。

  • 日付パラメータ:
    • クエリは、レポートの日付範囲を定義する:start_dateと:end_dateの2つのパラメータを受け入れます。両方のdateパラメータはYYYY-MM-DDの日付形式を受け入れます。
  • SELECT: クエリは2つのフィールドを選択します。
    • DATE(created_at) AS day: created_atタイムスタンプの日付部分を抽出し、レコードを作成日ごとに効果的にグループ化します。
    • COUNT(*) AS notifications_count: 日ごとのシステム生成PMの総数をカウントします。
  • FROM: すべてのトピック（個人メッセージを含む）のレコードを格納するtopicsテーブルをデータソースとして指定します。
  • WHERE: データセットを絞り込むための複数のフィルターが含まれています。
    • archetype = 'private_message': 個人メッセージであるエントリのみを含めます。
    • subtype = 'system_message': システム生成メッセージのみに選択をさらに絞り込みます。
    • created_at BETWEEN :start_date AND :end_date: パラメータで指定された範囲内に作成されたPMをフィルターします。
    • deleted_at IS NULL: 削除されたメッセージを除外します。
    • user_id > 0: メッセージがシステムまたは匿名アカウントではなく、実際のユーザーアカウントに関連付けられていることを保証します。
  • GROUP BY: 作成日ごとに結果をグループ化します。
  • ORDER BY: 日ごとのカウントの時系列順序を保証するために、最終結果セットを日ごとに昇順で並べ替えます。

結果例

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
「いいね！」 3
2

これを、例えばウェルカムPMのような特定のPMに紐づけることはできますか？

毎日何件送信されているか知りたいです。

ご意見をお聞かせください。

「いいね！」 1
3

はい、これを追加する最善の方法は、クエリの WHERE ステートメントにセクションを追加し、トピック title でフィルタリングすることです。

例：

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

これにより、すべての Greetings! メッセージが見つかります。

システムメッセージの中には実際のユーザーが含まれていないものがあるため、このタイプのクエリでは AND user_id > 0 の行を削除する必要がある場合があります。

ユーザーが削除する可能性のあるウェルカムメッセージもカウントするには、AND deleted_at IS NULL を削除することも検討してください。

正規表現（regex）を使用して、類似したタイトルのトピックに一致させることができます。

PostgreSQL で正規表現 (regex) を使用してトピックをタイトルで照合するには、文字列に対して正規表現を照合する ~ 演算子を使用できます。クエリ構造は次のようになります。

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

'YourRegexPatternHere' を、title フィールドに対して照合したい実際の正規表現パターンに置き換えてください。

たとえば、「Welcome」という単語（大文字と小文字を区別しない）を含むタイトルのトピックを検索する場合は、次のように使用できます。

SELECT *
FROM topics
WHERE title ~* 'Welcome'

~* 演算子は、大文字と小文字を区別しない照合に使用されます。

「いいね！」 3
4

素晴らしいですね。本当にありがとうございます！試してみます！

「いいね！」 1