当前位置: 首页 > >

『SQL』常考面试题(2??窗口函数)

发布时间:

一、窗口函数是什么
既可以显示原始基础数据也可以显示聚合数据。
二、为什么需要窗口函数
SQL中有一类函数叫聚合函数(聚合函数顾名思义就是聚集合并的意思,是对某个范围内的数值进行聚合,聚合后的结果是一个值或是各个类别对应的值。),比如count、sum、avg、min、max等,这些函数的可以将多行数据按照规整聚集为一行,一般聚集前的数据行要大于聚集后的数据行。而有时候我们不仅想要聚集前的数据,又想要聚集后的数据,这时候便引入了窗口函数。如图所示。

三、怎么用窗口函数


用于排序的窗口函数用于用户分组查询的窗口函数用于偏移分析的窗口函数

四、通过几道TMD面试题来了解窗口函数


4.1

某顶尖外卖*台数据分析师面试题。现有交易数据表user_goods_table如下:


user_name 用户名
goods_kind 用户订购的的外卖品类


现在*逑胫烂扛鲇没Ч郝虻耐饴羝防嗥梅植迹⑷〕雒扛鲇没Ч郝蜃疃嗟耐饴羝防嗍悄母觥


方法一:
select b.user_name,
b.goods_kind
from
(
select user_name,
goods_kind,
row_number() over(partition by user_name order by count(goods_kind) desc) as rank
from user_goods_table
group by user_name, goods_kind) b -- 添加分组之后可以
where b.rank = 1

考点:
1、子查询先求得每个用户各自购买品类数量排行分布。利用窗口函数 row_number。
2、在外层取出排行第一的品类即该用户购买最多的外卖品类。


方法二:

-- 每个用户购买的外卖品类偏好分布
select user_name,
goods_kind,
row_number() over(partition by user_name order by cnt desc) as rank
from
(
select user_name,
goods_kind,
count(1) as cnt
from user_goods_table
group by user_name,
goods_kind
)
-- 取出排行第一品类 即该用户购买最多的外卖品类
select user_name,
goods_kind
from
(
select user_name,
goods_kind,
row_number() over(partition by user_name order by cnt desc) as rank
from
(
select user_name,
goods_kind,
count(1) as cnt
from user_goods_table
group by user_name,
goods_kind
)
)
where rank = 1;

4.2

某顶尖支付*台数据分析面试题。现有交易数据表user_sales_table如下:
user_name 用户名
pay_amount 用户支付额度


现在*逑胫乐Ц督鸲钤谇20%的用户。


方法一:(一个用户一单)
select b.user_name,
b.pay_amount
from
(
select user_name,
pay_amount,
ntile(5) over(order by pay_amount desc) as level2
from temp_cwh_zf
) b
where b.level2 = 1

方法二:(一个用户多单)
select b.user_name
from
(
select user_name,
ntile(5) over(order by sum(pay_amount) desc) as level2
from temp_cwh_zf
group by user_name
) b
where b.level2 = 1


考点:
1、ntile 函数主要是用于将整表数据进行切片分组,默认是对表在不做任何操作之前进行切片分组的,比如现在整个表有9行数据,你要切片分成3组,那么就是第 1-3 行为一组,4-6 行为一组,7-9 行为一组。我们将店铺销量表切分成3组。
2、把销量表切分成3组了,但是对我们实际应用中没什么实际作用啊,你想一下,你拿一个乱序分组有什么用?如果我们和 order by结合使用就有用了,比如我先按照 sales 升序排列,然后再进行切片分组,这个时候的切片就有意义了。也可以在分组内(partition by)*行切片分组。


4.3

某顶尖小视频*台数据分析面试题。现有用户登陆表user_login_table如下:
user_name 用户名
date 用户登陆时间


现在*逑胫懒7天都登陆*台的重要用户。


select b.user_name
(select user_name,
date,lead(date,7)
over(partition by user_name
order by date desc) as date_7
from user_login_table) b
where b.date is not null
and date_sub(cast(b.date as date),interval 7 day)
= cast(b.date_7 as date)

考点:
1、利用偏移窗口函数lead()求得每个用户在每个登陆时间向后偏移7行的登陆时间。
2、date_sub()计算每个用户在每个登陆时间滞后7天的登陆时间
3、如果每个用户向后偏移7行的登陆时间正好等于滞后7天的时间,说明该用户连续登陆了7天(前提是已经清洗用户一天内多次登录的信息)
4、Cast()函数 类型转换。CHAR字符型/DATE日期/DATETIME日期和时间/DECIMALfloat/SIGNEDint/ TIME时间



友情链接: