Хочу создать пользовательский отчет — время между двумя последними ответами по теме

Создание сообщества Данные и отчетность
1

Аналогично времени до первого ответа, я ищу способ определить среднее время (за любой период) между двумя последними ответами в темах из выбранного списка категорий.

Этот запрос нужен для того, чтобы определить, подходит ли настройка автоматического закрытия тем.

У меня установлен плагин Data Explorer, но я недостаточно хорошо знаком со схемой базы данных (и, если уж на то пошло, мало что помню из университетского курса по базам данных и SQL, который я изучал в начале 90-х), чтобы создать такой отчёт самостоятельно.

Буду признателен за любые подсказки и предложения по тому, как извлечь эти данные.

ETA: Похоже, подход заключается в выполнении соединения (JOIN) между таблицами topics и posts (чтобы можно было фильтровать по категории), а затем нахождении двух записей с самыми поздними метками времени обновления и вычислении разницы между ними.

Поскольку суть задачи не зависит от того, является ли последний ответ ответом на предпоследний, а заключается в определении максимальной разницы во времени между двумя последними ответами (независимо от их конкретной взаимосвязи), чтобы понять, была ли тема достаточно старой для закрытия, а затем вычислить среднее значение по всем категориям (или по каждой категории в отдельности, что, вероятно, было бы более полезно),

Подумав ещё немного, я прихожу к выводу, что это может быть просто вариантом любого запроса, используемого в процессе rake topics:auto_close (разница лишь в том, что там используется текущее время, а не разница между двумя последними сообщениями в теме).

2

Для всех заинтересованных вот то, что, похоже, работает:

-- [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

Это должно вернуть среднее и медиану временных интервалов между метками времени создания. Метки времени обновления вызвали проблемы (по какой-то причине при простом усреднении я получил некоторые отрицательные значения).

При создании запроса я пользовался помощью ИИ, поэтому возможно, что в нём есть ошибки — буду рад любым предложениям по его улучшению (или исправлению ошибок). Полученные мной результаты казались разумными: это подтвердили проверки на меньших группах, а также изменения при увеличении диапазона дат, которые привели к трендам, соответствующим моим ожиданиям от работы с более крупной выборкой данных.