目录
题目一(计算25岁以上和以下的用户数量):
代码实现方法一:
select
(case when (age< 25 or age is null) then '25岁以下' else '25岁及以上' end) as age_cut,
count(*) as number
from user_profile
group by age_cut;
代码实现方法二:
select
if(age<25 or age is null,'25岁以下','25岁及以上') as age_cut,
count(*) as number
from user_profile
group by age_cut;
MySQL中关键字的执行顺序:
1. from
2. where
3. group by
4. having
5. select
6. order by
在group by执行的时候,select还没执行,它怎么会知道age_cut是指什么呢?
因为MySQL对查询做了增强没有严格遵循SQL的执行顺序,where后面不能用select中的别名,但是group by ,order by都是可以的,Oracle数据库严格遵循了SQL执行顺序在Oracle里group by是不能引用select里的别名的。
count(具体字段):表示统计分组后该字段下所有不为NULL的元素的总数。
count(*):统计分组后一个组的总行数。(包含某一字段下为NULL的元素)
count函数中,重复的数据会多次计算,如果要去重,就得搭配 distinct 使用,即count(distinct xxxxxx)。
代码思路:
我们要按 '25以下' 和 '25及以上' 分组,因此就要先用if语句或case语句将用户划分为 '25以下' 和 '25及以上' 两个年龄段。
题目指路:计算25岁以上和以下的用户数量_牛客题霸_牛客网 (nowcoder.com)
题目二(查看不同年龄段的用户明细):
代码实现方法:
select device_id,gender,
case
when (age>=20 and age<=24)
then '20-24岁'
when age>=25
then '25岁及以上'
when age<20
then '20及以下'
else '其他' end age_cut
from user_profile;
# 这里没用到分组函数不需要分组 group by age_cut;
题目指路:查看不同年龄段的用户明细_牛客题霸_牛客网 (nowcoder.com)
题目三(计算用户的平均次日留存率):
代码实现:
SELECT
COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
(SELECT DISTINCT device_id, date FROM question_practice_detail)as q1 # q1是包含全部成员的
LEFT JOIN
(SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2 # q2是符合on条件的
# 如果不加lert,那么得到的全是满足on条件的数据
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day);
为什么要用左连接:
没用左连接:
mysql> SELECT
-> q1.device_id,q2.device_id # COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
-> FROM
-> (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1 # q1是包含全部成员的
-> JOIN
-> (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2 # q2是符合on条件的
-> # 如果不加lert,那么得到的全是满足on条件的数据
-> ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day);
+-----------+-----------+
| device_id | device_id |
+-----------+-----------+
| 2315 | 2315 |
| 2315 | 2315 |
| 3214 | 3214 |
+-----------+-----------+
3 rows in set (0.00 sec)
用了左连接:
mysql> SELECT
-> q1.device_id,q2.device_id # COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
-> FROM
-> (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1 # q1是包含全部成员的
-> LEFT JOIN
-> (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2 # q2是符合on条件的
-> # 如果不加lert,那么得到的全是满足on条件的数据
-> ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day);
+-----------+-----------+
| device_id | device_id |
+-----------+-----------+
| 2138 | NULL |
| 3214 | NULL |
| 3214 | NULL |
| 6543 | NULL |
| 2315 | 2315 |
| 2315 | 2315 |
| 2315 | NULL |
| 3214 | 3214 |
| 3214 | NULL |
| 3214 | NULL |
+-----------+-----------+
10 rows in set (0.00 sec)
如果没用左连接的话,COUNT(q2.device_id) / COUNT(q1.device_id) 永远等于1。
题目指路:计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com)
题目四(统计每种性别的人数):
代码实现方法一:
select case when profile like '%,male' then 'male' when profile like '%,female' then 'female' end gender
,count(*)
from user_submit
group by gender;
代码实现方法二:
select case substr(profile,15) when 'male' then 'male' when 'female' then 'female' end gender
,count(*)
from user_submit
group by gender;
代码实现方法三:
select substr(profile,15) gender,count(*) number
from user_submit
where substr(profile,15)='male'
union
select substr(profile,15) gender,count(*) number
from user_submit
where substr(profile,15)='female';
代码实现方法四:
select "male" as gender,count(profile) from user_submit where substr(profile,15)='male'
union
select "female" as gender,count(profile) from user_submit where substr(profile,15)='female';
题目指路:统计每种性别的人数_牛客题霸_牛客网 (nowcoder.com)
题目五(找出每个学校GPA最低的同学):
错误的实现方法一:
select device_id,university,round(min(gpa),4) gpa
from user_profile
group by university;
错误,该SQL语句块左右列的长度不对。
按学校分类后,查询device_id显示的是该校所有学生的id
而按学校分类后,查询round(min(gpa),4)显示的仅仅是最小的那一个gpa
错误的实现方法二:
select device_id,university,gpa
from user_profile
where gpa in (select min(gpa) from user_profile group by university)
order by university;
在in子查询中没有加上university, 如果不加university的话,
假如存在以下情况:
1237 浙江大学 3.600 (浙江大学最低)
1239 清华大学 3.200 (清华大学最低)
1240 清华大学 3.600
那么会输出两个清华大学,因为浙江大学最低是3.6而清华大学正好有3.6这个gpa。
代码实现方法一:
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;
这样先用where筛选出所有最小的gpa及其对应的同学再进行打印结果,则打印的只会是gpa最小的那个同学的id。
一定要加university, 如果不加university的话,会输出两个清华大学!!!
in的基本语法是:
where value in (value1,value2,value3,…)
但除此之外,in后面的括号内还可以跟查询语句,如:
where (gpa) in (select min(gpa) from user_profile)
这被称为in的子查询。
in的子查询还可以查询多个语句,但多个语句必须一一对应,不能多,不能少,顺序不能换。
where (university,gpa) in (select university,min(gpa) from user_profile)
代码实现方法二:
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;
使用内连接将分数最低人连接起来在一张表中,这样查询device_id查询的就是内循环得到的那张表,就不会把所有的device_id都查询出来。
注意,on语句中的a.university=b.university不可省,因为如果那样的话就会把不同学校相同gpa看作是匹配的了。
题目指路:找出每个学校GPA最低的同学_牛客题霸_牛客网 (nowcoder.com)
题目六:(统计复旦用户8月练题情况):
代码实现方法:
select u.device_id,u.university,
count(question_id) question_cnt, sum(if(q.result='right', 1, 0)) right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id=q.device_id
where university='复旦大学' and substr(date,7,1)=8
group by device_id;
# 练习的总题目数指的是回答的总次数,不需要去重
注意:
分组函数里不能加条件,即不可以通过 count(result='right') 来统计出答题正确的数量。
分组函数不能用在where中。
分组函数不能嵌套使用。
启示:
想通过 count(某一字段=xxxx) 这种方式来获取个数的话可以使用 sum(if(q.result='right', 1, 0)) 的形式获取。