〇、概述
1、功能概述
高级查询:聚合函数(四舍五入)、分组过滤、排序、
2、建表语句
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);
INSERT INTO user_profile VALUES(1,2234,'male',21,'北京大学',3.2);
INSERT INTO user_profile VALUES(2,2235,'male',null,'复旦大学',3.8);
INSERT INTO user_profile VALUES(3,2236,'female',20,'复旦大学',3.5);
INSERT INTO user_profile VALUES(4,2237,'female',23,'浙江大学',3.3);
INSERT INTO user_profile VALUES(5,2238,'male',25,'复旦大学',3.1);
INSERT INTO user_profile VALUES(6,2239,'male',25,'北京大学',3.6);
INSERT INTO user_profile VALUES(7,2240,'male',null,'清华大学',3.3);
INSERT INTO user_profile VALUES(8,2241,'female',null,'北京大学',3.7);
3、表中数据
一、计算函数(聚合函数)
1、查找GPA最高的值
复旦大学学生gpa最高值是多少
SELECT
MAX(gpa)
FROM user_profile
WHERE university='复旦大学';
2、计算男生人数以及平均GPA
SELECT
COUNT(*) AS male_num,
ROUND(AVG(gpa),1) AS ave_gpa
FROM user_profile
WHERE gender='male';
二、分组查询
1、分组计算【四舍五入是ROUND】
分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
SELECT
gender,
university,
COUNT(*) 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 university, gender;
2、分组过滤
取出平均发贴数低于5的学校或平均回帖数小于20的学校。
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 ASC;