Zi 字媒體

本文是《如何七周成為數據分析師》的第十一篇教程，如果想要了解寫作初衷，可以先行閱讀七周指南。溫馨提示：如果您已經熟悉資料庫，大可不必再看這篇文章，或只挑選部分。我們在上一篇《SQL，從入門到熟練》文章已經掌握了除Join外的常用語法和函數，今天會通過一系列的練習徹底掌握SQL。我們知道，資料庫由多張表組成，表與表之間可以實現關聯。上圖就是一個簡單的關聯模型：Students.addressId = Address.idStudents.id = Scores.studentIdScores.courseId = Courses.id那麼，如何在SQL查詢語句中將兩個表聯接起來？我們將運用最重要的語法Join。上面語句，join將Students和Address兩表關聯，關聯需要一個或多個欄位作為聯接橋樑。例子中的橋樑就是addressid，我們使用on語句，將Students表的addressId欄位和Address的id欄位匹配。這裡需要注意的是，因為欄位可能重名，所以一旦使用了Join，欄位前應該加上表名，如Students.addressId和Address.id ，這種用法是為了欄位的唯一性，否則遇到重名，系統不知道使用哪個欄位，就會報錯。上圖是更優雅的寫法，將表命名為一個縮略的別名，避免了語句過於冗餘。不要使用拼音做別名，不是好習慣。Join語法有很多不同的變形，Left Join，Outer Join等，新人很容易混淆。這個我們可以用數學中的交集和並集掌握。上圖很清晰地解釋了各Join語法。Inner Join最常見，叫做內聯接，可以縮寫成Join，找的是兩張表共同擁有的欄位。Left Join叫做左聯接，以左表（join符號前的那張表）為主，返回所有的行。如果右表有共同欄位，則一併返回，如果沒有，則為空。selectPersons.LastName, Persons.FirstName, Orders.OrderNofromPersonsleft joinOrders onPersons.Id_P=Orders.Id_Porder byPersons.LastName於是輸出結果為：結果集中，Bush那一行的OrderNo為空，就是因為Id_P無法匹配上，返回了Null。如果改成Inner join，則不會返回整個Bush所在行。這是Inner Join和Left Join的區別，也是面試中經常會問到的題目。Right Join和Left Join沒有區別，A Left Join B 等價於 B Right Join A。Full Join叫做全聯接，也叫做Full Outer Join，意思是不管有的沒的，只要存在，就返回。最後兩行就是所謂的「不管有的沒的，只要存在字元串，就返回」的結果，它們Id_P並沒有匹配上，但還是給出了返回，只是為空欄位不同。這三者的關係，我們可以理解為：A Full Join B = A Left Join B + A Right Join B - A Inner Join B，這就是數學上的集合運算，雖然SQL的表並不能加減法。如果還一知半解，看最上面的Join示例圖，用面積的角度看也明白了。通過上面的例子，我們已經掌握了Join的主流語法，其他無非是變種。比如加約束條件 where XX is null，這裡的XX可以是結果為空的欄位。拿上文Left Join的例子演示：select Persons.LastName, Persons.FirstName, Orders.OrderNofrom Personsleft join Orderson Persons.Id_P=Orders.Id_PwhereOrders.Id_Pis Null最終返回的結果就是Bush這一行。當我們有多個欄位要匹配時，on後面可以通過 and 進行多項關聯。select*fromAjoin B on A.name = B.nameandA.phone = B.phone上圖就是一個簡單的適用場景，將用戶姓名和手機號進行多項關聯。它也可以加入其他的條件判斷。select * from Ajoin B on A.name = B.name and A.phone = B.phoneandB.sex = '男'我們再加一個and，將B表的用戶性別限定為男。這種用法等價於where B.sex = '男'。當數據量大到一定程度，通過這種約束條件，能優化查詢性能。到這裡，SQL的常用語法已經講解的差不多了，我們進行實戰吧。leetcode.com網站是知名的演算法競賽題，去上面刷SQL吧。註冊完後進入leetcode.com/problemset/database頁面。那裡有幾道MySQL題目。因為時間關係，我只講解Join相關，大家有興趣可以刷其他題，都不難的。SQLZoo也能刷，就是頁面丑了點，所以我十分感動地拒絕了它。我們從Easy開始，選擇題目Combine Two Tables。紅色字元是表名，第一列是欄位名，第二列是數據類型。題目希望我們通過兩張表輸出：FirstName, LastName, City, State四個欄位。單純的Inner Join就能完成了。記住噢，答案需要完全一致，也就是說最終的結果必須是四個欄位，不能多不能少，順序也不能亂，大小寫要嚴格。這一題大家自己做吧。通過後會有個綠色的Accepted提示。接下來選擇Medium難度的Department Highest Salary。這裡有兩張表，員工表和部門表，我們希望找出各個部門的最高薪水。selectd.Id, #這是部門ID d.NameasName, #這是部門名字 max(e.Salary)asSalary #這是最高薪水fromDepartment djoinEmployee eone.DepartmentId = d.Idgroup byd.Id上述的查詢語句找出了最高薪水的部門，我們是否能直接使用其作為答案？不能。這裡有一個邏輯的小陷阱，當最高薪水非單個時，使用max會只保留第一個，而不是列舉所有，所以我們需要更複雜的查詢。因為已經有了各部門最高薪水的數據，可以將它作為一張新表，用最高薪水關聯僱員表，獲得我們最終的答案。上面就是最終解法（#是解釋給你們看的，中文會報錯的），當然解法應該不是唯一的，大家有興趣可以繼續研究。最終，我們選Hard模式的Department Top Three Salaries。範例數據沒有一丁點變化，它需要我們求出各部門薪水前三的數據。如果最高薪水只有兩個，則輸出兩個。排名前三的數據，我們可以使用order by 降序排列出來，然後通過limit 限定為3，但是新的問題是：既要各部門前三，也存在排名並列的情況。此時order by就無能為力了。如果是SQL Server或者Oracle，我們可以使用row_number分組排序函數，但是MySQL沒有，其中的一種思路是利用set語法設置變數，間接應用row_number。我們還能使用另外一種思路。select* from Employeeasewhere ( selectcount(distincte1.Salary) fromEmployee e1 wheree1.Salary > e.Salary ande1.DepartmentId = e.DepartmentId ) < 3上述的例子巧妙地借用了子查詢。在where語句中，我們用子表e1與父表（外表）e進行比對。SQL是允許子查詢的表和父查詢的表進行運算的。e1.DepartmentId = e.DepartmentId作為條件約束，避免跨部門。e1.Salary > e.Salary則是邏輯判斷，通過count函數，逐行計算出e表中有多少薪水比e1的薪水低。因為e1表和e表實際上是等價的。所以返回的count(distinct e1.Salary) 代表e1表有中多少薪水比e表的高，上圖的例子，答案是2（90000和85000比它高）。如果是0，則代表e表中該行薪水最高（沒有比它高的），1代表第二高，2代表第三高。於是便過濾出Top 3的薪水。最後通過join計算出結果。在實際查詢過程中，不建議大家使用這種運算方式，因為運算效率不會快。其實換我，我更可能group by后導出結果用Excel處理。到這裡，大家對Join已經有一個大概的了解了吧。真實的數據查詢場景中，Join會用到很多，業務複雜用五六個Join也是常態，如果算上各類邏輯處理，SQL代碼行數可以破百。這時候，考驗的就是熟練度了。SQL只要多加訓練，並不是一門很難掌握的語言。除了技巧，還要看你對業務表的熟悉程度，一般公司發展大了，百來張表很正常，各類業務邏輯各種Join，各欄位的含義，這是同樣要花費時間的苦功夫。希望大家對SQL已經有一個初步的掌握了。SQL學好了，以後應用大數據的Hive和SparkSQL也是輕而易舉的。接下來，我們將要進入第五周的大魔王課程，統計學，從入門到放棄，哈哈哈。