您好！我们正在使用 Discourse 的云托管版本，并且已经运行了大约 1.5 年。我们希望能够获取一些关于平台采用和使用情况的历史指标。我们已经在“管理员报告”选项卡中查看了“注册用户”报告，但我们也想能够显示每月活跃用户总数。有人知道我该如何实现这个目标吗？我在“报告”或“数据浏览器”中找不到合适的方法。
此数据浏览器查询将返回唯一的月度用户访问量。这是否是您想要的？
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins
WITH not_allowed_groups AS (
SELECT gu.user_id FROM group_users gu
INNER JOIN groups g ON (g.id = gu.group_id)
WHERE g.name IN (:group_name)
)
SELECT
to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
非常感谢，但不太对……我们想要的是每个日历月末的活跃用户数（已注册且未停用或删除），无论他们当月是否访问过该平台。
我们正在查看注册用户图表，但给定月份的注册用户总数并不能反映我们的用户群，因为我们每个月都有因不活跃而自动停用的用户。我想知道唯一的方法是否是访问特定日期的历史用户列表导出？但因为我们是云托管的，所以我不知道我们是否有权访问。我们需要向 Discourse 请求吗？
我是 Discourse。 
您可以在此处提问，也可以直接发送电子邮件至 team@discourse.org。届时会有 SQL 知识更丰富的人员为您提供帮助。
4 个赞
我还不了解这个论坛，谢谢！您是否存储了云托管网站的历史备份，可以在需要时提供给我？
1 个赞
为确保持续经营，我们会存储备份，以便在紧急情况下进行恢复，但并非用于其他目的。
1 个赞
好的，我有一个新想法。我问了我们的人工智能助手。
SQL 查询：按月活跃用户 AI
HAWK:
请为我编写一个数据探索器查询，该查询返回一个用户列表，这些用户仍然活跃并且是在给定月份注册的。
DiscourseHelper:
搜索中
正在搜索：Discourse 数据探索器查询，用于查找在特定月份注册的活跃用户
阅读中
阅读：特定月份的活跃用户
要创建一个数据探索器查询，返回一个用户列表，这些用户仍然活跃并且是在特定月份注册的，您可以使用以下 SQL 查询。此查询假设活跃用户是指在给定时间范围内发布或回复的用户。
--[params]
-- int :month = 5
-- int :year = 2023
SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
AND EXTRACT(MONTH FROM u.created_at) = :month
AND EXTRACT(YEAR FROM u.created_at) = :year
AND u.last_seen_at IS NOT NULL -- 假设活跃意味着他们最近访问过网站
ORDER BY u.created_at
查询说明：
:month和:year是您可以设置的参数，用于筛选特定的月份和年份。u.active = TRUE确保用户帐户仍然处于活动状态。EXTRACT(MONTH FROM u.created_at) = :month和EXTRACT(YEAR FROM u.created_at) = :year将注册日期筛选到指定的月份和年份。u.last_seen_at IS NOT NULL认为只要用户在任何时候登录过，他们就是活跃的。- 结果提供用户 ID、用户名、注册日期和上次 seen 日期。
请随时根据您的需求调整参数和字段。如果您需要任何其他帮助，请告诉我！
如果您遗漏了什么，请随时提供更多上下文。
3 个赞
这是一个很棒的查询解释，谢谢！不过，不幸的是，我不确定这是否是我们想要的。
例如，如果我设置参数 01 和 2024，我看到的是今年的 1 月份。
我明白输出将包括所有在 1 月份注册并且今天仍然活跃的用户。对吗？
但我们想要的是自推出以来每个月（不是现在）u.active=TRUE 的用户数量。然后我们可以绘制图表，显示我们的用户群是如何随月份变化的。每个月末，对我们来说将是：
活跃用户 = 上个月的值 + 新注册用户 - 新删除用户 - 新停用用户
我在 Data Explorer 中没有看到任何关于停用或与 active 变量关联的日期的信息。那么，最有可能的办法是使用 staff action logs 并按日期进行分析，但我不认为导出功能对我们有效。当我们导出 staff action log 结果时，奇怪的是我们得到一个包含不同帖子内容的电子表格，而不是 staff actions 的表格。如果你们能帮助我们解决这个问题，也许这是一个替代的解决方案！ 你是否知道其他人有同样的问题？
一个有趣的问题。
我首先看了这里的示例。但这确实忽略了已删除的用户。你只能得到当时注册并且仍然存在的用户数量，而不是那些在此期间被删除的用户。
因此，我的想法是获取最后一个月注册的用户的 ID。这是当时可能拥有的最大用户数。然后可以从中减去已删除用户的数量。但是，机器人账户（例如 forum-helper）具有负 ID，但如果它们被删除，则会被计算在内。（但这可能是一个小的偏差）。我的查询是：
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- 生成开始日期和结束日期之间的月末日期
SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
-- 对于每个月末日期，查找该日期之前创建的最新用户
SELECT md.month_end,
(SELECT id
FROM users u
WHERE u.created_at < md.month_end
ORDER BY u.created_at DESC
LIMIT 1) AS user_max_id
FROM month_dates md
),
cumulative_deletion_count AS (
-- 计算每个月末日期之前的累计删除次数
SELECT md.month_end,
(SELECT COUNT(*)
FROM user_histories uh
WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
FROM month_dates md
)
SELECT
md.month_end,
ru.user_max_id,
cdc.deletions_count,
ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end
但它没有考虑到（停用）激活，这些信息也存储在 user_histories 表中。但这也许可以作为起点帮助你。
5 个赞