如何七周成為數(shù)據(jù)分析師11：SQL，從熟練到掌握

搜索

APP

起點(diǎn)課堂會(huì)員權(quán)益

職業(yè)體系課特權(quán)

線下行業(yè)大會(huì)特權(quán)

個(gè)人IP打造特權(quán)

30+門專項(xiàng)技能課

1300+專題課程

12場(chǎng)職場(chǎng)軟技能直播

12場(chǎng)求職輔導(dǎo)直播

12場(chǎng)專業(yè)技能直播

會(huì)員專屬社群

榮耀標(biāo)識(shí)

發(fā)布

注冊(cè) | 登錄

如何七周成為數(shù)據(jù)分析師11：SQL，從熟練到掌握

秦路

2017-08-03

5 評(píng)論 29094 瀏覽 123 收藏

14 分鐘

我們?cè)谏弦黄?a href="http://m.36881.com.cn/operate/738110.html" target="_blank" rel="noopener noreferrer">《如何七周成為數(shù)據(jù)分析師：SQL，從入門到熟練》文章已經(jīng)掌握了除Join外的常用語(yǔ)法和函數(shù)，今天會(huì)通過(guò)一系列的練習(xí)徹底掌握SQL。

我們知道，數(shù)據(jù)庫(kù)由多張表組成，表與表之間可以實(shí)現(xiàn)關(guān)聯(lián)。

上圖就是一個(gè)簡(jiǎn)單的關(guān)聯(lián)模型：

Students.addressId = Address.id
Students.id = Scores.studentId
Scores.courseId = Courses.id

那么，如何在SQL查詢語(yǔ)句中將兩個(gè)表聯(lián)接起來(lái)？我們將運(yùn)用最重要的語(yǔ)法Join。

select?*?fromStudents
joinAddress?onStudents.addressId = Address.id

上面語(yǔ)句，join將Students和Address兩表關(guān)聯(lián)，關(guān)聯(lián)需要一個(gè)或多個(gè)字段作為聯(lián)接橋梁。例子中的橋梁就是addressid，我們使用on語(yǔ)句，將Students表的addressId字段和Address的id字段匹配。

這里需要注意的是，因?yàn)樽侄慰赡苤孛?，所以一旦使用了Join，字段前應(yīng)該加上表名，如Students.addressId和Address.id ，這種用法是為了字段的唯一性，否則遇到重名，系統(tǒng)不知道使用哪個(gè)字段，就會(huì)報(bào)錯(cuò)。

select?*?fromStudents?ass
joinAddress?asa?ons.addressId = a.id

上圖是更優(yōu)雅的寫(xiě)法，將表命名為一個(gè)縮略的別名，避免了語(yǔ)句過(guò)于冗余。不要使用拼音做別名，不是好習(xí)慣。

Join語(yǔ)法有很多不同的變形，Left Join，Outer Join等，新人很容易混淆。這個(gè)我們可以用數(shù)學(xué)中的交集和并集掌握。

上圖很清晰地解釋了各Join語(yǔ)法。

Inner Join最常見(jiàn)，叫做內(nèi)聯(lián)接，可以縮寫(xiě)成Join，找的是兩張表共同擁有的字段。

Left Join叫做左聯(lián)接，以左表（join符號(hào)前的那張表）為主，返回所有的行。如果右表有共同字段，則一并返回，如果沒(méi)有，則為空。

我們以W3School上的數(shù)據(jù)為例：

select?Persons.LastName, Persons.FirstName, Orders.OrderNo
fromPersons
left joinOrders?onPersons.Id_P=Orders.Id_P
order byPersons.LastName

于是輸出結(jié)果為：

結(jié)果集中，Bush那一行的OrderNo為空，就是因?yàn)镮d_P無(wú)法匹配上，返回了Null。如果改成Inner join，則不會(huì)返回整個(gè)Bush所在行。這是Inner Join和Left Join的區(qū)別，也是面試中經(jīng)常會(huì)問(wèn)到的題目。

Right Join和Left Join沒(méi)有區(qū)別，A Left Join B 等價(jià)于 B Right Join A。

Full Join叫做全聯(lián)接，也叫做Full Outer Join，意思是不管有的沒(méi)的，只要存在，就返回。

還是以之前的例子演示，下面是Full Join：

最后兩行就是所謂的「不管有的沒(méi)的，只要存在字符串，就返回」的結(jié)果，它們Id_P并沒(méi)有匹配上，但還是給出了返回，只是為空字段不同。

這三者的關(guān)系，我們可以理解為：A Full Join B = A Left Join B + A Right Join B – A Inner Join B，這就是數(shù)學(xué)上的集合運(yùn)算，雖然SQL的表并不能加減法。如果還一知半解，看最上面的Join示例圖，用面積的角度看也明白了。

通過(guò)上面的例子，我們已經(jīng)掌握了Join的主流語(yǔ)法，其他無(wú)非是變種。比如加約束條件 where XX is null，這里的XX可以是結(jié)果為空的字段。拿上文Left Join的例子演示：

select?Persons.LastName, Persons.FirstName, Orders.OrderNo
fromPersons
left joinOrders
onPersons.Id_P=Orders.Id_P
whereOrders.Id_P?is Null

最終返回的結(jié)果就是Bush這一行。

當(dāng)我們有多個(gè)字段要匹配時(shí)，on后面可以通過(guò) and 進(jìn)行多項(xiàng)關(guān)聯(lián)。

select?*?fromA
joinB?onA.name = B.nameandA.phone = B.phone

上圖就是一個(gè)簡(jiǎn)單的適用場(chǎng)景，將用戶姓名和手機(jī)號(hào)進(jìn)行多項(xiàng)關(guān)聯(lián)。它也可以加入其他的條件判斷。

select?*?fromA
joinB?onA.name = B.nameandA.phone = B.phone?andB.sex = ‘男

我們?cè)偌右粋€(gè)and，將B表的用戶性別限定為男。這種用法等價(jià)于where B.sex = ‘男’。當(dāng)數(shù)據(jù)量大到一定程度，通過(guò)這種約束條件，能優(yōu)化查詢性能。

到這里，SQL的常用語(yǔ)法已經(jīng)講解的差不多了，我們進(jìn)行實(shí)戰(zhàn)吧。leetcode.com網(wǎng)站是知名的算法競(jìng)賽題，去上面刷SQL吧。

注冊(cè)完后進(jìn)入leetcode.com/problemset/database頁(yè)面。那里有幾道MySQL題目。因?yàn)闀r(shí)間關(guān)系，我只講解Join相關(guān)，大家有興趣可以刷其他題，都不難的。SQLZoo也能刷，就是頁(yè)面丑了點(diǎn)，所以我十分感動(dòng)地拒絕了它。

我們從Easy開(kāi)始，選擇題目Combine Two Tables。

紅色字符是表名，第一列是字段名，第二列是數(shù)據(jù)類型。題目希望我們通過(guò)兩張表輸出：FirstName, LastName, City, State四個(gè)字段。

單純的Inner Join就能完成了。記住噢，答案需要完全一致，也就是說(shuō)最終的結(jié)果必須是四個(gè)字段，不能多不能少，順序也不能亂，大小寫(xiě)要嚴(yán)格。這一題大家自己做吧。通過(guò)后會(huì)有個(gè)綠色的Accepted提示。

接下來(lái)選擇Medium難度的Department Highest Salary。

這里有兩張表，員工表和部門表，我們希望找出各個(gè)部門的最高薪水。

部門信息單獨(dú)為一張表，首先我們需要Join關(guān)聯(lián)起來(lái)，將部門分組求出最大值：

select?d.Id, ?#這是部門ID
d.NameasName, ?#這是部門名字
max(e.Salary)?asSalary ?#這是最高薪水
fromDepartment d
joinEmployee e
one.DepartmentId = d.Id
group byd.Id

上述的查詢語(yǔ)句找出了最高薪水的部門，我們是否能直接使用其作為答案？不能。這里有一個(gè)邏輯的小陷阱，當(dāng)最高薪水非單個(gè)時(shí)，使用max會(huì)只保留第一個(gè)，而不是列舉所有，所以我們需要更復(fù)雜的查詢。

因?yàn)橐呀?jīng)有了各部門最高薪水的數(shù)據(jù)，可以將它作為一張新表，用最高薪水關(guān)聯(lián)雇員表，獲得我們最終的答案。

上面就是最終解法（#是解釋給你們看的，中文會(huì)報(bào)錯(cuò)的），當(dāng)然解法應(yīng)該不是唯一的，大家有興趣可以繼續(xù)研究。

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

范例數(shù)據(jù)沒(méi)有一丁點(diǎn)變化，它需要我們求出各部門薪水前三的數(shù)據(jù)。如果最高薪水只有兩個(gè)，則輸出兩個(gè)。

上圖是給的范例結(jié)果。

排名前三的數(shù)據(jù)，我們可以使用order by 降序排列出來(lái)，然后通過(guò)limit 限定為3，但是新的問(wèn)題是：既要各部門前三，也存在排名并列的情況。此時(shí)order by就無(wú)能為力了。

如果是SQL Server或者Oracle，我們可以使用row_number分組排序函數(shù)，但是MySQL沒(méi)有，其中的一種思路是利用set語(yǔ)法設(shè)置變量，間接應(yīng)用row_number。我們還能使用另外一種思路。

select?* from Employee?ase
where (
select?count(distincte1.Salary)
fromEmployee e1
wheree1.Salary > e.Salary
ande1.DepartmentId = e.DepartmentId
) <3

上述的例子巧妙地借用了子查詢。在where語(yǔ)句中，我們用子表e1與父表（外表）e進(jìn)行比對(duì)。SQL是允許子查詢的表和父查詢的表進(jìn)行運(yùn)算的。

e1.DepartmentId = e.DepartmentId作為條件約束，避免跨部門。e1.Salary > e.Salary則是邏輯判斷，通過(guò)count函數(shù)，逐行計(jì)算出e表中有多少薪水比e1的薪水低。

因?yàn)閑1表和e表實(shí)際上是等價(jià)的。所以返回的count(distinct e1.Salary) 代表e1表有中多少薪水比e表的高，上圖的例子，答案是2（90000和85000比它高）。如果是0，則代表e表中該行薪水最高（沒(méi)有比它高的），1代表第二高，2代表第三高。于是便過(guò)濾出Top 3的薪水。最后通過(guò)join計(jì)算出結(jié)果。

在實(shí)際查詢過(guò)程中，不建議大家使用這種運(yùn)算方式，因?yàn)檫\(yùn)算效率不會(huì)快。其實(shí)換我，我更可能group by后導(dǎo)出結(jié)果用Excel處理。

到這里，大家對(duì)Join已經(jīng)有一個(gè)大概的了解了吧。真實(shí)的數(shù)據(jù)查詢場(chǎng)景中，Join會(huì)用到很多，業(yè)務(wù)復(fù)雜用五六個(gè)Join也是常態(tài)，如果算上各類邏輯處理，SQL代碼行數(shù)可以破百。這時(shí)候，考驗(yàn)的就是熟練度了。

SQL只要多加訓(xùn)練，并不是一門很難掌握的語(yǔ)言。除了技巧，還要看你對(duì)業(yè)務(wù)表的熟悉程度，一般公司發(fā)展大了，百來(lái)張表很正常，各類業(yè)務(wù)邏輯各種Join，各字段的含義，這是同樣要花費(fèi)時(shí)間的苦功夫。

希望大家對(duì)SQL已經(jīng)有一個(gè)初步的掌握了。SQL學(xué)好了，以后應(yīng)用大數(shù)據(jù)的Hive和SparkSQL也是輕而易舉的。

接下來(lái)，我們將要進(jìn)入第五周的大魔王課程，統(tǒng)計(jì)學(xué)，從入門到放棄，哈哈哈。