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

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

题目：有一堆的用户消费数据，字段为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的后面‘’