MySQL公司员工分析过程

一、分析目的

通过数据，分析企业员工结构，工资变化，以便了解公司的内部情况，改善企业管理方式。

二、数据来源及数据字段定义

数据来源：employees以及安装方式

数据大小：6张表，共计3919015条数据

数据库表结构：

departments:部门表, 记录部门编号和部门名称

dept_emp:部门员工表,记录各部门下属员工编号, 以及员工在该部门工作的起始和终止日期

dept_manager:部门经理表, 记录对应的部门经理的员工编号, 以及在该部门工作的起始和终止日期

employees:员工信息表, 记录员工初始日期, 姓名, 性别和雇佣日期信息

salaries:薪水表, 记录员工某段日期的薪水

titles:岗位表, 记录员工的岗位信息, 即该岗位任职起始和终止日期信息

三、数据处理

MySQL官方的数据库基本上已经是比较干净的数据，因此不需要太多的清洗工作。查看各个表格是否有缺失值：

1
2
3
4
5
6
7


SELECT COUNT(*), COUNT(dept_no), COUNT(dept_name) FROM departments;
SELECT COUNT(*), COUNT(emp_no), COUNT(dept_no), COUNT(from_date), COUNT(to_date) FROM dept_emp;
SELECT COUNT(*), COUNT(emp_no), COUNT(dept_no), COUNT(from_date), COUNT(to_date) FROM dept_manager;
SELECT COUNT(*), COUNT(emp_no), COUNT(birth_date), COUNT(first_name), COUNT(last_name), COUNT(gender), COUNT(hire_date) FROM employees;
SELECT COUNT(*), COUNT(emp_no), COUNT(salary), COUNT(from_date), COUNT(to_date) FROM salaries;
SELECT COUNT(*), COUNT(emp_no), COUNT(dept_no), COUNT(from_date), COUNT(to_date) FROM dept_emp;
SELECT COUNT(*), COUNT(emp_no), COUNT(title), COUNT(from_date), COUNT(to_date) FROM titles;

结果显示共有8个部门，24名管理岗员工，300024名员工，2844047条工资，443308条员工岗位数据，各列数据均没有缺失。

四、数据分析

4.1 公司发展趋势分析

可以从公司各年工资支出、各年平均工资变化及公司各年入职离职数量这3方面进行分析：

公司各年工资支出分析

1
2
3


SELECT YEAR(from_date), SUM(salary) AS cost
FROM salaries
GROUP BY YEAR(from_date);

将查询结果导入Excel，很容易得到：

公司各年平均工资变化

1
2
3


SELECT YEAR(from_date), AVG(salary)
FROM salaries
GROUP BY YEAR(from_date);

将查询结果导入Excel分析：

公司各年入职和离职数量

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


# 每年入职
SELECT YEAR(tt.from_date) AS from_date, COUNT(*) AS num
FROM(
SELECT emp_no, MIN(from_date) AS from_date, MAXx(to_date) AS to_date
FROM dept_emp
GROUP BY emp_no
) tt
GROUP BY YEAR(tt.from_date)
ORDER BY from_date;
# 每年离职
SELECT YEAR(tt.to_date) AS to_date, COUNT(*) AS num
FROM(
SELECT emp_no, MIN(from_date) AS from_date, MAXx(to_date) AS to_date
FROM dept_emp
GROUP BY emp_no
) tt
GROUP BY YEAR(tt.to_date)
ORDER BY to_date;

通过查询得到的数据，计算出下面两个指标:

每年在职员工数量
每年离职率

然后放入Excel中分析可得：

得出结论：公司每年公司各年工资支出、平均工资呈线性递增；公司各年入职和离职数量情况是入大于出，但离职率在逐年递增。

4.2 公司员工价值观

可以进行员工性别年龄的交叉分析：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


SELECT gender, ROUND((TO_DAYS('2002-08-01') - TO_DAYS(birth_date))/365,0)  AS ages,
COUNT(*)
FROM(
    SELECT emp_no, birth_date, gender
    FROM employees
) t1
JOIN(
    SELECT emp_no FROM titles WHERE TO_DATE='9999-01-01'
) t2 
ON t1.emp_no = t2.emp_no
GROUP BY gender, ROUND((TO_DAYS('2002-08-01') - TO_DAYS(birth_date))/365,0);

得出结论：公司男员工数量明显多于女员工，且公司员工的年龄位于38-50岁之间，各年龄分布均匀，没有太大差别。

4.3 年薪增长率

可以分析全公司总体每年的平均收入增长及岗位有升迁的员工平均收入增长：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


#总体每年收入增长
SELECT ROUND((max-min)/min/(years-1),2) AS increaceByYear, COUNT(*) AS num
FROM(
    SELECT emp_no, COUNT(*) AS years, MAX(salary) as max, MIN(salary) as min
FROM salaries
GROUP BY emp_no
HAVING years > 1
) tt
GROUP BY ROUND((max-min)/min/(years-1),2)
ORDER BY increaceByYear;
# 有岗位升迁的员工, 每年收入增长
SELECT ROUND((max-min)/min/(years-1),2) AS increaceByYear, COUNT(*) AS num
FROM(
    SELECT t1.emp_no, MAXx(salary) as max, MIN(salary) as min, COUNT(*) years
    FROM(
        SELECT emp_no, salary
        FROM salaries
    ) t1
    JOIN(
        SELECT emp_no, COUNT(*) AS tt
        FROM titles
        GROUP BY emp_no
        HAVING tt>1
    ) t2 
    ON t1.emp_no = t2.emp_no
    gGROUP BY t1.emp_no
    HAVING years > 1
) tt
GROUP BY ROUND((max-min)/min/(years-1),2)
ORDER BY increaceByYear;

在Excel中分析得：

得出结论：公司大部分员工的年收入都有一定增长，大部分的增长率处于0.02-0.06之间，而岗位有升迁的员工年收入增长普遍处于0.03-0.05之间，比没有岗位升迁的员工多了0.01。

4.4 各部门员工分布

可以分析各部门的人数差异及平均工资差异：

1
2
3
4
5
6
7
8


#各部门人数
SELECT dept_no, COUNT(*) FROM dept_emp GROUP BY dept_no;
#各部门平均工资
SELECT dept_no, AVG(salary) AS dept_avg_salary
FROM dept_emp d, salaries s
WHERE d.emp_no = s.emp_no
AND YEAR(s.to_date) = 9999
GROUP BY dept_no;

导入Excel可得：

得出结论：部门d004和d005的员工数最多，d007的员工平均工资最高。

文章目录