仪表板报告 - 首次响应时间

数据与报告
, ,
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 

-- 按日期计算首次响应时间
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  -- 普通帖子类型
    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, 可为空)
    • 提供时，将结果筛选到特定类别
    • 为 null 时，包含所有类别的帖子
  • :include_subcategories (boolean) - 默认值：false
    • 为 true 且提供了 category_id 时，包含所有子类别的主题
    • 为 false 时，仅包含完全指定类别的主题
  • :user_ids (user_id, 可为空)
    • 提供时，仅包含来自特定用户的回复
    • 为 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 个赞