在面试中被问到这样的题目,当时被问蒙了,后来查阅了一下相关资料搞懂了一些,记录一下。
题目:有一堆的用户消费数据,字段为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的后面‘’