题解 | #计算用户的平均次日留存率#

SELECT COUNT(date2.device_id)/COUNT(date1.device_id) AS avg_ret
FROM (SELECT DISTINCT device_id,date
        FROM question_practice_detail) AS date1
LEFT JOIN (SELECT DISTINCT device_id,date
        FROM question_practice_detail) AS date2
    ON date1.device_id = date2.device_id AND date1.date =DATE_SUB(date2.date, interval 1 day)

• FROM与连接采用DISTINCT，是保证同一天答过多道题的信息不纳入date1表中，这是因为DISTINCT是只查询对于id和date的双重组合的DISTINCT，从而同一id同一天的多道答题记录只会被记录一次。
• 连接时LEFT JOIN，保证不满足条件的信息也不会被删除，依然会根据date1的排列进行排序，此外由于date2相对于date1是不重复的，所以连接时，date1不会增加。
• 采用date2中存在id相同，且date2的日期比date1中的日期多一个单位的条件，进行连接，满足条件，则date1右侧多出date2的信息，不满足条件则为空值。
• 因此date2与date1的信息显示情况![[Pasted image 20240915163613.png]]
• 从而右侧代表满足条件的数量，左侧代表每天登录的用户数量，注意此时计数的时候不能使用DISTINCT，因为即使同一个用户多天连续登录，也应该分多天进行统计。