按用户对比活跃日期：统计间隔四周回归用户的SQL实现

针对你要按日统计回归用户数量的需求，我结合你的示例数据和回归用户定义（间隔四周未访问后再次访问的用户），整理了完整的实现方案：

需求回顾 #

• 输入数据：用户访问日志，包含user_id（用户ID）和visit_date（访问日期）
• 目标：按日期统计每日的回归用户数量，回归用户指间隔至少28天未访问后再次访问的用户

示例输入数据 #

完整SQL实现 #

WITH user_visits_ranked AS (
    -- 为每个用户的访问日期按时间排序，并获取上一次访问日期
    SELECT 
        user_id,
        visit_date,
        LAG(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) AS prev_visit_date
    FROM your_table_name  -- 替换成你的实际表名
),
returning_users AS (
    -- 筛选出符合回归用户定义的访问记录
    SELECT 
        visit_date AS stat_date,
        user_id
    FROM user_visits_ranked
    WHERE 
        prev_visit_date IS NOT NULL  -- 排除用户的首次访问记录
        AND DATE_DIFF(visit_date, prev_visit_date, DAY) >= 28  -- 间隔≥28天
)
-- 按日期统计每日回归用户数（去重避免同一用户当日多次访问重复计数）
SELECT 
    stat_date,
    COUNT(DISTINCT user_id) AS returning_user_count
FROM returning_users
GROUP BY stat_date
ORDER BY stat_date;

代码逻辑拆解 #

1. user_visits_ranked CTE：使用LAG()窗口函数，按用户分组、访问日期升序排序，为每条访问记录关联该用户的上一次访问日期，这是判断是否为回归用户的核心依据。
2. returning_users CTE：筛选出满足回归条件的记录：排除首次访问（无历史访问记录），且当前访问与上一次访问间隔≥28天，这些记录对应的用户就是当日的回归用户。
3. 最终统计：按日期分组，用COUNT(DISTINCT user_id)确保同一用户在同一天多次访问只被统计一次，得到每日的回归用户数量。

注意事项 #

• 不同SQL方言的日期差函数语法可能不同：
  • MySQL 可替换为DATEDIFF(visit_date, prev_visit_date) >= 28
  • PostgreSQL 可替换为(visit_date - prev_visit_date) >= INTERVAL '28 days'
• 如果需要包含首次访问后间隔28天以上的再次访问，当前逻辑已经覆盖，无需额外调整。

内容的提问来源于stack exchange，提问作者Lio Elbammalf