想构建一个自定义报告 - 关于某个话题的最后两次回复之间的时间

[hendersj]

与首次响应时间类似，我想确定在选定类别的主题中，最后两次响应之间的平均时间（在任何时间段内）。

此用例用于确定自动关闭设置是否合适。

我已经安装了数据浏览器插件，但我对数据库模式（或者说，我大学 90 年代初的数据库/SQL 课程所学内容已经忘得差不多了）了解不足，无法自己创建报告。

很乐意接受有关如何提取此数据的提示和建议。

预计到达时间：看来方法是连接主题和帖子（以便按类别过滤），然后找到具有两个最新更新时间戳的帖子并计算它们之间的差值。

由于练习的重点不是最后一个帖子是否是倒数第二个帖子的回复，而是确定两个最近回复之间的最大时间差（无论它们之间的具体关系如何），以便查看主题是否足够旧可以关闭，然后计算所有类别（或每个类别，这可能更有用）的平均值，

再仔细想想，这似乎只是 rake topics:auto_close 进程所使用的查询的一个变体（不同之处在于该进程使用当前时间而不是计算主题中两个最近帖子之间的差值）。

[hendersj]

对于感兴趣的人，这里是似乎有效的方法：

-- [params]
-- null date :start_date
-- null date :end_date
-- null int_list :category_ids

WITH RankedPosts AS (
    SELECT
        p.topic_id,
        p.created_at,
        ROW_NUMBER() OVER (PARTITION BY p.topic_id ORDER BY p.post_number DESC) AS rank
    FROM
        posts p
    WHERE
        p.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM topics t
            WHERE t.id = p.topic_id
            AND t.category_id IN (:category_ids)
        )
),
FilteredPosts AS (
    SELECT
        topic_id,
        created_at,
        rank
    FROM
        RankedPosts
    WHERE
        rank <= 2
),
PostDifferences AS (
    SELECT
        topic_id,
        EXTRACT(days FROM (MAX(created_at) FILTER (WHERE rank = 1) - MAX(created_at) FILTER (WHERE rank = 2)))::numeric(9,2) AS days_difference
    FROM
        FilteredPosts
    GROUP BY
        topic_id
)
SELECT
    t.category_id,
    AVG(pd.days_difference) AS avg_days_difference,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pd.days_difference) AS median_days_difference
FROM
    topics t
JOIN
    PostDifferences pd ON t.id = pd.topic_id
GROUP BY
    t.category_id
ORDER BY
    avg_days_difference DESC

这应该可以给出创建时间戳之间平均值和中位数。更新时间戳似乎有问题（由于某种原因，当我只计算平均值时，我得到了一些负值）。

我得到了 AI 的帮助来创建这个查询，所以有可能存在错误——如果有人有关于如何改进它（或纠正任何错误）的建议，我将不胜感激。我得到的结果在对较小群体进行的检查以及日期范围增加导致趋势与我从更大样本数据中获得的预期一致的更改方面，似乎是合理的。