本篇節(jié)選自書籍《對(duì)比Excel，輕松學(xué)習(xí)SQL數(shù)據(jù)分析》一書，主要講解數(shù)據(jù)分析面試中常見的30道SQL面試題。1-10題見：30道經(jīng)典SQL面試題講解(1-10)

11 行列互換

現(xiàn)在我們有下面這么一個(gè)表row_col_table，這個(gè)表中每年每月的銷量是一行數(shù)據(jù)：

我們需要把上面這種縱向存儲(chǔ)數(shù)據(jù)的方式改成下表所示的橫向存儲(chǔ)：

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

select
    year_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
from
    demo.row_col_table
group by
    year_num

解題思路：

我們要把縱向數(shù)據(jù)表轉(zhuǎn)換成橫向數(shù)據(jù)表，首先是把多行的年數(shù)據(jù)轉(zhuǎn)化為一年是一行，可以通過group by實(shí)現(xiàn)；group by一般需要與聚合函數(shù)一起使用，但是不是對(duì)所有數(shù)據(jù)進(jìn)行聚合，所以我們通過case when來達(dá)到對(duì)指定月份數(shù)據(jù)進(jìn)行聚合。

12 多列比較

現(xiàn)在表col_table中有col_1、col_2、col_3三列數(shù)據(jù)，我們需要根據(jù)這三列數(shù)據(jù)生成最后一列結(jié)果列，結(jié)果列的生成規(guī)則為：如果col_1大于col_2時(shí)選col_1列，如果col_2大于col_3列時(shí)選col_3列,否則選col_2列。

col_table表如下所示：

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

select
 col_1
 ,col_2
 ,col_3
 ,(case when col_1 > col_2 then col_1
     when col_2 > col_3 then col_3
   else col_2
   end) as all_result
from
 demo.col_table

解題思路：

這個(gè)多列比較其實(shí)就是一個(gè)多重判斷的過程，借助case when即可實(shí)現(xiàn)，先去判斷col_1和col_2的關(guān)系，然后再去判斷col_2和col_3的關(guān)系。這里需要注意一下各判斷的執(zhí)行順序，先去執(zhí)行第一行case when，然后再去執(zhí)行第二行的。最后運(yùn)行結(jié)果如下：

13 對(duì)成績進(jìn)行分組

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

subject_table表如下所示：

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

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
from
    demo.subject_table
group by 
    (case 
        when score < 60 then "60分以下"
        when score < 80 then "60-80分"
        when score < 100 then "80-100分"
    else "其他"
    end)

解題思路：

我們現(xiàn)在需要知道每個(gè)成績段內(nèi)的學(xué)生數(shù)，需要做的第一件事就是對(duì)成績進(jìn)行分段，利用的就是case when，對(duì)成績分段完成以后再對(duì)分段結(jié)果進(jìn)行g(shù)roup by，然后再在組內(nèi)計(jì)數(shù)獲得每個(gè)分段內(nèi)的學(xué)生數(shù)。最后運(yùn)行結(jié)果如下：

14 周累計(jì)數(shù)據(jù)獲取

現(xiàn)在我們有一個(gè)訂單明細(xì)表order_table，這張表中存儲(chǔ)了order_id(訂單id)、order_date(訂單日期)以及其他訂單相關(guān)信息，現(xiàn)在我們需要每天獲取本周累計(jì)的訂單數(shù)，本周累計(jì)是指本周一到獲取數(shù)據(jù)當(dāng)天，比如今天是周三，那么本周累計(jì)就是周一到周三。這個(gè)該怎么實(shí)現(xiàn)呢？

order_table表如下所示：

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

select
    curdate()
    ,count(order_id) as order_cnt
from
    demo.order_table
where 
    weekofyear(order_date) = weekofyear(curdate())
    and year(order_date) = year(curdate())

解題思路：

我們是要獲取本周累計(jì)的訂單數(shù)，只需要把本周的訂單明細(xì)篩選出來，然后對(duì)訂單id進(jìn)行計(jì)數(shù)就是我們想要的。那該怎么把本周的訂單明細(xì)篩選出來呢？讓訂單日期所屬的周與程序運(yùn)行當(dāng)日所屬的周是一個(gè)周，且所屬的年是同一年。后面這個(gè)條件一定要注意，因?yàn)橹軘?shù)在不同年份是會(huì)重復(fù)的，但是在同一年內(nèi)是不重復(fù)的。比如2019年有一個(gè)52周，2020年也會(huì)有，但是不會(huì)在一年里面出現(xiàn)兩個(gè)52周。最后運(yùn)行結(jié)果如下：

15 周環(huán)比數(shù)據(jù)獲取

我們現(xiàn)在需要根據(jù)訂單明細(xì)表order_table，獲取當(dāng)日的訂單數(shù)；當(dāng)日的環(huán)比訂單數(shù)，即昨天的數(shù)據(jù)。

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

select
    count(order_id) as order_cnt
    ,count(if(date_sub(curdate(),interval 1 day) = order_date,order_id,null)) last_order_cnt
from
    demo.order_table

解題思路：

當(dāng)日的訂單數(shù)比較好獲取，主要是環(huán)比數(shù)據(jù)的獲取，當(dāng)訂單日期等于當(dāng)日日期向前偏移1天的日期時(shí)，對(duì)order_id進(jìn)行計(jì)數(shù)就是昨日的訂單數(shù)。這里面需要注意的是，當(dāng)if條件不滿足時(shí)，結(jié)果為null，而不能是別的，因?yàn)閏ount(null)=0，而count()其他內(nèi)容不等于0。最后運(yùn)行結(jié)果如下：

16 查找獲獎(jiǎng)同學(xué)信息

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

table1表如下所示：

table2表如下所示：

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

select
    table1.*
from
    demo.table1
left join
    demo.table2
    on table1.id = table2.id
where
    table2.id is not null

解題思路：

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

17 計(jì)算用戶留存情況

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

user_login表如下所示：

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

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
    (select
        t1.uid
        ,t1.first_time
        ,t2.last_time
        ,datediff(t2.last_time,t1.first_time) day_value
    from
        (select
            uid
            ,date(min(login_time)) as first_time
        from
            demo.user_login
        group by
            uid)t1
    left join
        (select
            uid
            ,date(max(login_time)) as last_time
        from
            demo.user_login
        group by
            uid)t2
    on 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)

解題思路：

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

18 篩選最受歡迎的課程

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

course_table表如下所示：

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

select 
    course
    ,count(id) as stu_num
from
    demo.course_table
group by
    course
order by
    count(id) desc
limit 1

解題思路：

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

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

現(xiàn)在再想一下，如果有多門課程選擇人數(shù)一樣多時(shí)怎么辦？先想一下再看我的思路。

select 
    course
    ,count(id) as stu_num
from
    demo.course_table
group by
    course
having 
    count(id) = (select 
                 max(stu_num)
                from
                 (select 
                        course
                        ,count(id) as stu_num
                    from
                        demo.course_table
                    group by
                        course
                    )a
                )

解題思路：

如果存在被選擇一樣多的課程，我們要把一樣多的課程全部篩選出來。首先我們還是需要把每門課程以及被選擇的人數(shù)獲取出來，獲取思路與第一種思路是一樣的，也是針對(duì)課程進(jìn)行g(shù)roup by，然后再針對(duì)組內(nèi)的人數(shù)進(jìn)行計(jì)數(shù)；不同點(diǎn)在于最多人數(shù)獲取上。第一種思路是默認(rèn)選擇人數(shù)最多的課程只有一個(gè)，而第二種思路是假設(shè)選擇人數(shù)最多的課程有多個(gè)時(shí)，我們就需要把選擇人數(shù)最多的人數(shù)算出來，這里利用子查詢?nèi)ド?；最后再利用having對(duì)分組后的結(jié)果進(jìn)行篩選，從而得到選擇人數(shù)最多的課程。

19 篩選出每個(gè)年級(jí)最受歡迎的三門課程

還是前面的course_table，現(xiàn)在我們想知道每個(gè)年級(jí)被選擇最多的三門課程,該怎么實(shí)現(xiàn)呢？

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

select 
    *
from
    (select
        grade
        ,course
        ,stu_num
        ,row_number() over(partition by grade order by stu_num desc) as course_rank
    from
        (select
            grade
            ,course
            ,count(id) as stu_num
        from
            demo.course_table
        group by
            grade
            ,course
        )a
    )b
where 
    b.course_rank < 4

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

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

20 求累積和

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

consum_order_table表如下所示：

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

select
    count(uid)
from
    (select
        uid
        ,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_rate
    from
        (select
            uid
            ,sum(amount) amount
        from
            demo.consum_order_table
        group by 
            uid
        )
    uid_table)t
where 
    t.consum_amount_rate < 0.8

解題思路：

我們要獲取人80%的訂單金額最少由多少用戶貢獻(xiàn)的，因?yàn)槲覀儸F(xiàn)在只有一個(gè)訂單明細(xì)表，所以我們需要先生成一個(gè)人維度的訂單金額表，然后再在這個(gè)人維度表的基礎(chǔ)上去進(jìn)行累積和，累計(jì)和的實(shí)現(xiàn)可以通過窗口函數(shù)來實(shí)現(xiàn)，這樣就可以得到人維度的累積訂單金額，在生成累積和的時(shí)候需要按照訂單金額進(jìn)行降序排列，這樣就可以得到最少的人數(shù)，最后再利用一個(gè)子查詢，獲取到全部的訂單金額，用累積訂單金額去除全部訂單金額，就可以得到累積的訂單金額貢獻(xiàn)情況。最后運(yùn)行結(jié)果如下：

想進(jìn)一步了解更多內(nèi)容的同學(xué)，可以閱讀《對(duì)比Excel，輕松學(xué)習(xí)SQL數(shù)據(jù)分析》一書。

▊《對(duì)比Excel，輕松學(xué)習(xí)SQL數(shù)據(jù)分析》

張俊紅 著

學(xué)習(xí)SQL 的主要原因是工作需要。網(wǎng)上關(guān)于數(shù)據(jù)相關(guān)崗位的招聘都要求有熟練使用SQL 這一條，為什么會(huì)這樣呢？這是因?yàn)槲覀冐?fù)責(zé)的是與數(shù)據(jù)相關(guān)的工作，而獲取數(shù)據(jù)是我們工作的第一步，比如，你要通過數(shù)據(jù)做決策，但是現(xiàn)在公司的數(shù)據(jù)基本上不存儲(chǔ)在本地Excel 表中，而是存儲(chǔ)在數(shù)據(jù)庫中，想要從數(shù)據(jù)庫中獲取數(shù)據(jù)就需要使用SQL，所以熟練使用SQL 成了數(shù)據(jù)相關(guān)從業(yè)者入職的必要條件。本書的所有代碼和函數(shù)均以MySQL 8.0 為主。

（掃碼了解本書詳情）

如果喜歡本文
歡迎 在看丨留言丨分享至朋友圈 三連


 熱文推薦  

你好，這是微視AI還原的李煥英
從產(chǎn)品經(jīng)理到產(chǎn)品架構(gòu)師
書單|互聯(lián)網(wǎng)企業(yè)面試案頭書之程序員技術(shù)篇
一文讀懂火爆全網(wǎng)的「螞蟻呀嘿」


▼點(diǎn)擊閱讀原文，獲取本書詳情~