SQL,從熟練到掌握

本文是如何快速成為數據分析師的第十一篇教程,如果想要了解寫作初衷,可以先行閱讀七周指南。溫馨提示:如果您已經熟悉資料庫,大可不必再看這篇文章,或只挑選部分。

我們在上一篇《SQL,從入門到熟練》文章已經掌握了除Join外的常用語法和函數,今天會通過一系列的練習徹底掌握SQL。

我們知道,資料庫由多張表組成,表與表之間可以實現關聯。

上圖就是一個簡單的關聯模型:

Students.addressId = Address.id

Students.id = Scores.studentId

Scores.courseId = Courses.id

那麼,如何在SQL查詢語句中將兩個表聯接起來?我們將運用最重要的語法Join。

select * from Studentsnjoin Address on Students.addressId = Address.idn

上面語句,join將Students和Address兩表關聯,關聯需要一個或多個欄位作為聯接橋樑。例子中的橋樑就是addressid,我們使用on語句,將Students表的addressId欄位和Address的id欄位匹配。

這裡需要注意的是,因為欄位可能重名,所以一旦使用了Join,欄位前應該加上表名,如Students.addressId和Address.id ,這種用法是為了欄位的唯一性,否則遇到重名,系統不知道使用哪個欄位,就會報錯。

select * from Students as snjoin Address as a on s.addressId = a.idn

上圖是更優雅的寫法,將表命名為一個縮略的別名,避免了語句過於冗餘。不要使用拼音做別名,不是好習慣。

Join語法有很多不同的變形,Left Join,Outer Join等,新人很容易混淆。這個我們可以用數學中的交集和並集掌握。

上圖很清晰地解釋了各Join語法。

Inner Join最常見,叫做內聯接,可以縮寫成Join,找的是兩張表共同擁有的欄位。

Left Join叫做左聯接,以左表(join符號前的那張表)為主,返回所有的行。如果右表有共同欄位,則一併返回,如果沒有,則為空。

我們以W3School上的數據為例:

select Persons.LastName, Persons.FirstName, Orders.OrderNonfrom Personsnleft join Orders on Persons.Id_P=Orders.Id_Pnorder by Persons.LastNamen

於是輸出結果為:

結果集中,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,意思是不管有的沒的,只要存在,就返回。

還是以之前的例子演示,下面是Full 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.OrderNonfrom Personsnleft join Ordersnon Persons.Id_P=Orders.Id_Pnwhere Orders.Id_P is Nulln

最終返回的結果就是Bush這一行。

當我們有多個欄位要匹配時,on後面可以通過 and 進行多項關聯。

select * from Anjoin B on A.name = B.name and A.phone = B.phonen

上圖就是一個簡單的適用場景,將用戶姓名和手機號進行多項關聯。它也可以加入其他的條件判斷。

select * from Anjoin B on A.name = B.name and A.phone = B.phone and B.sex = n

我們再加一個and,將B表的用戶性別限定為男。這種用法等價於where B.sex = 男。當數據量大到一定程度,通過這種約束條件,能優化查詢性能。

到這裡,SQL的常用語法已經講解的差不多了,我們進行實戰吧。leetcode.com網站是知名的演算法競賽題,去上面刷SQL吧。

註冊完後進入leetcode.com/problemset頁面。那裡有幾道MySQL題目。因為時間關係,我只講解Join相關,大家有興趣可以刷其他題,都不難的。SQLZoo也能刷,就是頁面丑了點,所以我十分感動地拒絕了它。

我們從Easy開始,選擇題目Combine Two Tables。

紅色字元是表名,第一列是欄位名,第二列是數據類型。題目希望我們通過兩張表輸出:FirstName, LastName, City, State四個欄位。

單純的Left Join就能完成了。記住噢,答案需要完全一致,也就是說最終的結果必須是四個欄位,不能多不能少,順序也不能亂,大小寫要嚴格。這一題大家自己做吧。通過後會有個綠色的Accepted提示。

接下來選擇Medium難度的Department Highest Salary。

這裡有兩張表,員工表和部門表,我們希望找出各個部門的最高薪水。

部門信息單獨為一張表,首先我們需要Join關聯起來,將部門分組求出最大值:

select d.Id, #這是部門IDn d.Name as Name, #這是部門名字n max(e.Salary) as Salary #這是最高薪水nfrom Department dnjoin Employee enon e.DepartmentId = d.Idngroup by d.Idn

上述的查詢語句找出了最高薪水的部門,我們是否能直接使用其作為答案?不能。這裡有一個邏輯的小陷阱,當最高薪水非單個時,使用max會只保留第一個,而不是列舉所有,所以我們需要更複雜的查詢。

因為已經有了各部門最高薪水的數據,可以將它作為一張新表,用最高薪水關聯僱員表,獲得我們最終的答案。

上面就是最終解法(#是解釋給你們看的,中文會報錯的),當然解法應該不是唯一的,大家有興趣可以繼續研究。

最終,我們選Hard模式的Department Top Three Salaries。

範例數據沒有一丁點變化,它需要我們求出各部門薪水前三的數據。如果最高薪水只有兩個,則輸出兩個。

上圖是給的範例結果。

排名前三的數據,我們可以使用order by 降序排列出來,然後通過limit 限定為3,但是新的問題是:既要各部門前三,也存在排名並列的情況。此時order by就無能為力了。

如果是SQL Server或者Oracle,我們可以使用row_number分組排序函數,但是MySQL沒有,其中的一種思路是利用set語法設置變數,間接應用row_number。我們還能使用另外一種思路。

select * from Employee as enwhere (n select count(distinct e1.Salary) n from Employee e1n where e1.Salary > e.Salaryn and e1.DepartmentId = e.DepartmentIdn ) < 3n

上述的例子巧妙地借用了子查詢。在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也是輕而易舉的。

接下來,我們將要進入第五周的大魔王課程,統計學,從入門到放棄,哈哈哈。

——————

歡迎關注我的個人公眾號:tracykanc

推薦閱讀:

如何看待近兩年用戶行為數據分析平台的發展?
財務分析經典圖表及製作方法(第1季)
酷炫的matplotlib
產品流量分析

TAG:数据分析 | MySQL入门 | 互联网数据分析 |