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

データとレポート
, ,
1

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

このレポートは、指定された期間内に作成された投稿の毎日のカウントを提供します。これは、通常のトピックでのアクティビティを追跡するように設計されており、プライベートメッセージやその他の特別なアーキタイプからの投稿は除外されます。

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

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

SQLクエリの説明

  • パラメータ:
    • クエリは、レポートの期間を定義する :start_date と :end_date の2つのパラメータを受け入れます。両方の日付パラメータは YYYY-MM-DD の日付形式を受け入れます。

SQLクエリは次の操作を実行します。

  • データ選択:
    • 各投稿が作成された日時（created_at::date）を選択し、時間コンポーネントを無視するために日付形式にキャストします。
    • また、各日に作成された投稿数（COUNT(p.id)）をカウントします。
  • 結合:
    • posts テーブルと topics テーブルを INNER JOIN を使用して結合します。この結合により、既存のトピックに関連付けられた投稿のみが考慮されます。
    • 削除されたトピック（t.deleted_at ISNULL）を除外します。
  • フィルタ:
    • 指定された期間内の投稿のみを含めるように投稿をフィルタリングします（p.created_at::date BETWEEN :start_date AND :end_date）。
    • 削除された投稿を除外します（p.deleted_at ISNULL）。
    • 結果を通常のトピックからの投稿に制限します（t.archetype = 'regular'）。
    • p.post_type = 1 の投稿のみを考慮し、モデレーターアクション、ウィスパー、small_action の投稿は除外します。
  • グループ化と並べ替え:
    • 結果は投稿作成日（GROUP BY p.created_at::date）でグループ化されます。
    • 最終的な出力は、日付の昇順（ORDER BY 1）で並べ替えられます。ここで 1 は、日付である SELECT ステートメントの最初の列を参照します。

結果例

Day Count
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
「いいね！」 2
2

これは素晴らしいです。
日付として実行時にカテゴリ/サブカテゴリを定義する方法はありますか？
そして、ボーナスとして、ユーザーごとに結果をリストするか、ユーザーを定義することもできますか？

やろうとしているのは、サポートチケットエリアでサポートスタッフが（範囲）中に投稿した投稿の数を確認することです。

3

はい、次のクエリを使用できます。

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "ユーザー",
    p.created_at::date AS "日付",
    COUNT(p.id) AS "カウント"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

パラメータ：

  • :start_date & :end_date: レポート期間を定義します（必須）。
  • :category_id: 特定のカテゴリのオプションフィルター。
  • :user_id: 特定のユーザーのオプションフィルター。
  • :include_subcategories: 選択したカテゴリのサブカテゴリを含めるオプション。

このクエリは以下を示します：

  • ユーザー: 投稿作成者のユーザー名。
  • 日付: 投稿が作成されたカレンダー上の日付。
  • カウント: その日付にそのユーザーが作成した投稿数。

サンプルデータ：

ユーザー 日付 カウント
user 1 2023-01-01 3
user 2 2023-01-01 2
user 3 2023-01-01 1
user 1 2023-01-02 2
user 2 2023-01-02 3
user 1 2023-01-03 1
… … …
「いいね！」 2
4

ありがとうございます。大変助かります！

「いいね！」 2