偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

高頻HIVE-SQL筆試題詳解(一)

數(shù)據(jù)庫 其他數(shù)據(jù)庫
最近身邊有不少小伙伴在考慮跳槽的事情,免不了的會遇到一些SQL的面試題,根據(jù)過往的經(jīng)歷和大伙兒的反饋,我們整理歸納了一些問題出來,以供大家參考。

[[394565]]

最近身邊有不少小伙伴在考慮跳槽的事情,免不了的會遇到一些SQL的面試題。

根據(jù)過往的經(jīng)歷和大伙兒的反饋,我們整理歸納了一些問題出來,以供大家參考。

1.連續(xù)活躍問題

這個問題有兩個變形:(1)某APP用戶活躍記錄表active,有uid(用戶id)、dt(活躍日期)字段,求出連續(xù)出勤3天及以上的用戶數(shù) (2)某APP用戶活躍記錄表active,有uid(用戶id)、dt(活躍日期)字段,求每個用戶的最大連續(xù)活躍天數(shù) 以上兩個問題要求輸出的數(shù)據(jù)不同,但都是要考察對于窗口函數(shù)lead、lag的掌握能力。第二個是在第一個問題基礎(chǔ)上的延伸,也可以作為此類問題的通解。我們按照順序分別來進行解答。首先,我們構(gòu)建一個表以供測試說明,形如下圖:


考慮到權(quán)限問題,這里就不單獨新建hive表了,直接在with子句中union出來一個臨時表:

  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) 

(1)要知道一個用戶連續(xù)活躍,那么肯定是要對他的所有活躍記錄進行排序的。對于active這個表,每天每個活躍用戶都會有一條記錄,我們將每個用戶的活躍記錄按日期順序排列,如果上一條記錄的日期與本條記錄的日期剛好相差1天,那么這兩條記錄就是連續(xù)的。反過來也就是說,連續(xù)出勤的記錄之間,日期差值為1,推而廣之,如果用戶連續(xù)出勤了N天,那么這N條記錄之中任意相鄰的兩條都是差1天,而這段記錄開始的日期到結(jié)束的日期之間的差值則是N-1。如果某用戶連續(xù)出勤了4天,那么從他第3天出勤的記錄往前數(shù)第6條記錄就剛好是他連續(xù)出勤的開始日期,所以,我們從每條記錄往前數(shù)第6條的日期與本條記錄的日期差值剛好是2天的話,就表示這期間沒有間斷。 我們利用lead獲取每一條記錄其往前第2條記錄的日期,與本條記錄日期求差值,如果這個差值等于2,就表明該用戶連續(xù)出勤了3天及以上。具體計算sql如下所示,可以求得共有3個用戶連續(xù)活躍了3天及以上。

  1. select 
  2. count(DISTINCT uid) 
  3. from
  4.         SELECT UID, 
  5.                dt, 
  6.                lag(dt,2)over(PARTITION BY UID ORDER BY dt)dt2 
  7.         FROM active 
  8.         )x 
  9. where datediff(dt,dt2)=2 

這是從后往前數(shù),同樣的利用lag函數(shù)可以獲取每條記錄往后數(shù)第2條記錄的日期,如果有差值等于2的記錄,那么也可以表明用戶連續(xù)出勤了3天及以上。(2)有了第一個問題作為鋪墊,再來看第二個問題,相信大家應(yīng)該更容易有思路了。在這個問題下,沒有明確給出要計算連續(xù)多少天活躍,而是要求每個用戶的最大連續(xù)活躍天數(shù)。如此一來,我們就不能直接錯位相減了,而是需要清晰地找到每一段連續(xù)活躍的起止時間點。所以我們首先要判斷每條記錄與其相鄰的記錄之間是否連續(xù),如果不連續(xù)則說明這條記錄是某一段連續(xù)活躍的起點或者終點。


給所有記錄標記上是否是斷點之后,我們就可以為每條記錄去匹配距離他最近的一次起點:

根據(jù)上圖的子查詢,我們就能知道每條活躍記錄是從哪天開始連續(xù)活躍的了,然后求每個用戶UID下所有活躍記錄中與其起始日期最大的差值即可:

  1. select 
  2. UID,max(datediff(dt,start_dt))days 
  3. from
  4.     select 
  5.     UID,dt,max(if(if_continue=0,dt,null))over(PARTITION BY UID ORDER BY dt)start_dt 
  6.     from
  7.         select 
  8.         UID,dt,dt2,if(datediff(dt,dt2)=1,1,0)if_continue 
  9.         from
  10.                 SELECT UID, 
  11.                        dt, 
  12.                        lag(dt,1)over(PARTITION BY UID ORDER BY dt)dt2 
  13.                 FROM active 
  14.                 )x 
  15.     )y 
  16. )z 
  17. group by UID 

如此,就求得了所有用戶的最大連續(xù)活躍天數(shù)了:

無論是第一種還是第二種,主要都是考察窗口函數(shù)的應(yīng)用。熟練掌握窗口函數(shù),并靈活運用,此類問題就不在話下了。

 

責(zé)任編輯:姜華 來源: 數(shù)師兄
相關(guān)推薦

2021-04-28 08:03:15

HIVESQLlead

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筆試題

2014-04-29 14:58:24

筆試題微軟筆試題

2015-04-07 14:05:13

前端阿里在線筆試題

2021-01-14 10:24:33

嵌入式筆試面試

2009-06-22 13:43:00

java算法

2020-03-03 17:47:07

UDP TCP面試題

2020-06-01 08:39:12

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

2010-08-18 10:17:00

2010-08-16 15:27:22

雅虎筆試題

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

谷歌筆試題谷歌筆試題

2021-01-22 11:58:30

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

2009-02-27 10:46:32

DBA筆試題阿里巴巴
點贊
收藏

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