拼多多面试题：如何查找前 20% 的数据？

【题目】

用户访问次数表，列名包括用户编号、用户类型、访问量。要求在剔除访问次数前 20% 的用户后，每类用户的平均访问次数。（拼多多、网易面试题）

【解题思路】

使用逻辑树分析方法可以把这个复杂的问题拆解为 3 个子问题：

1）找出访问次数前 20% 的用户
2）剔除访问次数前 20% 的用户
3）每类用户的平均访问次数

下面分别来解决每个子问题

1. 访问次数前 20% 的用户

先按 “访问次数” 排名，然后就可以找到”前 20%”的数据。排名问题在《猴子从零学会 sql》里讲过可以用窗口函数来解决。

首先对所有用户的访问量按从低到高的顺序用窗口函数排名：

select *,
      row_number() over(order by 访问量 desc) as 排名
from 用户访问次数表;

排名后，如何找出前 20% 的数据呢？

排名 <= 最大的排名值 * 20%, 就是前 20% 的数据。

把前面的排名结果表当作临时表 a，加上筛选条件（where）对应的 sql 语句如下：

select * 
from a
where 排名<= 最大的排名 * 0.2;

最大的排名值如何得到呢？可以用下面的 sql 语句：

select max(排名)
from a;

把前面的 sql 语句组合到一起就得到了筛选出排名前 20% 的数据了：

select * 
from a
where 排名<= (select max(排名) from a) * 0.2;

2. 剔除访问次数前 20% 的用户

题目要求是 “剔除访问次数前 20% 的用户”，也就是把上面 sql 语句里的 where 条件中的 <= 变成 > 就获取到相反的数据了。

select * 
from a
where 排名 > (select max(排名) from a) * 0.2;

把前面得到的临时表 a 的 sql 语句带入后就是：

select * 
from 
(select *,
       row_number() over(order by 访问量 desc) as 排名
from 用户访问次数表) as a
where 排名 > (select max(排名) from a) * 0.2;

3. 每类用户的平均访问次数

当 “每个” 出现的时候，就要想到《猴子从零学会 sql》里讲过的这时候就是要分组汇总了。

按 “用户类型” 分组(group by)，然后汇总求平均访问次数 avg(访问次数）。

select 用户类型,avg(访问量)
from b
group by 用户类型;

这里的表 b 就是前面第 2 步得到的临时表，带入 sql 里就是：

select 用户类型,avg(访问量)
from 
(select * 
from 
(select *,
       row_number() over(order by 访问量 desc) as 排名
from 用户访问次数表) as a
where 排名 > (select max(排名) from a) * 0.2) as b
group by 用户类型;

【本题考点】

1.面对复杂问题的分析能力

要会使用逻辑树分析方法将复杂问题拆解成简单问题排名问题使用窗口函数来实现。

2.当有 “每个” 出现的时候，要想到用分组汇总，下图是常用的汇总函数

3.选出前百分之 N 的问题如何解决？下面是这类问题的解决模版

1）先使用窗口函数对数据排名得到临时表 a

select *,
       row_number() over(order by 排名的列 desc) as 排名
from 表名;

2）然后用表 a 筛选出前百分之 N 的数据

select * 
from a
where 排名 <= (select max(排名) from a) * 百分之N;

3）如果是剔除前前百分之 N 的数据，也就是选出后 (1 - 百分之 N) 的数据。例如选出后 80% 的数据，就把上面的 where 子句里的 <= 修改成 >

select * 
from a
where 排名 > (select max(排名) from a) * 百分之N;