如何在PostgreSQL中计算用户连续签到天数的平均值

一、PostgreSQL计算实现 #

核心思路：先识别每个连续签到的区间，统计每个区间的天数，再对所有区间求平均值。

完整SQL查询 #

WITH sign_in_groups AS (
    SELECT
        customer_id,
        day_checkin,
        -- 连续日期减去递增行号会得到相同值，以此标记同一签到区间
        day_checkin - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY day_checkin) AS group_id
    FROM your_table_name
    -- 可根据需求添加月份/时间范围过滤，比如WHERE month_checkin = 10
),
group_durations AS (
    SELECT
        customer_id,
        COUNT(*) AS consecutive_days
    FROM sign_in_groups
    GROUP BY customer_id, group_id
)
SELECT
    customer_id,
    ROUND(AVG(consecutive_days), 2) AS avg_consecutive_sign_in_days
FROM group_durations
GROUP BY customer_id;

逻辑解释 #

1. sign_in_groups 阶段：按用户分组，对签到日期排序后，用日期减去行号生成group_id——连续签到的日期对应相同的group_id，以此区分不同的签到周期。
2. group_durations 阶段：按用户和group_id分组，统计每组的记录数，得到每个连续签到周期的天数。
3. 最终计算：对每个用户的所有签到周期天数求平均，得到该用户的平均连续签到天数（用ROUND保留两位小数优化可读性）。

如果需要计算全平台用户的整体平均，去掉最后一步的GROUP BY customer_id即可。

二、Metabase仪表盘制作建议 #

• 基础问题创建：直接将上述SQL作为自定义问题导入Metabase，复杂逻辑不建议用可视化查询器拖拽构建，效率更低。
• 可视化选型：
  • 单用户核心指标：用数值卡片展示平均连续签到天数，突出重点；
  • 多用户对比：用条形图或表格，横轴放用户ID，纵轴放平均天数，直观区分差异；
• 动态过滤：添加customer_id、month_checkin过滤器，支持快速切换查看不同用户、不同月份的数据；
• 布局优化：顶部放核心数值卡片，下方搭配趋势图（如按月度的平均连续天数变化）或用户排名表，让信息层级清晰；
• 时效性设置：若数据实时更新，给仪表盘配置自动刷新（如每小时一次），确保数据同步最新状态。

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