SQL实现分组统计用户每月最晚访问日期（含空值）及留存状态标记

1. 统计用户每月最晚访问记录 #

你之前的查询每个月仅返回1个用户，核心原因是没有生成「全量用户+所有月份」的笛卡尔积组合，直接左关联只会返回有访问记录的用户匹配结果。
以下是PostgreSQL语法的实现代码，替换your_table_name为实际表名即可使用：

WITH
-- 生成目标月份序列，可根据需求调整时间范围
month_series AS (
    SELECT 
        EXTRACT(MONTH FROM generate_series) AS month,
        DATE_TRUNC('month', generate_series) AS month_start
    FROM generate_series('2020-01-01'::timestamp, '2020-12-01'::timestamp, interval '1 month')
),
-- 取所有去重用户ID
distinct_users AS (
    SELECT DISTINCT "User ID" AS user_id
    FROM your_table_name
),
-- 生成用户+月份的全量组合
user_month_full AS (
    SELECT m.month, u.user_id
    FROM month_series m
    CROSS JOIN distinct_users u
),
-- 聚合原表取每个用户每月最晚访问时间
user_month_visit AS (
    SELECT
        "User ID" AS user_id,
        EXTRACT(MONTH FROM "Visit Date") AS month,
        MAX("Visit Date") AS latest_visit
    FROM your_table_name
    GROUP BY 1,2
)
-- 左关联得到最终结果
SELECT
    f.month AS "Month",
    f.user_id AS "User ID",
    v.latest_visit AS "Visit Date"
FROM user_month_full f
LEFT JOIN user_month_visit v
ON f.user_id = v.user_id AND f.month = v.month
ORDER BY f.user_id, f.month;

2. 用户访问状态标记 #

基于你给出的示例规则，默认留存周期为3个月，可直接在上述逻辑基础上扩展，不需要额外做子查询：

WITH
-- 上述4个CTE保持不变，省略重复内容
month_series AS (...),
distinct_users AS (...),
user_month_full AS (...),
user_month_visit AS (...),
-- 新增：计算每个用户的首次访问月份、留存周期结束月份
user_first_visit AS (
    SELECT
        "User ID" AS user_id,
        MIN(EXTRACT(MONTH FROM "Visit Date")) AS first_month,
        MIN(EXTRACT(MONTH FROM "Visit Date")) + 3 AS retention_end_month -- 留存周期可按需调整
    FROM your_table_name
    GROUP BY 1
),
-- 关联所有基础数据
base_data AS (
    SELECT
        f.month,
        f.user_id,
        v.latest_visit,
        fv.first_month,
        fv.retention_end_month,
        CASE WHEN v.latest_visit IS NOT NULL THEN 1 ELSE 0 END AS has_visit,
        LAG(CASE WHEN v.latest_visit IS NOT NULL THEN f.month END) OVER (PARTITION BY f.user_id ORDER BY f.month) AS last_visit_month
    FROM user_month_full f
    LEFT JOIN user_month_visit v ON f.user_id = v.user_id AND f.month = v.month
    LEFT JOIN user_first_visit fv ON f.user_id = fv.user_id
)
SELECT
    month AS "Month",
    user_id AS "User ID",
    CASE
        WHEN month < first_month OR month > retention_end_month THEN null
        WHEN month = first_month THEN 'FIRST'
        WHEN has_visit = 1 AND (last_visit_month IS NULL OR month - last_visit_month > 1) THEN 'REACTIVATE'
        ELSE 'RETENTION'
    END AS "Type"
FROM base_data
ORDER BY user_id, month;

内容的提问来源于stack exchange，提问作者Kira Katou