Postgres中对UNION合并的两张表统计用户连续活跃天数的实现方法

核心逻辑 #

统计连续活动天数采用经典的日期偏移分组法，核心逻辑是：连续的活动日期减去对应排序行号的间隔天数后，会得到相同的分组基准值，以此完成连续周期的分组统计。

完整实现SQL #

WITH union_activity AS (
    -- 合并两张结构相同的活动表，替换为实际表名即可
    SELECT id, email, timestamp FROM 表1
    UNION ALL
    SELECT id, email, timestamp FROM 表2
),
user_date_activity AS (
    -- 时间戳转日期，同时去重同一用户同一天的多条记录，避免统计错误
    SELECT DISTINCT 
        id, 
        email, 
        DATE(timestamp) AS activity_date
    FROM union_activity
),
date_grp AS (
    SELECT 
        id,
        email,
        activity_date,
        -- 按用户分区、活动日期排序生成行号，计算分组基准值
        activity_date - (ROW_NUMBER() OVER (PARTITION BY id, email ORDER BY activity_date)) * INTERVAL '1 day' AS grp
    FROM user_date_activity
)
SELECT 
    id,
    email,
    COUNT(*) AS length
FROM date_grp
GROUP BY id, email, grp
ORDER BY id, MIN(activity_date);

说明 #

你之前的查询无法正确分组的原因是窗口函数排序字段误用了id而非活动日期，且没有针对日期连续性做判定逻辑，所以无法按连续周期完成分组。上述SQL在你的样例数据下运行，可直接得到你期望的统计结果。

内容的提问来源于stack exchange，提问作者Ruben Ugarte