建站
咨詢
售后
建站咨詢
新聞中心
本文是《如何七周成為數(shù)據(jù)分析師》的第十篇教程，如果想要了解寫作初衷，可以先行閱讀七周指南。溫馨提示：如果您已經(jīng)熟悉數(shù)據(jù)庫，大可不必再看這篇文章，或只挑選部分。
在《寫給新人的數(shù)據(jù)庫指南》，我們已經(jīng)成功的安裝數(shù)據(jù)庫，并且導(dǎo)入數(shù)據(jù)，今天進入SQL實戰(zhàn)練習(xí)。SQL是數(shù)據(jù)庫的查詢語言，語法結(jié)構(gòu)簡單，相信本文會讓你從入門到熟練。
掌握SQL后，不論你是產(chǎn)品經(jīng)理、運營人員或者數(shù)據(jù)分析師，都會讓你分析的能力邊界無限拓展。別猶豫了，趕快上車吧!
以下的語句都在SequelPro的Query頁面運行，其他操作頁面不會有太大差異。標點符號必須為英文，這是新人很容易犯的錯誤。
SQL最小化的查詢結(jié)構(gòu)如下：
- select column from table
table是我們的表名，column是我們想要查詢的字段/列，column可以用 * 代替，指代全部字段，意為從table表查詢所有數(shù)據(jù)。
where 是基礎(chǔ)查詢語法，用于條件判斷。
- select * from DataAnalyst
- where city = '上海'
上圖是最簡化的查詢語句，將所有城市為上海的職位數(shù)據(jù)過濾出來。我們也可以用 and 進行多條件判斷。
- 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。此時需要用括號明確邏輯判斷的優(yōu)先級。
- select * from DataAnalyst
- where (city = '上海' and positionName = '數(shù)據(jù)分析師') or (city = '北京' and positionName = '數(shù)據(jù)產(chǎn)品經(jīng)理')
這條語句的含義是查找出上海的數(shù)據(jù)分析師或者是北京的產(chǎn)品經(jīng)理。當(dāng)有括號時，會優(yōu)先進行括號內(nèi)的判斷，當(dāng)有多個括號時，對最內(nèi)層括號先進行判斷，然后依次往外。
接下來的問題來了，當(dāng)我們要查詢多個條件，比如北京上海廣州深圳南京這些城市，難道一個個用and關(guān)聯(lián)起來?這太麻煩了，我們可以使用 in 。
- select * from DataAnalyst
- where city in ('北京','上海','廣州','深圳','南京')
當(dāng)我們遇到字段數(shù)據(jù)類型是數(shù)值時，也可以使用符號> 、>=、< 、<=、!= 進行邏輯判斷，!= 指的是不等于，等價于 <> 。
- select * from DataAnalyst
- where companyId >= 10000
上例是篩選出公司ID >= 10000的職位，為數(shù)值時，不需要像字符串一樣加引號。
當(dāng)我們需要取區(qū)間數(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ù)分析開頭，無所謂后面是什么。
除了上面所講，還有一個常用的語法是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)計計數(shù)了每個城市擁有的職位數(shù)量。括號里面的1代表以第一列為計數(shù)標準。這里出現(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 會只計算唯一的positionId個數(shù)。日常工作中，活躍用戶數(shù)、文章UV，都是用distinct 計算獲得，這是唯一標示符ID的重要作用。
除了count，還有max，min，sum，avg等函數(shù)，也叫做聚合函數(shù)。用法和Excel沒什么區(qū)別。
當(dāng)我們在group by 添加多個字段，它將以多維的形式進行數(shù)據(jù)聚合。
- select city,workYear,count(distinct positionId) from DataAnalyst
- group by city,workYear
這就是數(shù)據(jù)分析師常用的多維分析法，通過group by 切分不同的維度進行對比，在不利用BI的情況下，通過SQL進行快速數(shù)據(jù)分析。
接下來學(xué)習(xí)邏輯判斷，SQL也有if函數(shù)，和Excel的用法一摸一樣，通過它我們能進行復(fù)雜的運算。比如我想統(tǒng)計各個城市中有多少數(shù)據(jù)分析職位，其中，電商領(lǐng)域的職位有多少，在其中的占比?
industryField是公司的行業(yè)領(lǐng)域，雖然我們能用where like 計算出有幾個電商的數(shù)據(jù)分析師，但是占比的計算會比較麻煩，此時可以用if。
- select if(industryField like '%電子商務(wù)%',1,0) from DataAnalyst
上面的公式利用if判斷出哪些是電商行業(yè)的數(shù)據(jù)分析師，哪些不是。if函數(shù)中間的字段代表為true時返回的值，不過因為包含重復(fù)數(shù)據(jù)，我們需要將其改成positionId。之后，用它與group by 組合就能達成目的了。
- 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都會納入計數(shù)，所以第三個參數(shù)需要寫成null，代表不是電商的職位就排除在計算之外。
接下來是新的問題，如果我想找出各個城市，數(shù)據(jù)分析師崗位數(shù)量在500以上的城市有哪些，應(yīng)該怎么計算?有兩種方法，第一種，是使用having語句，它對聚合后的數(shù)據(jù)結(jié)果進行過濾。
- select city,count(distinct positionId) from DataAnalyst
- group by city having count(distinct positionId) >= 500
第二種，是利用嵌套子查詢。
我們將第一次查詢獲得的城市職位數(shù)的結(jié)果，看作一張新的表，利用as 將它命名為t1( table1 的簡寫)，將職位數(shù)命名為一個新的字段counts。然后外面再套一層select 過濾出counts >=500。
這種查詢方式就叫嵌套子查詢，使用場景比較廣泛，where 后面也能跟子查詢。
很多時候，數(shù)據(jù)是凌亂的，我們希望結(jié)果能夠呈現(xiàn)一定的順序，這時候就用到order by語句。
- select city,count(distinct positionId) as counts from DataAnalyst
- group by city
- order by counts
看，數(shù)據(jù)就按照統(tǒng)計結(jié)果升序排列，如果需要降序，則是order by counts desc，后面加一個desc就好了。如果是多個字段，按逗號分隔即可。
我們再來熟悉SQL的常用函數(shù)，首先是時間。因為我們的練習(xí)數(shù)據(jù)中沒有時間，首先用now創(chuàng)建出一個時間字段。
- select now()
直接執(zhí)行它，就能獲得當(dāng)前的系統(tǒng)時間，精確到秒。其實select不一定后面要跟from。
- select date(now())
它代表的是獲得當(dāng)前日期，week函數(shù)獲得當(dāng)前第幾周，month函數(shù)獲得當(dāng)前第幾個月。其余還包括，quarter，year，day，hour，minute。
時間函數(shù)也包含各種參數(shù)，比如week，因為中西方計算第幾天是不一樣的，西方把周日算作一周中的第一天，而我們習(xí)慣周一。
- select week(now(),0)
除了以上的日期表達，也可以使用dayofyear、weekofyear 的形式計算。它和上面的部分函數(shù)等價。
怎么對時間進行加減法呢?這時候靠date_add函數(shù)出馬。
- select date_add(date(now()) ,interval 1 day)
我們可以改變1為負數(shù)，達到減法的目的，也能更改day為week、year等，進行其他時間間隔的運算。如果是求兩個時間的間隔，則是datediff(date1,date2)或者timediff(time1,time2)。
時間函數(shù)的運用比較靈活，沒有特殊限定，網(wǎng)絡(luò)上的文檔和教程也不少，可以深入學(xué)習(xí)。
最后是數(shù)據(jù)清洗類的函數(shù)。
- select left(salary,1) from DataAnalyst
MySQL支持left、right、mid等函數(shù)，這里又和Excel一樣。我們通過salary計算數(shù)據(jù)分析師的工資吧(這一步驟，在曾經(jīng)的文章中已經(jīng)用Excel和BI多次講解，所以我就不多贅述了，只講過程，不熟悉的同學(xué)可以看歷史內(nèi)容)。
首先利用locate函數(shù)查找第一個k所在的位置。
- select locate("k",salary),salary from DataAnalyst
然后使用left函數(shù)截取薪水的下限。
- select left(salary,locate("k",salary)-1),salary from DataAnalyst
為了獲得薪水的上限，要用substr函數(shù)，或者mid，兩者等價。
- substr(字符串，從哪里開始截，截取的長度)
薪水上限的開始位置是「-」位置往后推一位。截取長度是整個字符串減去「-」所在位置，剛好是后半段我們需要的內(nèi)容，不過這個內(nèi)容是包含「K」的，所以最后結(jié)果還得再減去1。
這里不了解不要緊，可以將計算過程分步驟運行?；旧?，了解了上面寫法的含義，文本清洗這塊就沒有問題了(not like用來清洗亂七八糟的薪水，我簡單處理了)。再然后計算不同城市不同工作年限的平均薪資。
上面語句，我們用了文本清洗、子查詢嵌套、分組聚合、排序等多種用法，屬于較復(fù)雜的查詢。重復(fù)數(shù)據(jù)的問題，因為我是復(fù)制了一份北京數(shù)據(jù)，數(shù)量剛好乘二，對平均數(shù)沒有影響，感興趣的朋友可以再加一步清洗掉它。
下面是三道思考題：
查詢出哪家公司招聘的崗位數(shù)最多;
查詢出O2O、電子商務(wù)、互聯(lián)網(wǎng)金融這三個行業(yè)，哪個行業(yè)的平均薪資最高;
查詢出各城市的最高薪水Top3是哪家公司哪個崗位。
做完上面的題目，你已經(jīng)神功初成，數(shù)據(jù)分析的SQL意見沒有大問題了。更復(fù)雜的查詢，也無非是嵌套更多的內(nèi)容，本質(zhì)思路是一樣的。
講到這里，只剩join語法還沒有教大家。因為練習(xí)數(shù)據(jù)只有一張表，而join又是SQL中比較容易混淆的難點，我會單獨開一篇內(nèi)容講解，到時候使用SQLZoo和LeetCode的案例。
LeetCode是老牌的算法競賽網(wǎng)站，可以在上面和全世界的程序員比拼算法，當(dāng)然我們只練習(xí)SQL，完成后，至少能秒殺全世界50%的程序員吧。
本文名稱：SQL，從入門到熟練
地址分享：http://www.dlmjj.cn/article/dhiojee.html
