本篇節選自書籍《對比Excel，輕松學習SQL數據分析》一書，主要講解數據分析面試中常見的30道SQL面試題。1-10題見：30道經典SQL面試題講解(1-10)

11 行列互換

現在我們有下面這么一個表row_col_table，這個表中每年每月的銷量是一行數據：

year_nummonth_numsales

2019	1	100
2019	2	200
2019	3	300
2019	4	400
2020	1	200
2020	2	400
2020	3	600
2020	4	800

我們需要把上面這種縱向存儲數據的方式改成下表所示的橫向存儲：

year_numm1m2m3m4

2019	100	200	300	400
2020	200	400	600	800

自己先想一下代碼怎么寫，然后再參考我的代碼。

selectyear_num,sum(case?when?month_num?=?1?then?sales?end)?as?m1,sum(case?when?month_num?=?2?then?sales?end)?as?m2,sum(case?when?month_num?=?3?then?sales?end)?as?m3,sum(case?when?month_num?=?4?then?sales?end)?as?m4 fromdemo.row_col_table group?byyear_num

解題思路：

我們要把縱向數據表轉換成橫向數據表，首先是把多行的年數據轉化為一年是一行，可以通過group by實現；group by一般需要與聚合函數一起使用，但是不是對所有數據進行聚合，所以我們通過case when來達到對指定月份數據進行聚合。

12 多列比較

現在表col_table中有col_1、col_2、col_3三列數據，我們需要根據這三列數據生成最后一列結果列，結果列的生成規則為：如果col_1大于col_2時選col_1列，如果col_2大于col_3列時選col_3列,否則選col_2列。

col_table表如下所示：

col_1col_2col_3

5	10	7
1	10	6
9	3	5
5	2	9
10	4	3
5	2	9
5	8	6
8	8	6

自己先想一下代碼怎么寫，然后再參考我的代碼。

selectcol_1,col_2,col_3,(case?when?col_1?>?col_2?then?col_1when?col_2?>?col_3?then?col_3else?col_2end)?as?all_result fromdemo.col_table

解題思路：

這個多列比較其實就是一個多重判斷的過程，借助case when即可實現，先去判斷col_1和col_2的關系，然后再去判斷col_2和col_3的關系。這里需要注意一下各判斷的執行順序，先去執行第一行case when，然后再去執行第二行的。最后運行結果如下：

col_1col_2col_3all_result

5	10	7	7
1	10	6	6
9	3	5	9
5	2	9	5
10	4	3	10
5	2	9	5
5	8	6	6
8	8	6	6

13 對成績進行分組

現在有一個某科目的學生成績表subject_table，這張表存儲了每位學生的id、score(成績)以及其他信息，我們想知道60分以下、60-80分、80-100分這三個成績段內分別有多少學生，該怎實現呢？

subject_table表如下所示：

idscore

1	56
2	91
3	67
4	54
5	56
6	69
7	61
8	83
9	99

自己先想一下代碼怎么寫，然后再參考我的代碼。

select(case?when?score?<?60?then?"60分以下"when?score?<?80?then?"60-80分"when?score?<?100?then?"80-100分"else?"其他"end)?as?score_bin,count(id)?as?stu_cnt fromdemo.subject_table group?by?(case?when?score?<?60?then?"60分以下"when?score?<?80?then?"60-80分"when?score?<?100?then?"80-100分"else?"其他"end)

解題思路：

我們現在需要知道每個成績段內的學生數，需要做的第一件事就是對成績進行分段，利用的就是case when，對成績分段完成以后再對分段結果進行group by，然后再在組內計數獲得每個分段內的學生數。最后運行結果如下：

score_binstu_cnt

60分以下	3
80-100分	3
60-80分	3

14 周累計數據獲取

現在我們有一個訂單明細表order_table，這張表中存儲了order_id(訂單id)、order_date(訂單日期)以及其他訂單相關信息，現在我們需要每天獲取本周累計的訂單數，本周累計是指本周一到獲取數據當天，比如今天是周三，那么本周累計就是周一到周三。這個該怎么實現呢？

order_table表如下所示：

order_idorder_date

1	2019/1/8
2	2019/1/9
3	2019/1/10
4	2019/1/11
5	2020/1/8
6	2020/1/9
7	2020/1/10
8	2020/1/11
9	2020/1/12

自己先想一下代碼怎么寫，然后再參考我的代碼。

selectcurdate(),count(order_id)?as?order_cnt fromdemo.order_table where?weekofyear(order_date)?=?weekofyear(curdate())and?year(order_date)?=?year(curdate())

解題思路：

我們是要獲取本周累計的訂單數，只需要把本周的訂單明細篩選出來，然后對訂單id進行計數就是我們想要的。那該怎么把本周的訂單明細篩選出來呢？讓訂單日期所屬的周與程序運行當日所屬的周是一個周，且所屬的年是同一年。后面這個條件一定要注意，因為周數在不同年份是會重復的，但是在同一年內是不重復的。比如2019年有一個52周，2020年也會有，但是不會在一年里面出現兩個52周。最后運行結果如下：

cur_dateorder_cnt

2020-01-12	5

15 周環比數據獲取

我們現在需要根據訂單明細表order_table，獲取當日的訂單數；當日的環比訂單數，即昨天的數據。

自己先想一下代碼怎么寫，然后再參考我的代碼。

selectcount(order_id)?as?order_cnt,count(if(date_sub(curdate(),interval?1?day)?=?order_date,order_id,null))?last_order_cnt fromdemo.order_table

解題思路：

當日的訂單數比較好獲取，主要是環比數據的獲取，當訂單日期等于當日日期向前偏移1天的日期時，對order_id進行計數就是昨日的訂單數。這里面需要注意的是，當if條件不滿足時，結果為null，而不能是別的，因為count(null)=0，而count()其他內容不等于0。最后運行結果如下：

order_cntlast_order_cnt

9	1

16 查找獲獎同學信息

現在有一張學生信息表table1，這張表記錄了id、name等一些其他信息；還有另外一張獲獎名單表table2，這張表記錄了獲獎學生的id和name。現在我們想要通過table1獲取獲獎學生的更多信息。

table1表如下所示：

idname

1	王小鳳
2	劉詩迪
3	李思雨
4	張文華
5	張青云
6	徐文杰
7	李智瑞
8	徐雨秋
9	孫皓然

table2表如下所示：

idname

1	王小鳳
2	劉詩迪
3	李思雨
7	李智瑞
8	徐雨秋
9	孫皓然

自己先想一下代碼怎么寫，然后再參考我的代碼。

selecttable1.* fromdemo.table1 left?joindemo.table2on?table1.id?=?table2.id wheretable2.id?is?not?null

解題思路：

我們要獲取獲獎同學的全部信息，已知table1表中存儲了全部學生的全部信息，我們用table1去左連接table2，如果該同學有獲獎，就會在table2中能找到，反之則找不到。所以我們就可以利用table2的id是否為空來判斷該同學有沒有獲獎，進而把我們想要的信息通過where條件篩選出來。最后運行結果如下：

idname

1	王小鳳
2	劉詩迪
3	李思雨
7	李智瑞
8	徐雨秋
9	孫皓然

17 計算用戶留存情況

現在有一張用戶登陸表user_login，這張表記錄了每個用戶每次的登陸時間，uid(用戶id)和login_time(登陸時間)。我們想看用戶的次日留存數、三日留存數、七日留存數，只要用戶從首次登陸以后再有登陸就算留存下來了，該怎么實現呢？

user_login表如下所示：

uidlogin_time

1	2019/1/1 6:00
1	2019/1/2 10:00
1	2019/1/4 19:00
2	2019/1/2 10:00
2	2019/1/3 9:00
2	2019/1/9 14:00
3	2019/1/3 8:00
3	2019/1/4 10:00

自己先想一下代碼怎么寫，然后再參考我的代碼。

select(case?when?t3.day_value?=?1?then?"次日留存"when?t3.day_value?=?3?then?"三日留存"when?t3.day_value?=?7?then?"七日留存"else?"其他"end)?as?type,count(t3.uid)?uid_cnt from(selectt1.uid,t1.first_time,t2.last_time,datediff(t2.last_time,t1.first_time)?day_valuefrom(selectuid,date(min(login_time))?as?first_timefromdemo.user_logingroup?byuid)t1left?join(selectuid,date(max(login_time))?as?last_timefromdemo.user_logingroup?byuid)t2on?t1.uid?=?t2.uid)t3 group?by(case?when?t3.day_value?=?1?then?"次日留存"when?t3.day_value?=?3?then?"三日留存"when?t3.day_value?=?7?then?"七日留存"else?"其他"end)

解題思路：

留存是指用戶用戶從首次登陸以后再有登陸就算留存下來，不同時長的留存表示這么時長以后仍會再次登陸，比如三日登陸表示用戶自首次登陸以后第三天也會進行登陸。我們現在要計算不同留存時長的用戶數，首先需要計算不同用戶的留存時長，可以用該用戶的最后一次登陸時間與首次登陸時間做差就是該用戶的留存時長，然后再對留存時長進行分組聚合就得到了我們想要的不同留存時長的用戶數。最后運行結果如下：

typeuid_cnt

三日留存	1
七日留存	1
次日留存	1

18 篩選最受歡迎的課程

現在有一張學生科目表course_table，這張表存儲了每一位學生的id、name(姓名)、grade(年級)、course(選修課程)以及一些其他信息，現在我們想知道哪門課被學生選的人數最多？

course_table表如下所示：

idnamegradecourse

1	王小鳳	一年級	心理學
2	劉詩迪	二年級	心理學
3	李思雨	三年級	社會學
4	張文華	一年級	心理學
5	張青云	二年級	心理學
6	徐文杰	三年級	計算機
7	李智瑞	一年級	心理學
8	徐雨秋	二年級	計算機
9	孫皓然	三年級	社會學
10	李春山	一年級	社會學

自己先想一下代碼怎么寫，然后再參考我的代碼。

select?course,count(id)?as?stu_num fromdemo.course_table group?bycourse order?bycount(id)?desc limit?1

解題思路：

我們是要獲取被選人數最多的課程，首先需要對課程進行分組，使用的是group by；然后再對組內人數進行計數，即選擇該課程的人數，使用的count；然后再對課程人數進行降序排列，使用的是order by；最后把排在第一的課程篩選出來，就是我們要的被選擇人數最多的課程。最后結果如下：

coursestu_num

心理學	5

想一下上面這種思路是否有問題呢？如果要是有兩門或者多門課程的選擇人數一樣多的時候上面的這種思路得出來的結果是否還正確呢？顯然是不正確的。

現在再想一下，如果有多門課程選擇人數一樣多時怎么辦？先想一下再看我的思路。

select?course,count(id)?as?stu_num fromdemo.course_table group?bycourse having?count(id)?=?(select?max(stu_num)from(select?course,count(id)?as?stu_numfromdemo.course_tablegroup?bycourse)a)

解題思路：

如果存在被選擇一樣多的課程，我們要把一樣多的課程全部篩選出來。首先我們還是需要把每門課程以及被選擇的人數獲取出來，獲取思路與第一種思路是一樣的，也是針對課程進行group by，然后再針對組內的人數進行計數；不同點在于最多人數獲取上。第一種思路是默認選擇人數最多的課程只有一個，而第二種思路是假設選擇人數最多的課程有多個時，我們就需要把選擇人數最多的人數算出來，這里利用子查詢去生成；最后再利用having對分組后的結果進行篩選，從而得到選擇人數最多的課程。

19 篩選出每個年級最受歡迎的三門課程

還是前面的course_table，現在我們想知道每個年級被選擇最多的三門課程,該怎么實現呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

select?* from(selectgrade,course,stu_num,row_number()?over(partition?by?grade?order?by?stu_num?desc)?as?course_rankfrom(selectgrade,course,count(id)?as?stu_numfromdemo.course_tablegroup?bygrade,course)a)b where?b.course_rank?<?4

解題思路：這是典型的獲取組內排名的問題，我們前面的一個問題是獲取報名人數最多的課程，只需要把每門課程的報名人數獲取到，然后把最多的一個取出來就是我們想要的。可是現在這個問題不僅要獲取最多的，還要獲取第二多、第三多的。而且還是每個年級內的第一、第二、第三多。對于這種問題，我們可以使用窗口函數來實現，先生成每門課程的報名人數，然后再利用row_number()生成每個年級內每門課程的排序結果，最后再通過排序結果篩選出我們需要的排序。最后運行結果如下：

gradecoursestu_numcourse_rank

一年級	心理學	3	1
一年級	社會學	1	2
三年級	社會學	2	1
三年級	計算機	1	2
二年級	心理學	2	1
二年級	計算機	1	2

當然，我們這里可以通過where條件篩選任意排名的課程。比如如果要篩選排名第5-8的課程，只需要讓where條件中的b.course_rank between 5 and 8即可。

20 求累積和

現在有一張2019年一整年的訂單表consum_order_table，consum_order_table包含order_id(訂單id)、uid(用戶id)、amount(訂單金額)，現在我們想看下80%的訂單金額最少是由多少用戶貢獻的，該怎么實現呢？

consum_order_table表如下所示：

order_iduidamount

201901	1	10
201902	2	20
201903	3	15
201904	3	15
201905	4	20
201906	4	20
201907	5	25
201908	5	25
201909	6	30
201910	6	30
201911	7	35
201912	7	35

自己先想一下代碼怎么寫，然后再參考我的代碼。

selectcount(uid) from(selectuid,amount,sum(amount)?over(order?by?amount?desc)?as?consum_amount,(sum(amount)?over(order?by?amount?desc))/(select?sum(amount)?from?demo.consum_order_table)?as?consum_amount_ratefrom(selectuid,sum(amount)?amountfromdemo.consum_order_tablegroup?by?uid)uid_table)t where?t.consum_amount_rate?<?0.8

解題思路：

我們要獲取人80%的訂單金額最少由多少用戶貢獻的，因為我們現在只有一個訂單明細表，所以我們需要先生成一個人維度的訂單金額表，然后再在這個人維度表的基礎上去進行累積和，累計和的實現可以通過窗口函數來實現，這樣就可以得到人維度的累積訂單金額，在生成累積和的時候需要按照訂單金額進行降序排列，這樣就可以得到最少的人數，最后再利用一個子查詢，獲取到全部的訂單金額，用累積訂單金額去除全部訂單金額，就可以得到累積的訂單金額貢獻情況。最后運行結果如下：

count(uid)

4

感興趣的同學可以點擊下方鏈接了解：

總結

以上是生活随笔為你收集整理的30道经典SQL面试题讲解(11-20)的全部內容，希望文章能夠幫你解決所遇到的問題。

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