在数据中选取满足条件的前几名

在面试中被问到这样的题目,当时被问蒙了,后来查阅了一下相关资料搞懂了一些,记录一下。

题目:有一堆的用户消费数据,字段为id、城市、性别、消费总额等,要求选取每个城市消费总额的top n。面试官提示,可以用python,也可以用任何sql语言。

这里记录的是oracle的做法。查看表的信息:

select * from table;

选取单个城市的消费总额 top n

选取gz城市的消费总额top 2:
select * from (select * from table where city=’gz’ order by amount desc) where rownum<3;

选取bj城市的消费总额top 2:
select * from (select * from table where city=’bj’ order by amount desc) where rownum<3;

选取所有城市的消费总额 top n

使用rank() over 函数,这个函数可以为每个元组定一个“排名”,所以可以作为一个属性(rank() over as num)。

非rank over方法

select userid,city,amount from table a where( select count(*) from table b where a.city = b.city and b.amount > a.amount) <4 order by a.city,a.amount desc;

如果要最少的top4 则改成 b.amount <a.amount 注意这里的符号是 <,下一节rank() over方法对应的符号则是 <=。

如果是有消费总额重复的情况呢?我们增加两条来自广州的土豪的记录,这样广州的前3名的消费总额都是5100。为了验证这种方法能不能处理总额重复的情况,我们选取top2:

rank() over方法

rank() over(partition by 按什么划分 order by 按什么排序)

我们按city划分,按amount排序,所以我们通过以下语句获取top 4:

sql> select * from (select userid, city, amount, rank() over(partition by city order by amount desc) as num from table) where num<=4;

如果是有消费总额重复的情况呢?

我们同样通过rank() over函数来获取top 2看看:

select * from (select userid, city, amount, rank() over(partition by city order by amount desc) as num from table) where num<=2;

dense_rank() over

用rank() over获取top4,可以得到这样的结果:

可以看到,此时前三名是并列第一,所以他们的排名都是1,而第四名的排名为4。若使用dense_rank() over(稠密的排名):

可以看到,前三名依然是并列第一,而第四名的排名为2,也就是说,他们的排名是稠密的(连续)。

一些错误做法

这样只能返回全局的排名,不能在城市内排名
select userid,city,amount from table where city in (select distinct city from table) group by city,amount,userid order by amount desc;

select 的属性要在group by中,也不能select *

sex不在group by中,会报错

order by的属性要在group by中,不然会报错,而且order by要放在group by的后面‘’

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐