自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

高頻HIVE-SQL筆試題詳解

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

[[396437]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)師兄」，作者數(shù)師兄。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)師兄公眾號(hào)。

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

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

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 

結(jié)果如下：

至此，連續(xù)活躍的問(wèn)題就告一段落了。下面我們來(lái)看另一個(gè)常見(jiàn)問(wèn)題：

2.行列轉(zhuǎn)換

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

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

仍然是使用with構(gòu)造lives和record兩個(gè)簡(jiǎn)單的臨時(shí)表，詳細(xì)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 

這里簡(jiǎn)單說(shuō)明下：lateral view 是為原表調(diào)用explode函數(shù)，將一個(gè)array或者map的字段分解成多行并作為一個(gè)臨時(shí)表，然后再與其他字段組合到一起。

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

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

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

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

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

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

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

  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 

其結(jié)果如下：

 

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

 

責(zé)任編輯：武曉燕 來(lái)源： 數(shù)師兄
HIVESQLlead
相關(guān)推薦

2021-04-21 08:11:09

HIVE-SQLSQL數(shù)據(jù)庫(kù)

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面試題緩存

2009-06-15 17:18:25

Java筆試題

2015-04-07 14:05:13

前端阿里在線筆試題

2010-08-16 15:27:22

雅虎筆試題

2020-06-01 08:39:12

JavaScript開(kāi)發(fā)技術(shù)

2010-08-18 10:17:00

2010-08-11 11:32:57

谷歌筆試題谷歌筆試題

2010-08-11 11:57:02

微軟筆試題微軟筆試題

2010-08-11 12:07:08

騰訊筆試題騰訊筆試題

2010-08-11 11:22:00

IBM筆試題IBM筆試

2009-07-28 13:35:18

2021-01-22 11:58:30

MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)

2009-07-28 13:09:51

ASP.NET筆試題

2009-02-27 10:46:32

DBA筆試題阿里巴巴

2009-07-14 10:05:02

HCDA認(rèn)證考試筆試題

2011-08-11 17:39:25

Objective-C筆試題

2010-08-31 23:15:42

IT筆試題企業(yè)
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)