ダッシュボードレポート - 初回応答時間

データとレポート
, ,
1

これは、応答時間ダッシュボードレポートのSQLバージョンです。

このダッシュボードレポートは、指定された期間内のトピックへの平均応答時間に関する洞察を提供します。レポートは、トピック作成者以外の誰かによって最初の返信が投稿されるまでにかかった時間を計算します。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-12-31
-- null category_id :category_id
-- boolean :include_subcategories = false
-- null user_id :user_ids 

-- Time to first response by date
SELECT 
  t.created_at AS "date",
  CAST(AVG(t.hours)::numeric(10,2) AS float) AS "response_time_hours"
FROM (
  SELECT 
    t.id, 
    t.created_at::date AS created_at, 
    EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS "hours"
  FROM topics t
  JOIN posts p ON p.topic_id = t.id
  WHERE 
    t.created_at >= :start_date
    AND t.created_at < :end_date
    AND t.archetype <> 'private_message'
    AND t.deleted_at IS NULL
    AND p.deleted_at IS NULL
    AND p.post_number > 1
    AND p.user_id != t.user_id
    AND p.post_type = 1  -- Regular post type
    AND EXTRACT(EPOCH FROM p.created_at - t.created_at) > 0
    AND (:category_id IS NULL OR 
         CASE WHEN :include_subcategories THEN 
           t.category_id IN (
             WITH RECURSIVE subcategories AS (
               SELECT id FROM categories WHERE id = :category_id
               UNION
               SELECT c.id FROM categories c
               JOIN subcategories sc ON sc.id = c.parent_category_id
             )
             SELECT id FROM subcategories
           )
         ELSE 
           t.category_id = :category_id
         END
        )
    AND (:user_ids IS NULL OR p.user_id IN (SELECT unnest(string_to_array(:user_ids, ','))::int))
  GROUP BY t.id
) t
GROUP BY t.created_at
ORDER BY t.created_at

SQLクエリの説明

このレポートは、トピックが最初の意味のある応答を受け取る速さを次のように測定します。

  • 応答時間の計算: 各トピックについて、次の条件を満たす最初の投稿を見つけます。
    • 元のトピック作成者からのものではない
    • 投稿番号が1より大きい（最初の投稿ではない）
    • 通常の投稿である（post_type = 1）
    • トピックの後に作成された（正の時間差）
    • 削除されていない
  • 日付ごとのグループ化: これらの応答時間をトピックが作成された日付ごとに集計します。
  • 結果は次を示します:
    • 「date」: トピックが作成された日付
    • 「hours」: その日付に作成されたトピックの最初の応答までの平均時間（時間単位）
  • 除外:
    • プライベートメッセージは除外されます
    • 削除されたトピックと投稿は除外されます
    • 自己返信は除外されます

パラメータ

  • :start_date (date) - YYYY-MM-DD形式
    • レポート期間の開始日
    • この日付以降に作成されたトピックのみが含まれます
  • :end_date (date) - YYYY-MM-DD形式
    • レポート期間の終了日
    • この日付より前に作成されたトピックのみが含まれます
  • :category_id (category_id, nullable)
    • 指定された場合、結果を特定のカテゴリにフィルタリングします
    • nullの場合、すべてのカテゴリのトピックが含まれます
  • :include_subcategories (boolean) - デフォルト: false
    • trueで、category_idが指定されている場合、すべてのサブカテゴリのトピックが含まれます
    • falseの場合、指定された正確なカテゴリのトピックのみが含まれます
  • :user_ids (user_id, nullable)
    • 指定された場合、特定のユーザーからの応答のみが含まれます
    • nullの場合、すべてのユーザーからの応答が含まれます
    • コンマ区切りのリストとして複数のユーザーIDを受け入れることができます

結果例

date response_time_hours
2023-11-12 29.87
2023-11-13 81.52
2023-11-14 5.17
2023-11-15 6.51
2023-11-16 7.75
… …
「いいね！」 3