用户页面指标

srinivas.chilukuri · 2024 年10 月 31 日 00:52

@SaraDev
能否提供 https://meta.discourse.org/u?cards=no&order=post_count 处的指标的 SQL 查询？
请参考下图

指标

收到的点赞数
赠送的点赞数
查看的主题数
阅读的帖子数
访问天数
解决方案
干杯

我们正在使用版主类别，因此我们正在修改版主查询以适用于任何给定的组

SaraDev · 2024 年10 月 31 日 19:54

你好 @srinivas.chilukuri，

可以通过 Data Explorer 使用 directory_items 表来检索 /u 用户页面的统计信息。

用户目录页面指标

-- [params]
-- int :period
-- 时间段选项：
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly

SELECT
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM
    directory_items di
WHERE
    di.period_type = :period
ORDER BY
    di.user_id

该表使用 period_type 字段进行筛选，而不是使用典型的 start_date 和 end_date 参数。以下值对应于目录页面上可用的不同时间段：

1：全部时间
2：每年
3：每月
4：每周
5：每日
6：每季度

此报告的示例结果如下所示：

user	likes_received	likes_given	topics_viewed	topic_count	post_count	days_visited	posts_read	solutions	cheers
Username1	4	17	250	69	116	480	217	10	844100
Username2	2	5	47	0	2	43	59	1	112305
Username3	0	4	2	0	0	2	7	0	3100
…	…	..	…	…	…	…	…	…	…

srinivas.chilukuri · 2024 年10 月 31 日 23:35

@SaraDev
我需要开始日期和结束日期。有没有办法在给定开始日期和结束日期的情况下获取这些指标？

用户
收到的点赞数
给予的点赞数
查看的主题数
主题数
帖子数
访问天数
阅读的帖子数
解决方案
赞赏

注意：我获取的是部分用户的指标，而非全部用户。

SaraDev · 2024 年11 月 1 日 20:23

如果您想查看您网站上用户的这些指标，并按特定的开始和结束日期进行筛选，您将需要一个查询，该查询在单独的 CTE 中提取每个指标的数据，然后在一个最终的 SELECT 语句中合并结果。

下面是它的样子：

用户指标

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-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
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM
        user_stats us
    WHERE
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY
        us.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(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    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
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

查询说明：

参数：
- :start_date 和 :end_date 是定义要查询数据日期范围的参数。
公共表表达式 (CTE)：
- likes_received：计算指定日期范围内每个用户收到的点赞数（action_type = 2）。
- likes_given：计算指定日期范围内每个用户给予的点赞数（action_type = 1）。
- user_metrics：聚合用户统计信息，例如在指定日期范围内首次发帖的用户的主题浏览量、帖子阅读量和访问天数。
- solutions：计算指定日期范围内每个用户提供的解决方案数（action_type = 15）。
- cheers：汇总指定日期范围内每个用户的游戏化分数。
最终选择：
- 主查询为每个用户选择用户参与度指标，包括收到的点赞数、给予的点赞数、浏览的主题数、阅读的帖子数、访问天数、提供的解决方案数和收到的欢呼数。
- 它使用 LEFT JOIN 来确保包含所有用户，即使他们在某些类别中没有活动，并使用 COALESCE 用零填充。

示例结果

user_id	likes_received	likes_given	topics_viewed	posts_read	days_visited	solutions	cheers
1	10	5	20	100	15	2	30
2	0	3	5	20	5	0	10
—	—	—	—	—	—	—	—

srinivas.chilukuri · 2025 年2 月 27 日 07:53

@SaraDev

我们使用了上面帖子中提供的查询，并有以下问题：

user_stats 表中的 user_metrics 是否是此信息的正确来源？考虑到 user_stats 是一个静态表，总结了用户加入 Discourse 以来的指标，它可能不适合在特定时间范围内（例如，从开始日期到结束日期）过滤指标。
时间序列比较 (T/S C/O)
对于一组给定的用户，我们比较了用户页面上可用的时间段数据，并注意到显著的差异。

主要差异：

topics_entered
posts_read_count
days_visited

您能否澄清是否有更好的方法来检索有时间限制的用户指标？

Jagster · 2025 年2 月 27 日 21:01

PG::UndefinedColumn: ERROR:  column uv.topic_id does not exist
LINE 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

SaraDev · 2025 年2 月 27 日 23:14

您说得对，user_stats 表是一个静态表，总结了用户加入 Discourse 以来的终生指标。

相反，要按日期过滤指标，例如 posts_read_count 和 days_visited，我们将需要使用 user_visits 数据库表来获取 posts。我们还需要使用 topic_views 表来按日期过滤 topics_entered 指标。

您观察到的差异源于使用 user_stats 表而不是 user_visits 和 topic_views 等其他表来按日期过滤这些统计数据。

为解决此问题，我们可以更新查询以使用这些数据库表：

以下是查询的更新版本：

用户页面指标

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

请注意，通过此方法，user_visits 表中的 posts_read 数据有一个重要区别——它不计算用户自己发布的帖子，而 user_stats 表中的数据确实包含用户自己撰写的帖子，因此您在此查询和用户页面之间仍可能发现这两个统计数据之间存在差异。

话题		回复	浏览量
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	138	2019 年12 月 31 日
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	6	605	2025 年2 月 27 日
PAID Data explorer challenge: chronological list of user topics started, replied and liked Marketplace sql-query	7	3250	2016 年12 月 2 日
Complex user activity report requirement Data & reporting sql-query	22	1736	2021 年8 月 5 日
Daily graph of user time online Feature	2	2129	2015 年10 月 1 日

用户页面指标

相关话题