SQL(入门实战04)

1. 统计每种性别的人数

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

示例：user_submit

根据示例，你的查询应返回以下结果 ：

示例1

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');

输出

male|2
female|5

题解

获取profile中性别
1.用substring_index(profile,',',-1)获取性别
2.用case 函数和find_in_set()函数
3.用like模糊查找

方式一：
SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

方式二： 最优
select
 case 
 when find_in_set("male",profile)  then 'male' 
 else  'female' 
 end
 as  gender, count(*) number
from user_submit 
group by gender

方式三：
SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

2. 提取博客URL中的用户名

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

示例：user_submit

根据示例，你的查询应返回以下结果：

示例1：

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');

题解

提取blog_url中
1.replace()函数进行替换
2.trim()函数进行删除
3.substring_index()函数进行分割

方式一：
select
device_id, replace(blog_url,'http:/url/','') as user_name
from user_submit

方式二：
select
device_id, trim('http:/url/' from blog_url) as user_name
from user_submit


方式三：
select device_id ,substring_index(blog_url,'/',-1) as user_name
from user_submit

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

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

示例：user_profile

根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：

示例1：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

输出：

6543|北京大学|3.200
4321|复旦大学|3.600
2131|山东大学|3.300
2315|浙江大学|3.600

题解

思路：
1.获取每个学校的最低分数 
select university,min(gpa) from user_profile group by university
2.根据university,gpa获取每个学校的最低分数,得到device_id

方式一：最优
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

方式二： 用右连接显示每个学校的最低分数
select t1.device_id, t1.university, t1.gpa
from user_profile t1
right join
(
select  university, min(gpa) as gpa
from user_profile
group by  university
)t2
on t1.university =t2.university and t1.gpa=t2.gpa
order by t1.university

方式三： 如果学校有两个学生都为最低分数，rank()会显示两个，row_number()只会显示一个
select device_id,university ,gpa
from (
	select *,
	rank() over (partition by university  order by gpa)as run
-- row_number() over (partition by university  order by gpa)as run
	from user_profile
)as t1
where run =1
order by university 

拓展

   row_number() over()分组排序功能
   在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于
   where  group by  order by 的执行。
   partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，
   它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，
   而聚合函数一般只有一个反映统计值的记录。

	使用ROW_NUMBER删除重复数据 
---假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

示例：

例如：employee,根据部门分组排序。

 SELECT 
 empno,
 WORKDEPT,
 SALARY,
 Row_Number() OVER (partition by  workdept ORDER BY salary desc) rank 
 FROM employee 

rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

select 
workdept,
salary,
rank() over(partition by workdept order by salary) as dense_rank_order
from emp 
order by workdept; 

dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的

select 
workdept,
salary,
dense_rank() over(partition by workdept order by salary) as dense_rank_order from emp 
order by workdept; 

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

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

示例：用户信息表user_profile

示例：question_practice_detail

根据示例，你的查询应返回以下结果：

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出

3214|复旦大学|3|0
4321|复旦大学|0|0

题解

思路：
1.判断是复旦大学，8月份
2.获取总题目count(question_id)
3.用sum(if())判断，因为要判断题目是否答题正确,是一个累计的过程。
4.按列聚合：需要输出每个用户的统计结果，因此加上group by up.device_id

方式一：
select 
	t1.device_id,
	t1.university,
	count(question_id) as  question_cnt,
	sum(if(result='right',1,0)) as right_question_cnt
from user_profile t1
left join question_practice_detail t2
on t1.device_id=t2.device_id and month(t2.date)=8
where t1.university='复旦大学' 
group by t1.device_id

方式二：
select
    u1.device_id,u1.university,count(q.question_id),
    sum(case when q.result='right' then 1 else 0 end )
    from user_profile u1
    left join question_practice_detail q
    on u1.device_id=q.device_id
    WHERE
    u1.university='复旦大学'
    and
    (month(q.date) =8 or month(q.date) is null)
    group by u1.device_id