來源：數據管道/作者：愛德寶器

01?背 景

SQL中有一類函數叫聚合函數，比如count、sum、avg、min、max等，這些函數的可以將多行數據按照規(guī)整聚集為一行，一般聚集前的數據行要大于聚集后的數據行。

而有時候我們不僅想要聚集前的數據，又想要聚集后的數據，這時候便引入了窗口函數。

下面通過幾道TMD面試題介紹一下如何使用窗口函數。涉及知識點有用于排序的窗口函數、用于用戶分組查詢的窗口函數、用于偏移分析的窗口函數，每種會通過一道面試題背景題解答。

02?正 文

1、某頂尖外賣平臺數據分析師面試題?，F(xiàn)有交易數據表user_goods_table如下：

• user_name??? 用戶名

• goods_kind???用戶訂購的的外賣品類

現(xiàn)在老板想知道每個用戶購買的外賣品類偏好分布，并取出每個用戶購買最多的外賣品類是哪個。

輸出要求如下：

• user_name? ? 用戶名

• goods_kind? ?該用戶購買的最多外賣品類

思路：利用窗口函數 row_number求得每個用戶各自購買品類數量排行分布，并取出排行第一的品類即該用戶購買最多的外賣品類。

參考題解：

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) b?where?b.rank =1

2、某頂尖支付平臺數據分析面試題?，F(xiàn)有交易數據表user_sales_table如下：

• user_name? ? ?用戶名

• pay_amount? ?用戶支付額度

現(xiàn)在老板想知道支付金額在前20%的用戶。

輸出要求如下：

• user_name? ? ? ? 用戶名（前10%的用戶）

思路：利用窗口函數 ntile將每個用戶和對應的支付金額分成5組（這樣每組就有1/5），取分組排名第一的用戶組即前支付金額在前20%的用戶。

注意：這里是求前20%的用戶而不是求支付排在前20的用戶

參考題解：

select?b.user_name?from?
(select?
user_name,
ntile(5)?over(order?by?sum(pay_amount)?desc)?as?level
from?user_sales_table?group?by?user_name ) b?
where?b.level =?1

3、某頂尖小視頻平臺數據分析面試題?，F(xiàn)有用戶登陸表user_login_table如下：

• user_name? ? ?用戶名

• date? ? 用戶登陸時間

現(xiàn)在老板想知道連續(xù)7天都登陸平臺的重要用戶。

輸出要求如下：

• user_name? ? ?用戶名（連續(xù)7天都登陸的用戶數）? ? ? ? ?

思路：首先利用偏移窗口函數lead求得每個用戶在每個登陸時間向后偏移7行的登陸時間，再計算每個用戶在每個登陸時間滯后7天的登陸時間，如果每個用戶向后偏移7行的登陸時間正好等于滯后7天的時間，說明該用戶連續(xù)登陸了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,7)) =?cast(b.date_7?as?date)

03?總 結

本文分別從3家數據分析面試題了解了窗口函數的實際應用場景，當然假設是大家都已知道窗口函數的語法，窗口函數的使用也確實可以衡量作為數據分析師對SQL能力的掌握程度，當然不管是學習何種用法都要結合實際應用背景思考為何需要這種分析函數。

-END-

掃描/識別下方二維碼

回復【大廠面試題】

即可免費領取10+大廠數據分析崗面試題及答案