計算最大連續(xù)活躍天的方法步驟

本篇內(nèi)容介紹了“計算最大連續(xù)活躍天的方法步驟”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

本次我們就先講述一下最大連續(xù)活躍天數(shù)的第二種解法，然后再講述另一常見面試SQL題目。

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

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

結(jié)果如下：

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

2.行列轉(zhuǎn)換

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

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

仍然是使用with構(gòu)造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  是為原表調(diào)用explode函數(shù)，將一個array或者map的字段分解成多行并作為一個臨時表，然后再與其他字段組合到一起。

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

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

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.求留存

對于多數(shù)互聯(lián)網(wǎng)公司來說，任何時候留存率都是重中之重。所以在數(shù)據(jù)分析的面試當中，求留存率的SQL題目出現(xiàn)的頻率也是極高。

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

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

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

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

其結(jié)果如下：

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

“計算最大連續(xù)活躍天的方法步驟”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！