Benutzerseitige Metriken

Daten & Berichterstattung
8

Sie haben Recht, dass die Tabelle user_stats eine statische Tabelle ist, die die lebenslangen Metriken eines Benutzers seit seinem Beitritt zu Discourse zusammenfasst.

Stattdessen würden wir für die Filterung von Metriken nach Datum wie posts_read_count und days_visited die Datenbanktabelle user_visits für posts verwenden. Wir würden auch die Tabelle topic_views verwenden, um die Metriken topics_entered nach Datum zu filtern.

Die von Ihnen beobachteten Diskrepanzen ergeben sich aus der Verwendung der Tabelle user_stats anstelle anderer Tabellen wie user_visits und topic_views zum Filtern dieser Statistiken nach Datum.

Um dies zu beheben, können wir die Abfrage aktualisieren, um stattdessen diese Datenbanktabellen zu verwenden:

Hier ist eine aktualisierte Version der Abfrage:

Benutzerseitige Metriken

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM
        topic_views tv
    WHERE
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY
        tv.user_id
),
days_and_posts AS (
    SELECT
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM
        user_visits uv
    WHERE
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY
        uv.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

Beachten Sie, dass bei dieser Methode die Daten posts_read in der Tabelle user_visits eine wichtige Unterscheidung aufweisen – sie zählen keine eigenen Beiträge eines Benutzers, während die Daten aus der Tabelle user_stats selbst verfasste Beiträge enthalten. Daher können Sie zwischen diesen beiden Statistiken in dieser Abfrage und auf der Benutzerseite immer noch einen Unterschied feststellen.

1 „Gefällt mir“