本文是《如何七周成為數(shù)據(jù)分析師》的第十篇教程，如果想要了解寫作初衷，可以先行閱讀七周指南。溫馨提示：如果您已經(jīng)熟悉數(shù)據(jù)庫，大可不必再看這篇文章，或只挑選部分。

在《寫給新人的數(shù)據(jù)庫指南》，我們已經(jīng)成功的安裝數(shù)據(jù)庫，并且導(dǎo)入數(shù)據(jù)，今天進(jìn)入SQL實(shí)戰(zhàn)練習(xí)。SQL是數(shù)據(jù)庫的查詢語言，語法結(jié)構(gòu)簡(jiǎn)單，相信本文會(huì)讓你從入門到熟練。

掌握SQL后，不論你是產(chǎn)品經(jīng)理、運(yùn)營人員或者數(shù)據(jù)分析師，都會(huì)讓你分析的能力邊界無限拓展。別猶豫了，趕快上車吧！

以下的語句都在SequelPro的Query頁面運(yùn)行，其他操作頁面不會(huì)有太大差異。標(biāo)點(diǎn)符號(hào)必須為英文，這是新人很容易犯的錯(cuò)誤。

SQL最小化的查詢結(jié)構(gòu)如下：

select?column?from?table

table是我們的表名，column是我們想要查詢的字段／列，column可以用 * 代替，指代全部字段，意為從table表查詢所有數(shù)據(jù)。

where 是基礎(chǔ)查詢語法，用于條件判斷。

select?*?from?DataAnalyst

where?city = ‘上?！?/p>

上圖是最簡(jiǎn)化的查詢語句，將所有城市為上海的職位數(shù)據(jù)過濾出來。我們也可以用 and 進(jìn)行多條件判斷。

select?*?from?DataAnalyst

where?city = ‘上海’ and?positionName = ‘數(shù)據(jù)分析師’

or 語句則是或的關(guān)系

select?*?from?DataAnalyst

where?city = ‘上海’ or positionName = ‘數(shù)據(jù)分析師’

查找城市為上海，或者職位名稱是數(shù)據(jù)分析師的數(shù)據(jù)，它們是并集。

當(dāng)我們涉及到非常復(fù)雜的與或邏輯判斷，應(yīng)該怎么辦？比如即滿足條件AB，又要滿足條件C，或者是滿足條件DE。此時(shí)需要用括號(hào)明確邏輯判斷的優(yōu)先級(jí)。

select?*?from?DataAnalyst

where?(city = ‘上海’?and?positionName = ‘數(shù)據(jù)分析師’)?or?(city = ‘北京’?andpositionName = ‘數(shù)據(jù)產(chǎn)品經(jīng)理’)

這條語句的含義是查找出上海的數(shù)據(jù)分析師或者是北京的產(chǎn)品經(jīng)理。當(dāng)有括號(hào)時(shí)，會(huì)優(yōu)先進(jìn)行括號(hào)內(nèi)的判斷，當(dāng)有多個(gè)括號(hào)時(shí)，對(duì)最內(nèi)層括號(hào)先進(jìn)行判斷，然后依次往外。

接下來的問題來了，當(dāng)我們要查詢多個(gè)條件，比如北京上海廣州深圳南京這些城市，難道一個(gè)個(gè)用and關(guān)聯(lián)起來？這太麻煩了，我們可以使用 in 。

select?*?from?DataAnalyst

where??city in (‘北京’,’上海’,’廣州’,’深圳’,’南京’)

當(dāng)我們遇到字段數(shù)據(jù)類型是數(shù)值時(shí)，也可以使用符號(hào)> 、>=、< 、<=、!= 進(jìn)行邏輯判斷，!= 指的是不等于，等價(jià)于 <> 。

select?*?from?DataAnalyst

where?companyId >= 10000

上例是篩選出公司ID >= 10000的職位，為數(shù)值時(shí)，不需要像字符串一樣加引號(hào)。

當(dāng)我們需要取區(qū)間數(shù)值時(shí)，使用 between and

select?*?from?DataAnalyst

where?companyId?between?10000?and?20000

between and 包括數(shù)值兩端的邊界，等同于?companyId >=10000 and?companyId <= 20000。

如果要模糊查找，能用like。

select?*?from?DataAnalyst

where?positionName like ‘%數(shù)據(jù)分析%’

語句的含義是在positionName列查找包含「數(shù)據(jù)分析」字段的數(shù)據(jù)，%代表的是通配符，含義是無所謂「數(shù)據(jù)分析」前面后面是什么內(nèi)容。如果是?‘數(shù)據(jù)分析%’ ，則代表字段必須以數(shù)據(jù)分析開頭，無所謂后面是什么。

除了上面所講，還有一個(gè)常用的語法是not，代表邏輯的逆轉(zhuǎn)，常見not in、not like、not null等。

接下來我們學(xué)習(xí)group by，它是數(shù)據(jù)分析中常見的語法，目的是將數(shù)據(jù)按組／維度劃分。類似于Excel中的數(shù)據(jù)透視表，我們以city為例。

select?*?from?DataAnalyst

group by?city

它將城市劃分成幾組，通過group by 可以快速的瀏覽數(shù)據(jù)有哪些城市。我們看一下它的高階用法。

select?city,count(1)?from?DataAnalyst

group by?city

上述語句，使用count函數(shù)，統(tǒng)計(jì)計(jì)數(shù)了每個(gè)城市擁有的職位數(shù)量。括號(hào)里面的1代表以第一列為計(jì)數(shù)標(biāo)準(zhǔn)。這里出現(xiàn)新的問題，當(dāng)我們遇到重復(fù)數(shù)據(jù)怎么辦？在DataAnalyst 這張表中，北京職位包含重復(fù)的職位ID，我們需要去重。

select?city,count(distinct?positionId)?from?DataAnalyst

group?by?city

北京的數(shù)據(jù)一下子少了2000，多余的重復(fù)值被排除在外。distinct 是去重函數(shù)，distinct positionId 會(huì)只計(jì)算唯一的positionId個(gè)數(shù)。日常工作中，活躍用戶數(shù)、文章UV，都是用distinct 計(jì)算獲得，這是唯一標(biāo)示符ID的重要作用。

除了count，還有max，min，sum，avg等函數(shù)，也叫做聚合函數(shù)。用法和Excel沒什么區(qū)別。

當(dāng)我們?cè)趃roup by 添加多個(gè)字段，它將以多維的形式進(jìn)行數(shù)據(jù)聚合。

select?city,workYear,count(distinct?positionId)?from?DataAnalyst

group by?city,workYear

這就是數(shù)據(jù)分析師常用的多維分析法，通過group by 切分不同的維度進(jìn)行對(duì)比，在不利用BI的情況下，通過SQL進(jìn)行快速數(shù)據(jù)分析。

接下來學(xué)習(xí)邏輯判斷，SQL也有if函數(shù)，和Excel的用法一摸一樣，通過它我們能進(jìn)行復(fù)雜的運(yùn)算。比如我想統(tǒng)計(jì)各個(gè)城市中有多少數(shù)據(jù)分析職位，其中，電商領(lǐng)域的職位有多少，在其中的占比？

industryField是公司的行業(yè)領(lǐng)域，雖然我們能用where like 計(jì)算出有幾個(gè)電商的數(shù)據(jù)分析師，但是占比的計(jì)算會(huì)比較麻煩，此時(shí)可以用if。

select?if(industryField?like?‘%電子商務(wù)%’,1,0)?from?DataAnalyst

上面的公式利用if判斷出哪些是電商行業(yè)的數(shù)據(jù)分析師，哪些不是。if函數(shù)中間的字段代表為true時(shí)返回的值，不過因?yàn)榘貜?fù)數(shù)據(jù)，我們需要將其改成positionId。之后，用它與group by 組合就能達(dá)成目的了。

select?city,

count(distinct?positionId),

count(if(industryField?like?‘%電子商務(wù)%’,positionId,null))

from?DataAnalyst

group by?city

第一列數(shù)字是職位總數(shù)，第二列是電商領(lǐng)域的職位數(shù)，相除就是占比。記住，count是不論0還是1都會(huì)納入計(jì)數(shù)，所以第三個(gè)參數(shù)需要寫成null，代表不是電商的職位就排除在計(jì)算之外。

接下來是新的問題，如果我想找出各個(gè)城市，數(shù)據(jù)分析師崗位數(shù)量在500以上的城市有哪些，應(yīng)該怎么計(jì)算？有兩種方法，第一種，是使用having語句，它對(duì)聚合后的數(shù)據(jù)結(jié)果進(jìn)行過濾。

select?city,count(distinct?positionId)?from?DataAnalyst

group by?city?having?count(distinct?positionId) >= 500

第二種，是利用嵌套子查詢。

我們將第一次查詢獲得的城市職位數(shù)的結(jié)果，看作一張新的表，利用as 將它命名為t1( table1 的簡(jiǎn)寫)，將職位數(shù)命名為一個(gè)新的字段counts。然后外面再套一層select 過濾出counts >=500。

這種查詢方式就叫嵌套子查詢，使用場(chǎng)景比較廣泛，where 后面也能跟子查詢。

很多時(shí)候，數(shù)據(jù)是凌亂的，我們希望結(jié)果能夠呈現(xiàn)一定的順序，這時(shí)候就用到order by語句。

select?city,count(distinct?positionId)?as?counts?from?DataAnalyst

group by?city

order by?counts

看，數(shù)據(jù)就按照統(tǒng)計(jì)結(jié)果升序排列，如果需要降序，則是order by counts desc，后面加一個(gè)desc就好了。如果是多個(gè)字段，按逗號(hào)分隔即可。

我們?cè)賮硎煜QL的常用函數(shù)，首先是時(shí)間。因?yàn)槲覀兊木毩?xí)數(shù)據(jù)中沒有時(shí)間，首先用now創(chuàng)建出一個(gè)時(shí)間字段。

select?now()

直接執(zhí)行它，就能獲得當(dāng)前的系統(tǒng)時(shí)間，精確到秒。其實(shí)select不一定后面要跟from。

select?date(now())

它代表的是獲得當(dāng)前日期，week函數(shù)獲得當(dāng)前第幾周，month函數(shù)獲得當(dāng)前第幾個(gè)月。其余還包括，quarter，year，day，hour，minute。

時(shí)間函數(shù)也包含各種參數(shù)，比如week，因?yàn)橹形鞣接?jì)算第幾天是不一樣的，西方把周日算作一周中的第一天，而我們習(xí)慣周一。

select?week(now(),0)

除了以上的日期表達(dá)，也可以使用dayofyear、weekofyear 的形式計(jì)算。它和上面的部分函數(shù)等價(jià)。

怎么對(duì)時(shí)間進(jìn)行加減法呢？這時(shí)候靠date_add函數(shù)出馬。

select?date_add(date(now()) ,interval?1?day)

我們可以改變1為負(fù)數(shù)，達(dá)到減法的目的，也能更改day為week、year等，進(jìn)行其他時(shí)間間隔的運(yùn)算。如果是求兩個(gè)時(shí)間的間隔，則是datediff(date1,date2)或者timediff(time1,time2)。

時(shí)間函數(shù)的運(yùn)用比較靈活，沒有特殊限定，網(wǎng)絡(luò)上的文檔和教程也不少，可以深入學(xué)習(xí)。

最后是數(shù)據(jù)清洗類的函數(shù)。

select?left(salary,1)?from?DataAnalyst

MySQL支持left、right、mid等函數(shù)，這里又和Excel一樣。我們通過salary計(jì)算數(shù)據(jù)分析師的工資吧（這一步驟，在曾經(jīng)的文章中已經(jīng)用Excel和BI多次講解，所以我就不多贅述了，只講過程，不熟悉的同學(xué)可以看歷史內(nèi)容）。

首先利用locate函數(shù)查找第一個(gè)k所在的位置。

select?locate(“k”,salary),salary?from?DataAnalyst

然后使用left函數(shù)截取薪水的下限。

select?left(salary,locate(“k”,salary)-1),salary?from?DataAnalyst

為了獲得薪水的上限，要用substr函數(shù)，或者mid，兩者等價(jià)。

substr（字符串，從哪里開始截，截取的長(zhǎng)度）

薪水上限的開始位置是「-」位置往后推一位。截取長(zhǎng)度是整個(gè)字符串減去「-」所在位置，剛好是后半段我們需要的內(nèi)容，不過這個(gè)內(nèi)容是包含「K」的，所以最后結(jié)果還得再減去1。

這里不了解不要緊，可以將計(jì)算過程分步驟運(yùn)行。基本上，了解了上面寫法的含義，文本清洗這塊就沒有問題了（not like用來清洗亂七八糟的薪水，我簡(jiǎn)單處理了）。再然后計(jì)算不同城市不同工作年限的平均薪資。

上面語句，我們用了文本清洗、子查詢嵌套、分組聚合、排序等多種用法，屬于較復(fù)雜的查詢。重復(fù)數(shù)據(jù)的問題，因?yàn)槲沂菑?fù)制了一份北京數(shù)據(jù)，數(shù)量剛好乘二，對(duì)平均數(shù)沒有影響，感興趣的朋友可以再加一步清洗掉它。

下面是三道思考題：

• 查詢出哪家公司招聘的崗位數(shù)最多；
• 查詢出O2O、電子商務(wù)、互聯(lián)網(wǎng)金融這三個(gè)行業(yè)，哪個(gè)行業(yè)的平均薪資最高；
• 查詢出各城市的最高薪水Top3是哪家公司哪個(gè)崗位。

做完上面的題目，你已經(jīng)神功初成，數(shù)據(jù)分析的SQL意見沒有大問題了。更復(fù)雜的查詢，也無非是嵌套更多的內(nèi)容，本質(zhì)思路是一樣的。

講到這里，只剩join語法還沒有教大家。因?yàn)榫毩?xí)數(shù)據(jù)只有一張表，而join又是SQL中比較容易混淆的難點(diǎn)，我會(huì)單獨(dú)開一篇內(nèi)容講解，到時(shí)候使用SQLZoo和LeetCode的案例。

LeetCode是知名的算法競(jìng)賽網(wǎng)站，可以在上面和全世界的程序員比拼算法，當(dāng)然我們只練習(xí)SQL，完成后，至少能秒殺全世界50%的程序員吧。

后續(xù)的SQL或者Python語句，我可能會(huì)用截圖形式，或者找個(gè)好看點(diǎn)的內(nèi)聯(lián)樣式，否則在微信端會(huì)變形的很難看。

相關(guān)閱讀

互聯(lián)網(wǎng)數(shù)據(jù)分析能力的養(yǎng)成，需一份七周的提綱

如何七周成為數(shù)據(jù)分析師01：常見的Excel函數(shù)全部涵蓋在這里了

如何七周成為數(shù)據(jù)分析師02：Excel技巧大揭秘

如何七周成為數(shù)據(jù)分析師03：手把手教你Excel實(shí)戰(zhàn)

如何七周成為數(shù)據(jù)分析師：Excel技巧之甘特圖繪制（項(xiàng)目管理）

如何七周成為數(shù)據(jù)分析師：Excel技巧之打造多級(jí)菜單

如何七周成為數(shù)據(jù)分析師04：數(shù)據(jù)可視化之經(jīng)典圖表合集

如何七周成為數(shù)據(jù)分析師05：數(shù)據(jù)可視化之打造升職加薪的報(bào)表

如何七周成為數(shù)據(jù)分析師06：數(shù)據(jù)可視化之手把手打造BI

如何七周成為數(shù)據(jù)分析師07：快速掌握麥肯錫的分析思維

如何七周成為數(shù)據(jù)分析師08：如何建立數(shù)據(jù)分析的思維框架？

如何七周成為數(shù)據(jù)分析師09：寫給新人的數(shù)據(jù)庫指南

#專欄作家#

秦路，微信公眾號(hào)ID：tracykanc，人人都是產(chǎn)品經(jīng)理專欄作家。

本文由 @秦路?原創(chuàng)發(fā)布于人人都是產(chǎn)品經(jīng)理。未經(jīng)許可，禁止轉(zhuǎn)載。