用SQL分析找工作

來自專欄數據分析

1. 工具的安裝

電腦系統：mac

mysql服務端：https://dev.mysql.com/downloads/mysql/

mysql客戶端：navicat（網上可以找到破解版）

sublime text: 可以打開sql、對excel文件進行轉碼的工具，類似windows中的notepad++

2. 數據準備

數據來源：使用爬蟲工具在前程無憂上爬取了深圳市產品經理招聘信息前10頁數據

數據格式：轉存為csv

3. 通過分析希望得出以下問題的結論

1）如果投遞數據產品經理崗位，我的期待薪資填多少會比較符合行情？

2）投遞什麼樣的公司，面試概率高？

4. 數據導入

1）在mysql中新建資料庫work

select database work；

2）選中表，右鍵導入嚮導，導入csv源數據

【注意】

（1）為了避免源數據字元串過長，需要將數類型改成text，長度不限制，否則很有可能會導入失敗;

（2）若源數據中有中文，在導入時注意編碼要選擇簡體中文，否則導入出來的數據會亂碼。

(3) 源數據保存格式：csv的兼容性好，不建議直接使用xls/xlsx格式進行導入。

5. 數據清洗

1）為了便於搜索，把中文的列名重名為英文

alter table work_analysischange column `職位名稱`job_title text,change column `薪資` salary text,change column `公司名稱`company text,change column `工作地點`address text,change column `發布時間`date text;

2）為了便於排重和建立數據間的映射關係，給表新增列id，並作為主鍵

alter table work_analysisadd column id int auto_increment primary key;

3）選擇子集：選取跟數據產品經理相關的崗位，其餘數據刪除

delete from work_analysiswhere job_title not like %數據%;

4）排重：由於表中沒有唯一標識的數據列（例如崗位編號），因此無法排重。

5）缺失值處理：刪除空值

delete from work_analysiswhere job_title is nullor salary is nullor company is nullor address is nullor date is null;

5）一致化處理和異常值處理

salary中需要將最低薪資、最高薪資截取出來，以數值的形式呈現，並增加平均值的計算

-- 新增列alter table work_analysisadd column low_salary float, add column high_salary float, add column avg_salary float;-- 截取最低薪資update work_analysisset low_salary = substring_index(salary, -,1)*1000where salary like %千%and salary not like %年;update work_analysisset low_salary = substring_index(salary, -,1)*10000where salary like %萬%and salary not like %年;-- 截取最高薪資update work_analysisset high_salary = substring_index(substring_index(salary,-,-1),/,1)*1000where salary like %千%and salary not like %年;update work_analysisset high_salary = substring_index(substring_index(salary,-,-1),/,1)*10000where salary like %萬%and salary not like %年;-- 排除異常值update work_analysisset low_salary = substring_index(salary, -,1)*1000/12where salary like %千%and salary like %年;update work_analysisset low_salary = substring_index(salary, -,1)*10000/12where salary like %萬%and salary like %年;update work_analysisset high_salary = substring_index(substring_index(salary,-,-1),/,1)*1000/12where salary like %千%and salary like %年;update work_analysisset high_salary = substring_index(substring_index(salary,-,-1),/,1)*10000/12where salary like %萬%and salary like %年;-- 取整update work_analysisset low_salary = round(low_salary,0);update work_analysisset high_salary = round(high_salary,0);-- 計算平均值update work_analysisset avg_salary= round((low_salary+high_salary)/2,0);

清洗結果如下：

6. 解題思路

問題1

1) 查找跟「數據產品經理」相關的崗位名稱和對應的平均薪資範圍

select max(avg_salary), min(avg_salary)from work_analysis;

【結論】：深圳市數據產品經理相關崗位平均薪資在4250--40000元之間

2）將數據產品經理的薪資進行分組和計數

select sum(case when avg_salary <= 10000 then 1 else 0 end) as low_salary,sum(case when avg_salary between 10001 and 20000 then 1 else 0 end) as mid_salary,sum(case when avg_salary >= 20001 then 1 else 0 end) as high_salaryfrom work_analysis;

【結論】數據產品經理的薪資都集中在10000-20000之間，屬於中高薪崗位。

因此，對於文章開始拋出的問題1

如果投遞數據產品經理崗位，我的期待薪資填多少會比較符合行情？

可得出如下結論：如果在數據產品崗位有初級經驗（例如1-3年），可將期待薪資填寫在10000-20000元之間，如果經驗豐富（例如3年以上），可將期待薪資往20000以上填，但最高40000封頂，如果超過了這個上限，估計很少公司會給offer。

問題2

查找出薪資範圍在10000-20000之間的招聘數據產品經理的公司以及對應薪資，按發布日期薪資降序排序。

select company, avg_salary, datefrom work_analysiswhere avg_salary between 10001 and 20000order by date DESC, avg_salary DESC;

因此，對於文章開始拋出的問題2

投遞什麼樣的公司，面試概率高？

可得出結論：由於是初級經驗，因此投遞簡歷最好投遞薪資在10000-20000之間的公司，這樣對應的能力比較匹配，面試的概率也會提高。此外，投遞的時候，最好按照順序投遞，因為此結果是按崗位發布時間降序排序的，通常情況下，發布時間離當前越近，表示招人需求越迫切，而發布時間離當前日期比較遠，該企業很可能已經有意向的候選人了，只不過該候選人還未入職。

推薦閱讀：