如何在PostgreSQL中构建D0-D30 N日用户留存图表？
2026-8-14
按注册日期构建D0-D30用户留存图表的实现求助
我需要为**所有注册且完成至少一项核心操作（上传图片或查看图片）**的用户，构建按注册日期分组的D0至D30日留存图表。其中：
- D0指用户注册当日
- 各N日留存率 = 该注册日用户中在Dn当日完成核心操作的人数 / 该注册日总用户数
示例说明
用户1和用户2均于2022-10-01注册（D0），后续操作如下：
So for example, user 1 and 2 both join on 2022-10-01 (day 0) ----------------------------------------------------------------------------------------- * user 1 views a picture (day 0) | * user 2 adds a picture (day 0) * user 1 adds a picture (day 1) | * user 2 add a picture (day 1) * user 1 does nothing (day 2) | * user 2 views a picture (day 2) * user 1 views a picture (day 3) | * user 2 adds and views multiple pictures (day 3) * user 1 does nothing (day 4) | * user 2 does nothing (day 4)
对应的留存图表格式如下：
date_joined | # of users | D0 | D1 | D2 | D3 | D4 | ... | D30 ---------------------------------------------------------------------- 2022-10-01 | 2 | 100% | 100% | 50% | 100% | 0% | ... | X%
- D2留存率50%：仅用户2完成查看图片的核心操作，2人中1人活跃
- D4留存率0%：两人均未进行核心操作
现有表结构
用户表（user table）
----------------- id | join_date ----------------- 1 | 2022-10-01 2 | 2022-10-01
图片表（picture table）
id | timestamp | userId | url ------------------------------------------------- 1 | 2022-10-01 10:00:00 | 2 | http://www... 2 | 2022-10-02 08:00:00 | 1 | http://www... 3 | 2022-10-02 09:00:00 | 2 | http://www... 4 | 2022-10-04 05:00:00 | 2 | http://www... 5 | 2022-10-04 06:00:00 | 2 | http://www...
浏览表（view table）
id | timestamp | userId ---------------------------------- 1 | 2022-10-01 11:00:00 | 1 2 | 2022-10-03 07:00:00 | 2 3 | 2022-10-04 09:00:00 | 1 4 | 2022-10-04 10:00:00 | 2 5 | 2022-10-04 11:00:00 | 2
当前遇到的问题
我目前能实现按日统计完成核心操作的总用户数，结果如下：
date | # users who performed core action ----------------------------------------- 2022-10-01 | 100 2022-10-02 | 59 2022-10-03 | 129 2022-10-04 | 143
但该结果不符合需求：它仅展示每日完成核心操作的总用户数，无法按注册日期追踪对应用户群体在30天内的留存情况。我需要实现按注册日期分组，展示该组用户在D0到D30各日的留存率，恳请提供实现思路或解决方案。
内容的提问来源于stack exchange，提问作者Aditya Mohile
相关产品推荐
相关产品推荐
