> For the complete documentation index, see [llms.txt](https://houzidata.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://houzidata.gitbook.io/sql/di-4-zhang/pin-duo-duo-mian-shi-ti-ru-he-cha-zhao-qian-20-de-shu-ju.md).

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

![](/files/-MV5l82P13JyD4ka71UK)

**【题目】**

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

![](/files/-MV5lDG2s5OJQYb23oS4)

**【解题思路】**

使用[逻辑树分析方法](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649246563\&idx=2\&sn=3ffe509999d144d23dec5acc101fc2ef\&chksm=835fc353b4284a45ce01391453fe2fec1b225bbd6bbdb67dd7f304aacdd4f21f60d0b27ba309\&scene=21#wechat_redirect)可以把这个复杂的问题拆解为 3 个子问题：

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

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

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

先按 “访问次数” 排名，然后就可以找到”前 20%”的数据。排名问题在[《猴子 从零学会 sql》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)里讲过可以用窗口函数来解决。

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

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

![](/files/-MV5lGb7K0acNADOxUX3)

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

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

![](/files/-MV5lJeCn3tg9kKqXdRY)

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

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

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

```sql
select max(排名)
from a;
```

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

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

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

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

![](/files/-MV5lRI7W0Xf0_1fgOQM)

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

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

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

![](/files/-MV5lVpaBOvRXrRIcIC6)

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

当 “每个” 出现的时候，就要想到[《猴子 从零学会 sql》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649246563\&idx=2\&sn=3ffe509999d144d23dec5acc101fc2ef\&chksm=835fc353b4284a45ce01391453fe2fec1b225bbd6bbdb67dd7f304aacdd4f21f60d0b27ba309\&scene=21#wechat_redirect)里讲过的这时候就是要分组汇总了。

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

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

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

```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 用户类型;
```

![](/files/-MV5lcAOt61iBpv2sEkG)

**【本题考点】**

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

要会使用[逻辑树分析方法](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649246563\&idx=2\&sn=3ffe509999d144d23dec5acc101fc2ef\&chksm=835fc353b4284a45ce01391453fe2fec1b225bbd6bbdb67dd7f304aacdd4f21f60d0b27ba309\&scene=21#wechat_redirect)将复杂问题拆解成简单问题排名问题使用窗口函数来实现。

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

![](/files/-MV5lfYZJM3PFzyVTZIe)

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

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

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

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

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

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

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

推荐：[如何从零学会 sql？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)

![](/files/-MV5m8U1n5H2JmQrgRE8)