????????都說“金九銀十”，馬上十月份即將結束，相信還有相當多的小伙伴沒找到合適的工作。在筆試過程中，總會出現那么一兩道“有趣”的SQL題，來檢測應聘者的一個邏輯思維，這對于初入職場的“小白”也是非常不友好。不用擔心，本篇博客，博主整理了幾道在面試中高頻出現的“SQL”筆試題，助你在接下來的面試中一往無前，勢如破竹!
????????

文章目錄

• • 1、查詢連續登陸3天以上的用戶
  • • step1: 用戶登錄日期去重
    • step2: 用row_number() over()函數計數
    • step3:日期減去計數值得到結果
    • step4:根據id和結果分組并計算count
  • 2、統計每個用戶的累計訪問次數
  • • step1: 修改數據格式
    • step2: 計算每人單月訪問量
    • step3: 按月累計計算訪問量
    • 完整SQL
  • 3、分組TopN
  • • step1：查詢每個店鋪被每個用戶訪問次數
    • step2:計算每個店鋪被用戶訪問次數排名
    • step3: 取每個店鋪排名前3的數據
    • 完整SQL
  • 結語

---

1、查詢連續登陸3天以上的用戶

????????這是一道非常經典的問題，這里提供其中一種思路。

????????表信息如下圖：

????????

step1: 用戶登錄日期去重

????????因為一個用戶同一天可能登錄多次，所以我們首先需要用用戶登錄日期去重。

select DISTINCT date(date) as "日期",id from demo01;

????????查詢結果:

step2: 用row_number() over()函數計數

????????有了第一步去重后的結果，我們可以對其進行開窗，以id分組，日期升序排序，獲取到每個日期的排名。

SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `日期`) AS cum FROM (SELECT DISTINCT date(date) AS `日期`, idFROM demo01 ) a;

????????查詢結果：
????????

????????相信看到這里，各位小伙伴已經看出其中的“玄機”了~為什么我們需要在這一步對時間進行一個排序呢？

????????可以發現，用row_number開窗之后的名次是連續的，那么如果日期也是連續的，它們的差值不就是一個固定的值了嗎?

step3:日期減去計數值得到結果

????????因為菌哥這里演示用的是hql，所以這里獲取日期差值使用了date_sub函數。

SELECT *, date_sub(`日期`, cum) AS `結果` FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `日期`) AS cumFROM (SELECT DISTINCT date(date) AS `日期`, idFROM demo01) a ) b;

????????查詢結果：
????????

step4:根據id和結果分組并計算count

????????最后一步，我們直接根據step3中獲取到的差值，根據id和差值進行一個分組求count即可。如果是要求連續登錄3天以上，我們直接判斷 count 的個數大于等于3即可。

SELECT id, COUNT(*) FROM (SELECT *, date_sub(`日期`, cum) AS `結果`FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `日期`) AS cumFROM (SELECT DISTINCT date(date) AS `日期`, idFROM demo01) a) b ) c GROUP BY id, `結果` HAVING COUNT(*) >= 3;

????????運行結果:

????????
????????答案已經出來了，id為1和3的用戶至少連續登錄了3天及以上，他們分別連續登錄的時長為3天和4天。

2、統計每個用戶的累計訪問次數

????????這個同樣也是經常在筆試中出現的題目，大家可以根據作者的思路回顧一下:

????????表信息如下圖：

????????要求使用SQL統計出每個用戶的累積訪問次數，如下表所示：

用戶id月份小計累積

u01	2017-01	11	11
u01	2017-02	12	23
u02	2017-01	12	12
u03	2017-01	8	8
u04	2017-01	3	3

step1: 修改數據格式

????????從結果反推，需要查詢實現按照 年-月 分組的數據，所以我們這一步先對原數據進行一個處理。

selectuserId,date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,visitCount fromaction;t1

????????處理結果：

step2: 計算每人單月訪問量

????????為了讓子查詢看起來更加的美觀，我們這里先用t1代替上一步的結果。通過這一步，我們就可以獲取到每個用戶，每個月的訪問量。

selectuserId,mn,sum(visitCount) mn_count fromt1 group by userId,mn;t2

????????查詢的結果：
????????

????????

step3: 按月累計計算訪問量

????????我們將第二步的結果用變量 t2 來表示。到這一步，我們用一個sum開窗函數，對userid進行分組，mn時間進行排序即可大功告成。

selectuserId,mn,mn_count,sum(mn_count) over(partition by userId order by mn) mn_all from t2;

????????最終結果：

完整SQL

????????溫馨提示:上述的步驟展示的都是不完整的SQL，每步使用變量代替前一步的SQL語句只是為了方便給大家展示，實際上運行的結果都是作者將完整的SQL放進去跑的哈~

selectuserId,mn,mn_count,sum(mn_count) over(partition by userId order by mn) mn_all from ( selectuserId,mn,sum(visitCount) mn_countfrom(selectuserId,date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,visitCountfromaction)t1 group by userId,mn)t2;

3、分組TopN

????????有50W個店鋪，每個顧客訪客訪問任何一個店鋪的任何一個商品時都會產生一條訪問日志，訪問日志存儲的表名為Visit，訪客的用戶id為user_id，被訪問的店鋪名稱為shop。

????????

????????
????????需求：每個店鋪訪問次數top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數。

step1：查詢每個店鋪被每個用戶訪問次數

????????因為我們最終需要獲取每個店鋪訪問量top3的用戶信息，所以在這一步，我們就先把每個店鋪的每個用戶的訪問次數計算出來。

select shop,user_id,count(*) ct from visit group by shop, user_id; t1

????????計算結果：

step2:計算每個店鋪被用戶訪問次數排名

????????有了第一步每個店鋪下所被訪問用戶的訪問量，我們想獲取前三，毫無疑問，我們需要使用到開窗函數 rank。

????????可能就有朋友問了，為什么不能用 row_number ？

????????主要還是 row_number 對于相同數據的排名不是一樣的，如果我們取Topic3,出現了相同訪問次數的數據，那我們肯定都得保留下來的對吧~~

select shop,user_id,ct,rank() over (partition by shop order by ct desc) rk from t1; t2

????????計算結果：

step3: 取每個店鋪排名前3的數據

????????有了 step2 的結果，我們想要取每個店鋪前三的數據豈不是輕而易舉~

select shop,user_id,ct from t2 where rk<=3;

????????計算結果:

????????

完整SQL

????????好了，結果已經查詢出來了，這里把上面step的SQL整合到一起~

select shop,user_id,ct from (select shop,user_id,ct,rank() over (partition by shop order by ct desc) rkfrom (select shop,user_id,count(*) ctfrom visitgroup by shop,user_id) t1) t2 where rk <= 3;

結語

????????我們不論是看書還是刷題，不在于數量多少，而一定要求“精”。這就要求我們學會去思考，學會舉一反三。真正具備解題能力的人，我相信一定不是把時間花在大量刷題上，而是懂得從不同類型的習題上，及時總結復習的人。

????????以上3道SQL“小菜”怕是滿足不了大伙，以后有機會再為大家總結些別的題目，本篇文章到這里就結束了。對技術宇宙充滿好奇，喜歡本文的朋友，可以掃碼關注作者原創公眾號【猿人菌】，我們下期見!

掃碼關注

關注即可獲取高質量思維導圖，互聯網一線大廠面經，大數據珍藏精品書籍...期待您的關注!

總結

以上是生活随笔為你收集整理的3道常见的SQL笔试题，你要不要来试试!的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。