Rails+Postgres：按用户起始时间分组统计Checkpoint数据

要实现按用户各自起始日的相对天数统计Checkpoint数据，需要先确定每个用户的起始时间，再为每个用户生成其专属的180天时间序列，最后关联Checkpoint表并按相对天数汇总。

步骤说明 #

• 确定用户起始时间：如果Users表有start_date字段直接使用；若没有，从Checkpoint表取该用户最早的记录日期作为起始日。
• 生成用户专属时间序列：为每个用户生成从起始日开始的0到179天（共180天）的偏移，计算出每个用户的相对天数（第1天、第2天…）和对应的实际日期。
• 关联Checkpoint数据：将用户的时间序列与Checkpoint表关联，统计每个用户每天的Checkpoint数量。
• 按相对天数汇总：把所有用户的同相对天数的数据汇总，得到用于计算平均数量的基础数据。

修正后的SQL代码 #

-- 获取每个用户的起始日期（可根据表结构选择来源）
WITH user_start_dates AS (
    SELECT 
        user_id,
        start_date AS user_start -- 若Users表无start_date，替换为：MIN(checkpoints_date) AS user_start
    FROM users
    WHERE user_id IN (1,2,3)
    -- 若从Checkpoint表取起始日，替换为以下查询：
    -- FROM checkpoints
    -- WHERE user_id IN (1,2,3)
    -- GROUP BY user_id
),
-- 为每个用户生成180天的时间序列（含相对天数与实际日期）
user_dates AS (
    SELECT 
        usd.user_id,
        offs AS day_offset, -- 0=起始日（对应第1天），1=第2天…，179=第180天
        (usd.user_start + offs::INTERVAL)::DATE AS actual_date
    FROM user_start_dates usd
    CROSS JOIN GENERATE_SERIES(0, 179) AS offs
    -- 可选：过滤掉超过当前日期的记录
    WHERE (usd.user_start + offs::INTERVAL)::DATE <= CURRENT_DATE
),
-- 统计每个用户每日的Checkpoint数量
user_daily_checkpoints AS (
    SELECT 
        ud.user_id,
        ud.day_offset,
        COUNT(c.id) AS checkpoints_count
    FROM user_dates ud
    LEFT JOIN checkpoints c 
        ON ud.user_id = c.user_id 
        AND ud.actual_date = c.checkpoints_date::DATE
    GROUP BY ud.user_id, ud.day_offset
)
-- 按相对天数汇总所有用户数据
SELECT 
    day_offset + 1 AS day_number, -- 转换为第1天、第2天的直观格式
    SUM(checkpoints_count) AS total_checkpoints,
    COUNT(DISTINCT user_id) AS active_users,
    AVG(checkpoints_count) AS avg_checkpoints_per_user
FROM user_daily_checkpoints
GROUP BY day_offset
ORDER BY day_offset;

代码解释 #

• user_start_dates：核心是确定每个用户的统计起始点，适配不同的表结构设计。
• user_dates：通过CROSS JOIN GENERATE_SERIES为每个用户生成独立的时间序列，保证每个用户都有完整的180天统计周期。
• user_daily_checkpoints：用左连接确保即使用户某天没有创建Checkpoint，也能保留该天的记录（数量为0），避免数据缺失。
• 最终查询：按相对天数聚合数据，直接输出可用于图表展示的总数量、活跃用户数和人均Checkpoint数，完全匹配需求中“汇总所有用户第N天数据”的要求。

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