成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

高頻HIVE-SQL筆試題詳解

作者： 數師兄
運維 數據庫運維
在上期的SQL題目中，我們以求連續活躍為例，講述了lead 、lag錯位相減的用法。但若不采用錯位相減的話，其實也可以實現這一目的。本次我們就先講述一下最大連續活躍天數的第二種解法，然后再講述另一常見面試SQL題目。

[[396437]]

本文轉載自微信公眾號「數師兄」，作者數師兄。轉載本文請聯系數師兄公眾號。

在上期的SQL題目中，我們以求連續活躍為例，講述了lead 、lag錯位相減的用法。但若不采用錯位相減的話，其實也可以實現這一目的。本次我們就先講述一下最大連續活躍天數的第二種解法，然后再講述另一常見面試SQL題目。

這一方法就是利用row_number給每一行生成一個連續的序號，這樣，在連續活躍的時間段，就有了一個對照值，利用日期和行號這兩者就可以得到一個差值，在連續活躍的時段內，其差值是相同的，那么我們按照這個差值進行分組就可以進一步計算本次連續活躍有多少天了。

SQL如下

  1. WITH active AS 
  2.   (SELECT 100 UID, 
  3.               '2021-04-01'dt 
  4.    UNION ALL SELECT 101 UID, 
  5.                         '2021-04-01'dt 
  6.    UNION ALL SELECT 102 UID, 
  7.                         '2021-04-01'dt 
  8.    UNION ALL SELECT 103 UID, 
  9.                         '2021-04-01'dt 
  10.    UNION ALL SELECT 100 UID, 
  11.                         '2021-04-02'dt 
  12.    UNION ALL SELECT 101 UID, 
  13.                         '2021-04-02'dt 
  14.    UNION ALL SELECT 102 UID, 
  15.                         '2021-04-02'dt 
  16.    UNION ALL SELECT 103 UID, 
  17.                         '2021-04-02'dt 
  18.    UNION ALL SELECT 104 UID, 
  19.                         '2021-04-02'dt 
  20.    UNION ALL SELECT 100 UID, 
  21.                         '2021-04-03'dt 
  22.    UNION ALL SELECT 104 UID, 
  23.                         '2021-04-03'dt 
  24.    UNION ALL SELECT 101 UID, 
  25.                         '2021-04-04'dt 
  26.    UNION ALL SELECT 102 UID, 
  27.                         '2021-04-04'dt 
  28.    UNION ALL SELECT 103 UID, 
  29.                         '2021-04-04'dt 
  30.    UNION ALL SELECT 104 UID, 
  31.                         '2021-04-04'dt 
  32.    UNION ALL SELECT 105 UID, 
  33.                         '2021-04-04'dt 
  34.    UNION ALL SELECT 102 UID, 
  35.                         '2021-04-03'dt) 
  36. SELECT UID, 
  37.        max(days)days 
  38. FROM 
  39.   (SELECT date_sub(dt,rn), 
  40.           UID, 
  41.           count(DISTINCT dt)days 
  42.    FROM 
  43.      (SELECT UID, 
  44.              dt, 
  45.              row_number()over(PARTITION BY UID 
  46.                               ORDER BY dt)rn 
  47.       FROM active)x 
  48.    GROUP BY date_sub(dt,rn), 
  49.             UID)y 
  50. GROUP BY UID 

結果如下：

至此，連續活躍的問題就告一段落了。下面我們來看另一個常見問題：

2.行列轉換

(1)某直播產品，直播記錄表lives中有liveid(直播id)、invite_list(邀約名單，string，用戶id以“，”分割)字段，直播觀看記錄表record中有uid(用戶id)、liveid(直播id)、duration(觀看時長)，試求出邀約名單中所有用戶的觀看時長

由于存在邀約用戶未進房觀看的問題，所以我們直接從觀看記錄中來取，不一定能將名單中所有用戶包含進去，所以，我們需要以直播記錄中的邀約名單為準。這就需要利用explode和lateral view來將這個string類型轉換為行記錄的形式。

仍然是使用with構造lives和record兩個簡單的臨時表，詳細SQL如下：

  1. with lives as( 
  2. select 100 liveid,'A01,A02,A03,A04'invite_list union all 
  3. select 101 liveid,'A05,A06,A07,A08'invite_list  
  4. ), 
  5. record as( 
  6. select 100 liveid,'A01' uid,30 duration union all 
  7. select 100 liveid,'A02' uid,50 duration union all 
  8. select 100 liveid,'A03' uid,15 duration union all 
  9. select 101 liveid,'A07' uid,20 duration union all 
  10. select 101 liveid,'A08' uid,60 duration 
  11. ) 
  12.  
  13. select 
  14. list.liveid,list.ulist,if(record.duration is null,0,record.duration)duration 
  15. from ( 
  16. select liveid,ulist 
  17. from lives  
  18. lateral view explode(split(invite_list,',')) uid as ulist 
  19. )list left join record on list.liveid=record.liveid and list.ulist=record.uid 

這里簡單說明下：lateral view 是為原表調用explode函數，將一個array或者map的字段分解成多行并作為一個臨時表，然后再與其他字段組合到一起。

(2)某APP用戶訪問頁面記錄record，含有字段uid(用戶id)、pageid(頁面id)，將每個用戶的訪問路徑輸出在一個字段中

前一道題目是列轉行，這道題是行專列。利用collect_set可以將分組下面某個字段的所有記錄聚合成一個列表，然后可以進一步使用concat_ws函數把array轉為字符串，并用“>”將其連接起來。

  1. WITH record as 
  2.   (SELECT 100 UID,'A01' pageid 
  3.    UNION ALL SELECT 100 UID,'A02' pageid 
  4.    UNION ALL SELECT 100 UID,'A03' pageid 
  5.    UNION ALL SELECT 101 UID,'A07' pageid 
  6.    UNION ALL SELECT 101 UID,'A08' pageid) 
  7.     
  8.     
  9. SELECT UID, 
  10.        concat_ws('>',collect_list(pageid))page 
  11. FROM record 
  12. GROUP BY UID 

3.求留存

對于多數互聯網公司來說，任何時候留存率都是重中之重。所以在數據分析的面試當中，求留存率的SQL題目出現的頻率也是極高。

所謂“留存率”，通常是定義一個初始行為與一個后繼行為，符合初始條件的用戶量在經過N天之后，剩余的比例就是留存率。發散來講，回訪、復看等概念也與留存相類似。如：

某APP新注冊用戶表user，含有字段uid(用戶id)、dt(注冊日期)，活躍表active，有字段uid(用戶id)，dt(活躍日期)，求每天新增用戶的次日留存率、次2日留存率、次3日留存率……次7日留存率

此題目就是以新注冊作為初始行為，活躍作為后繼行為。顯而易見地，我們可以將初始行為與后繼行為做左連接，然后按注冊日期進行聚合，求相應的新增用戶和次N日留存人數。

  1. WITH user as 
  2.   (SELECT 100 UID,'2021-01-01' dt 
  3.    UNION ALL SELECT 101 UID,'2021-01-01' dt 
  4.    UNION ALL SELECT 102 UID,'2021-01-01' dt 
  5.    UNION ALL SELECT 103 UID,'2021-01-02' dt 
  6.    UNION ALL SELECT 104 UID,'2021-01-02' dt 
  7.    UNION ALL SELECT 105 UID,'2021-01-02' dt 
  8.    UNION ALL SELECT 106 UID,'2021-01-02' dt), 
  9.    active as( 
  10.    select 100 UID,'2021-01-01'dt union all 
  11.    select 100 UID,'2021-01-02'dt union all 
  12.    select 100 UID,'2021-01-03'dt union all 
  13.    select 100 UID,'2021-01-05'dt union all 
  14.    select 100 UID,'2021-01-07'dt union all 
  15.    select 101 UID,'2021-01-01'dt union all 
  16.    select 101 UID,'2021-01-07'dt union all 
  17.    select 102 UID,'2021-01-01'dt union all 
  18.    select 103 UID,'2021-01-01'dt union all 
  19.    select 103 UID,'2021-01-02'dt union all 
  20.    select 103 UID,'2021-01-03'dt union all 
  21.    select 103 UID,'2021-01-05'dt union all 
  22.    select 104 UID,'2021-01-02'dt union all 
  23.    select 104 UID,'2021-01-03'dt union all 
  24.    select 104 UID,'2021-01-04'dt union all 
  25.    select 105 UID,'2021-01-02'dt union all 
  26.    select 105 UID,'2021-01-03'dt union all 
  27.    select 105 UID,'2021-01-04'dt union all 
  28.    select 105 UID,'2021-01-05'dt union all 
  29.    select 105 UID,'2021-01-07'dt union all 
  30.    select 106 UID,'2021-01-02'dt union all 
  31.    select 106 UID,'2021-01-04'dt  
  32.    ) 
  33.  
  34. select 
  35. user.dt, 
  36. count(distinct user.uid)new_user, 
  37. count(distinct if(datediff(active.dt,user.dt)=1,active.uid,null))retain1, 
  38. count(distinct if(datediff(active.dt,user.dt)=2,active.uid,null))retain2, 
  39. count(distinct if(datediff(active.dt,user.dt)=3,active.uid,null))retain3, 
  40. count(distinct if(datediff(active.dt,user.dt)=4,active.uid,null))retain4, 
  41. count(distinct if(datediff(active.dt,user.dt)=5,active.uid,null))retain5, 
  42. count(distinct if(datediff(active.dt,user.dt)=6,active.uid,null))retain6, 
  43. count(distinct if(datediff(active.dt,user.dt)=7,active.uid,null))retain7 
  44. from user left join active on user.uid=active.uid 
  45. group by user.dt 

其結果如下：

 

這種方法可能比較麻煩一些，得把要求的每一個留存指標都要單獨寫一個字段。若想省事兒，可以單獨求出每日的新增數量，再將新增表與活躍表連接而求出每個注冊日期在后續每天的活躍數量，然后將新增數量與活躍數量再進行關聯，從而求得任意周期的留存。

 

責任編輯：武曉燕 來源： 數師兄
HIVESQLlead
相關推薦

2021-04-21 08:11:09

HIVE-SQLSQL數據庫

2009-08-01 22:47:13

ASP.NET筆試題目ASP.NET

2021-12-08 11:18:21

Spring Bean面試題生命周期

2021-02-23 12:43:39

Redis面試題緩存

2015-04-07 14:05:13

前端阿里在線筆試題

2009-06-15 17:18:25

Java筆試題

2010-08-11 11:57:02

微軟筆試題微軟筆試題

2010-08-11 12:07:08

騰訊筆試題騰訊筆試題

2009-07-28 13:35:18

2010-08-11 11:22:00

IBM筆試題IBM筆試

2010-08-11 11:32:57

谷歌筆試題谷歌筆試題

2020-06-01 08:39:12

JavaScript開發技術

2010-08-18 10:17:00

2010-08-16 15:27:22

雅虎筆試題

2021-01-22 11:58:30

MySQL數據庫開發

2009-07-14 10:05:02

HCDA認證考試筆試題

2010-08-18 10:27:56

2010-08-31 23:15:42

IT筆試題企業

2011-08-11 17:39:25

Objective-C筆試題

2020-07-14 16:08:33

數據分析Python筆試
點贊
收藏

51CTO技術棧公眾號

主站蜘蛛池模板： 成人亚洲| 欧美自拍另类 | 国产精品不卡一区二区三区 | 在线观看成年人视频 | 一级女毛片 | www.四虎.com| 日韩欧美在线播放 | 日韩精品一区二区三区免费观看 | 欧美一级黄色免费 | 黄色毛片网站在线观看 | 狠狠操av | 96久久久久久 | 日本激情一区二区 | 久久久91精品国产一区二区三区 | 在线观看日本网站 | 三级特黄特色视频 | 国产在线高清 | 狠狠草视频| 日本一区二区三区四区 | 精品一区二区久久久久久久网精 | 日韩欧美高清 | 一级特黄色毛片 | 日日夜夜视频 | 偷偷操视频| 超碰精品在线 | 狠狠干综合视频 | 美女在线观看av | 亚洲视频免费在线观看 | 日本国产一区二区 | 日韩av一区二区在线观看 | 亚洲精品一区二区 | 欧美free性| 国产精品明星裸体写真集 | 成人免费一区二区三区视频网站 | 亚洲色欧美另类 | 国产精品一区二区视频 | 韩日一区二区三区 | 国产精品一区二区三区免费观看 | 亚洲成av人片在线观看无码 | 欧美人妇做爰xxxⅹ性高电影 | 精品一区二区久久久久久久网站 |