刷牛ke的第二天2024.1.25

第二十三题统计每个学校各难度的用户平均刷题

select university,
difficult_level,
round(count(qpd.question_id)/count(distinct qpd.device_id),4) as avg_answer_cnt
from question_practice_detail as qpd

left join user_profile as up 
on up.device_id = qpd.device_id

left join question_detail as qd
on qd.question_id =qpd.question_id
 
group by university,difficult_level

university和diffivult_level都是在qpd表中，avg_answer_cnt是后两个表连接后计算的结果，三个数据需要三表连接才能够查询，用left join多表连接进行连接。左连接不管怎么样，左表都是完整返回的当只有一个条件a.id=b.id的时候：左连接就是相当于左边一条数据，匹配右边表的所有行，满足on后面的第一个条件a.id=b.id的进行返回。

---

第二十四题统计每个用户的平均刷题

select t1.university,
t3.difficult_level,
round(count(t2.result)/count(distinct t1.device_id),4) as avg_answer_cnt
 from question_practice_detail as t2
 left join user_profile as t1
 on t1.device_id = t2.device_id
 left join question_detail as t3 
 on t3.question_id = t2.question_id
 group by t1.university,t3.difficult_level
 having t1.university = '山东大学';

最后一张表也是要连接三张表，但是最后需要筛选一下，只需要用group by 和having 来进行筛选。

---

第二十五题查找山东大学或性别为男的信息

select device_id,gender,age,gpa
from user_profile 
where university='山东大学' 

union all

select device_id,gender,age,gpa
from user_profile 
where gender = 'male';

主要考察的是不去重 union all，union all是对两个结果集进行并集操作，包括重复行，不进行排序。

---

第二十六题计算25岁以上和以下的用户数量

select 
 (case
        when age>=25 then '25岁及以上'
        else '25岁以下'end) as age_cut,
        count(*) as number
from user_profile 
group by age_cut

 利用case来对age_cut里的数据进行赋值， case when then else end 可以理解为java的if-else if -else。可以理解为流程控制语句或条件控制语句。可以实现资料获取的时候，可以更多的条件和自定义逻辑。when可以写好多个，else意思为其他类型。

---

第二十七题查看不同年龄段的用户明细

select device_id,gender,
 case
        when age>=25 then'25岁及以上'
        when age>=20 then'20-24岁'
        when age<20  then'20岁以下'
        else '其他'
    end as age_cnt
    from user_profile
 

用casewhen来进行划分。

---

第二十八题计算用户8月每天的练题数量

select 
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date) =8 and year(date)=2021
group by date

用day来筛选时间日期，用where来筛选月和年。要理解day函数的原理。

---

第二十九题计算用户的平均次日保存率

select 
    count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
    from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1;

思路：平均次日留存率=次日留存用户数/对应的当日用户数，利用left outer join对表进行自联结，方便后续的，datediff来找出相差一天，达成桥梁作用。分母count(distinct q1.device_id,q1.date) 通过DISTINCT 对当日的device_id，date进行双项剔重，利用COUNT()统计用户数；同样的分子count(distinct q2.device_id,q2.date)对次日的device_id，date进行双项剔重。

---

第三十题统计每种性别的人数

select 
    if(profile like '%female','female','male') as gender,count(*) as number
    from user_submit
    group by gender;

利用like来对profile中的数据进行提取，LIKE '%female','female','male'，因为性别就是非男即女，可以用like中这个形式来进行布尔判断从而赋值。

---

第三十一题提取博客url中的用户名

select device_id,
SUBSTRING_INDEX(blog_url,"/",-1) as user_name
from user_submit

substring_index来截取blog_url是要截取的对象，“”里放的是截取的符号，-1是倒数第一个就开始截取。

---

第三十二题截取年龄

select substring_index(substring_index(profile,',',3),',',-1) as age,
count(device_id) as number
from user_submit
group by age

也是利用substring_index来截取数据，截取的东西在中间，两个来截取。

---

第三十三题找出每个学校gpa最低的同学

select
    device_id,
    university,
    gpa
from (select
          device_id,
          university,
          gpa,
          row_number() over(partition by university order by gpa) as ranking
      from user_profile) as t
where ranking=1

主要考察开窗函数，row_number() over(partition by 分组列 order by 排序列 desc)来进行排序。

代码中的ranking为以学校的排序。

---