人力成本统计分析

单击此处编辑母版标题样式,第一级,第二级,第三级,第四级,*,home back,first prev,next last,人力成本统计分析任务,人事管理,任务背景,2-1,你们项目组现在负责开发一个人事管理系统，你负责人力成本统计分析报表生成任务。,项目经理告诉你：,报表涉及的两个数据表,DEPARTMENTS,、,EMPLOYEES,已经设计好，可通过,dept_emp.txt,脚本创建,报表应提供如下信息：,各部门人数，工资总额，平均工资，最高工资，最低工资，工资差距指数（标准方差）,除报表外，应采取饼图、柱状图、曲线图等直观方式来显示统计结果,任务背景,2-2,项目经理告诉你,通过你开发的报表、图表，人力资源部经理可容易的了解：,哪些部门工资水平高？,哪些部门工资水平低？,哪些部门工资差距比较大？,哪些部门工资成本最高？,参考下面的报表设计，有助于你完成任务,报表设计,3-1,工资水平分析报表,报表设计,3-2,工资差异分析报表,报表设计,3-3,人力成本分析报表,关键技术,分组（聚集）函数,分组函数介绍,APEX,图表创建,参见下面的,构建步骤,报表构建,9-1,工资水平分析报表,创建页，选择,chart,页类型,报表构建,9-2,下一步选择,Flash Chart,图表类型,下一步，,page name,输入：,salary_analysis,下一步，选择,Do not use tabs,报表构建,9-3,下一步，选择,2D Line Chart Type,在本页，选择图表类型后,，点击,Update,按钮可预览图表效果,报表构建,9-4,下一步输入,SQL,查询,Build Query,按钮可激活查询构造向导，引导你构造查询语句,报表构建,9-5,创建并运行页,编辑本页，添加报表区域,报表构建,9-6,选择,SQL Report,报表构建,9-7,输入查询语句，并创建区域，运行页,报表构建,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;,报表构建,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;,图表测试,根据你所创建的图表，分析现有数据：,哪个部门工资水平最高？,哪个部门工资水平差异最大？,哪个部门人力成本最高？,作业与练习,计算平均提成,员工的提成为,salary*COMMISSION_PCT,第一种方法：只计算有提成员工的平均提成,第二种方法：计算所有员工的平均提成,计算员工总人数,计算员工的不同工资数,计算员工的不同工作种类数目,(JOB_ID),总结,掌握分组函数使用方法,掌握,APEX,图表使用方法,分组（聚集）函数,27-1,在,SQL,中，以下分组函数可对整个表或一组特定的行执行运算。每个函数返回一个结果。,MIN,用于存储任何数据类型的列，返回最小值。,MAX,用于存储任何数据类型的列，返回最大值。,COUNT,COUNT(表达式)返回表达式列中非空值的数量。,COUNT(*)返回表中满足SELECT 语句的条件的行数。,AVG,用于存储数值数据的列，计算平均值。,SUM,用于存储数值数据的列，计算值的总和。,分组（聚集）函数,27-2,VARIANCE,：,用于存储数值数据的列，计算数据在平均值左右的分布情况。例如，如果班级上次测验的平均分是,82%,，学生的分数范围在,40%,到,100%,之间，则此分数的差异将比学生的分数范围在,78%,到,88%,的情况下大。,STDDEV,：,与,variance,类似，标准偏差测量数据的分布情况。对于平均值大致相同的两组数据，分布越广，标准偏差就越大。,例如，有四个数字,: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,分组（聚集）函数,27-3,组函数使用要点：,组函数会忽略空值。因为任何值和空值进行运算，结果还是空值。忽略空值可确保得到一个运算结果。,在下面的示例中，计算平均加班费率时没有使用,(null),值。,分组（聚集）函数,27-4,组函数使用要点：,SELECT,子句中可有多个基于相同或不同列的组函数。,还可使用,WHERE,子句，将组函数限制在表的子集上。,SELECT MAX(salary),MIN(salary),MIN(employee_id),FROM employees,WHERE department_id=60;,分组（聚集）函数,27-5,组函数使用要点：,MIN,和,MAX,两个组函数可用于任何数据类型。,使用这些函数，可以找出列表中最后一个人的姓名、最低薪金或最早的聘用日期。,例如，很容易在按字母排序的雇员列表中找到姓名列在第一行的雇员。,SELECT MIN(last_name),FROM employees;,分组（聚集）函数,27-6,组函数使用要点：,组函数忽略空值。,MIN,和,MAX,可用于任何数据类型；,SUM,、,AVG,、,STDDEV,和,VARIANCE,只能用于数值数据类型。,分组（聚集）函数,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-8,GROUP BY,可以使用,GROUP BY,子句将表中的行分成较小的组。然后，可以使用组函数返回每个组的汇总信息。,在所示的,SELECT,语句中，根据“部门标识”对行进行分组，然后将,AVG,函数自动应用于每个组。,SELECT department_id,AVG(salary),FROM employees,GROUP BY department_id;,分组（聚集）函数,27-9,GROUP BY,如果想知道每个部门中雇员的最高薪金，该怎样做？可使用,GROUP BY,子句指定依据哪一列对行进行分组？,SELECT MAX(salary),FROM employees,GROUP BY department_id;,但如何区分哪个最高薪金属于哪个部门呢？,分组（聚集）函数,27-10,GROUP BY,通常，我们采用的方法是在,SELECT,列表中包含,GROUP BY,列。,SELECT department_id,MAX(salary),FROM employees,GROUP BY department_id;,分组（聚集）函数,27-11,组函数要求，在,SELECT,子句中所列出的任何不属于组函数组成部分的列，都必须在,GROUP BY,子句中列出。,此示例中有什么错误？,SELECT job_id,last_name,AVG(salary),FROM employees,GROUP BY job_id;,分组（聚集）函数,27-12,还可使用,WHERE,子句排除一些行，然后再将剩余行分组。,SELECT department_id,MAX(salary),FROM employees,WHERE last_name King,GROUP BY department_id;,分组（聚集）函数,27-13,GROUP BY,原则,使用,GROUP BY,子句时，应遵循的重要原则有：,如果,SELECT,子句中不仅包含组函数（,AVG,、,SUM,、,COUNT,、,MAX,、,MIN,、,STDDEV,、,VARIANCE,），还包含其它个体列，则这些个体列还必须出现在,GROUP BY,子句中。,不能在,GROUP BY,子句中使用列别名。,WHERE,子句可在对行进行分组之前排除某些行。,分组（聚集）函数,27-14,组内组,有时需要将组分为更小的组。例如，您可能希望根据部门对所有雇员进行分组；然后，在每个部门中，按职务进行分组。,本示例说明每个部门中从事每项工作的雇员各有多少。,SELECT department_id,job_id,count(*),FROM employees,WHERE department_id 40,GROUP BY department_id,job_id;,分组（聚集）函数,27-15,嵌套组函数,使用,GROUP BY,时，可以嵌套两层组函数。,SELECT max(avg(salary),FROM employees,GROUP by department_id;,此查询将返回几个值？答案是一个,此查询将找出每个部门的平均薪金，然后从该列表中选择最大值。,分组（聚集）函数,27-16,HAVING,假设我们想找出每个部门的最高薪金，但仅针对有多个雇员的部门进行查找，那该怎么做？此示例中有什么错误？,SELECT department_id,MAX(salary),FROM employees,WHERE COUNT(*)1,GROUP BY department_id;,下一张幻灯片将解决此问题。,分组（聚集）函数,27-17,组函数使用要点：,不能在,WHERE,子句中使用组函数：,分组（聚集）函数,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;,分组（聚集）函数,27-19,在,SELECT,语句中，尽管可将,HAVING,子句用于,GROUP BY,子句之前，但建议您按以下所示的顺序放置每条子句。如果使用,ORDER BY,子句，请始终将其放在最后！,SELECT,列,组函数,FROM,表,WHERE,GROUP BY,HAVING,ORDER BY,分组（聚集）函数,27-20,DISTINCT,关键字,DISTINCT,用于仅返回查询中的非重复值或非重复值的组合。,请看右边的查询。如果不使用关键字,DISTINCT,，该查询将返回,D_CDS,表中