查看用户在某天刷题后第二天还会再来刷题的平均概率

查看用户在某天刷题后第二天还会再来刷题的平均概率 1、数据准备

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');

2、查询sql

方式一：使用mysql8版本的位移函数

select count(1) / cnt
from (select date, datediff(lead(date, 1, '2021-08-30') over (), date) day, count(1) over () cnt
      from (select distinct device_id,
                            date
            from question_practice_detail) as a) as s
where day = 1;

方式二：使用联表查询

select COUNT(b.device_id) / count(a.device_id) AS avg_ret
from (select distinct device_id, date 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 b.date = date_add(a.date, INTERVAL 1 DAY);