sql 查询--计算用户会在第二天登录的平均概率

麻辣清汤

于 2023-03-12 15:59:49 发布

阅读量268

点赞数

分类专栏： mysql 文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/weixin_52001949/article/details/129476610

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

~insert 代码在底部~

https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199

在这里插入图片描述

数据对象：一个用户一天做题为一条记录，“第二天还会再来”表示同一个用户在第二天也做了题目，
数据案例：用户A-2022-03-18，用户A-2022-03-19，出现了第二天还会再来
难点：
① 注意去重同一个用户同一天多次做题，这个只作为一条记录，需要同时对（device_id,date）进行去重（存在一个有些不确定的地方：同一个人多次“第二天再来”是否只算一次，是以人为计量，还是以人+日期进行计量？题目是以人+日期计量）
② “第二天data_add(date, interval ‘num’ ‘type’),num表示数字，type表示日期类型，如day、month、year等
③确定再来的记录数量，通过表关联（left join … on …），左表为第一天做题，右表为第二天做题，可以将第二天做题时间-1，此时当设备id，做题日期相同时，如果两表都有数据，则符合“第二天还会再来”,如果两表有一个不存在，则不符合条件

根据日期差为1 ，用户id 连接两表

select  
	a.device_id,a.date,b.device_id,b.date
from 
	question_practice_detail a 
	left JOIN question_practice_detail b
	-- 链接条件 （日期和用户）
	on a.date = b.date-1 and a.device_id = b.device_id

在这里插入图片描述

-没有匹配上的则该记录没有第二天登录

在这里插入图片描述

一天内重复登录去重计算
根据用户和时间进行计量算出留存率

-- 完整代码
select  
	count(distinct b.device_id,b.date)/count(distinct a.device_id,a.date)
from question_practice_detail a 
	left JOIN question_practice_detail b
on a.date = b.date-1 and a.device_id = b.device_id

在这里插入图片描述


drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
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
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
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');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');