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的结果)