【今日刷题】SQL33 截取出年龄

题目

• 找出每个学校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

select语句后不接 device_id的执行结果

• 可以利用窗口函数来解决这个问题，关于窗口函数的介绍可以参考视频：【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

执行结果：

执行结果