SQL 筛选指定列在各组最大值的数据

提示

Hive SQL 教程欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123（备注：sql ）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《Python之光》。

本例我们将利用窗口函数，计算每个分组下的指定列最大值。需求背景是，在一个包含有各个班级成绩的数据表中，我们想查询中在各班级中语文成绩第一名的数据，接下来我们就一起分析完成这个需求。

数据与分析

本需求中 students 表中的数据为：

'''
id|name|class|b_year|gender|math|chinese|english|
--+----+-----+------+------+----+-------+-------+
 1|张涛  |    1|  1950|男     |  66|     77|     88|
 2|王琳  |    2|  2010|女     |  88|     99|     77|
 3|赵丹丹 |    1|  1996|女     |  55|     55|     55|
 4|李成  |    2|  2011|男     |  54|     87|     99|
 5|赵天成 |    3|  2000|男     |  77|     66|     88|
 6|田迪  |    1|  1988|女     |  78|     99|     76|
 7|王卫栋 |    2|  1966|男     |  88|     66|     88|
 8|周平  |    1|  1988|男     |  77|     99|     76|
 9|武明  |    3|  1977|男     |  78|     66|     88|
'''

关键字段为： class 为班级，chinese 是语文。

本需求的解决思路是先按组为语文成绩进行排名，并将这个排名增加到新列（虚拟列，不用真正插入数据库），然后再利用这个排名列进行筛选。

SQL 及解析

按组为语文成绩进行排名就要用到窗口函数，排名序号我们暂时用 row_number()，它可以按照我们的分组要求给每条记录赋一个序号，这个序号不重复。窗口函数分组时我们按 class 班级列进行分组，再按 chinese 语文列进行排序，排序时要指定排序方法，默认是升序（ASC 小的在前），要用 desc 关键字改为降序（大的在前），row_number() 的序号就是按这个排序赋值的。

这一步的代码如下：

select
	id,
	name,
	class,
	chinese,
	row_number() over(partition by class
order by
		chinese desc) as rnbr
from
	students

'''
id|name|class|chinese|rnbr|
--+----+-----+-------+----+
 6|田迪  |    1|     99|   1|
 8|周平  |    1|     99|   2|
 1|张涛  |    1|     77|   3|
 3|赵丹丹 |    1|     55|   4|
 2|王琳  |    2|     99|   1|
 4|李成  |    2|     87|   2|
 7|王卫栋 |    2|     66|   3|
 5|赵天成 |    3|     66|   1|
 9|武明  |    3|     66|   2|
'''

我们看到 rnbr 是我们要增加的序号列，最后我们对这一步产生的数据再加一层进行筛选，用 where 筛选出 rnbr 为 1 的行。

最终代码为：

select
	*
from
(
	select
		id,
		name,
		class,
		chinese,
		row_number() over(partition by class
	order by
			chinese desc) as rnbr
	from
		students
) as t
where
	t.rnbr = 1

'''
id|name|class|chinese|rnbr|
--+----+-----+-------+----+
 6|田迪  |    1|     99|   1|
 2|王琳  |    2|     99|   1|
 5|赵天成 |    3|     66|   1|
'''

也可以利用 with as 语句让代码更加清晰：

-- 对在各班的名次进行标记
with tab as (
select
	id,
	name,
	class,
	chinese,
	row_number() over(partition by class order by
		chinese desc) as rnbr
from
	students
)

-- 筛选
select
	*
from
	tab
where  rnbr = 1

至此，我们完成了需求。

其他

在本需求中，如果对相同成绩考虑并列名次，可以用 rank() 和 dense_rank() 窗口函数，详见本教程相关介绍。

（完）

SQL 筛选指定列在各组最大值的数据

数据与分析

SQL 及解析

其他

相关内容