作者 |?數據管道

?責編 | 徐威龍

封圖|?CSDN 下載于視覺中國

SQL中有一類函數叫聚合函數，比如count、sum、avg、min、max等，這些函數的可以將多行數據按照規整聚集為一行，一般聚集前的數據行要大于聚集后的數據行。而有時候我們不僅想要聚集前的數據，又想要聚集后的數據，這時候便引入了窗口函數。

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

正文

1、某頂尖外賣平臺數據分析師面試題。現有交易數據表user_goods_table如下：

• user_name 用戶名

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

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

輸出要求如下：

• 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、某頂尖支付平臺數據分析面試題。現有交易數據表user_sales_table如下：

• user_name 用戶名

• pay_amount 用戶支付額度

現在老板想知道支付金額在前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、某頂尖小視頻平臺數據分析面試題。現有用戶登陸表user_login_table如下：

• user_name 用戶名

• date 用戶登陸時間

現在老板想知道連續7天都登陸平臺的重要用戶。

輸出要求如下：

• user_name 用戶名（連續7天都登陸的用戶數）

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

總結

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

推薦閱讀：百萬人學AI：CSDN重磅共建人工智能技術新生態突破性能極限——阿里云神龍最新ASPLOS論文解讀漫畫：如何給女朋友解釋什么是熔斷？ 疫情期間天天對你“開槍”的額溫槍，你知道它的工作原理嗎？| 原力計劃 如何更新你的機器學習模型？手把手帶你設計一個可持續的預測模型！ 區塊鏈數據分析，讓你看清交易對手 真香，朕在看了！

總結

以上是生活随笔為你收集整理的Hive 热门数据分析面试题解析的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。