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

【题目】

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

【解题思路】

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

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

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

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

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. 每类用户的平均访问次数

按 “用户类型” 分组(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;

Previous链家面试题：如何分析留存率？Next图解面试题：如何查找工资前三高的员工

Last updated 4 years ago

Was this helpful?