Benutzerdefinierter Bericht erstellen: Zeit zwischen den letzten beiden Antworten zu einem Thema

Daten & Berichterstattung
1

Ähnlich wie bei der Zeit bis zur ersten Antwort suche ich nach einer Möglichkeit, die durchschnittliche Zeit (über einen beliebigen Zeitraum) zwischen den letzten beiden Antworten in Themen einer ausgewählten Liste von Kategorien zu ermitteln.

Der Anwendungsfall hierfür ist die Bestimmung, ob eine automatische Schließungseinstellung angemessen ist.

Ich habe das Data Explorer Plugin installiert, aber ich weiß nicht genug über das Datenbankschema (oder erinnere mich an meine Datenbank-/SQL-Kurse aus den frühen 90ern), um den Bericht selbst zu erstellen.

Ich würde mich über Hinweise und Vorschläge freuen, wie ich diese Daten abrufen kann.

ETA: Es scheint, dass der Ansatz darin besteht, Themen und Beiträge zu verbinden (damit ich nach Kategorie filtern kann) und dann die Beiträge mit den beiden neuesten Zeitstempeln zu finden und sie zu differenzieren.

Da der Sinn der Übung nicht davon abhängt, ob die neueste Antwort eine Antwort auf die zweitneueste ist, sondern um die maximale Zeitdifferenz zwischen den beiden neuesten Antworten (unabhängig von ihrer spezifischen Beziehung) zu ermitteln, um zu sehen, ob das Thema alt genug zum Schließen war, und dann den Durchschnitt über alle Kategorien (oder pro Kategorie, was wahrscheinlich nützlicher wäre) zu berechnen.

Wenn ich noch ein wenig darüber nachdenke, scheint dies nur eine Variation der Abfrage zu sein, die im rake topics:auto_close-Prozess verwendet wird (der Unterschied besteht darin, dass dieser die aktuelle Zeit verwendet, anstatt die beiden neuesten Beiträge im Thema zu differenzieren).

2

Für alle, die interessiert sind, hier ist, was zu funktionieren scheint:

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

Dies sollte den Mittelwert und Median der Zeiten zwischen den Erstellungs-Zeitstempeln ergeben. Die Zeitstempel für Updates schienen problematisch zu sein (ich erhielt aus irgendeinem Grund einige negative Werte, wenn ich nur Durchschnittswerte berechnete).

Ich hatte einige KI-Unterstützung bei der Erstellung der Abfrage, daher ist es möglich, dass es Fehler gibt – und ich freue mich, wenn jemand Vorschläge hat, wie sie verbessert (oder Fehler korrigiert) werden können. Die Ergebnisse, die ich erhielt, schienen auf Überprüfungen kleinerer Gruppen sowie auf Änderungen, die sich aus der Erweiterung des Datumsbereichs ergaben und Trends aufwiesen, die mit den Erwartungen aus der Verwendung einer größeren Datenstichprobe übereinstimmten, vernünftig zu sein.

3

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