以下结合使用MySQL和Tableau对公司员工信息进行统计及可视化:
数据来源
数据链接:https://www.dropbox.com/s/3czfpe0njsq868q/employees_mod.sql?dl=0
数据库预览
数据库名称: employees_mod
数据库表:
数据提取及可视化
1.从1990年开始每年入职男性和女性员工的人数明细
MySQL提取数据:
SELECT
YEAR(d.from_date) AS calendar_year,
e.gender,
COUNT(e.emp_no) AS num_of_employees
FROM
t_employees e
JOIN
t_dept_emp d ON d.emp_no = e.emp_no
GROUP BY calendar_year , e.gender
HAVING calendar_year >= 1990;
导出数据表:Tableau导入数据表:
Tableau可视化:
2.比较从1990年开始每年不同部门的男性和女性管理人员的人数
MySQL提取数据:
SELECT
d.dept_name,
ee.gender,
dm.emp_no,
dm.from_date,
dm.to_date,
e.calendar_year,
CASE
WHEN YEAR(dm.to_date) >= e.calendar_year AND YEAR(dm.from_date) <= e.calendar_year THEN 1
ELSE 0
END AS active
FROM
(SELECT
YEAR(hire_date) AS calendar_year
FROM
t_employees
GROUP BY calendar_year) e
CROSS JOIN
t_dept_manager dm
JOIN
t_departments d ON dm.dept_no = d.dept_no
JOIN
t_employees ee ON dm.emp_no = ee.emp_no
ORDER BY dm.emp_no, calendar_year;
导出数据表:
Tableau导入数据表:
Tableau可视化:
3.比较直到 2002 年女性和男性员工的平均工资,并添加一个过滤器查看每个部门的平均工资
MySQL提取数据:
SELECT
e.gender,
d.dept_name,
ROUND(AVG(s.salary), 2) AS salary,
YEAR(s.from_date) AS calendar_year
FROM
t_salaries s
JOIN
t_employees e ON s.emp_no = e.emp_no
JOIN
t_dept_emp de ON de.emp_no = e.emp_no
JOIN
t_departments d ON d.dept_no = de.dept_no
GROUP BY d.dept_no , e.gender , calendar_year
HAVING calendar_year <= 2002
ORDER BY d.dept_no;
导出数据表:
Tableau导入数据表:
Tableau可视化:
4.创建一个 SQL 存储过程,该过程可获取特定工资范围(例:5000,9000)内每个部门的平均男性和女性工资
MySQL提取数据:
DROP PROCEDURE IF EXISTS filter_salary;
DELIMITER $$
CREATE PROCEDURE filter_salary (IN p_min_salary FLOAT, IN p_max_salary FLOAT)
BEGIN
SELECT
e.gender, d.dept_name, AVG(s.salary) as avg_salary
FROM
t_salaries s
JOIN
t_employees e ON s.emp_no = e.emp_no
JOIN
t_dept_emp de ON de.emp_no = e.emp_no
JOIN
t_departments d ON d.dept_no = de.dept_no
WHERE s.salary BETWEEN p_min_salary AND p_max_salary
GROUP BY d.dept_no, e.gender;
END$$
DELIMITER ;
CALL filter_salary(50000, 90000);
导出数据表:
Tableau导入数据表:
Tableau可视化:
5.制作dashboard
Tableau可视化: