1 / 46

人力成本统计分析任务

人事管理. 人力成本统计分析任务. 任务背景 2-1. 你们项目组现在负责开发一个人事管理系统，你负责人力成本统计分析报表生成任务。 项目经理告诉你： 报表涉及的两个数据表 DEPARTMENTS 、 EMPLOYEES 已经设计好，可通过 dept_emp.txt 脚本创建 报表应提供如下信息： 各部门人数，工资总额，平均工资，最高工资，最低工资，工资差距指数（标准方差） 除报表外，应采取饼图、柱状图、曲线图等直观方式来显示统计结果. 任务背景 2-2. 项目经理告诉你 通过你开发的报表、图表，人力资源部经理可容易的了解： 哪些部门工资水平高？

florence-vance
Download Presentation

人力成本统计分析任务

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 人事管理 人力成本统计分析任务

  2. 任务背景 2-1 • 你们项目组现在负责开发一个人事管理系统，你负责人力成本统计分析报表生成任务。 • 项目经理告诉你： • 报表涉及的两个数据表 DEPARTMENTS、EMPLOYEES 已经设计好，可通过 dept_emp.txt脚本创建 • 报表应提供如下信息： • 各部门人数，工资总额，平均工资，最高工资，最低工资，工资差距指数（标准方差） • 除报表外，应采取饼图、柱状图、曲线图等直观方式来显示统计结果

  3. 任务背景 2-2 • 项目经理告诉你 • 通过你开发的报表、图表，人力资源部经理可容易的了解： • 哪些部门工资水平高？ • 哪些部门工资水平低？ • 哪些部门工资差距比较大？ • 哪些部门工资成本最高？ • 参考下面的报表设计，有助于你完成任务

  4. 报表设计 3-1 • 工资水平分析报表

  5. 报表设计 3-2 • 工资差异分析报表

  6. 报表设计 3-3 • 人力成本分析报表

  7. 关键技术 • 分组（聚集）函数 • 分组函数介绍 • APEX 图表创建 • 参见下面的构建步骤

  8. 报表构建 9-1 • 工资水平分析报表 • 创建页，选择 chart 页类型

  9. 报表构建 9-2 • 下一步选择 Flash Chart 图表类型 • 下一步， page name 输入： salary_analysis • 下一步，选择 Do not use tabs

  10. 报表构建 9-3 • 下一步，选择 2D Line Chart Type 在本页，选择图表类型后·，点击 Update 按钮可预览图表效果

  11. 报表构建 9-4 • 下一步输入 SQL 查询 Build Query 按钮可激活查询构造向导，引导你构造查询语句

  12. 报表构建 9-5 • 创建并运行页 • 编辑本页，添加报表区域

  13. 报表构建 9-6 • 选择 SQL Report

  14. 报表构建 9-7 • 输入查询语句，并创建区域，运行页

  15. 报表构建 9-8 • 工资差异分析报表 • 重复创建图表步骤 • 选择 2D Column 图表类型 • 用下面 SQL 语句来创建 SELECT null link, department_name label, VARIANCE(SALARY) 工资方差 FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name;

  16. 报表构建 9-9 • 人力成本分析报表 • 重复创建图表步骤 • 选择 3D Pie 图表类型 • 用下面 SQL 语句来创建 SELECT null link, department_name label, SUM(SALARY) 工资总额 FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name;

  17. 图表测试 • 根据你所创建的图表，分析现有数据： • 哪个部门工资水平最高？ • 哪个部门工资水平差异最大？ • 哪个部门人力成本最高？

  18. 作业与练习 • 计算平均提成 • 员工的提成为 salary*COMMISSION_PCT • 第一种方法：只计算有提成员工的平均提成 • 第二种方法：计算所有员工的平均提成 • 计算员工总人数 • 计算员工的不同工资数 • 计算员工的不同工作种类数目(JOB_ID)

  19. 总结 • 掌握分组函数使用方法 • 掌握 APEX 图表使用方法

  20. 分组（聚集）函数 27-1 • 在SQL 中，以下分组函数可对整个表或一组特定的行执行运算。每个函数返回一个结果。 • MIN • 用于存储任何数据类型的列，返回最小值。 • MAX • 用于存储任何数据类型的列，返回最大值。 • COUNT • COUNT(表达式) 返回表达式列中非空值的数量。 • COUNT(*) 返回表中满足SELECT 语句的条件的行数。 • AVG • 用于存储数值数据的列，计算平均值。 • SUM • 用于存储数值数据的列，计算值的总和。

  21. 分组（聚集）函数 27-2 • 例如，有四个数字: 1、7、4、 8 • 最小值 MIN 是 1 • 最大值 MAX 是 8 • 总和 SUM 是 20 = 1+7+ 4+8 • 数目 COUNT 是 4 • 平均值 AVG 是 5 = SUM/ COUNT • 方差 VARIANCE 是 • ( (1-AVG)* (1-AVG) + • (7-AVG)* (7-AVG) + • (4-AVG)* (4-AVG) + • (8-AVG)* (8-AVG) )/ (COUNT-1) =10 • 标准差 STDDEV 是方差 VARIANCE 的平方根 3.16 • VARIANCE： • 用于存储数值数据的列，计算数据在平均值左右的分布情况。例如，如果班级上次测验的平均分是82%，学生的分数范围在40% 到100% 之间，则此分数的差异将比学生的分数范围在78% 到88% 的情况下大。 • STDDEV： • 与variance 类似，标准偏差测量数据的分布情况。对于平均值大致相同的两组数据，分布越广，标准偏差就越大。

  22. 分组（聚集）函数 27-3 • 组函数使用要点： • 组函数会忽略空值。因为任何值和空值进行运算，结果还是空值。忽略空值可确保得到一个运算结果。 • 在下面的示例中，计算平均加班费率时没有使用(null) 值。

  23. 分组（聚集）函数 27-4 • 组函数使用要点： • SELECT 子句中可有多个基于相同或不同列的组函数。 • 还可使用WHERE 子句，将组函数限制在表的子集上。 SELECT MAX(salary), MIN(salary), MIN(employee_id) FROM employees WHERE department_id = 60;

  24. 分组（聚集）函数 27-5 • 组函数使用要点： • MIN 和MAX 两个组函数可用于任何数据类型。 • 使用这些函数，可以找出列表中最后一个人的姓名、最低薪金或最早的聘用日期。 • 例如，很容易在按字母排序的雇员列表中找到姓名列在第一行的雇员。 SELECT MIN(last_name) FROM employees;

  25. 分组（聚集）函数 27-6 • 组函数使用要点： • 组函数忽略空值。 • MIN 和MAX 可用于任何数据类型； • SUM、AVG、STDDEV 和VARIANCE 只能用于数值数据类型。

  26. 分组（聚集）函数 27-7 • 组函数使用语法 SELECT [col1, ] GROUP_FUNCTION(col2) [,…] FROM table1,… [WHERE condition] [GROUP BY col1,…] [HAVING group_condition] [ORDER BY col1,…]; • 如果有 WHERE 子句，先执行 WHERE ，根据其条件排除掉无关记录 • 如果有 GROUP 子句，则先按其指定列的值将剩余记录分组；否则所有记录被看作一组 • 对每组记录计算分组函数 • 如果有 HAVING 子句，则根据其中条件选择要显示哪些分组的结果 • 执行 SELECT 子句，返回相应列 • 执行 ORDER BY子句，对返回结果进行排序

  27. 分组（聚集）函数 27-8 • GROUP BY • 可以使用GROUP BY 子句将表中的行分成较小的组。然后，可以使用组函数返回每个组的汇总信息。 • 在所示的SELECT 语句中，根据“部门标识”对行进行分组，然后将AVG 函数自动应用于每个组。 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

  28. 分组（聚集）函数 27-9 • GROUP BY • 如果想知道每个部门中雇员的最高薪金，该怎样做？可使用GROUP BY 子句指定依据哪一列对行进行分组？ SELECT MAX(salary) FROM employees GROUP BY department_id; • 但如何区分哪个最高薪金属于哪个部门呢？

  29. 分组（聚集）函数 27-10 • GROUP BY • 通常，我们采用的方法是在SELECT 列表中包含GROUP BY 列。 SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

  30. 分组（聚集）函数 27-11 • 组函数要求，在SELECT 子句中所列出的任何不属于组函数组成部分的列，都必须在GROUP BY 子句中列出。 • 此示例中有什么错误？ SELECT job_id, last_name, AVG(salary) FROM employees GROUP BY job_id;

  31. 分组（聚集）函数 27-12 • 还可使用WHERE 子句排除一些行，然后再将剩余行分组。 SELECT department_id, MAX(salary) FROM employees WHERE last_name <> ‘King’ GROUP BY department_id;

  32. 分组（聚集）函数 27-13 • GROUP BY 原则 • 使用GROUP BY 子句时，应遵循的重要原则有： • 如果SELECT 子句中不仅包含组函数（AVG、SUM、COUNT、MAX、MIN、STDDEV、VARIANCE），还包含其它个体列，则这些个体列还必须出现在GROUP BY 子句中。 • 不能在GROUP BY 子句中使用列别名。 • WHERE 子句可在对行进行分组之前排除某些行。

  33. 分组（聚集）函数 27-14 • 组内组 • 有时需要将组分为更小的组。例如，您可能希望根据部门对所有雇员进行分组；然后，在每个部门中，按职务进行分组。 • 本示例说明每个部门中从事每项工作的雇员各有多少。 SELECT department_id, job_id, count(*) FROM employees WHERE department_id > 40 GROUP BY department_id, job_id;

  34. 分组（聚集）函数 27-15 • 嵌套组函数 • 使用GROUP BY 时，可以嵌套两层组函数。 SELECT max(avg(salary)) FROM employees GROUP by department_id; • 此查询将返回几个值？答案是一个——此查询将找出每个部门的平均薪金，然后从该列表中选择最大值。

  35. 分组（聚集）函数 27-16 • HAVING • 假设我们想找出每个部门的最高薪金，但仅针对有多个雇员的部门进行查找，那该怎么做？此示例中有什么错误？ SELECT department_id, MAX(salary) FROM employees WHERE COUNT(*) > 1 GROUP BY department_id; 下一张幻灯片将解决此问题。

  36. 分组（聚集）函数 27-17 • 组函数使用要点： • 不能在WHERE 子句中使用组函数：

  37. 分组（聚集）函数 27-18 • HAVING • 与使用WHERE 子句限定所选行一样，您可以使用HAVING 子句来限定组。 • 在使用GROUP BY 和HAVING 子句的查询中，将首先对行进行分组，然后应用组函数，这样将仅显示与HAVING 子句匹配的组。 • WHERE 子句用于限制行；HAVING 子句用于限制由GROUP BY 子句返回的组。 SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING COUNT(*) > 1;

  38. 分组（聚集）函数 27-19 • 在SELECT 语句中，尽管可将HAVING 子句用于GROUP BY 子句之前，但建议您按以下所示的顺序放置每条子句。如果使用ORDER BY 子句，请始终将其放在最后！ SELECT 列, 组函数 FROM 表 WHERE GROUP BY HAVING ORDER BY

  39. 分组（聚集）函数 27-20 • DISTINCT • 关键字DISTINCT 用于仅返回查询中的非重复值或非重复值的组合。 • 请看右边的查询。如果不使用关键字DISTINCT，该查询将返回D_CDS 表中的所有年份值。 SELECT year as ‘CD Year’ FROM d_cds;

  40. 分组（聚集）函数 27-21 SELECT DISTINCT year AS ‘CD Year’ FROM d_cds; • DISTINCT • 要消除重复行，请按此处所示使用DISTINCT 关键字。 • 使用DISTINCT 关键字只返回了所有CD 年份一次，而没有重复值。

  41. 分组（聚集）函数 27-22 • DISTINCT • 关键字DISTINCT 可与所有组函数一起使用。使用DISTINCT 后，函数将仅考虑非重复值。 • 为什么右侧的两个语句生成不同的结果？ SELECT SUM(salary) FROM employees WHERE department_id = 90; SELECT SUM(DISTINCT salary) FROM employees WHERE department_id = 90;

  42. 分组（聚集）函数 27-23 • DISTINCT • 将DISTINCT 与组函数（如COUNT）一起使用时，结果集将返回非重复列值的数量。 • 雇员们一共从事多少种不同的工作？ • 有多少种不同的薪金？ SELECT COUNT (DISTINCT job_id) FROM employees; SELECT COUNT (DISTINCT salary) FROM employees;

  43. 分组（聚集）函数 27-24 • COUNT • COUNT(表达式) 返回表达式列中非空值的数量。 • COUNT(DISTINCT 表达式) 返回表达式列中唯一且非空值的数量。 SELECT COUNT (YEAR) FROM d_cds WHERE year < 2001; SELECT COUNT (DISTINCT year) FROM d_cds WHERE year < 2001;

  44. 分组（聚集）函数 27-25 • COUNT • 第一个查询为什么返回null 值？共列出了六条备注，但count 函数只返回了5。为什么？ • 因为COUNT 忽略列中的null 值。 SELECT comments FROM d_play_list_items; SELECT COUNT(comments) FROM d_play_list_items;

  45. 分组（聚集）函数 27-26 • COUNT • COUNT(*) 返回表中满足SELECT 语句的条件的行数。 • 例如，要了解在表中2001 年之前出品了多少张CD，可在SELECT 语句中使用COUNT。 • 希望计算所有行（包括在一列或多列中有空值的行）时，可使用 COUNT(*)。 SELECT COUNT (*) FROM d_cds WHERE year < 2001;

  46. 分组（聚集）函数 27-27 <返回> • NVL • 有时需要在组函数中包括空值。 • 例如，了解每天顾客所下订单的平均数量，可判断每个月所订的食品数。 • 有几天餐厅未营业没有顾客，但店主发现相比较仅计算有顾客的天数的平均值而言，计算包括每一天的平均值反映的情况要准确。 • 可使用以下语句开始编写包括空值的SELECT 语句 SELECT AVG(NVL(customer_orders, 0))

More Related