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

正规的连续天计算，应该排序：

WITH a AS (
    SELECT DISTINCT device_id
         , date
         , row_number() OVER (PARTITION BY device_id ORDER BY date) rn
    FROM question_practice_detail
    GROUP BY device_id, date -- 一个用户一天可能登录多次，所以按device_id，date去重
),b AS ( SELECT  device_id user_id
           , count(*)  days_count
       --  ,date -- 测试使用
        ,date_add(a.date, INTERVAL -rn DAY)  BEGIN_day -- 每个连续的第一天
        -- 排序得到的序列号，如果减去后得到的结果是同一天，则说明是连续的；否则是不连续的。
      FROM a
      GROUP BY user_id, BEGIN_day )
 SELECT
#         count(if(b.days_count > 1, 1, NULL))  -- 每个连续的第一天，有几个（按device_id）连续就有几个第一天
#       , sum(if(b.days_count > 1, days_count, NULL)) -- 所有总的连续天（所有device_id），包含当天
#       ,sum(days_count) --所有天
#  ,  -- 所有总的连续天减去（-）每个连续的第一天，第二天登录的
        (sum(if(b.days_count > 1, days_count, NULL))-count(if(b.days_count > 1, 1, NULL)))/sum(days_count) avg_ret
 FROM b