SQL教你如何找到数据分析工作

众所周知，在过去那个信息处理能力受限的时代，世界上的数据量是十分巨大的，却缺乏用来分析所收集数据的工具。那如何选择样本呢？统计学家认为应对数据采用随机抽样的方法，并且证明了采样分析的精确性随着采样随机性的增加而大幅提高，但与样本数量的增加关系不大。为什么会这样？简单来说，当样本数量达到了某个值之后，我们从新的个体身上得到的信息就会越来越少，就像经济学中的边际效应递减一样。

然而，任何方法都存在缺陷，随机采样也不例外。它的成功依赖于采样的绝对随机性，但是要实现采样的随机性是非常困难的。那还有什么办法能够代替随机采样呢？

如今，进入到大数据时代后，技术条件已经有了很大的提高，我们慢慢意识到自己拥有了能够收集和处理更大规模数据的能力，尤其是数据库技术。所以，我们完全可以利用所有的数据，而不是依靠随机采样去获得一部分数据。在那次流感趋势预测中，谷歌公司并不是依赖于对随机样本的分析，而是分析了整个美国几十亿条互联网检索记录。因此，随着数据库和数据处理技术发生了翻天覆地的改变，我们可以轻易对这些数据进行收集、存储、管理以及分析等。正是因为数据库的作用越来越大，其所包含的功能也更加复杂、繁多。

在大数据时代，每个人都应该掌握一门数据库（DB）以及数据库语言（SQL）。

数据库（DB）主要分为关系数据库（RDB）和非关系数据库（NoSQL）。两者存在许多显著的不同点，其中最重要的是NoSQL不使用SQL作为查询语言。

SQL（ Structured Query Language，结构化查询语言）是一种特定目的程序语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。SQL在1986年成为美国国家标准学会（ANSI）的一项标准，在1987年成为国际标准化组织（ISO）标准。

说到数据库的学习，网上也有许多教程，主要是看自己喜欢哪一种。在此，我倾向于MySQL，因为这是开源的数据库。MariaDB也很不错，这是MySQL的一门代替品。若想要了解关于MySQL的SQL的具体语法，可以看看我的这篇文章：@如何快速入门MySQL &＃8211; 墨染如尘的文章 &＃8211; 知乎 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 公司名称 = &＃8221; OR 公司性质 = &＃8221; OR 公司规模 = &＃8221; OR

职位名称 = &＃8221; OR 职位月薪 = &＃8221; OR 工作经验 = &＃8221; OR

学历 = &＃8221; OR 反馈率 = &＃8221;;

由上图可知，表中存在3849条有空值的记录。

③接下来进行筛选，查出都有哪些字段存在空字符串：

经过排查，发现只有反馈率存在空字符串：

SELECT * FROM workdata WHERE 反馈率 = &＃8216;

从上图中也可以看出，反馈率字段为空的记录是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)

)),&＃8217;%&＃8217;) FROM workdata_new WHERE 反馈率 != &＃8221;)

WHERE 序号 IN (SELECT 序号 FROM workdata_new WHERE 反馈率 = &＃8221;);

DROP TABLE workdata_new;

COMMIT;

此时，再查询是否有空值：

图中显示均为null，说明已经不存在缺失值了。

3、一致化处理

一致化处理就是对数据进行数据抽取，使得数据字段满足基本的数据分析需求。

通过观察，可以发现：

• 公司性质和公司规模字段中冒号前的字符串属于多余的，需要去掉
• 工作经验和学历两个字段之间有些错位，需要纠正
• 工作经验和学历两个字段纠正后，也存在与上述字符串多余问题，需要将其中多余的字符串去掉
• 工作经验和学历两个字段纠正且去掉多余字符串后，会出现缺失值，需要填充
• 职位月薪需要分裂为最高月薪和最低月薪，且要转化为数值型数据

1）公司性质和公司规模字段：

字符串多余问题：

• 公司性质列：

UPDATE workdata

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

上图只是我截取的一部分数据。我观察所有数据，发现有些是空字符串，因此将其都填充为“其他”。

SET 公司性质 = &＃8216;其他&＃8217;

WHERE 公司性质 = &＃8221; ;

• 公司规模列：

UPDATE workdata

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

2)工作经验和学历字段：

①错位处理：

由于都是学历字列错位到工作经验列，因此先更改学历列。在学历列中查找是否是学历，如果不是就用没有更改的工作经验列替换。

• 学历列：

UPDATE workdata

SET 学历 = 工作经验

WHERE LEFT(学历,2) != &＃8216;学历&＃8217;;

• 工作经验列：

SET 工作经验 = &＃8216;经验：不限&＃8217;

WHERE LEFT(工作经验,2) != &＃8216;经验&＃8217;;

②多余字符串处理：

• 工作经验列：

UPDATE workdata

SET 工作经验 = SUBSTR(工作经验,4);

• 学历列：

UPDATE workdata

SET 学历 = SUBSTR(学历,4);

3）职位月薪字段：

分列职位月薪字段，先添加两列（最低薪水和最高薪水），然后分别赋值。

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

UPDATE workdata

SET 最低薪水 = CAST(SUBSTRING_INDEX(职位月薪,&＃8217;-&＃8216;,1) AS SIGNED INTEGER) ,

最高薪水 = CAST(SUBSTRING_INDEX(职位月薪,&＃8217;-&＃8216;,-1) AS SIGNED INTEGER)

WHERE 职位月薪 != &＃8216;面议&＃8217; AND 职位月薪 != &＃8216;1000元以下&＃8217;;

但是，上图中出现了空值，这是因为职位月新字段中存在着“面议”或“1000元以下”等数据。这时，我们只需要求出最低薪水与最高薪水的平均值作为表中的平均月薪列，并将“面议”或“1000元以下”等赋值给相应的记录。

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

UPDATE workdata

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

UPDATE workdata

SET 平均月薪 = 职位月薪

WHERE 职位月薪 = &＃8216;面议&＃8217; OR 职位月薪 = &＃8216;1000元以下&＃8217;;

4、数据排序

经过上述数据清洗步骤的操作，得到了干净的数据，此时可以将平均月薪作为数据排序的标准，并且按照降序的方式进行排序，得到结果如下：

5、异常值处理

在上述数据集中，我们需要对数据分析岗位进行分析，所以需要通过职位名称来判断该职位是否是数据分析岗位。

我们可以查找“职位名称”字段中是否有“产品经理”、“分析师”、“数据分析”、“商业分析”、“数据运营”、“电商运营”、“用户运营”、“数据挖掘”、“算法专家”以及“数据工程师”等字符串，若存在，则说明是数据分析岗位，反之则不是数据分析岗位，将其删除。当然，每个公司对于数据分析岗位的定义都是不尽相同的，这里的称呼只能大致概括，不能包含全部。如有错误，还请指正。

START TRANSACTION;

CREATE TABLE workdata_new1 SELECT DISTINCT * FROM workdata;

DELETE FROM workdata

WHERE 序号 NOT IN

(SELECT 序号 FROM workdata_new1

WHERE 职位名称 LIKE &＃8216;%产品经理%&＃8217;

OR 职位名称 LIKE &＃8216;%分析师%&＃8217;

OR 职位名称 LIKE &＃8216;%数据分析%&＃8217;

OR 职位名称 LIKE &＃8216;%商业分析%&＃8217;

OR 职位名称 LIKE &＃8216;%数据运营%&＃8217;

OR 职位名称 LIKE &＃8216;%电商运营%&＃8217;

OR 职位名称 LIKE &＃8216;%用户运营%&＃8217;

OR 职位名称 LIKE &＃8216;%数据挖掘%&＃8217;

OR 职位名称 LIKE &＃8216;%算法专家%&＃8217;

OR 职位名称 LIKE &＃8216;%数据工程师%&＃8217;);

DROP TABLE workdata_new1;

COMMIT;

如图，通过筛选，得到了773个数据分析岗位。

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

描述统计分为两大部分：数据描述和指标统计。

• 数据描述：用来对数据进行基本情况的刻画，包括：数据总数、时间跨度、时间粒度、空间范围、空间粒度、数据来源等。建模还要看数据的极值、分布、离散度等内容。
• 指标统计：用来作报告，分析实际情况的数据指标，可粗略分为四大类：变化、分布、对比、预测。

描述分析的产出是图表，而解决需求也是将基于这些图表产出。

（当然，若要产出图表，还是要使用制作图表类工具，比如Excel，Power BI等）

五、数据可视化（解决需求）

1、成都市数据分析岗位的薪水如何？工作经验要求如何？

1）月薪：

SELECT SUM(CASE WHEN 平均月薪=&＃8217;面议&＃8217; THEN 1 ELSE 0 END) AS 面议,

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

THEN 1 ELSE 0 END) AS &＃8216;2000以下&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;2000-4000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;4000-6000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;6000-8000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;8000-10000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;10000-15000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;15000-20000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;20000-30000&＃8217;,

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

THEN 1 ELSE 0 END) AS &＃8216;30000以上&＃8217;

FROM workdata;

从图中可以看出，月薪在4000-6000的最多，其次是6000-8000和10000-15000（相差不大），后面依次是8000-10000，2000-4000。总体看来，数据分析师的薪资水平大致在4000-15000之间，还是相当不错的。

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

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

WHERE 平均月薪 != &＃8216;面议&＃8217;

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 平均月薪 != &＃8216;面议&＃8217;

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 学历 != &＃8216;硕士&＃8217;

AND (工作经验=&＃8217;不限&＃8217; OR 工作经验=&＃8217;无经验&＃8217; OR 工作经验=&＃8217;1年以下&＃8217;)

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

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

上述图片中共有95家满足条件的公司，我按照平均月薪进行降序排列，得到前20的公司：

SELECT * FROM workdata

WHERE 学历 != &＃8216;硕士&＃8217;

AND (工作经验=&＃8217;不限&＃8217; OR 工作经验=&＃8217;无经验&＃8217; OR 工作经验=&＃8217;1年以下&＃8217;)

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