七日留存的计算方法

转载：https://www.jianshu.com/p/cf8cf682dcba

转载：https://blog.csdn.net/caojian107/article/details/109543513?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~aggregatepage~first_rank_ecpm_v1~rank_v31_ecpm-11-109543513.pc_agg_new_rank&utm_term=hivesql%E6%B1%82%E5%89%8D%E4%B8%83%E5%A4%A9&spm=1000.2123.3001.4430

 

如题，求首次曝光用户数和用户留存数，表t_stay有两个字段，分别是用户id和曝光时间，有曝光就会生成一条纪录。表中存的是历史所有的数据。需求是得到如下表格的结果：

首次曝光人数，是在dt这一天，是用户在历史中的首次曝光。

次1日留存人数，是dt这天首次曝光的这些人，在次1日有多少留存人数。

注意t_stay是历史所有数据、我们想要看的数据的时间范围是2020-01-01到2020-01-30，也就是说我们的结果要有30条记录（30行）。

dt	首次曝光人数	次1日留存人数	次2	次3	次4	次5	次6	次7
2020-01-01
2020-01-02
2020-01-03
2020-01-04
2020-01-05

 

在做这道题的过程中，我发现我的思维很容易受限制，自动带入到之前类似题目的那个思路中去，可能得到的不是最优的解，是一个效率极低的答案。

第一个指标首次曝光人数怎么求？

思路1：可以用当天曝光的用户id去重得到表1，与曝光时间小于当天的历史用户id左连接，关联不上的就是首次曝光的用户。（得到userid+当天的日期）。

这个方法我写的时候发现一次只能求出一天的结果，但是我们要的是1号到30号的。

思路2：把历史表按照人、日去重，然后按照用户id分组，得到最小曝光日期对应的日。（得到userid+首次曝光日期）。

第二个指标次n日留存人数怎么求？

思路1：首次曝光的表1（userid+日期）join表2（历史所有userid+日期），关联条件是(表1.日期=表2.日期-1day and 表1.userid=表2.userid)。

思路2：首次曝光的表1（userid+日期）join表2（历史所有userid+日期），关联条件是表1.userid=表2.userid，在sum(case when)中去写逻辑。

如果两个指标都用思路1，一次只能求出一天的结果，显然不够简洁。我使用了思路2+思路2的方法，sql如下：

首先准备数据