ダッシュボードレポート - フラグ

データとレポート
, ,
1

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

このレポートは、指定された期間内のオプションの特定のカテゴリ（およびオプションでそのサブカテゴリ）内のフラグ付き投稿の数をカウントします。この情報は、コミュニティマネージャーやモデレーターがフォーラムの特定のセクションで発生したフラグの量​​を理解するのに役立ち、追加の注意やモデレーションが必要なトレンドや領域を特定するのに役立ちます。

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

SQLクエリの説明

SQLクエリは、指定された期間とカテゴリ内で、各日付に作成されたフラグ（reviewablesテーブルのReviewableFlaggedPostレコードで表される）の数をカウントするように設計されています。再帰的な共通テーブル式（CTE）を使用して、選択したカテゴリのサブカテゴリをオプションで含めます。

パラメータ

  • :start_date: フラグをカウントする期間の開始日。
  • :end_date: フラグをカウントする期間の終了日。
  • :category_id: フラグをカウントするカテゴリのID。0に設定されている場合、カテゴリのフィルタは適用されません。
  • :include_subcategories: 指定されたカテゴリのサブカテゴリをカウントに含めるかどうかを決定するブール値。

クエリの内訳

  1. 再帰CTE (subcategories):
  • このCTEは、:category_idパラメータによって提供されるIDを持つカテゴリを選択することから始まります。
  • 次に、categoriesテーブルとsubcategories CTEをparent_category_idフィールドで結合することにより、そのカテゴリのすべてのサブカテゴリを再帰的に選択します。
  • 結果は、フィルタリングに使用されるすべてのサブカテゴリ（初期カテゴリを含む）のリストです。
  1. メインクエリ:
  • クエリは、フラグ作成の日付（flag_date）と、その日付のフラグのカウント（flags_count）の2つの列を選択します。
  • reviewablesテーブルとcategoriesテーブルを結合して、各フラグをそのカテゴリに関連付けます。
  1. フィルタ:
  • クエリはreviewablesレコードをフィルタリングして、フラグ付き投稿を表すReviewableFlaggedPostタイプのもののみを含めます。
  • フラグのcreated_at日付が指定された日付範囲（:start_dateから:end_date）内にあることを確認します。
  • :category_idおよび:include_subcategoriesパラメータに基づく条件付きフィルタを適用します。
    • :category_idが0の場合、カテゴリフィルタは適用されません。
    • :include_subcategoriesがtrueの場合、クエリは初期カテゴリとそのすべてのサブカテゴリからのフラグを含みます。
    • :include_subcategoriesがfalseの場合、クエリは初期カテゴリからのフラグのみを含みます。
  1. グループ化と順序付け:
  • 結果はフラグ作成の日付ごとにグループ化され、日ごとのカウントが提供されます。
  • 結果は日付ごとに並べ替えられ、データを時系列順に表示します。

結果例

flag_date flags_count
2023-11-19 2
2023-11-22 1
2023-11-23 1
2023-11-24 2
2023-11-26 1
… …
「いいね！」 1