【SQL基础】【记住重命名】高级查询：聚合函数（四舍五入）、分组过滤、排序、

〇、概述

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;