作者 | 數據管道
封圖| CSDN 下載於視覺中國
SQL中有一類函數叫聚合函數，比如count、sum、avg、min、max等，這些函數的可以將多行數據按照規整聚集為一行，一般聚集前的數據行要大於聚集後的數據行。而有時候我們不僅想要聚集前的數據，又想要聚集後的數據，這時候便引入了窗口函數。
下面通過幾道TMD面試題介紹一下如何使用窗口函數。涉及知識點有用於排序的窗口函數、用於用戶分組查詢的窗口函數、用於偏移分析的窗口函數，每種會通過一道面試題背景題解答。
正文
1、某頂尖外賣平臺數據分析師面試題。現有交易數據表user_goods_table如下：
- user_name 用戶名
- goods_kind 用戶訂購的的外賣品類
現在老闆想知道每個用戶購買的外賣品類偏好分佈，並取出每個用戶購買最多的外賣品類是哪個。
輸出要求如下：
- user_name 用戶名
- goods_kind 該用戶購買的最多外賣品類
思路，利用窗口函數 row_number求得每個用戶各自購買品類數量排行分佈，並取出排行第一的品類即該用戶購買最多的外賣品類。
參考題解：
<code>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 /<code>
2、某頂尖支付平臺數據分析面試題。現有交易數據表user_sales_table如下：
- user_name 用戶名
- pay_amount 用戶支付額度
現在老闆想知道支付金額在前20%的用戶。
輸出要求如下：
- user_name 用戶名（前10%的用戶）
思路，利用窗口函數 ntile將每個用戶和對應的支付金額分成5組（這樣每組就有1/5），取分組排名第一的用戶組即前支付金額在前20%的用戶。（注意這裡是求前20%的用戶而不是求支付排在前20的用戶）
參考題解：
<code>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/<code>
3、某頂尖小視頻平臺數據分析面試題。現有用戶登陸表user_login_table如下：
- user_name 用戶名
- date 用戶登陸時間
現在老闆想知道連續7天都登陸平臺的重要用戶。
輸出要求如下：
- user_name 用戶名（連續7天都登陸的用戶數）
思路，首先利用偏移窗口函數lead求得每個用戶在每個登陸時間向後偏移7行的登陸時間，再計算每個用戶在每個登陸時間滯後7天的登陸時間，如果每個用戶向後偏移7行的登陸時間正好等於滯後7天的時間，說明該用戶連續登陸了7天。
參考題解：
<code>select b.user_name(select user_name,date,lead(date,7) over(partition by user_name order by date desc) as date_7from user_login_table) b where b.date is not nulland date_sub(cast(b.date as date,7)) = cast(b.date_7 as date)/<code>
總結
本文分別從3家數據分析面試題了解了窗口函數的實際應用場景，當然假設是大家都已知道窗口函數的語法，窗口函數的使用也確實可以衡量作為數據分析師對SQL能力的掌握程度，當然不管是學習何種用法都要結合實際應用背景思考為何需要這種分析函數。
閱讀更多 CSDN雲計算 的文章
