题目
- 找出每个学校GPA最低的同学
- 题目描述:现在运营想要找到每个学校gpa最低的同学来做调研,请你取出每个学校的最低gpa。
-
示例数据如下图:
-
返回结果:
分析
- 最直观的想法是利用聚合函数min()与group by语句对university字段进行分组,代码如下:
select
device_id,university,min(gpa) as gpa
from
user_profile
group by
university
- 事实上以上代码会报错:
程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_profile.device_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
- 原因是由于MySQL的sql_mode设置为only_full_group_by,而查询中的device_id一列不在GROUP BY子句中,也没有在聚合函数中,所以MySQL不知道如何处理这device_id列。如果代码中select语句后不接 device_id将不会报错,但执行结果将不包含device_id一列。执行结果如下图:
select
university,min(gpa) as gpa
from
user_profile
group by
university
- 可以利用窗口函数来解决这个问题,关于窗口函数的介绍可以参考视频:【MySQL编程思想】第43节 窗口函数。简单来说利用聚合函数,只会展示分组聚合后的结果,数据表原有的信息不会显示,但是利用窗口函数,可以在原数据表后面新增一列,更为直观的展示聚合结果,方便与原数据做对比。以下为窗口函数示意代码:
select
device_id,university,gpa,min(gpa) over(partition by university) as min_gpa
from
user_profile
- 以上代码输出的最后一列,相当于对数据按照university分组求得最小值之后,根据元组数据所在分组将结果添加为新的一列。比如对于第一行2138北京大学,他的gpa是3.400,通过最后一列,我们可以知道北京大学最低的gpa是3.200。
- 利用窗口函数,我们可以很方便的完成题目要求,思路如下:利用窗口函数求出按university分组的最小值min_gpa,之后筛选出gpa一列与min_gpa一列值相同的元组数据,投影其device_id、university与gpa即可,代码如下:
select
device_id,university,min_gpa
from
(select
device_id,university,gpa,min(gpa) over(partition by university) as min_gpa
from
user_profile) as up
where
up.gpa = up.min_gpa
执行结果: