Vorrei creare un report personalizzato - tempo tra le ultime due risposte su un argomento

Dati e reportistica
1

Simile al tempo di prima risposta, sto cercando un modo per determinare qual è il tempo medio (su qualsiasi periodo di tempo) tra le ultime due risposte negli argomenti in un elenco selezionato di categorie.

Il caso d’uso per questo è determinare se un’impostazione di chiusura automatica è appropriata.

Ho installato il plugin Data Explorer, ma non so abbastanza dello schema del database (o, peraltro, non ricordo molto del mio corso universitario di database/SQL dei primi anni '90) per creare il report da solo.

Apprezzerei suggerimenti e indicazioni su come estrarre questi dati.

ETA: Sembra che l’approccio sia quello di fare un join tra argomenti e post (in modo da poter filtrare per categoria), e quindi trovare i post con i due timestamp di aggiornamento più recenti e differenziarli.

Poiché il punto dell’esercizio non dipende dal fatto che l’ultimo sia una risposta al penultimo, ma dal determinare il massimo differenziale di tempo tra le due risposte più recenti (indipendentemente dalla loro specifica relazione) al fine di vedere se l’argomento era abbastanza vecchio da essere chiuso, e quindi calcolare la media tra tutte le categorie (o per categoria, il che sarebbe probabilmente più utile),

Pensandoci un po’ di più, sembra che questa possa essere solo una variazione di qualsiasi query utilizzata nel processo rake topics:auto_close (la differenza è che quella utilizza il tempo corrente piuttosto che differenziare i due post più recenti nell’argomento).

2

Per chi fosse interessato, ecco cosa sembra funzionare:

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

Questo dovrebbe fornire la media e la mediana dei tempi tra i timestamp di creazione. I timestamp di aggiornamento sembravano problematici (ho ottenuto alcuni valori negativi per qualche motivo quando facevo solo le medie).

Ho avuto un po’ di assistenza AI nella creazione della query, quindi è possibile che ci siano degli errori e sarei felice se qualcuno avesse suggerimenti su come migliorarla (o correggere eventuali errori). I risultati che ho ottenuto sembravano ragionevoli in base ai controlli effettuati su gruppi più piccoli, nonché ai cambiamenti derivanti dall’aumento dell’intervallo di date che hanno portato a tendenze in linea con le aspettative che avevo dall’uso di un campione di dati più ampio.

3

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