有哪些常見的SQL面試題

本篇內(nèi)容介紹了“有哪些常見的SQL面試題”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

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出來一個臨時表：

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)要知道一個用戶連續(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天及以上。

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)有了第一個問題作為鋪墊，再來看第二個問題，相信大家應(yīng)該更容易有思路了。在這個問題下，沒有明確給出要計算連續(xù)多少天活躍，而是要求每個用戶的最大連續(xù)活躍天數(shù)。如此一來，我們就不能直接錯位相減了，而是需要清晰地找到每一段連續(xù)活躍的起止時間點。所以我們首先要判斷每條記錄與其相鄰的記錄之間是否連續(xù)，如果不連續(xù)則說明這條記錄是某一段連續(xù)活躍的起點或者終點。

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

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

“有哪些常見的SQL面試題”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！