人力成本统计分析

人力成本统计分析任务人事管理2homebackfirstprevnextlast任务背景2-1•你们项目组现在负责开发一个人事管理系统，你负责人力成本统计分析报表生成任务。•项目经理告诉你：–报表涉及的两个数据表DEPARTMENTS、EMPLOYEES已经设计好，可通过dept_emp.txt脚本创建–报表应提供如下信息：各部门人数，工资总额，平均工资，最高工资，最低工资，工资差距指数（标准方差）–除报表外，应采取饼图、柱状图、曲线图等直观方式来显示统计结果3homebackfirstprevnextlast任务背景2-2•项目经理告诉你–通过你开发的报表、图表，人力资源部经理可容易的了解：哪些部门工资水平高？哪些部门工资水平低？哪些部门工资差距比较大？哪些部门工资成本最高？–参考下面的报表设计，有助于你完成任务4homebackfirstprevnextlast报表设计3-1•工资水平分析报表5homebackfirstprevnextlast报表设计3-2•工资差异分析报表6homebackfirstprevnextlast报表设计3-3•人力成本分析报表7homebackfirstprevnextlast关键技术•分组（聚集）函数–分组函数介绍•APEX图表创建–参见下面的构建步骤8homebackfirstprevnextlast报表构建9-1•工资水平分析报表•创建页，选择chart页类型9homebackfirstprevnextlast报表构建9-2•下一步选择FlashChart图表类型•下一步，pagename输入：salary_analysis•下一步，选择Donotusetabs10homebackfirstprevnextlast报表构建9-3•下一步，选择2DLineChartType在本页，选择图表类型后·，点击Update按钮可预览图表效果11homebackfirstprevnextlast报表构建9-4•下一步输入SQL查询BuildQuery按钮可激活查询构造向导，引导你构造查询语句12homebackfirstprevnextlast报表构建9-5•创建并运行页•编辑本页，添加报表区域13homebackfirstprevnextlast报表构建9-6•选择SQLReport14homebackfirstprevnextlast报表构建9-7•输入查询语句，并创建区域，运行页15homebackfirstprevnextlast报表构建9-8•工资差异分析报表–重复创建图表步骤–选择2DColumn图表类型–用下面SQL语句来创建SELECTnulllink,department_namelabel,VARIANCE(SALARY)工资方差FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYdepartment_name;16homebackfirstprevnextlast报表构建9-9•人力成本分析报表–重复创建图表步骤–选择3DPie图表类型–用下面SQL语句来创建SELECTnulllink,department_namelabel,SUM(SALARY)工资总额FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYdepartment_name;17homebackfirstprevnextlast图表测试•根据你所创建的图表，分析现有数据：–哪个部门工资水平最高？–哪个部门工资水平差异最大？–哪个部门人力成本最高？18homebackfirstprevnextlast作业与练习•计算平均提成–员工的提成为salary*COMMISSION_PCT第一种方法：只计算有提成员工的平均提成第二种方法：计算所有员工的平均提成•计算员工总人数•计算员工的不同工资数•计算员工的不同工作种类数目(JOB_ID)19homebackfirstprevnextlast总结•掌握分组函数使用方法•掌握APEX图表使用方法20homebackfirstprevnextlast分组（聚集）函数27-1•在SQL中，以下分组函数可对整个表或一组特定的行执行运算。每个函数返回一个结果。–MIN用于存储任何数据类型的列，返回最小值。–MAX用于存储任何数据类型的列，返回最大值。–COUNTCOUNT(表达式)返回表达式列中非空值的数量。COUNT(*)返回表中满足SELECT语句的条件的行数。–AVG用于存储数值数据的列，计算平均值。–SUM用于存储数值数据的列，计算值的总和。21homebackfirstprevnextlast分组（聚集）函数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.1622homebackfirstprevnextlast分组（聚集）函数27-3•组函数使用要点：–组函数会忽略空值。因为任何值和空值进行运算，结果还是空值。忽略空值可确保得到一个运算结果。–在下面的示例中，计算平均加班费率时没有使用(null)值。23homebackfirstprevnextlast分组（聚集）函数27-4•组函数使用要点：–SELECT子句中可有多个基于相同或不同列的组函数。–还可使用WHERE子句，将组函数限制在表的子集上。SELECTMAX(salary),MIN(salary),MIN(employee_id)FROMemployeesWHEREdepartment_id=60;24homebackfirstprevnextlast分组（聚集）函数27-5•组函数使用要点：–MIN和MAX两个组函数可用于任何数据类型。–使用这些函数，可以找出列表中最后一个人的姓名、最低薪金或最早的聘用日期。–例如，很容易在按字母排序的雇员列表中找到姓名列在第一行的雇员。SELECTMIN(last_name)FROMemployees;25homebackfirstprevnextlast分组（聚集）函数27-6•组函数使用要点：–组函数忽略空值。–MIN和MAX可用于任何数据类型；–SUM、AVG、STDDEV和VARIANCE只能用于数值数据类型。26homebackfirstprevnextlast分组（聚集）函数27-7•组函数使用语法SELECT[col1,]GROUP_FUNCTION(col2)[,…]FROMtable1,…[WHEREcondition][GROUPBYcol1,…][HAVINGgroup_condition][ORDERBYcol1,…];–如果有WHERE子句，先执行WHERE，根据其条件排除掉无关记录–如果有GROUP子句，则先按其指定列的值将剩余记录分组；否则所有记录被看作一组–对每组记录计算分组函数–如果有HAVING子句，则根据其中条件选择要显示哪些分组的结果–执行SELECT子句，返回相应列–执行ORDERBY子句，对返回结果进行排序27homebackfirstprevnextlast分组（聚集）函数27-8•GROUPBY–可以使用GROUPBY子句将表中的行分成较小的组。然后，可以使用组函数返回每个组的汇总信息。–在所示的SELECT语句中，根据“部门标识”对行进行分组，然后将AVG函数自动应用于每个组。SELECTdepartment_id,AVG(salary)FROMemployeesGROUPBYdepartment_id;28homebackfirstprevnextlast分组（聚集）函数27-9•GROUPBY–如果想知道每个部门中雇员的最高薪金，该怎样做？可使用GROUPBY子句指定依据哪一列对行进行分组？SELECTMAX(salary)FROMemployeesGROUPBYdepartment_id;–但如何区分哪个最高薪金属于哪个部门呢？29homebackfirstprevnextlast分组（聚集）函数27-10•GROUPBY–通常，我们采用的方法是在SELECT列表中包含GROUPBY列。SELECTdepartment_id,MAX(salary)FROMemployeesGROUPBYdepartment_id;30homebackfirstprevnextlast分组（聚集）函数27-11•组函数要求，在SELECT子句中所列出的任何不属于组函数组成部分的列，都必须在GROUPBY子句中列出。–此示例中有什么错误？SELECTjob_id,last_name,AVG(salary)FROMemployeesGROUPBYjob_id;31homebackfirstprevnextlast分组（聚集）函数27-12•还可使用WHERE子句排除一些行，然后再将剩余行分组。SELECTdepartment_id,MAX(salary)FROMemployeesWHERElast_name‘King’GROUPBYdepartment_id;32homebackfirstprevnextlast分组（聚集）函数27-13•GROUPBY原则–使用GROUPBY子句时，应遵循的重要原则有：如果SELECT子句中不仅包含组函数（AVG、SUM、COUNT、MAX、MIN、STDDEV、VARIANCE），还包含其它个体列，则这些个体列还必须出现在GROUPBY子句中。不能在GROUPBY子句中使用列别名。WHERE子句可在对行进行分组之前排除某些行。33homebackfirstprevnextlast分组（聚集）函数27-14•组内组–有时需要将组分为更小的组。例如，您可能希望根据部门对所有雇员进行分组；然后，在每个部门中，按职务进行分组。–本示例说明每个部门中从事每项工作的雇员各有多少。SELECTdepartment_id,job_id,count(*)FROMemployeesWHEREdepartment_id40GROUPBYdepartment_id,job_id;34homebackfirstprevnextlast分组（聚集）函数27-15•嵌套组函数–使用GROUPBY时，可以嵌套两层组函数。SELECTmax(avg(salary))FROMemployeesGROUPbydepartment_id;–此查询将返回几个值？答案是一个——此查询将找出每个部门的平均薪金，然后从该列表中选择最大值。35homebackfirstprevnextlast分组（聚集）函数27-16•HAVING–假设我们想找出每个部门的最高薪金，但仅针对有多个雇员的部门进行查找，那该怎么做？此示例中有什么错误？SELECTdepartment_id,MAX(salary)FROMemployeesWHERECOUNT(*)1GROUPBYdepartment_id;下一张幻灯片将解决此问题。36homebackfirstprevnextlast分组（聚集）函数27-17•组函数使用要点：–不能在WHERE子句中使用组函数：37hom