用好CASE，輕鬆表達複雜條件
22 人贊了文章
SQL語言在數據分析中的重要性不言而喻，它是每個數據分析師必備的技能。畢竟，能否準確、快速地從資料庫中取數、得到想要的數據，關係到之後進行的每一步分析。
相對於很多其他語言來說，SQL是最容易入門的。不誇張地說，一些簡單的語句一天之內就能學會。但是，如果想要用最簡單、最高效的語句得到想要的結果，還是需要下一番功夫的。
就我個人而言，因為公司用了Oracle資料庫，有時候也會需要編寫一些語句來驗證或是提取數據。但用得最多的無非就是SELECT，或者JOIN幾個表，都是非常粗淺的內容。為了今後的職業發展，我從頭開始系統地學習了SQL語言。
學完之後可以在SQLZOO這個網站上做練習，題目都很經典。部分答案請參考我的另一篇文章：
Miaooo：SQLZOO
我用的參考書主要是日本作者MICK的《SQL基礎教程》和《SQL進階教程》，鏈接如下：
SQL基礎教程 (豆瓣)SQL進階教程 (豆瓣)
這兩本書我都非常推薦，講得淺顯易懂但是卻很實用。
其中有一個知識點給我的印象非常深刻：CASE表達式的運用。接下來我將著重介紹該表達式的一種用法，這也是進階教程中所反覆提到的：特徵函數。作者這樣介紹：
在這裡，CASE表達式的作用相當於進行判斷的函數，用來判斷各個元素（=行）是否滿足了某種條件的集合。
他對此的評價是：
熟練掌握這些方法之後，不管多麼複雜的條件都能輕鬆表達出來（不是誇張，是真的）。
下面就讓我們來研究下是不是真的這麼厲害。
首先了解一下CASE表達式的語法。
CASE WHEN <判斷表達式> THEN <表達式> WHEN <判斷表達式> THEN <表達式> WHEN <判斷表達式> THEN <表達式> ... ELSE <表達式>END
《SQL基礎教程》中這樣介紹：
CASE表達式會對最初的WHEN字句中的<判斷表達式>進行判斷開始執行。如果該表達式的真值為真（TRUE），那麼就返回THEN字句中的表達式，CASE表達式到此為止。如果結果不為真，那麼就跳轉到下一句WHEN字句的判斷之中。如果直到最後的WHEN字句為止返回結果都不為真，那麼就會返回ELSE中的表達式，執行終止。
通過一個簡單的例子就能了解。
假設有這樣一張名為「province」的表：
想要在結果中顯示每個省份屬於南方還是北方，可以這樣編寫SQL語句：
SELECT name, (CASE WHEN name=上海 THEN 南方 WHEN name=北京 THEN 北方 WHEN name=廣東 THEN 南方 WHEN name=浙江 THEN 南方 WHEN name=河北 THEN 北方 ELSE NULL END) AS areaFROM province
得到的結果如下：
那麼，CASE表達式的特徵函數是如何使用的呢？
我們看一個書上的例子：
一張名為Students的表，記錄了學生編號、學院和提交日期。我們想要從中查詢哪些學院的學生全部提交了報告。用CASE表達式可以這樣寫：
SELECT dptFROM StudentsGROUP BY dptHAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END)
這個語句的重點是最後一句，翻譯成通俗的語言就是：這個學院的總人數和提交日期不為空的人數相同，也就等同於全部的學生都提交了報告。
接下來看一個複雜些的例子：
一張名為TestResult的表（部分內容），記錄了如下信息。我們想要查詢女生平均分比男生平均分高的班級。乍一看是不是覺得無從下手？使用CASE表達式的特徵函數就能輕鬆解決：
SELECT classFROM TestResultsGROUP BY classHAVING AVG(CASE WHEN sex = 男 THEN score ELSE NULL END) < AVG(CASE WHEN sex = 女 THEN score ELSE NULL END)
是不是有一種豁然開朗的感覺？而且代碼非常簡潔優雅。
利用CASE表達式的特徵函數的核心思想就是，在HAVING語句後面將需要查詢的條件列出來，用CASE表達式判斷是否滿足條件，滿足則進行運算，不滿足賦0或者空值（視情況而定）。聚合函數都可以運用在判斷之中。
這一運用在數據分析中也頗有用處，下面我們將在MySQL中，對一個真實的數據集進行實際操作，進行初步的分析。MySQL的安裝等請參考以下猴子老師的文章，可以說非常詳細了：
猴子：超級詳細的mysql安裝指南
本次處理的數據來源於kaggle網站，是一個有關線上課程的數據集，收集了哈佛和MIT開辦的一系列課程的參與情況。
在導入資料庫之前數據經過了一些預處理，將課程主題分成了4列表示，以便進行之後的處理。
將其導入Navicat Premium中。
首先看一下具體的列名，可以看到該數據集包括了以下內容：Institution（機構）、Course Number（課程編號）、Launch Date（發布日期）、Course Title（課程名稱）、Instructors（講師）、Course Subject（課程主題）、Year（年份）、Honor Code Certificates（是否有榮譽證書）、Participants (Course Content Accessed)（參加人數）、Audited (> 50% Course Content Accessed)（學完一半的人數）、Certified（獲得證書）、 %Audited（%學完一半的人數）、%Certified（%獲得證書）% Certified of > 50% Course Content Accessed（%學完一半的人中獲得證書的人數） 、%Played Video（看過視頻的人數） 、%Posted in Forum （參與論壇的人數%）、%Grade Higher Than Zero（%評分高於0）、Total Course Hours (Thousands)（課程總時長）、Median Hours for Certification（獲得證書所花時間中位數）、Median Age（年齡中位數）、% Male（%男性）、% Female（%女性）、% Bachelors Degree or Higher（%高於本科學歷）。
先查看一下有多少不同主題的課程。創建一個視圖備用。
CREATE VIEW Course_Subject(`subject`)ASSELECT DISTINCT `Course Subject1`FROM appendixUNIONSELECT DISTINCT `Course Subject2`FROM appendixUNIONSELECT DISTINCT `Course Subject3`FROM appendixUNIONSELECT DISTINCT `Course Subject4`FROM appendixORDER BY `Course Subject1`
得到如下結果：
再創建一個視圖，把課程和主題對應起來。為了便於觀察，我們用開辦日期、課程編號、講師來唯一確定一個課程。
CREATE VIEW Course_Number_Subject(launch_date, number, instructors, subject)ASSELECT app.`Launch Date`,app.`Course Number`, app.Instructors, CS.subjectFROM appendix AS app JOIN Course_Subject AS csON CS.subject IN (app.`Course Subject1`, app.`Course Subject2`, app.`Course Subject3`, app.`Course Subject4`)
得到如下結果：
下面我們根據課程主題進行分析。
如果想要得到哪些主題中，80%以上的課程男性比女性參與者多，應該如何查詢呢？
嘗試以下代碼：
SELECT subjectFROM Course_Number_Subject AS cns JOIN appendix AS appON cns.number = app.`Course Number` AND cns.launch_date = app.`Launch Date` AND cns.instructors = app.InstructorsGROUP BY subjectHAVING COUNT(*)*0.8 < SUM(CASE WHEN app.`% Male` > 50 THEN 1 ELSE 0 END)
得到如下結果：
可以看出，男性在技術、數學、健康、政府等方面比女性更感興趣。
想要知道哪些主題中，70%以上的課程獲得證書的概率都在5%以上？可以這樣改寫：
SELECT subjectFROM Course_Number_Subject AS cns JOIN appendix AS appON cns.number = app.`Course Number` AND cns.launch_date = app.`Launch Date` AND cns.instructors = app.InstructorsGROUP BY subjectHAVING COUNT(*)*0.7 < SUM(CASE WHEN app.`% Certified` > 5 THEN 1 ELSE 0 END)
得到的結果如下：
可以看出，文科性質的課程更容易獲得證書。
如果想要查詢課程主題的其他信息，改變CASE表達式中的條件就可以了。
該數據集還有很多其他值得探索的方向，這裡只著重於CASE表達式的使用，其他的不多作介紹了。
原先覺得自己已經入了SQL的門，越看書越覺得自己的知識實在是太淺薄。我最近在看一本《Oracle查詢優化改寫》：
Oracle查詢優化改寫技巧與案例 (豆瓣)
這本書適合有一定基礎的同學學習，看完上面兩本可以拿來翻翻，主要是要在實際的資料庫練手，書中有一些案例還是很實用的。
簡單的語句想要高效、準確，還是需要下功夫的。學無止境，與大家共勉！
推薦閱讀：