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

SQL排名实战

最新推荐文章于 2023-02-13 11:39:05 发布

转载最新推荐文章于 2023-02-13 11:39:05 发布 · 481 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/bellz/p/10630554.html

文章标签：

#python #数据库 #面试

本文介绍如何使用SQL查询每个城市的消费TopN用户，包括Oracle SQL的rank()和dense_rank()函数使用方法，以及Python实现方式。

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

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

转载于:https://www.cnblogs.com/bellz/p/10630554.html