如何七周成為數(shù)據(jù)分析師10：SQL，從入門到熟練

8 評論 66726 瀏覽 228 收藏 18 分鐘

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

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

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

以下的語句都在SequelPro的Query頁面運行，其他操作頁面不會有太大差異。標點符號必須為英文，這是新人很容易犯的錯誤。

SQL最小化的查詢結構如下：

select?column?from?table

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

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

select?*?from?DataAnalyst

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

image.png

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

select?*?from?DataAnalyst

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

or 語句則是或的關系

select?*?from?DataAnalyst

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

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

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

select?*?from?DataAnalyst

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

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

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

select?*?from?DataAnalyst

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

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

select?*?from?DataAnalyst

where?companyId >= 10000

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

當我們需要取區(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等。

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

select?*?from?DataAnalyst

group by?city

image.png

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

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

group by?city

image.png

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

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

group?by?city

image.png

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

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

當我們在group by 添加多個字段，它將以多維的形式進行數(shù)據(jù)聚合。

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

group by?city,workYear

image.png

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

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

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

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

image.png

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

select?city,

count(distinct?positionId),

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

from?DataAnalyst

group by?city

image.png

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

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

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

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

image.png

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

image.png

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

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

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

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

group by?city

order by?counts

image.png

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

我們再來熟悉SQL的常用函數(shù)，首先是時間。因為我們的練習數(shù)據(jù)中沒有時間，首先用now創(chuàng)建出一個時間字段。

select?now()

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

select?date(now())

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

時間函數(shù)也包含各種參數(shù)，比如week，因為中西方計算第幾天是不一樣的，西方把周日算作一周中的第一天，而我們習慣周一。

select?week(now(),0)

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

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

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

image.png

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

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

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

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

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

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

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

image.png

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

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

image.png

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

substr（字符串，從哪里開始截，截取的長度）

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

image.png

這里不了解不要緊，可以將計算過程分步驟運行?；旧希私饬松厦鎸懛ǖ暮x，文本清洗這塊就沒有問題了（not like用來清洗亂七八糟的薪水，我簡單處理了）。再然后計算不同城市不同工作年限的平均薪資。

image.png

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

下面是三道思考題：

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

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

講到這里，只剩join語法還沒有教大家。因為練習數(shù)據(jù)只有一張表，而join又是SQL中比較容易混淆的難點，我會單獨開一篇內(nèi)容講解，到時候使用SQLZoo和LeetCode的案例。

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

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

相關閱讀

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

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

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

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

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

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

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

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

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

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

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

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

#專欄作家#

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

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

更多精彩內(nèi)容，請關注人人都是產(chǎn)品經(jīng)理微信公眾號或下載App
秦路
小角色，公眾號:tracykanc
47篇作品 2484431總閱讀量
08-026269 瀏覽
一文梳理：基金賬戶體系（定義、種類、體系、結構）
09-063388 瀏覽
秀才封號，品味區(qū)隔下的老年人精神需求
04-274696 瀏覽
“少數(shù)人”的勝利，Midjourney走上AIGC神壇
07-086530 瀏覽
小紅書種草，播客種樹？
10-172550 瀏覽
雙十一現(xiàn)貨開賣，京東第三次“減負”
評論
評論請登錄

  1. 圖片無法顯示怎么辦？

    來自上海 回復

  2. 第三題：select t1.city, t1.company, t1.position, t1.salary from dataanalyst as t1
    where (SELECT count(*) FROM dataanalyst as t2 where t1.city = t2.city AND t2.salary>t1.salary)<3
    ORDER BY t1.city,t1.salary desc;

    來自上海 回復

  3. 第一題：SELECT company, count(position) as 崗位數(shù) FROM dataanalyst GROUP BY company ORDER BY `崗位數(shù)` DESC limit 1；
    第二題：SELECT industry, avg(salary) as `avgsalary` from datanalyst GROUP BY industry ORDER BY avgsalary DESC limit 1;

    來自上海 回復

  4. 思考題：
    1，哪家公司的招聘職位最多？
    select first(companyshortname) as maxpositons from (select companyid,companyshortname,count(distinct positionid) as positions from dataanalyst
    group by companyid
    order by positions desc) as t1
    請問第一題可以這樣寫不
    第二題中，還是遇到了和計算電商領域的職位數(shù)同樣的問題，如果考慮positionid存在重復項，那最后計算的平均薪資里面也可能會包括重復項（如果重復數(shù)據(jù)是這三個領域中的職位的話），請問用distinc要怎么提取出非重復項其他列的數(shù)據(jù)呢

    來自北京 回復

  5. 請問在計算電商領域的職位數(shù)的時候，不用考慮positionid存在重復的情況嗎，這樣一個city的職位綜述排除了重復項，但是電商領域的職位數(shù)未排除重復項結果是否有影響哦？

    來自北京 回復

  6. 作者好，首先，你的文章對我很有幫助，我也學到很多。我想問個問題，就是我按照你給的格式寫的查詢條件，可是就是篩選不出來數(shù)據(jù)，不知道是什么原因。
    select * from DataAnalyst
    where city = ‘上海’
    這是我輸入的查詢條件，表用的是你給的表，命名也是用的跟你一樣的?？刹恢罏樯冻鲥e了

    來自北京 回復

    1. 結尾少了；

      來自上海 回復

    2. MySQL查詢語句用分號做結束符

      來自北京 回復