Procurando construir um relatório personalizado - tempo entre as duas últimas respostas em um tópico

Dados e relatórios
1

Semelhante ao tempo de primeira resposta, estou procurando uma maneira de determinar qual é o tempo médio (em qualquer período) entre as duas últimas respostas em tópicos em uma lista selecionada de categorias.

O caso de uso para isso é determinar se uma configuração de fechamento automático é apropriada.

Tenho o plugin Data Explorer instalado, mas não sei o suficiente sobre o esquema do banco de dados (ou, aliás, não me lembro muito do meu curso de banco de dados/SQL da faculdade no início dos anos 90) para criar o relatório sozinho.

Agradeceria dicas e sugestões sobre como extrair esses dados.

ETA: Parece que a abordagem é fazer um join entre tópicos e posts (para que eu possa filtrar por categoria) e, em seguida, encontrar os posts com os dois timestamps de atualização mais recentes e diferenciá-los.

Como o objetivo do exercício não depende de o último ser uma resposta ao penúltimo, mas sim determinar o diferencial de tempo máximo entre as duas respostas mais recentes (independentemente de seu relacionamento específico) para ver se o tópico era antigo o suficiente para ser fechado, e, em seguida, calcular a média em todas as categorias (ou por categoria, o que provavelmente seria mais útil),

Pensando um pouco mais sobre isso, parece que isso pode ser apenas uma variação da consulta usada no processo rake topics:auto_close (a diferença é que esta usa o tempo atual em vez de diferenciar os dois posts mais recentes no tópico).

2

Para quem estiver interessado, eis o que parece funcionar:

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

Isso deve fornecer a média e a mediana dos tempos entre os timestamps de criação. Os timestamps de atualização pareciam problemáticos (obtive alguns valores negativos por algum motivo ao apenas fazer médias).

Tive alguma ajuda de IA na criação da consulta, então é possível que haja erros - e ficarei feliz se alguém tiver sugestões sobre como melhorá-la (ou corrigir quaisquer erros). Os resultados que obtive pareceram razoáveis com base em verificações feitas em grupos menores, bem como em mudanças resultantes do aumento do intervalo de datas, resultando em tendências que se alinharam com as expectativas que eu tinha de usar uma amostra de dados maior.

3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.