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

select
    avg(if (datediff (date2, date1) = 1, 1, 0)) as avg_rate
from
    (
        select distinct
            device_id,
            date as date1,
            lead (date) over (
                partition by
                    device_id
                order by
                    date
            ) as date2
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            ) as qpd_device_date
    ) as id_last_next_date

lead (date) over (partition by device_id order by date )

• lead找到某列的下一次的动作然后将日期进行拼接
• lead(date)表示对date进行寻找，寻找partition by device_id的下一次时间
• partition by device_id表示按照此列进行分组
• order by date按照此列进行排序

select
    count(distinct q2.device_id, q2.date) / count(distinct q1.device_id, q1.date)
from
    question_practice_detail as q1
    left outer join question_practice_detail as q2 on q1.device_id = q2.device_id
    and datediff (q1.date, q2.date) = 1

提取表格中的device_id，date作为临时表格，左侧添加device_id和date2，添加的条件为device_id相等和datediff (q1.date, q2.date) = 1

datediff (q1.date, q2.date) = 1的意思是两个date相差1

select count(uniq_id_date_date)/count(qpd_date) as avg_rate
from(
select distinct
    qpd.device_id,
    qpd.date as qpd_date,
    uniq_id_date.date as uniq_id_date_date
from
    question_practice_detail as qpd
    left join (
        select distinct
            device_id,
            date
        from
            question_practice_detail
    ) as uniq_id_date on uniq_id_date.device_id = qpd.device_id and date_add(qpd.date,interval 1 day)=uniq_id_date.date
) as id_last_next_date

首先提取表中的device_id和date 并在原表左拼接，使device_id和date_add(qpd.date,interval 1 day)=uniq_id_date.date相等，符合这两个条件的即为连续两天都去参加的人

date_add(qpd.date,interval 1 day)=uniq_id_date.date表示对date列每个元素加一天，,interval 1 day可以变为月份，年等单位。