在数据中选取满足条件的前几名
在面试中被问到这样的题目，当时被问蒙了，后来查阅了一下相关资料搞懂了一些，记录一下。
题目：有一堆的用户消费数据，字段为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的后面‘’
python 做法
假设已经用pandas处理好数据了，现在得到一个list叫data
对于语句for x in data，x表示数据表中的一个元组（即一行记录），x[0]为userid，x[1]为city，x[2]为sex，x[3]为amount。
我们可以通过如下的排序语句，可以排出'gz'和'bj'城市各自的Top2：
l = sorted([x for x in data if x[1] == 'gz'],key=lambda x:x[-1],reverse=True)[0:2]
# output: [[1, 'gz', 1, 1000], [3, 'gz', 1, 500]]
l = sorted([x for x in data if x[1] == 'bj'],key=lambda x:x[-1],reverse=True)[0:2]
# output: [[2, 'bj', 0, 2000], [4, 'bj', 1, 2000]]
我们可以通过如下的排序语句，在每个城市内按消费数目排列：
d = sorted(sorted(data, key=lambda x:x[-1], reverse=True), key=lambda x:x[1])
# output:
# [[2, 'bj', 0, 2000],
# [4, 'bj', 1, 2000],
# [1006, 'bj', 1, 1000],
# [1, 'gz', 1, 1000],
# [3, 'gz', 1, 500],
# [1005, 'gz', 1, 500]]
为了输出每个城市的Top2，我们先要获取城市列表（需要去重操作）：
citys = list(set([x[1] for x in data]))
# output: ['gz', 'bj']
for city in citys:
print [x for x in d if x[1]==city][:2]
# output: [[1, 'gz', 1, 1000], [3, 'gz', 1, 500]]
# [[2, 'bj', 0, 2000], [4, 'bj', 1, 2000]]
为了方便叙述，我们不妨设上述“Top2列表”为\(T\)，按每个城市内按消费数目排列的列表为\(D\)
考虑并列的情况，其实就是检查在列表\(D-T\)中是否存在元素\(\bar{x}\)，其消费金额与列表\(T\)中任意一个元素\(x\)的消费金额相等的情况
我们容易看出，\(x\)有两个特点：
- \(x\)的userid还未在T中出现
- \(x\)的消费金额与\(T\)中最后一个元素\(x_2\)的消费金额相等（因为\(D\)是递减的）
故我们可以写出这样的语句：
citys = list(set([x[1] for x in data]))
for city in citys:
res = [x for x in d if x[1]==city][:2]
uids = [x[0] for x in res]
tied = [x for x in d if x[1]==city and x[-1]==res[-1][-1] and not x[0] in uids]
if tied: # 有可能不存在这样的x，那么tied为空
res.append(tied)
print res
# output:
# [[1, 'gz', 1, 1000], [3, 'gz', 1, 500], [[1005, 'gz', 1, 500]]]
# [[2, 'bj', 0, 2000], [4, 'bj', 1, 2000]]
