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

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

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

select * from table;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci

选取单个城市的消费总额 Top N

选取gz城市的消费总额Top 2:

select * from (select * from table where city='gz' order by amount desc) where rownum<3;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_元组_02

选取bj城市的消费总额Top 2:

select * from (select * from table where city='bj' order by amount desc) where rownum<3;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci_03

选取所有城市的消费总额 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;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_元组_04

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

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

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci_05

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;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci_06

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

我们同样通过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;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci_07

dense_rank() over

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

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci_08

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

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_元组_09

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

一些错误做法

这样只能返回全局的排名,不能在城市内排名

select userid,city,amount from table where city in (select distinct city from table) group by city,amount,userid order by amount desc;

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_元组_10

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

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_元组_11


sex不在group by中,会报错

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_ci_12


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

python 做法

假设已经用pandas处理好数据了,现在得到一个list叫data

es should 只满足一个条件 和满足多个条件 排序 对满足条件的数据排名_python_13

对于语句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\)有两个特点:

  1. \(x\)的userid还未在T中出现
  2. \(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]]