[[394565]]

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

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

1.連續活躍問題

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

考慮到權限問題，這里就不單獨新建hive表了，直接在with子句中union出來一個臨時表：

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

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

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

根據上圖的子查詢，我們就能知道每條活躍記錄是從哪天開始連續活躍的了，然后求每個用戶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 

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

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