beyondman5219

V1

2022/03/12阅读:50主题:简

牛客sql练习2022.3.12

基础部分

DROP TABLE IF EXISTS user_profile;
CREATE TABLE `user_profile` (
`id` INT NOT NULL,
`device_id` INT NOT NULL,
`gender` VARCHAR(14NOT NULL,
`age` INT ,
`university` VARCHAR(32NOT NULL,
`province` VARCHAR(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',NULL,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

SELECT t1.device_id ,t1.gender,t1.age,t1.university FROM user_profile t1;
-- 根据大学分组,查询有相同大学的学校
SELECT t1.university FROM user_profile t1 GROUP BY t1.university HAVING COUNT(t1.university)>1;
-- 查询相同学校的学生信息,并且按设备id倒序
SELECT t2.* FROM user_profile t2 WHERE t2.`university` IN 
(
SELECT t1.university FROM user_profile t1 
 WHERE t1.age IS NOT NULL  
 GROUP BY t1.university 
 HAVING COUNT(t1.university)>1 
)
ORDER BY t2.`device_id` DESC ;

-- 所有北京大学的学生进行用户调研,请你从用户信息表中取出满足条件的数据,结果返回设备id和学校。
SELECT t1.device_id,t1.university  FROM user_profile t1 WHERE t1.university = '北京大学';
-- 24岁以上的用户开展分析,请你取出满足条件的设备ID、性别、年龄、学校。
SELECT t1.device_id ,t1.gender,t1.age,t1.university FROM user_profile t1 WHERE t1.`age`>24;
-- 运营想要针对20岁及以上且23岁及以下的用户开展分析,请你取出满足条件的设备ID、性别、年龄。
SELECT t1.device_id ,t1.gender,t1.age FROM user_profile t1 WHERE t1.age BETWEEN 20 AND 23;
--  题目:现在运营想要查看除复旦大学以外的所有用户明细,请你取出相应数据
SELECT  t1.device_id ,t1.gender,t1.age,t1.university FROM user_profile t1 WHERE t1.university !='复旦大学';
-- 请你取出所有年龄值不为空的用户的设备ID,性别,年龄,学校的信息。
SELECT  t1.device_id ,t1.gender,t1.age,t1.university FROM user_profile t1 WHERE t1.age IS NOT NULL;

高级操作符练习

DROP TABLE IF EXISTS user_profile;
CREATE TABLE `user_profile` (
`id` INT NOT NULL,
`device_id` INT NOT NULL,
`gender` VARCHAR(14NOT NULL,
`age` INT ,
`university` VARCHAR(32NOT NULL,
`gpa` FLOAT,
`active_days_within_30` INT ,
`question_cnt` INT ,
`answer_cnt` INT );
INSERT INTO user_profile VALUES(1,1234,'male',21,'北京大学',3.4,6,10,20);
INSERT INTO user_profile VALUES(2,1235,'male',NULL,'复旦大学',4.0,15,20,60);
INSERT INTO user_profile VALUES(3,1236,'female',20,'北京大学',3.2,3,7,15);
INSERT INTO user_profile VALUES(4,1237,'female',23,'浙江大学',3.6,9,25,32);
INSERT INTO user_profile VALUES(5,1238,'male',25,'山东大学',3.9,25,78,95);
INSERT INTO user_profile VALUES(6,1239,'male',25,'清华大学',3.2,5,16,36);
INSERT INTO user_profile VALUES(7,1240,'male',NULL,'清华大学',3.6,8,26,60);
INSERT INTO user_profile VALUES(8,1241,'female',NULL,'北京理工大学',3.7,12,10,22);

SELECT * FROM user_profile

-- 现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研,请你取出相关数据。
SELECT t1.device_id, t1.gender, t1.age, t1.university, t1.gpa 
FROM user_profile t1 WHERE t1.`gender` = 'male' AND t1.gpa>3.5 ;
-- 现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研
SELECT t1.device_id, t1.gender, t1.age, t1.university, t1.gpa 
FROM user_profile t1 WHERE t1.`university` = '北京大学' OR t1.gpa>3.7 ;
-- 现在运营想要找到学校为北大、复旦和山大的同学进行调研
SELECT t1.device_id, t1.gender, t1.age, t1.university, t1.gpa 
FROM user_profile t1 WHERE t1.`university` IN ('北京大学','复旦大学','山东大学') ;
-- 现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 
-- 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研
SELECT t1.device_id, t1.gender, t1.age, t1.university, t1.gpa 
FROM user_profile t1 WHERE (t1.gpa >3.5 AND t1.university = '山东大学')
 OR (t1.gpa >3.8 AND t1.university = '复旦大学') ;
-- 现在运营想查看所有大学中带有北京的用户的信息(以北京开头的)
SELECT t1.device_id, t1.age, t1.university FROM user_profile t1 WHERE t1.`university` LIKE '北京%'
-- 现在运营想查看所有大学中带有北京的用户的信息(大学中包含北京字符的)
SELECT t1.device_id, t1.age, t1.university FROM user_profile t1 WHERE t1.`university` LIKE '%北京%'
-- 现在运营想查看所有大学已北京**结尾的大学(最后模糊匹配是两个字符)
SELECT t1.device_id, t1.age, t1.university FROM user_profile t1 WHERE t1.`university` LIKE '%北京__'
-- 现在运营想查看所有大学已北京**开头的大学(最后模糊匹配是两个字符)
SELECT t1.device_id, t1.age, t1.university FROM user_profile t1 WHERE t1.`university` LIKE '北京__'
-- 现在运营想查看所有大学已北京**开头的大学(最后模糊匹配是四个个字符)
SELECT t1.device_id, t1.age, t1.university FROM user_profile t1 WHERE t1.`university` LIKE '北京____'
-- 现在运营想查看所有四个汉字且结尾最后是大学的数据行
SELECT t1.device_id, t1.age, t1.university FROM user_profile t1 WHERE t1.`university` LIKE '__大学'
-- 运营想要知道复旦大学学生gpa最高值是多少
SELECT MAX(t1.`gpa`) gpa FROM user_profile t1 WHERE t1.`university` = '复旦大学'  

SELECT gpa FROM 
(SELECT gpa, row_number()
over(PARTITION BY university ORDER BY gpa DESCAS ranking
FROM user_profile WHERE university = '复旦大学')
 AS t WHERE t.ranking = 1;

SELECT ROUND(MAX(gpa),2FROM user_profile WHERE university = '复旦大学'

-- 现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少
SELECT COUNT(t1.device_id) AS male_num , ROUND(AVG(t1.gpa),1AS avg_gpa FROM user_profile t1 WHERE t1.gender = 'male'

-- 请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
-- 查询返回结果需要对性别和学校分组,示例如下,结果保留1位小数,1位小数之后的四舍五入
SELECT t1.gender, t1.university 
COUNT(t1.id) AS user_num
AVG(active_days_within_30) AS avg_active_day
AVG(question_cnt) AS avg_question_cnt
 FROM  user_profile t1 GROUP BY t1.gender , t1.university  
-- 现在运营想查看每个学校用户的平均发贴和回帖情况,寻找低活跃度学校进行重点运营,
-- 请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
-- 结果,请你保留3位小数(系统后台也会自动校正),3位之后四舍五入:
SELECT  t1.university
ROUND(AVG(question_cnt),3AS avg_question_cnt 
ROUND(AVG(answer_cnt),3AS avg_answer_cnt
 FROM user_profile t1 GROUP BY t1.university  
 HAVING avg_question_cnt < 5 OR avg_answer_cnt < 20
 
-- 现在运营想要查看不同大学的用户平均发帖情况,并期望结果按照平均发帖情况进行升序排列
SELECT  t1.university, AVG(question_cnt) AS avg_question_cnt 
FROM user_profile t1 GROUP BY t1.university  ORDER BY avg_question_cnt 

多表查询

DROP TABLE IF EXISTS `user_profile`;
DROP TABLE IF  EXISTS `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` INT NOT NULL,
`gender` VARCHAR(14NOT NULL,
`age` INT ,
`university` VARCHAR(32NOT NULL,
`gpa` FLOAT,
`active_days_within_30` INT
);
CREATE TABLE `question_practice_detail` (
`device_id` INT NOT NULL,
`question_id`INT NOT NULL,
`result` VARCHAR(32NOT NULL
);

INSERT INTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(3214,'male',NULL,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);
INSERT INTO question_practice_detail VALUES(2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(6543,111,'right');
INSERT INTO question_practice_detail VALUES(2315,115,'right');
INSERT INTO question_practice_detail VALUES(2315,116,'right');
INSERT INTO question_practice_detail VALUES(2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(2131,114,'right');
INSERT INTO question_practice_detail VALUES(5432,113,'wrong');

SELECT * FROM `user_profile`;
SELECT * FROM `question_practice_detail`;

-- 现在运营想要查看所有来自浙江大学的用户题目回答明细情况
SELECT device_id, question_id, result FROM question_practice_detail t2 WHERE t2.device_id IN
(
 SELECT t1.`device_id` FROM user_profile t1 WHERE  t1.`university` = '浙江大学'
)
-- 运营想要了解 每个学校 答过题的 用户 平均答题数量情况
/*
分析思路
select 查询结果 [university,'每个人平均答题数量':问题数/设备数(一个设备对应多个题,要去重)]
from 从哪张表中查找数据[两张表联结]
where 查询条件 [无]
group by 分组条件 [university]
*/

SELECT t1.university, COUNT(t2.question_id)/COUNTDISTINCT t2.`device_id`)  
FROM question_practice_detail t2 
LEFT OUTER JOIN user_profile  t1 
ON  t2.device_id = t1.device_id  
GROUP BY t1.university 
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(14NOT NULL,
`age` INT ,
`university` VARCHAR(32NOT 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(32NOT NULL
);
CREATE TABLE `question_detail` (
`id` INT NOT NULL,
`question_id`INT NOT NULL,
`difficult_level` VARCHAR(32NOT 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');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
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');

-- 运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量
-- 请你写一个SQL查询,计算不同学校、不同难度的用户平均答题量,
-- 根据示例,你的查询应返回以下结果(结果在小数点位数保留4位,4位之后四舍五入)
/*
首先对不同学校进行分组,然后再根据不同难度进行分组,这样得到的每一组都是
同一个学校以及同一个难度的学生。接下来就需要如何考虑组织显示结果,每个学
校,每个难度,这个直接写在select即可,主要在于如何计算这个学校这个难度的
用户平均刷提数?可不可以直接用刷题总数除以参加刷题的人数?不可以,因为一
个人有可能会刷多个同一个难度的题,要精准查询参加刷题人数,首先需要对参加
人数进行去重然后再进行求平均数
*/

SELECT t1.university,t3.difficult_level
,ROUND(COUNT(t2.question_id) /  COUNTDISTINCT t2.`device_id`),4
AS avg_answer_cnt FROM user_profile t1
LEFT JOIN question_practice_detail t2 
ON t1.device_id = t2.device_id 
LEFT JOIN question_detail t3 
ON t2.`question_id` = t3.`question_id` 
WHERE t3.difficult_level IS NOT NULL  GROUP BY t1.university,t3.difficult_level ;

-- 示例解答
SELECT university,difficult_level,COUNT(qp.question_id) /COUNT(DISTINCT qp.device_id) avg_answer_cnt
FROM question_practice_detail qp
    LEFT JOIN user_profile u ON qp.device_id=u.device_id
    LEFT JOIN question_detail qd ON qp.question_id=qd.question_id 
GROUP BY university,difficult_level

-- 运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数
-- 左外链接
SELECT 
  t1.university,t3.difficult_level,COUNT(t2.question_id)/ COUNTDISTINCT t2.device_id) 
FROM 
 user_profile t1 LEFT JOIN question_practice_detail t2
 ON t1.device_id = t2.device_id LEFT JOIN question_detail t3
 ON t2.question_id = t3.question_id 
WHERE
 t1.university = '山东大学'  
GROUP BY 
 t3.difficult_level ;
 -- 内连接
SELECT 
  t1.university,t3.difficult_level,COUNT(t2.question_id)/ COUNTDISTINCT t2.device_id) 
FROM 
 user_profile t1 INNER JOIN question_practice_detail t2
 ON t1.device_id = t2.device_id INNER JOIN question_detail t3
 ON t2.question_id = t3.question_id 
WHERE
 t1.university = '山东大学'  
GROUP BY 
 t3.difficult_level ;
 
-- 内连接
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;
    
-- 运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据,
-- 请取出相应结果,结果不去重  先输出学校为山东大学再输出性别为男生的信息
SELECT device_id , gender , age , gpa FROM user_profile t1 WHERE t1.university = '山东大学'
UNION ALL
SELECT device_id,gender,age,gpa FROM user_profile t2 WHERE   t2.gender = 'male';
-- 现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段,分别查看这两个年龄段用户数量
-- 本题注意:age为null 也记为 25岁以下
-- 不符合要求
SELECT ('25岁以下'AS age_cut, (
SELECT  COUNT(device_id)  FROM user_profile t1 WHERE t1.`age`<25 OR t1.`age` IS NULL
)  AS number

-- 示例sql 条件函数
SELECT
IF(age>=25,'25岁及以上','25岁以下' ) AS age_cut,
COUNT(device_id) AS number
FROM user_profile
GROUP BY age_cut

分类:

后端

标签:

后端

作者介绍

beyondman5219
V1