牛客 基础sql 困难难度 通过率28% sql29 计算用户的平均次日留存率

[SQL29 计算用户的平均次日留存率](计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com))

题目描述：

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

分析问题：

• 第一种：left join

1. 想要求出这个平均概率，关键在于对date字段的使用，从问题中不难看出某天刷题后第二天还会再来，它是一个连续的过程，我们可以使用使用date = date+1和device_id相等的连接字段进行自连接，使能够连续的行连接在一起
2. 但是我们要求的是用户刷题的平均概率，当直接连接的话，如果可能有一个用户在一天内做了几道题的情况，left join自连接之后出现多个相同的行从而出现错误，所以我们要一开始使用distinct去重

select
#q2是通过date差值连接后的剩下的用户，q1是时间去重剩下的全部用户
    count(q2.device_id) / count(q1.device_id)
from
    (
        #去重，防止连接后出现重复行
        select distinct
            device_id,
            date
        from
            question_practice_detail
    ) q1
    left join (
        #去重
        select distinct
            device_id,
            date
        from
            question_practice_detail
    ) q2 on q1.device_id = q2.device_id
    and 
    #使用date之间的差值为一来作为连接条件
    q2.date = date_add(q1.date, interval 1 day);

• 第二种：inner join 加 /

1. 同时在这平均概率求法中，也可以使用 inner join进行求解，它的关键在于使用inner join（连接条件与上面相同date = date+1和device_id相等）找出连接后的剩下的行数，在与时间去重后的剩下的行数做除法运算，此法是在第一时间没有想到left join的同学的一个好做法，但这种方法不太推荐，效率不高

select
    (
        select
            count(distinct q1.device_id, q1.date) #去重
        from
            question_practice_detail q1
            inner join question_practice_detail q2 on 
            datediff (q1.date, q2.date) = -1 #date差值为一
            and q1.device_id = q2.device_id
    ) / (
        select
            count(distinct q3.device_id, date)
        from
            question_practice_detail q3
    )

• 第三种：滑动窗口函数

1. 同样先进行去重操作，同时查询仅需要的字段，然后作为小表a1被使用，对a1进行lead()函数滑窗,得到表a2
2. 求平均概率，使用count(第一天&&第二天)/count(第一天)，但在表中，并表中count不了第一天&&第二天，所以使用**if()**函数把符合条件的行进行转换（条件：lead_day与date差值为一），转换的结果为1或0，再使用sum()函数统计1。这样count(第一天&&第二天)/count(第一天)转换成sum(if(datediff(lead_date, date) = 1, 1, 0))/count(1)（为什么是1，因为表a2中每一行都符合第一天）
3. 而avg()函数刚好等于sum()/count()，所以再进行改写。

select
    avg(if(datediff(lead_date, date) = 1, 1, 0))
from
    (
        select distinct
            device_id,
            date,
            lead(date) over (partition by device_id order by date)  lead_date
        from
            (
                select distinct
                    device_id,
                    date
                from
                    question_practice_detail
            )  a1
    ) as a2

sql创建代码：

drop table if  exists `question_practice_detail`;

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');