MySQL---日期函数

题目1：计算用户8月每天的练题数量

问题分解：

• 限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, "%Y-%m")="202108"

• 每天：按天分组group by date

• 题目数量：count(question_id)

• 表头重命名：as

• 输出示例中每天的字段只取了几号，要去掉年月，用day函数即可

示例代码：

    select day(date) as day,count(question_id) as question_cnt
    from question_practice_detail
    where month(date)=8 and year(date)=2021
    group by day

注：

按照每一天（几号）进行分组

题目2：计算用户的平均次日留存率

问题分解：

次日留存，指的是同一用户（在本题中则为同一设备，即device_id）在当天和第二天都进行刷题。注意，在这题我们不关心同一用户（设备）在这天答了什么题、答题结果如何，只关心他是否答题，因此对于这题来说存在重复的数据（如下图红框所示），需要使用 DISTINCT 去重。

次日留存率计算方式：

使用两个子查询，查询出两个去重的数据表，并使用条件（q2.date应该是q1.date的后一天）进行筛选，如下所示（数据未显示完全，从左至右顺序，列表名为 q1.device_id, q1.date, q2.device_id, q2.date)

因为使用的是q1左级联q2，所以q1的所有信息是显示的；而q2中只显示留存的信息，否则为null。

最后，分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数，即可算出次日留存率。

示例代码：

    select
        count(q2.device_id) / count(q1.device_id) as avg_ret
    from
        (select distinct device_id, date from question_practice_detail) as q1
    left join
        (select distinct device_id, date from question_practice_detail) as q2
    on q1.device_id = q2.device_id and q2.date = DATE_ADD(q1.date, interval 1 day)

注：

• MySQL中，COUNT在对列进行计数时不统计值为 null的条目。

• DATE_ADD(q1.date, interval 1 day)：SQL 日期函数，作用是给 q1 的日期加 1 天（比如 q1.date 是 2021-08-01，加 1 天就是 2021-08-02）；

细节补充：

1. 为什么用 LEFT JOIN 而不是 INNER JOIN？

   LEFT JOIN 会保留 q1 的所有记录（不管是否匹配到 q2），这样 COUNT (q1.device_id) 才能统计 “基准日总用户 数”；如果用 INNER JOIN，只会保留匹配成功的记录，无法计算分母。

2. 为什么要 DISTINCT 去重？

   避免同一个用户同一天多次答题导致计数重复（比如用户 2315 答了 5 道题，去重后只算 1 个用户，否则分母会被放大）。

3. DATE_ADD 函数：

   作用是给日期加指定时间，interval 1 day= 加 1 天，interval 1 month= 加 1 个月，是计算时间差的常用函数。