最近听小伙伴说了一个这样一个问题:学生表有两列姓名,分数,求张三在班级的排名。(注:学生姓名没有重复,分数可以重复,同分数的人排名一样)
样例数据:
student | score |
---|---|
张三 | 90 |
李四 | 100 |
王五 | 80 |
王小明 | 80 |
李小鹏 | 70 |
据说万能的知识库有一个这样的方法:
我试验了一下,未能成功。
方法如下:
select student,score,(select count(score)+1 as rank
from aa b
where b.score > a.score
)AS rank
from aa a
order by score desc
若这样运行,会报错:
Error: org.apache.spark.sql.AnalysisException: Correlated column is not allowed in a non-equality predicate:
这里不可以是非等的判定条件,所以我就改了一下条件,变成等于,像如下这样:
select student,score,(select count(score)+1 as rank
from aa b
where b.score = a.score
)AS rank
from aa a
order by score desc
结果为:
student | score | rank |
---|---|---|
李四 | 100 | 2 |
张三 | 90 | 2 |
王五 | 80 | 3 |
王小明 | 80 | 3 |
李小鹏 | 70 | 2 |
这样就达不到效果了。(不过也有可能是我hive sql不支持这种操作,SqlServer,mysql有可能可以哦~)
所以,我就想了一种更加简单的方法,在hive sql也可以使用:
select student,
score,
dense_rank()OVER(order by score desc) as rank
from aa
结果:
student | score | rank |
---|---|---|
李四 | 100 | 1 |
张三 | 90 | 2 |
王五 | 80 | 3 |
王小明 | 80 | 3 |
李小鹏 | 70 | 4 |
所以,要取张三的成绩,可直接:
select student,
score,
dense_rank()OVER(order by score desc) as rank
from aa
where student = '张三'
以上是我尝试的一个方式,不过肯定还有别的方法,欢迎大家提出建议哦~