高頻HIVE-SQL筆試題詳解

作者：數師兄 2021-04-28 08:03:15

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

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

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

SQL如下

WITH active AS 
  (SELECT 100 UID, 
              '2021-04-01'dt 
   UNION ALL SELECT 101 UID, 
                        '2021-04-01'dt 
   UNION ALL SELECT 102 UID, 
                        '2021-04-01'dt 
   UNION ALL SELECT 103 UID, 
                        '2021-04-01'dt 
   UNION ALL SELECT 100 UID, 
                        '2021-04-02'dt 
   UNION ALL SELECT 101 UID, 
                        '2021-04-02'dt 
   UNION ALL SELECT 102 UID, 
                        '2021-04-02'dt 
   UNION ALL SELECT 103 UID, 
                        '2021-04-02'dt 
   UNION ALL SELECT 104 UID, 
                        '2021-04-02'dt 
   UNION ALL SELECT 100 UID, 
                        '2021-04-03'dt 
   UNION ALL SELECT 104 UID, 
                        '2021-04-03'dt 
   UNION ALL SELECT 101 UID, 
                        '2021-04-04'dt 
   UNION ALL SELECT 102 UID, 
                        '2021-04-04'dt 
   UNION ALL SELECT 103 UID, 
                        '2021-04-04'dt 
   UNION ALL SELECT 104 UID, 
                        '2021-04-04'dt 
   UNION ALL SELECT 105 UID, 
                        '2021-04-04'dt 
   UNION ALL SELECT 102 UID, 
                        '2021-04-03'dt) 
SELECT UID, 
       max(days)days 
FROM 
  (SELECT date_sub(dt,rn), 
          UID, 
          count(DISTINCT dt)days 
   FROM 
     (SELECT UID, 
             dt, 
             row_number()over(PARTITION BY UID 
                              ORDER BY dt)rn 
      FROM active)x 
   GROUP BY date_sub(dt,rn), 
            UID)y 
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如下：

with lives as( 
select 100 liveid,'A01,A02,A03,A04'invite_list union all 
select 101 liveid,'A05,A06,A07,A08'invite_list  
), 
record as( 
select 100 liveid,'A01' uid,30 duration union all 
select 100 liveid,'A02' uid,50 duration union all 
select 100 liveid,'A03' uid,15 duration union all 
select 101 liveid,'A07' uid,20 duration union all 
select 101 liveid,'A08' uid,60 duration 
) 
 
select 
list.liveid,list.ulist,if(record.duration is null,0,record.duration)duration 
from ( 
select liveid,ulist 
from lives  
lateral view explode(split(invite_list,',')) uid as ulist 
)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轉為字符串，并用“>”將其連接起來。

WITH record as 
  (SELECT 100 UID,'A01' pageid 
   UNION ALL SELECT 100 UID,'A02' pageid 
   UNION ALL SELECT 100 UID,'A03' pageid 
   UNION ALL SELECT 101 UID,'A07' pageid 
   UNION ALL SELECT 101 UID,'A08' pageid) 
    
    
SELECT UID, 
       concat_ws('>',collect_list(pageid))page 
FROM record 
GROUP BY UID

3.求留存

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

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

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

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

WITH user as 
  (SELECT 100 UID,'2021-01-01' dt 
   UNION ALL SELECT 101 UID,'2021-01-01' dt 
   UNION ALL SELECT 102 UID,'2021-01-01' dt 
   UNION ALL SELECT 103 UID,'2021-01-02' dt 
   UNION ALL SELECT 104 UID,'2021-01-02' dt 
   UNION ALL SELECT 105 UID,'2021-01-02' dt 
   UNION ALL SELECT 106 UID,'2021-01-02' dt), 
   active as( 
   select 100 UID,'2021-01-01'dt union all 
   select 100 UID,'2021-01-02'dt union all 
   select 100 UID,'2021-01-03'dt union all 
   select 100 UID,'2021-01-05'dt union all 
   select 100 UID,'2021-01-07'dt union all 
   select 101 UID,'2021-01-01'dt union all 
   select 101 UID,'2021-01-07'dt union all 
   select 102 UID,'2021-01-01'dt union all 
   select 103 UID,'2021-01-01'dt union all 
   select 103 UID,'2021-01-02'dt union all 
   select 103 UID,'2021-01-03'dt union all 
   select 103 UID,'2021-01-05'dt union all 
   select 104 UID,'2021-01-02'dt union all 
   select 104 UID,'2021-01-03'dt union all 
   select 104 UID,'2021-01-04'dt union all 
   select 105 UID,'2021-01-02'dt union all 
   select 105 UID,'2021-01-03'dt union all 
   select 105 UID,'2021-01-04'dt union all 
   select 105 UID,'2021-01-05'dt union all 
   select 105 UID,'2021-01-07'dt union all 
   select 106 UID,'2021-01-02'dt union all 
   select 106 UID,'2021-01-04'dt  
   ) 
 
select 
user.dt, 
count(distinct user.uid)new_user, 
count(distinct if(datediff(active.dt,user.dt)=1,active.uid,null))retain1, 
count(distinct if(datediff(active.dt,user.dt)=2,active.uid,null))retain2, 
count(distinct if(datediff(active.dt,user.dt)=3,active.uid,null))retain3, 
count(distinct if(datediff(active.dt,user.dt)=4,active.uid,null))retain4, 
count(distinct if(datediff(active.dt,user.dt)=5,active.uid,null))retain5, 
count(distinct if(datediff(active.dt,user.dt)=6,active.uid,null))retain6, 
count(distinct if(datediff(active.dt,user.dt)=7,active.uid,null))retain7 
from user left join active on user.uid=active.uid 
group by user.dt

其結果如下：

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

責任編輯：武曉燕來源：數師兄