SQL教你如何找到數據分析工作

08-20

SQL教你如何找到數據分析工作

來自專欄互聯網數據分析-阿墨16 人贊了文章

眾所周知，在過去那個信息處理能力受限的時代，世界上的數據量是十分巨大的，卻缺乏用來分析所收集數據的工具。那如何選擇樣本呢？統計學家認為應對數據採用隨機抽樣的方法，並且證明了採樣分析的精確性隨著採樣隨機性的增加而大幅提高，但與樣本數量的增加關係不大。為什麼會這樣？簡單來說，當樣本數量達到了某個值之後，我們從新的個體身上得到的信息就會越來越少，就像經濟學中的邊際效應遞減一樣。

然而，任何方法都存在缺陷，隨機採樣也不例外。它的成功依賴於採樣的絕對隨機性，但是要實現採樣的隨機性是非常困難的。那還有什麼辦法能夠代替隨機採樣呢？

如今，進入到大數據時代後，技術條件已經有了很大的提高，我們慢慢意識到自己擁有了能夠收集和處理更大規模數據的能力，尤其是資料庫技術。所以，我們完全可以利用所有的數據，而不是依靠隨機採樣去獲得一部分數據。在那次流感趨勢預測中，谷歌公司並不是依賴於對隨機樣本的分析，而是分析了整個美國幾十億條互聯網檢索記錄。因此，隨著資料庫和數據處理技術發生了翻天覆地的改變，我們可以輕易對這些數據進行收集、存儲、管理以及分析等。正是因為資料庫的作用越來越大，其所包含的功能也更加複雜、繁多。

在大數據時代，每個人都應該掌握一門資料庫（DB）以及資料庫語言（SQL）。

資料庫（DB）主要分為關係資料庫（RDB）和非關係資料庫（NoSQL）。兩者存在許多顯著的不同點，其中最重要的是NoSQL不使用SQL作為查詢語言。

SQL（ Structured Query Language，結構化查詢語言）是一種特定目的程序語言，用於管理關係資料庫管理系統（RDBMS），或在關係流數據管理系統（RDSMS）中進行流處理。SQL在1986年成為美國國家標準學會（ANSI）的一項標準，在1987年成為國際標準化組織（ISO）標準。

說到資料庫的學習，網上也有許多教程，主要是看自己喜歡哪一種。在此，我傾向於MySQL，因為這是開源的資料庫。MariaDB也很不錯，這是MySQL的一門代替品。若想要了解關於MySQL的SQL的具體語法，可以看看我的這篇文章：@如何快速入門MySQL - 墨染如塵的文章 - 知乎 https://zhuanlan.zhihu.com/p/40280591。

當然，資料庫除了具有收集、存儲、截取、安全保障、備份等基礎功能外，還可以用於分析數據。那應該如何利用SQL進行數據分析呢？接下來，我會利用最新的智聯招聘網站的成都市數據分析職位招聘數據進行簡單數據分析。（在此，參照我的文章：@怎樣找到一個適合自己的數據分析崗位）

一、提出問題

1、成都市數據分析崗位的薪水如何？工作經驗要求如何？

2、成都市數據分析崗位的薪水和工作經驗要求有何關係？

3、成都市招聘數據分析崗位的公司性質、公司規模如何？從事哪些數據分析崗位最好（從工作經驗和學歷兩方面分析）？

4、根據自己的實際情況，哪些公司的數據分析崗位適合自己？

二、理解數據

1、數據獲取

首先，利用GooSeeker網路爬蟲軟體爬取智聯網的成都市數據分析職位招聘數據（爬蟲教程詳見@當我第一次接觸爬蟲）。

在爬取前選擇了公司名稱、公司性質、公司規模、職位名稱、工作經驗、學歷、職位月薪、反饋率等信息作為數據欄位。然後將爬取完成後生成的數個xml文件進行轉換，輸出為xlsx格式並導入Excel中，得到智聯招聘網站的成都市數據分析職位招聘原始數據。

由於原始數據較為繁雜、不易查看，所以我進行一些簡單的處理，只保留了公司名稱、公司性質、公司規模、職位名稱、工作經驗、學歷、職位月薪、反饋率等有用信息，得到較為乾淨的數據（有5529個數據分析崗位）：

2、理解數據

公司名稱：一個公司的稱呼，指哪些公司在招聘
公司性質：企業在國民經濟行業分類里隸屬的行業類別，比如教育、金融、電商等
公司規模：是指按有關標準和規定劃分的企業規模。一般分為特大型、大型、中型、小型、微型。
職位名稱：公司給數據分析師的某種定義，不同的公司可能會不一樣
工作經驗：一般都是指工作經驗時間。（從事某行業時間，超過6~12個月算1年； 1~6個月算1年）
學歷：主要指受教育水平
職位月薪：公司每月給員工發的工資
反饋率：指企業HR對求職者所投簡歷的反饋,是面試還是不合適等做出具體的一種回應。

3、數據的導入

數據的輸入有兩種方法，第一種是導入外部數據，第二種是直接寫入數據。我們用MySQL與Navicat Premium連接後，可以直接導入Excel文件（.xls），如下：

點擊下一步後，

導入成功後，得到數據表：

三、數據清洗

1、刪除重複記錄

①刪除重複值

在表中完全重複的記錄，也即所有欄位均重複的記錄，若要刪除，需要保留一條，因此用distinct關鍵字進行篩選，代碼如下：

START TRANSACTION;

CREATE TABLE workdata_new SELECT DISTINCT * FROM workdata;

DELETE FROM workdata;

INSERT INTO workdata SELECT * FROM workdata_new;

DROP TABLE workdata_new;

COMMIT;

上圖中的紅圈裡顯示，記錄共5270條，原始數據時5529條，表明有559條重複數據。

②在資料庫的表中，刪除了重複值後，發現還沒有主鍵。此時可以插入一序列，作為主鍵，便於查詢和管理。

ALTER TABLE workdata

ADD COLUMN 序號 INTEGER AUTO_INCREMENT PRIMARY KEY;

可見，表中已經添加了序號列，並作為該表的主鍵。

2、缺失值處理

Mysql中可以使用IS NULL或者通過比較是否是空字元串來判斷空值。在我們的數據表中要判斷所有欄位是否為空值，或者是否是空字元串，下面我們分別給出判斷數據表空值和空字元串的代碼：

①判斷是否為空值：

SELECT * FROM workdata

WHERE 公司名稱 IS NULL OR 公司性質 IS NULL OR 公司規模 IS NULL OR

職位名稱 IS NULL OR 職位月薪 IS NULL OR 工作經驗 IS NULL OR

學歷 IS NULL OR 反饋率 IS NULL;

由上圖可知，表中不存在空值。

②判斷是否為空字元串：

SELECT * FROM workdata

WHERE 公司名稱 = OR 公司性質 = OR 公司規模 = OR

職位名稱 = OR 職位月薪 = OR 工作經驗 = OR

學歷 = OR 反饋率 = ;

由上圖可知，表中存在3849條有空值的記錄。

③接下來進行篩選，查出都有哪些欄位存在空字元串：

經過排查，發現只有反饋率存在空字元串：

SELECT * FROM workdata WHERE 反饋率 =

從上圖中也可以看出，反饋率欄位為空的記錄是3849條記錄。這與上面的數據相符，說明表中存在的缺失值應是反饋率的缺失而導致的。因此，只需將反饋率的缺失值補上即可。

④在前面學過，處理缺失值的方法主要有四種：通過人工手動補全、刪除缺失數據、用平均值代替缺失值以及用統計模型計算的值去代替缺失值。在此處，我們可以用平均值代替缺失值。不過這裡的平均值是指除開空值後的數據的平均值。

START TRANSACTION;

CREATE TABLE workdata_new SELECT DISTINCT * FROM workdata;

UPDATE workdata

SET 反饋率 = (SELECT CONCAT(ROUND(AVG(

CAST(LEFT(反饋率,LENGTH(反饋率)-1) AS SIGNED INTEGER)

)),%) FROM workdata_new WHERE 反饋率 != )

WHERE 序號 IN (SELECT 序號 FROM workdata_new WHERE 反饋率 = );

DROP TABLE workdata_new;

COMMIT;

此時，再查詢是否有空值：

圖中顯示均為null，說明已經不存在缺失值了。

3、一致化處理

一致化處理就是對數據進行數據抽取，使得數據欄位滿足基本的數據分析需求。

通過觀察，可以發現：

公司性質和公司規模欄位中冒號前的字元串屬於多餘的，需要去掉
工作經驗和學歷兩個欄位之間有些錯位，需要糾正
工作經驗和學歷兩個欄位糾正後，也存在與上述字元串多餘問題，需要將其中多餘的字元串去掉
工作經驗和學歷兩個欄位糾正且去掉多餘字元串後，會出現缺失值，需要填充
職位月薪需要分裂為最高月薪和最低月薪，且要轉化為數值型數據

1）公司性質和公司規模欄位：

字元串多餘問題：

公司性質列：

UPDATE workdata

SET 公司性質 = SUBSTR(公司性質,6);

上圖只是我截取的一部分數據。我觀察所有數據，發現有些是空字元串，因此將其都填充為「其他」。

SET 公司性質 = 其他

WHERE 公司性質 = ;

公司規模列：

UPDATE workdata

SET 公司規模 = SUBSTR(公司規模,6);

2)工作經驗和學歷欄位：

①錯位處理：

由於都是學歷字列錯位到工作經驗列，因此先更改學歷列。在學歷列中查找是否是學歷，如果不是就用沒有更改的工作經驗列替換。

學歷列：

UPDATE workdata

SET 學歷 = 工作經驗

WHERE LEFT(學歷,2) != 學歷;

工作經驗列：

SET 工作經驗 = 經驗：不限

WHERE LEFT(工作經驗,2) != 經驗;

②多餘字元串處理：

工作經驗列：

UPDATE workdata

SET 工作經驗 = SUBSTR(工作經驗,4);

學歷列：

UPDATE workdata

SET 學歷 = SUBSTR(學歷,4);

3）職位月薪欄位：

分列職位月薪欄位，先添加兩列（最低薪水和最高薪水），然後分別賦值。

ALTER TABLE workdata ADD (最低薪水 INTEGER,最高薪水 INTEGER);

UPDATE workdata

SET 最低薪水 = CAST(SUBSTRING_INDEX(職位月薪,-,1) AS SIGNED INTEGER) ,

最高薪水 = CAST(SUBSTRING_INDEX(職位月薪,-,-1) AS SIGNED INTEGER)

WHERE 職位月薪 != 面議 AND 職位月薪 != 1000元以下;

但是，上圖中出現了空值，這是因為職位月新欄位中存在著「面議」或「1000元以下」等數據。這時，我們只需要求出最低薪水與最高薪水的平均值作為表中的平均月薪列，並將「面議」或「1000元以下」等賦值給相應的記錄。

ALTER TABLE workdata ADD (平均月薪 VARCHAR(255));

UPDATE workdata

SET 平均月薪 = CAST((最低薪水+最高薪水)/2 AS CHAR);

UPDATE workdata

SET 平均月薪 = 職位月薪

WHERE 職位月薪 = 面議 OR 職位月薪 = 1000元以下;

4、數據排序

經過上述數據清洗步驟的操作，得到了乾淨的數據，此時可以將平均月薪作為數據排序的標準，並且按照降序的方式進行排序，得到結果如下：

5、異常值處理

在上述數據集中，我們需要對數據分析崗位進行分析，所以需要通過職位名稱來判斷該職位是否是數據分析崗位。

我們可以查找「職位名稱」欄位中是否有「產品經理」、「分析師」、「數據分析」、「商業分析」、「數據運營」、「電商運營」、「用戶運營」、「數據挖掘」、「演算法專家」以及「數據工程師」等字元串，若存在，則說明是數據分析崗位，反之則不是數據分析崗位，將其刪除。當然，每個公司對於數據分析崗位的定義都是不盡相同的，這裡的稱呼只能大致概括，不能包含全部。如有錯誤，還請指正。

START TRANSACTION;

CREATE TABLE workdata_new1 SELECT DISTINCT * FROM workdata;

DELETE FROM workdata

WHERE 序號 NOT IN

(SELECT 序號 FROM workdata_new1

WHERE 職位名稱 LIKE %產品經理%

OR 職位名稱 LIKE %分析師%

OR 職位名稱 LIKE %數據分析%

OR 職位名稱 LIKE %商業分析%

OR 職位名稱 LIKE %數據運營%

OR 職位名稱 LIKE %電商運營%

OR 職位名稱 LIKE %用戶運營%

OR 職位名稱 LIKE %數據挖掘%

OR 職位名稱 LIKE %演算法專家%

OR 職位名稱 LIKE %數據工程師%);

DROP TABLE workdata_new1;

COMMIT;

如圖，通過篩選，得到了773個數據分析崗位。

四、構建模型（描述分析）

描述統計分為兩大部分：數據描述和指標統計。

數據描述：用來對數據進行基本情況的刻畫，包括：數據總數、時間跨度、時間粒度、空間範圍、空間粒度、數據來源等。建模還要看數據的極值、分布、離散度等內容。
指標統計：用來作報告，分析實際情況的數據指標，可粗略分為四大類：變化、分布、對比、預測。

描述分析的產出是圖表，而解決需求也是將基於這些圖表產出。

（當然，若要產出圖表，還是要使用製作圖表類工具，比如Excel，Power BI等）

五、數據可視化（解決需求）

1、成都市數據分析崗位的薪水如何？工作經驗要求如何？

1）月薪：

SELECT SUM(CASE WHEN 平均月薪=面議 THEN 1 ELSE 0 END) AS 面議,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)<=2000

THEN 1 ELSE 0 END) AS 2000以下,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>2000 AND CAST(平均月薪 AS SIGNED INTEGER)<=4000

THEN 1 ELSE 0 END) AS 2000-4000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>4000 AND CAST(平均月薪 AS SIGNED INTEGER)<=6000

THEN 1 ELSE 0 END) AS 4000-6000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>6000 AND CAST(平均月薪 AS SIGNED INTEGER)<=8000

THEN 1 ELSE 0 END) AS 6000-8000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>8000 AND CAST(平均月薪 AS SIGNED INTEGER)<=10000

THEN 1 ELSE 0 END) AS 8000-10000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>10000 AND CAST(平均月薪 AS SIGNED INTEGER)<=15000

THEN 1 ELSE 0 END) AS 10000-15000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>15000 AND CAST(平均月薪 AS SIGNED INTEGER)<=20000

THEN 1 ELSE 0 END) AS 15000-20000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>20000 AND CAST(平均月薪 AS SIGNED INTEGER)<=30000

THEN 1 ELSE 0 END) AS 20000-30000,

SUM(CASE WHEN CAST(平均月薪 AS SIGNED INTEGER)>30000

THEN 1 ELSE 0 END) AS 30000以上

FROM workdata;

從圖中可以看出，月薪在4000-6000的最多，其次是6000-8000和10000-15000（相差不大），後面依次是8000-10000，2000-4000。總體看來，數據分析師的薪資水平大致在4000-15000之間，還是相當不錯的。

除去需要面議的平均月薪外，剩下的平均月薪進行降序排列，取出月薪工資最高的15家公司。

SELECT 公司名稱,平均月薪 FROM workdata

WHERE 平均月薪 != 面議

ORDER BY CAST(平均月薪 AS SIGNED INTEGER) DESC LIMIT 15;

圖中是按照月薪排名的前15家公司，最高的是四川郵科通信技術有限公司（達32500元/月），其次是亞信科技（中國）有限公司（達30500元/月），後面依次是成都四方偉業軟體股份有限公司，四川優榮商業管理有限公司，成都數聚源科技有限公司等等。當然，工資越高，需要的條件也是越高的。若是達到了要求，也可以去試試。

2）工作經驗：

SELECT 工作經驗工作年限,COUNT(*) 公司頻數 FROM workdata

GROUP BY 工作經驗

ORDER BY 公司頻數 DESC;

從上圖可以發現，公司數據分析崗位對工作年限「不限」需求最大，這可能不太準確，因為我們做數據清洗時，是將空值都設為「不限」，因此可能需要進一步篩選。

公司數據分析崗位對工作年限在1-3年（最大）、3-5年（次之）的需求較大，而對工作年限在5-10年、1年以下和無經驗的需求較小。

2、成都市數據分析崗位的薪水和工作經驗要求有何關係？

SELECT 工作經驗工作年限,ROUND(AVG(CAST(平均月薪 AS SIGNED INTEGER))) 平均薪水 FROM workdata

GROUP BY 工作經驗

ORDER BY 平均薪水 DESC;

從上圖中可以看出，從「無經驗」到「工作經驗為5-10年」這個過程中，薪水的大致趨勢是上升的。說明數據分析這個工作的薪水是隨著工作經驗的增長而增長的，只要一直往下走，是越來越好的。

3、成都市招聘數據分析崗位的公司性質、公司規模如何？從事哪些數據分析崗位最好（從工作經驗和學歷兩方面分析）？

1）公司性質

SELECT 公司性質,COUNT(*) 公司頻數 FROM workdata

GROUP BY 公司性質

ORDER BY 公司頻數 DESC;

從上圖中，可以看出招聘數據分析崗位的公司主要是民營企業為主，股份制企業和上市公司也比較多。

2）公司規模

SELECT 公司規模,COUNT(*) 公司頻數 FROM workdata

GROUP BY 公司規模

ORDER BY 公司頻數 DESC;

從圖中可知，招聘數據分析崗位的公司規模一般達到100-499人（最多）、20-99人（次之）、1000-9999人（再次）的比較多。表明招聘數據分析最多的公司一般是中小型公司，大型公司在成都招聘的比較少。

3）數據分析崗位：工作經驗

SELECT 職位名稱,平均月薪 FROM workdata

WHERE 平均月薪 != 面議

ORDER BY CAST(平均月薪 AS SIGNED INTEGER) DESC LIMIT 15;

從上圖中可以發現，月薪最高的是大數據分析工程師，其次是數據分析工程師和大數據挖掘分析師。總的來看，月薪比較高的職位主要是大數據工程師一類、數據挖掘工程師以及數據產品經理等。

4）數據分析崗位：工作經驗

SELECT 學歷,COUNT(*) 公司頻數 FROM workdata

GROUP BY 學歷

ORDER BY 公司頻數 DESC;

從圖中可以發現，公司招聘數據分析崗位對學歷的要求主要是本科和大專，對碩士和中專也有要求，但是不多。

4、根據自己的實際情況，哪些公司的數據分析崗位適合自己？

我的實際情況是：

教育要求：本科

工作年限：應屆畢業生

預期工資：4000左右（即3000-5000之間）

（還可以自己添加條件，具體情況具體分析）

根據自己的實際情況，分析哪些公司的數據分析崗位適合自己：

SELECT * FROM workdata

WHERE 學歷 != 碩士

AND (工作經驗=不限 OR 工作經驗=無經驗 OR 工作經驗=1年以下)

AND (CAST(平均月薪 AS SIGNED INTEGER) >=3000

AND CAST(平均月薪 AS SIGNED INTEGER) <=5000);

上述圖片中共有95家滿足條件的公司，我按照平均月薪進行降序排列，得到前20的公司：

SELECT * FROM workdata

WHERE 學歷 != 碩士

AND (工作經驗=不限 OR 工作經驗=無經驗 OR 工作經驗=1年以下)

AND (CAST(平均月薪 AS SIGNED INTEGER) >=3000

AND CAST(平均月薪 AS SIGNED INTEGER) <=5000)

ORDER BY CAST(平均月薪 AS SIGNED INTEGER) DESC LIMIT 20;

以上就是滿足要求的前20的公司信息。現在正值秋招和校招的季節，各大公司對應屆畢業生的招聘也比較多，希望上面的分析能給大家帶來一點參考。

參考：

http://bluewhale.cc/2017-10-20/like-sql-using-sql-for-data-analysis.html

https://zhuanlan.zhihu.com/p/39714468