MySQL笔记 —— 牛客网刷题笔记

user_profile表

question_practice_detail表

question_detail表

29.计算用户的平均次日留存率

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

要计算的平均次日留存率，所以要获取每一天的第二天，即

select device_id,
	   date_add(date,INTERVAL 1 day) as addday
from question_practice_detail

然后和原表按照日期和device_id进行连接，按照device_id连接是因为要找到同一个人，两表按照同一个人进行连接后，此时左表的date是这个用户的全部登录日期，右表是这个用户据的全部登录日期加一的天数，按照日期进行连接，如果用户第二天登录了，那么字段值相同就能连接上

记得是以原表为左表，按照left join的方式进行连接

select distinct a.device_id,
    				a.date as date1,
    				b.addday as date2
    from question_practice_detail as a
    left join (select device_id,
               		  date_add(date,INTERVAL 1 day) as addday
               from question_practice_detail) as b
    on a.date=b.addday and a.device_id=b.device_id

运行结果为

分别对date1和date2求取count数量，然后相除即可得出第二天的留存率

另外这里有一个坑，如果在上面的查询语句中，加上一个b.device_id

select distinct a.device_id , a.date as date1 , b.device_id , b.addday as date2

可以正常查询出结果，但是图上的 device_id 和 device_id1 是mysql自动加上去的，为了方便结果的查看。但是把上面的查询语句变成子查询，放进select count(date2)/count(date1) from () 的括号里面后，mysql会认为有两个device_id，无法判别哪个device_id是哪个表，因为之前结果中的device_id1是mysql加上去的，不是自己指定的，这里需要自己分别为a.device_id和b.device_id指定一个别名

34.统计复旦用户8月练题情况

select device_id,
				university,
				count(question_id) as question_cnt,
				sum(if(result='right',1,0)) as right_question_cnt
from (select t1.device_id,
				t1.university,
				t2.question_id,
				t2.result,
				t2.date
from user_profile as t1
left join (select * 
						from question_practice_detail 
						where month(date)=08
					) as t2
on t1.device_id=t2.device_id
where t1.university='复旦大学'
order by t1.device_id) t
group by device_id;

分析：
首先扩展一下如何获取日期格式数据的年、月、日

select date,year(date),month(date),day(date) from question_practice_detail

因此提取question_practice_detail表中八月的练题情况

select * from question_practice_detail where month(date)=08

然后用user_profile表和上面提取的结果进行关联

select t1.device_id,
				t1.university,
				t2.question_id,
				t2.result,
				t2.date
from user_profile as t1
left join (select * 
		   from question_practice_detail 
		   where month(date)=08
		  ) as t2
on t1.device_id=t2.device_id
where t1.university='复旦大学'
order by t1.device_id

然后再对上面的这个结果，统计做题总数和正确次数即可

35.浙大不同难度题目的正确率

select difficult_level,
		(sum(if(t2.result='right',1,0)) / count(t2.question_id)) as correct_rate
from user_profile as t1,question_practice_detail as t2,question_detail as t3
where t1.university='浙江大学' 
		and t1.device_id = t2.device_id
		and t2.question_id = t3.question_id
group by difficult_level
order by correct_rate

这里就是先三表关联，关联的时候可以这样放在一起关联
然后是按照题目难度difficult_level进行分组，分组后分别统计当前分组内的全部题目数量和答对的题目要数量。统计题目数量用count即可，字段result分为正确和错误两种情况，所以要用 if 判断一下，正确返回1，错误返回0，然后求和得到正确的结果数
再用正确的结果数除以总的题目数，即可得到最终的正确率

注意事项：这里不能用

select 
		count(t2.question_id) as question_cnt,
		sum(if(t2.result='right',1,0)) as right_cnt,
		question_cnt / right_cnt

上面的这种写法是错误写法！因为这样mysql无法识别question_cnt和right_cnt，结果就会报错

补充事项：这里不用sum() / count() 也可以，直接 avg(if(t2.result=‘right’,1,0)) 也能得到正确率，但是这样小数位少很多（最右边correct字段即为avg的结果）