面试题（2）：如何查找前20%的数据？

天道Vax的时间宝藏

发布于 2021-08-11 14:29:00

6230

发布于 2021-08-11 14:29:00

文章被收录于专栏：用户5305560的专栏

题目：

用户访问次数表，列名包括用户编号、用户类型、访问量。要求在剔除访问次数前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 用户类型;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/06/06 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度