【MySQL日常刷题记录】牛客-非技术快速入门（二）

 SELECT 
   gender,
   university,
   COUNT(device_id) AS user_num,
   ROUND(AVG(active_days_within_30),1) AS avg_active_day,
   ROUND(AVG(question_cnt),1) AS avg_question_cnt
 FROM user_profile
 GROUP BY gender,university;

2 分组过滤练习题

 SELECT 
     university, 
     ROUND(AVG(question_cnt),3) AS avg_question_cnt,
     ROUND(AVG(answer_cnt),3) AS avg_answer_cnt
 FROM user_profile
 GROUP BY university
 HAVING avg_question_cnt<5 OR avg_answer_cnt<20;

3 分组排序练习题

 SELECT university,ROUND(AVG(question_cnt),4) AS avg_question_cnt
 FROM user_profile
 GROUP BY university
 ORDER BY avg_question_cnt;

多表查询

子查询

浙江大学用户题目回答情况

 # 子查询语句的写法
 SELECT
     device_id,
     question_id,
     result
 FROM question_practice_detail
 WHERE device_id IN 
     (SELECT device_id 
     FROM user_profile 
     WHERE university ='浙江大学')
 ORDER BY question_id;  
              
 # 内连接的写法
 SELECT q.device_id,q.question_id,q.result
 FROM user_profile3 AS u INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id
 WHERE u.university = '浙江大学'
 ORDER BY q.question_id;   
           
 
 SELECT *
 FROM user_profile3 UNION ALL question_practice_detail 
 # 此处不可以用UNION ALL并集连接 ，并集连接仅用于表结构相同的纵向扩展

知识点总结：

SELECT 语句是 SQL 的查询。我们迄今为止所看到的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。 SQL 还允许创建子查询（subquery），即嵌套在其他查询中的查询。

在 SELECT 语句中，子查询总是从内向外处理。在处理上面的 SELECT 语句时，DBMS实际上执行了两个操作。

首先，它执行下面的查询：

SELECT device_id FROM user_active WHERE university ='浙江大学'

这个查询可以返回所有的浙江大学用户的device_id，然后，这些值以IN 操作符要求的逗号分隔的格式传递给外部查询的 WHERE 子句外部查询变成：

SELECT cust_id FROM orders WHERE order_num IN (5432，,2131) ，

通过这样的方式，就可以得到我们想要的结果，返回所有浙江大学。

链接查询

1 统计每个学校的答过题的用户的平均答题数

 SELECT 
     u.university,
     ROUND(COUNT(q.question_id)/COUNT(DISTINCT q.device_id),4) AS avg_answer_cnt
 FROM 
     user_profile AS u INNER JOIN question_practice_detail AS q
     ON u.device_id = q.device_id
 GROUP BY u.university
 ORDER BY u.university;
 
 '''
思路：
 第一，每个学校，涉及到利用GROUP BY分组实现；
 第二，用户平均答题数=所有用户答题总数/所有用户数，分母所有用户涉及到COUNT(DISTINCT ...)对登录设备账号进行剔重【由于存在一个设备账号多次登录的情况】，分子所有用户答题总数涉及COUNT()对question_id进行计数，不需要剔重；
 第三，需要对两个表进行联结FROM ...JOIN...ON...
'''

2 统计每个学校各难度的用户平均刷题数

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

3 统计每个用户的平均刷题数

 SELECT 
     t1.university,
     t3.difficult_level,
     COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
 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
     t3.difficult_level;

组合查询

查询山东大学或者性别为男生的信息

# 注意题目要求：“分别查看” “结果不去重”
 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'
 
 # 尽量不用这种方式
 SELECT device_id,gender,age,gpa
 FROM user_profile
 WHERE gender = 'male' OR university = '山东大学';
 
'''
此处不用WHERE OR 的原因：
 第一or去重，
 第二实际应尽量避免在where子句中使用or来连接条件。
使用or可能会使索引失效，从而全表扫描。
mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效
'''

知识点总结：
多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句。 但是，SQL 也 允许执行多个查询（多条 SELECT 语句），并将结果作为一 个查询结果集返回。这些组合查询通常称为并（union）或复合查询 （compound query）。

主要有两种情况需要使用组合查询：
在一个查询中从不同的表返回结构数据；
对一个表执行多个查询，按一个查询返回数据。

Union
可用 UNION 操作符来组合数条 SQL 查询。利用 UNION，可给出多条 SELECT 语句，将它们的结果组合成一个结果集。
使用 UNION 很简单，所要做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION。但需要注意的是UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过， 各个列不需要以相同的次序列出）

Union all
UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一 条 SELECT 语 句中使用多个 WHERE 子句条件一样，使用 UNION 时，重复的行会被自动取消。 这是UNION 的默认行为，如果想不去重的返回所有的匹配行，可使用 UNION ALL而不是UNION。

必会的常用函数

条件函数

1 计算25岁以上和以下的用户数量

 # 方法1 CASE WHEN函数 
 SELECT
     CASE
         WHEN age < 25
         OR age IS NULL THEN '25岁以下'
         WHEN age >= 25 THEN '25岁及以上'
     END AS age_cut,              # 写法一：别名加AS
     COUNT(*) AS number
 FROM
     user_profile
 GROUP BY
     age_cut;
     
     
     
 SELECT
     CASE
         WHEN age < 25
         OR age IS NULL THEN '25岁以下'
         WHEN age >= 25 THEN '25岁及以上'
     END age_cut,                # 写法二：别名不加AS
     COUNT(*) number
 FROM
     user_profile
 GROUP BY
     age_cut;                                
 

# 方法2 IF函数
 SELECT
     IF(age>=25,'25岁及以上','25岁以下') AS age_cut,
     COUNT(device_id) AS number
 FROM
     user_profile
 GROUP BY
     age_cut;        
                        
     
 # 方法3 通过UNION连接两种选出的表（联合表）   
 select 
     "25岁以下" as age_cut, 
     count(device_id) as number
 from 
     user_profile
 where 
     (age < 25)&nbs***bsp;(age is null )
  
 union all 
  
 select 
     "25岁及以上" as age_cut, 
     count(device_id) as number
 from 
     user_profile
 where 
     age >= 25；

2 查看不同年龄段的用户明细

 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_cut
 FROM user_profile;

日期函数

1 计算用户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
 
 '''
 限定条件：2021年8月，写法有很多种，
 比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, "%Y-%m")="202108"
 每天：按天分组group by date
 题目数量：count(question_id)
 '''

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

文本函数

1 统计每种性别的人数

# SUBSTRING_INDEX的写法 
 SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(device_id) number
 FROM user_submit 
 GROUP BY gender;          
 

 # LIKE的写法
 SELECT IF(profile LIKE '%female','female','male') gender,COUNT(device_id) number
 FROM user_submit
 GROUP BY gender;         
 
 # 尽量不要用通配符* count(*)
 # 如果有同一个设备录入了不同的profile信息，还需要去重 用COUNT(device_id)

图片说明

2 提取博客URL中的用户名

 SELECT device_id,SUBSTRING_INDEX(blog_url,'/',-1) AS user_name
 FROM user_submit

3 截取出年龄

 SELECT 
     SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1) AS age,
     COUNT(device_id) AS number
 FROM user_submit
 GROUP BY age;

窗口函数

找出每个学校GPA最低的同学

'''
 SELECT device_id,university,min(gpa) 
 FROM user_profile 
 GROUP BY university; 
 因为学校与学生是一对多的关系，如果仅用min求出gpa最低的学生，查询结果中的id与学生不一定是对应的关系，因此此方法错误。
 '''
 
 #方法一：将表a的device_id,university,gpa和表b的university,min(gpa)连接起来找
 SELECT a.device_id,a.university,a.gpa 
 FROM user_profile a
   JOIN 
     (SELECT university,min(gpa) gpa 
      FROM user_profile 
      GROUP BY university) b
   on a.university=b.university and a.gpa=b.gpa
 ORDER BY university;                                # JOIN ON
 
 
 select a.device_id, a.university, a.gpa
 from user_profile a
   right join
     (select university, min(gpa) as gpa
     from user_profile
     group by university) as b
   on a.university=b.university and a.gpa=b.gpa     # RIGHT JOIN ON
 order by a.university                
 
 #  方法二：窗口函数
 select device_id, university, gpa
 from 
     (select *,
     row_number() over (partition by university order by gpa) as rn
     from user_profile)  as univ_min
 where rn=1
 order by university
 # 小提示：要找最大最小值对应的那条数据的其他字段信息，通常可以联系一下窗口函数取where rk=1。（直接max()或者min()查询到的其他字段是不对应的）
 
 # 方法三： 子查询
 select device_id,university,gpa
 from user_profile
 where (university,gpa) in 
   (select university,min(gpa) 
    from user_profile 
    group by university)
 order by university

综合练习

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

select
 device_id,
 university,
 sum(if(date = '2021-08',question_cnt,0)) question_cnt,
 sum(if(date = '2021-08',right_question_cnt,0)) right_question_cnt
  from (
 SELECT
  USER.university,
   USER.device_id,
  substring(de.date,1,7) as date,
  count( de.question_id ) as question_cnt,
  sum(IF( result = 'right', 1, 0 )) as right_question_cnt
 FROM
  user_profile
  USER LEFT JOIN question_practice_detail de ON USER.device_id = de.device_id 
 WHERE
  USER.university = '复旦大学' 
 GROUP BY
  USER.device_id,substring(de.date,1,7)
 ) t 
 group by device_id 
 
 SELECT
     u.device_id,
     university,
     SUM(IF(result IS NOT NULL, 1, 0)) AS questino_cnt,
     SUM(IF(result = "right", 1, 0)) AS right_question_cnt
 FROM
     user_profile u
     LEFT JOIN question_practice_detail q ON u.device_id = q.device_id
     AND MONTH(q.`date`) = "08"
 WHERE
     university = "复旦大学"
 GROUP BY
     u.device_id;
     
     
 select
     up.device_id,
     university,
     count(upd.device_id) question_cnt,
     count(
         case
             when result = 'right' then 1
             else null
         end
     ) right_question_cnt
 from
     user_profile as up
     left join question_practice_detail as upd on upd.device_id = up.device_id
 where
     university = '复旦大学'
     and (
         month (date) = '08'
         or month (date) is null
     )
 group by
     up.device_id;
     
 select
     u.device_id
     ,u.university
     ,sum(case when q.result is null then 0 else 1 end)  as question_cnt
     ,sum(case when q.result='right' then 1 else 0 end)  as right_question_cnt
 from user_profile as u
 left join question_practice_detail as q 
 on u.device_id =q.device_id
 where u.university = '复旦大学' 
 and (month(q.date) =8 or month(q.date) is null)
 group by u.device_id

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

 select difficult_level,
      avg(if(qpd.result='right', 1, 0)) as correct_rate
 #    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
 #    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
 from user_profile as up
  
 inner join question_practice_detail as qpd
     on up.device_id = qpd.device_id
  
 inner join question_detail as qd
     on qd.question_id = qpd.question_id
  
 where up.university = '浙江大学'
 group by qd.difficult_level
 order by correct_rate asc;

3 21年8月份练题总数

 select
     count(distinct device_id) as did_cnt,
     count(question_id) as question_cnt
 from question_practice_detail
 where date like "2021-08%"