[[394565]]

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

根據(jù)過(guò)往的經(jīng)歷和大伙兒的反饋，我們整理歸納了一些問(wèn)題出來(lái)，以供大家參考。

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

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

考慮到權(quán)限問(wèn)題，這里就不單獨(dú)新建hive表了，直接在with子句中union出來(lái)一個(gè)臨時(shí)表：

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) 

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

select 
count(DISTINCT uid) 
from( 
        SELECT UID, 
               dt, 
               lag(dt,2)over(PARTITION BY UID ORDER BY dt)dt2 
        FROM active 
        )x 
where datediff(dt,dt2)=2 

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

給所有記錄標(biāo)記上是否是斷點(diǎn)之后，我們就可以為每條記錄去匹配距離他最近的一次起點(diǎn)：

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

select 
UID,max(datediff(dt,start_dt))days 
from( 
    select 
    UID,dt,max(if(if_continue=0,dt,null))over(PARTITION BY UID ORDER BY dt)start_dt 
    from( 
        select 
        UID,dt,dt2,if(datediff(dt,dt2)=1,1,0)if_continue 
        from( 
                SELECT UID, 
                       dt, 
                       lag(dt,1)over(PARTITION BY UID ORDER BY dt)dt2 
                FROM active 
                )x 
    )y 
)z 
group by UID 

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

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