事后统计每个日历月的用户数量

数据与报告
10

一个有趣的问题。

我首先看了这里的示例。但这确实忽略了已删除的用户。你只能得到当时注册并且仍然存在的用户数量，而不是那些在此期间被删除的用户。

因此，我的想法是获取最后一个月注册的用户的 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 个赞