在PostgreSQL中按日期构建流失漏斗及统计各时段活跃用户数

针对你的需求，核心思路是先生成需要统计的所有日期时段（月/周/日），再逐一判断每个用户在该时段内是否处于活跃状态，最后统计每个时段的活跃用户总数。以下是具体实现：

基础按月统计SQL #

假设你的用户表名为users，字段使用你提供的中文名称：

WITH month_series AS (
    -- 生成从最早注册月份到当前月份的每月第一天序列
    SELECT
        generate_series(
            DATE_TRUNC('month', MIN(TO_DATE("注册日期", 'FMMonth DD, YYYY'))),
            DATE_TRUNC('month', CURRENT_DATE),
            INTERVAL '1 month'
        ) AS month_start
),
month_ranges AS (
    -- 转换为中文月份名称，并计算当月最后一天
    SELECT
        month_start,
        (month_start + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS month_end,
        TO_CHAR(month_start, 'FMMonth') AS 月份
    FROM month_series
)
-- 统计每个月的活跃用户数
SELECT
    mr.月份,
    COUNT(DISTINCT u.user_id) AS 活跃用户数
FROM month_ranges mr
LEFT JOIN "users" u
    -- 判断用户在当月是否活跃：注册日期≤当月最后一天，且注销日期≥当月第一天（或未注销）
    ON TO_DATE(u."注册日期", 'FMMonth DD, YYYY') <= mr.month_end
    AND (u."注销日期" IS NULL OR TO_DATE(u."注销日期", 'FMMonth DD, YYYY') >= mr.month_start)
-- 过滤掉注册当月（如不需要可删除此条件）
WHERE mr.month_start > DATE_TRUNC('month', TO_DATE('October 28, 2021', 'FMMonth DD, YYYY'))
GROUP BY mr.月份, mr.month_start
ORDER BY mr.month_start;

关键逻辑说明 #

1. 生成日期序列：使用generate_series函数创建连续的月份时段，确保覆盖所有需要统计的历史周期。
2. 活跃判断规则：用户在某个月份活跃的条件是：
   • 注册日期不晚于该月最后一天（确保当月前已注册）
   • 注销日期不早于该月第一天，或未注销（确保当月内至少有一天处于活跃状态）
3. 去重统计：用COUNT(DISTINCT user_id)避免同一用户被多次统计。

扩展到周/日统计 #

只需修改日期截断类型和序列间隔即可：

按周统计 #

WITH week_series AS (
    SELECT
        generate_series(
            DATE_TRUNC('week', MIN(TO_DATE("注册日期", 'FMMonth DD, YYYY'))),
            DATE_TRUNC('week', CURRENT_DATE),
            INTERVAL '1 week'
        ) AS week_start
),
week_ranges AS (
    SELECT
        week_start,
        (week_start + INTERVAL '1 week' - INTERVAL '1 day')::DATE AS week_end,
        TO_CHAR(week_start, 'IYYY-"W"IW') AS 周次 -- 格式如2021-W44
    FROM week_series
)
SELECT
    wr.周次,
    COUNT(DISTINCT u.user_id) AS 活跃用户数
FROM week_ranges wr
LEFT JOIN "users" u
    ON TO_DATE(u."注册日期", 'FMMonth DD, YYYY') <= wr.week_end
    AND (u."注销日期" IS NULL OR TO_DATE(u."注销日期", 'FMMonth DD, YYYY') >= wr.week_start)
GROUP BY wr.周次, wr.week_start
ORDER BY wr.week_start;

按日统计 #

WITH day_series AS (
    SELECT
        generate_series(
            MIN(TO_DATE("注册日期", 'FMMonth DD, YYYY')),
            CURRENT_DATE,
            INTERVAL '1 day'
        )::DATE AS 日期
)
SELECT
    ds.日期,
    COUNT(DISTINCT u.user_id) AS 活跃用户数
FROM day_series ds
LEFT JOIN "users" u
    ON TO_DATE(u."注册日期", 'FMMonth DD, YYYY') <= ds.日期
    AND (u."注销日期" IS NULL OR TO_DATE(u."注销日期", 'FMMonth DD, YYYY') >= ds.日期)
GROUP BY ds.日期
ORDER BY ds.日期;

内容的提问来源于stack exchange，提问作者Daniel G