【题目】
用户访问次数表，列名包括用户编号、用户类型、访问量。要求在剔除访问次数前 20% 的用户后，每类用户的平均访问次数。（拼多多、网易面试题）
【解题思路】
使用逻辑树分析方法可以把这个复杂的问题拆解为 3 个子问题：
1）找出访问次数前 20% 的用户
2）剔除访问次数前 20% 的用户
3）每类用户的平均访问次数
下面分别来解决每个子问题
- 访问次数前 20% 的用户
先按 “访问次数” 排名，然后就可以找到”前 20%”的数据。排名问题可以用窗口函数来解决。
首先对所有用户的访问量按从低到高的顺序用窗口函数排名：
select * , row_number() over (order by 访问量 desc) as 排名
from 用户访问次数表
select * from （select * , row_number() over (order by 访问量 desc) as 排名
from 用户访问次数表） as a
where 排名(select max(排名) from a)* 0.2
- 剔除访问次数前 20% 的用户
题目要求是 “剔除访问次数前 20% 的用户”，也就是把上面 sql 语句里的 where 条件中的
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
(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 排名
Original: https://blog.csdn.net/weixin_56660268/article/details/124923472
Author: 魔法少女卡卡
Title: 图解SQL 004 拼多多面试题：如何查找前 20% 的数据？
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/817724/
转载文章受原作者版权保护。转载请注明原作者出处！