数据探索器查询所有活跃用户（潜水员+发帖者)

社区 数据与报告
1

我想向团队展示我们当前的活跃用户数量统计，其中“活跃用户”的定义如下：

在过去一年内，已登录并阅读过内容或在我们的论坛中发布过帖子的用户。

我们有很多处于“邮件列表模式”的用户会通过电子邮件进行回复，因此如果仅按照以下查询统计阅读者数量，我们会遗漏很多用户：

我也可以获取在此发布过帖子的用户数量：

我只是不太擅长将这两个条件用 OR 逻辑组合起来，以便同时统计这两类用户。您能帮忙吗？

1 个赞
2

这将为您列出所有在过去一年内登录过并阅读过内容，或发布过帖子的用户。数据探索器还会显示具体人数。

SELECT p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days'
 AND t.deleted_at IS NULL
 AND t.visible = TRUE
 AND t.closed = FALSE
 AND t.archived = FALSE
 AND t.archetype = 'regular'
 AND p.deleted_at IS NULL
UNION
SELECT u.user_id
FROM user_visits u
WHERE u.posts_read > 0
 AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days'
ORDER BY user_id

可能还有更高效的实现方式，但这个方法确实有效 :slight_smile:。如果您需要针对特定时间段，可以将两处 > CURRENT_TIMESTAMP - INTERVAL '365 days' 修改为类似这样的格式：BETWEEN '20200101'::date AND '20210101'::date。

您在问题中提供了非常有用的信息和参考！我只需要挑选合适的部分并将它们组合起来即可。

3 个赞
3

谢谢！我始终相信，在可能的情况下，应让他人更容易提供帮助。

UNION —— 这正是我 SQL 知识中的盲点。现在问题已完全解决，非常感谢！待我的最终查询语句进一步优化后，我会在这里发布。

1 个赞
4

我最终做了一些不同的调整，因为我需要更多信息，而 UNION 显得有点受限。我还希望按群组进行拆分。我通过修改内嵌的“活跃潜水者”查询，并结合另一个用于提取特定群组信息的查询，成功实现了目标（我想）：

-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0

WITH included_users AS (
    SELECT gu.user_id
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name
),
posts_by_user AS (
    SELECT COUNT(*) AS posts, p.user_id
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
        AND t.deleted_at IS NULL
        AND t.visible = TRUE
        AND t.closed = FALSE
        AND t.archived = FALSE
        AND t.archetype = 'regular'
        AND p.deleted_at IS NULL
    GROUP BY p.user_id
),
posts_read_by_user AS (
    SELECT SUM(posts_read) AS posts_read, uv.user_id
    FROM user_visits uv
    WHERE uv.posts_read > 0
        AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
    GROUP BY uv.user_id
)

SELECT
    u.id AS "user_id",
    u.username_lower AS "username",
    u.last_seen_at,
    COALESCE(pbu.posts, 0) AS "posts_created",
    COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
    AND u.id > 0
    AND u.id IN (SELECT user_id FROM included_users)
    AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0)
ORDER BY u.id
4 个赞
5

感谢分享。我尝试使用此代码以及一个非常相似的代码来查询徽章，但收到此错误：

Contract violation:
Query does not return a 'granted_at' column

我是否缺少一些代码才能在徽章系统中使用此查询？我的用例是自动化“潜水者”组 :slight_smile:

6

狡猾！

徽章查询非常复杂——它们只需要返回“user_id”和“granted_at”。所以你需要对此进行一些修改。

我建议深入研究徽章查询相关的主题（仔细阅读它们），然后尝试一下。我也建议每天只运行一次，否则可能会有点吃力。

让我们知道你的进展！

1 个赞