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

【场景】：次日留存率

【分类】：时间函数、日期相差一天

分析思路

难点：

1.计算平均次日留存率，需要知道第一天和第二天的用户数量

题解中写出了3种得到第二天日期的方法

• 使用 date_add(expr, interval 1 day)
• 使用 date_sub(expr, interval 1 day)
• 使用 datediff(expr1,expr2)
• 使用 timestampdiff(day,expr1,expr2)

扩展

前往查看：MySQL 日期函数、时间函数在实际场景中的应用

求解代码

方法一：

使用 date_add(expr, interval 1 day)

select
    count(date2) / count(date1) as avg_ret
from(
    select distinct 
        a.device_id,
        a.date as date1,
        b.date as date2
    from question_practice_detail a
    left join(
        select distinct device_id, 
        date
        from question_practice_detail
    ) b on a.device_id = b.device_id and date_add(a.date, interval 1 day) = b.date
) c

方法二

使用 date_sub(expr, interval 1 day)

select
    count(date2) / count(date1) as avg_ret
from(
    select distinct 
        a.device_id,
        a.date as date1,
        b.date as date2
    from question_practice_detail a
    left join(
        select distinct device_id, 
        date
        from question_practice_detail
    ) b on a.device_id = b.device_id and date_sub(b.date, interval 1 day) = a.date
) c

方法三

使用 datediff(expr1,expr2)

select
    count(date2) / count(date1) as avg_ret
from(
    select distinct 
        a.device_id,
        a.date as date1,
        b.date as date2
    from question_practice_detail a
    left join(
        select distinct device_id, 
        date
        from question_practice_detail
    ) b on a.device_id = b.device_id and datediff(a.date,b.date)=1
) c

方法四

使用 timestampdiff(day,expr1,expr2)

select
    count(date2) / count(date1) as avg_ret
from(
    select distinct 
        a.device_id,
        a.date as date1,
        b.date as date2
    from question_practice_detail a
    left join(
        select distinct device_id, 
        date
        from question_practice_detail
    ) b on a.device_id = b.device_id and timestampdiff(day,a.date,b.date)=1
) c