ダッシュボードレポート - 投稿編集

データとレポート
, ,
1

これは投稿編集のためのダッシュボードレポートのSQLバージョンです。

このダッシュボードレポートは、特定の期間に編集された投稿を表示し、編集者、投稿の作成者、および投稿が編集された理由に関する情報が含まれています。このレポートは、管理者がフォーラム内で発生している編集アクティビティを監視および理解するのに役立つように設計されており、特に元の作成者以外のユーザーが投稿を編集した場合に焦点を当てています。

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

SELECT
    pr.created_at::date as day,
    pr.post_id as post_id,
    pr.user_id AS editor_user_id,
    p.user_id AS author_user_id,
    p.edit_reason
FROM post_revisions pr
JOIN posts p ON p.id = pr.post_id
WHERE pr.created_at BETWEEN :start_date AND :end_date
AND pr.user_id > 0
AND pr.user_id <> p.user_id
ORDER BY pr.created_at ASC

SQLクエリの説明

  • パラメータ宣言:
    • クエリは、レポートの日付範囲を定義する :start_date と :end_date の2つのパラメータを受け入れます。両方の日付パラメータは YYYY-MM-DD の日付形式を受け入れます。
  • データ選択: SELECT ステートメントは、最終的なレポートに表示される列を指定します。これらには以下が含まれます。
    • pr.created_at::date as day: 投稿リビジョンが作成された日付。時間の部分を削除するために date 型にキャストされています。
    • pr.post_id: 編集された投稿のID。
    • pr.user_id AS editor_user_id: 編集を行ったユーザーのID。元の投稿の作成者と区別するために editor_user_id というエイリアスが付けられています。
    • p.user_id AS author_user_id: 元の投稿を作成したユーザーのID。author_user_id というエイリアスが付けられています。
    • p.edit_reason: 提供された場合、編集の理由。
  • テーブルの結合: FROM 句には、post_revisions テーブル (pr) と posts テーブル (p) を結合する JOIN ステートメントが含まれています。結合は、post_revisions.post_id が posts.id と一致するという条件で行われ、リビジョンを元の投稿の詳細にリンクします。
  • フィルター: WHERE 句はデータにいくつかのフィルターを適用します。
    • pr.created_at BETWEEN :start_date AND :end_date: パラメータで指定された日付範囲内に作成された投稿リビジョンのみを含みます。
    • pr.user_id > 0: 編集者が実際のユーザーであり、システム操作ではないことを保証します（user_idが0は通常、システム操作を示します）。
    • pr.user_id <> p.user_id: 投稿の元の作成者によって行われたリビジョンを除外し、異なるユーザーによって行われた編集のみに焦点を当てます。
  • 結果の並べ替え: ORDER BY pr.created_at ASC 句は、各投稿リビジョンが作成されたタイムスタンプに基づいて、結果を古いものから新しいものへと並べ替えます。

結果例

day post editor_user author_user edit_reason
2023-12-20 post_abc user1 user2 Example Edit Reason
2023-12-21 post_dfg user3 user4 Example Edit Reason
2023-12-24 post_xyz user5 user6 NULL
「いいね！」 1