人力成本统计分析.pptx

《人力成本统计分析.pptx》由会员分享，可在线阅读，更多相关《人力成本统计分析.pptx（46页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

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

2、me back first prev next last任务背景任务背景 2-2项目经理告诉你项目经理告诉你通过你开发的报表、图表，人力资源部经理可容易的了解：哪些部门工资水平高？哪些部门工资水平低？哪些部门工资差距比较大？哪些部门工资成本最高？参考下面的报表设计，有助于你完成任务4home back first prev next last报表设计报表设计 3-1工资水平工资水平分析报表分析报表5home back first prev next last报表设计报表设计 3-2工资差异分析报表工资差异分析报表6home back first prev next last报表设计报表设计 3

3、-3人力成本分析报表人力成本分析报表7home back first prev next last关键技术关键技术分组（聚集）函数分组（聚集）函数分组函数介绍 APEX 图表创建图表创建参见下面的构建步骤8home back first prev next last报表构建报表构建 9-1工资水平分析报表工资水平分析报表创建页，选择创建页，选择 chart 页类型页类型9home back first prev next last报表构建报表构建 9-2下一步选择下一步选择 Flash Chart 图表类型图表类型下一步，下一步，page name 输入：输入：salary_analysis下

4、一步，选择下一步，选择 Do not use tabs 10home back first prev next last报表构建报表构建 9-3下一步，选择下一步，选择 2D Line Chart Type在本页，选择图表类型后，点击 Update 按钮可预览图表效果11home back first prev next last报表构建报表构建 9-4下一步输入下一步输入 SQL 查询查询Build Query 按钮可激活查询构造向导，引导你构造查询语句12home back first prev next last报表构建报表构建 9-5创建并运行页创建并运行页编辑本页，添加报表区域编辑本

5、页，添加报表区域13home back first prev next last报表构建报表构建 9-6选择选择 SQL Report14home back first prev next last报表构建报表构建 9-7输入查询语句，并创建区域，运行页输入查询语句，并创建区域，运行页15home back first prev next last报表构建报表构建 9-8工资差异分析报表工资差异分析报表重复创建图表步骤选择 2D Column 图表类型用下面 SQL 语句来创建SELECT null link,department_name label,VARIANCE(SALARY)工资方差

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

7、tment_idGROUP BY department_name;17home back first prev next last图表测试图表测试根据你所创建的图表，分析现有数据：根据你所创建的图表，分析现有数据：哪个部门工资水平最高？哪个部门工资水平差异最大？哪个部门人力成本最高？18home back first prev next last作业与练习作业与练习计算平均提成计算平均提成员工的提成为 salary*COMMISSION_PCT第一种方法：只计算有提成员工的平均提成第二种方法：计算所有员工的平均提成计算员工总人数计算员工总人数计算员工的不同工资数计算员工的不同工资数计算员工的不

8、同工作种类数目计算员工的不同工作种类数目(JOB_ID)19home back first prev next last总结总结掌握分组函数使用方法掌握分组函数使用方法掌握掌握 APEX 图表使用方法图表使用方法20home back first prev next last分组（聚集）函数分组（聚集）函数 27-1在在SQL 中，以下分组函数可对整个表或一组特定的行执行中，以下分组函数可对整个表或一组特定的行执行运算。每个函数返回一个结果。运算。每个函数返回一个结果。MIN用于存储任何数据类型的列，返回最小值。MAX用于存储任何数据类型的列，返回最大值。COUNTCOUNT(表达式)返回表达

9、式列中非空值的数量。COUNT(*)返回表中满足SELECT 语句的条件的行数。AVG用于存储数值数据的列，计算平均值。SUM用于存储数值数据的列，计算值的总和。21home back first prev next last分组（聚集）函数分组（聚集）函数 27-2VARIANCE：用于存储数值数据的列，计算数用于存储数值数据的列，计算数据在平均值左右的分布情况。例据在平均值左右的分布情况。例如，如果班级上次测验的平均分如，如果班级上次测验的平均分是是82%，学生的分数范围在，学生的分数范围在40%到到100%之间，则此分数的差异将比之间，则此分数的差异将比学生的分数范围在学生的分数范围在7

10、8%到到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

11、)/(COUNT-1)=10标准差 STDDEV 是方差 VARIANCE 的平方根 3.1622home back first prev next last分组（聚集）函数分组（聚集）函数 27-3组函数使用要点：组函数会忽略空值。因为任何值和空值进行运算，结果还是空值。忽略空值可确保得到一个运算结果。在下面的示例中，计算平均加班费率时没有使用(null)值。23home back first prev next last分组（聚集）函数分组（聚集）函数 27-4组函数使用要点：SELECT 子句中可有多个基于相同或不同列的组函数。还可使用WHERE 子句，将组函数限制在表的子集上。SELE

12、CT MAX(salary),MIN(salary),MIN(employee_id)FROM employeesWHERE department_id=60;24home back first prev next last分组（聚集）函数分组（聚集）函数 27-5组函数使用要点：MIN 和和MAX 两个组函数可用于任何数据类型。两个组函数可用于任何数据类型。使用这些函数，可以找出列表中最后一个人的使用这些函数，可以找出列表中最后一个人的姓名、最低薪金或最早的聘用日期。姓名、最低薪金或最早的聘用日期。例如，很容易在按字母排序的雇员列表中找到例如，很容易在按字母排序的雇员列表中找到姓名列在第一行

13、的雇员。姓名列在第一行的雇员。SELECT MIN(last_name)FROM employees;25home back first prev next last分组（聚集）函数分组（聚集）函数 27-6组函数使用要点：组函数忽略空值。组函数忽略空值。MIN 和和MAX 可用于任何数据类型；可用于任何数据类型；SUM、AVG、STDDEV 和和VARIANCE 只能用于数值只能用于数值数据类型。数据类型。26home back first prev next last分组（聚集）函数分组（聚集）函数 27-7组函数使用语法SELECT col1,GROUP_FUNCTION(col2),F

14、ROM table1,WHERE conditionGROUP BY col1,HAVING group_conditionORDER BY col1,;如果有如果有 WHERE 子句，先执行子句，先执行 WHERE，根据其条件排除掉无关记，根据其条件排除掉无关记录录如果有如果有 GROUP 子句，则先按其指定列的值将剩余记录分组；否则子句，则先按其指定列的值将剩余记录分组；否则所有记录被看作一组所有记录被看作一组对每组记录计算分组函数对每组记录计算分组函数如果有如果有 HAVING 子句，则根据其中条件选择要显示哪些分组的结果子句，则根据其中条件选择要显示哪些分组的结果执行执行 SELECT

15、 子句，返回相应列子句，返回相应列执行执行 ORDER BY子句，对返回结果进行排序子句，对返回结果进行排序27home back first prev next last分组（聚集）函数分组（聚集）函数 27-8GROUP BY可以使用可以使用GROUP BY 子子句将表中的行分成较小句将表中的行分成较小的组。然后，可以使用的组。然后，可以使用组函数返回每个组的汇组函数返回每个组的汇总信息。总信息。在所示的在所示的SELECT 语句语句中，根据中，根据“部门标识部门标识”对行进行分组，然后将对行进行分组，然后将AVG 函数自动应用于每函数自动应用于每个组。个组。SELECT departme

16、nt_id,AVG(salary)FROM employeesGROUP BY department_id;28home back first prev next last分组（聚集）函数分组（聚集）函数 27-9GROUP BY 如果想知道每个部门中雇员的最高薪金，该怎样做？可使用GROUP BY 子句指定依据哪一列对行进行分组？SELECT MAX(salary)FROM employeesGROUP BY department_id;但如何区分哪个最高薪金属于哪个部门呢？29home back first prev next last分组（聚集）函数分组（聚集）函数 27-10GROUP

17、 BY通常，我们采用的方法是在SELECT 列表中包含GROUP BY 列。SELECT department_id,MAX(salary)FROM employeesGROUP BY department_id;30home back first prev next last分组（聚集）函数分组（聚集）函数 27-11组函数要求，在SELECT 子句中所列出的任何不属于组函数组成部分的列，都必须在GROUP BY 子句中列出。此示例中有什么错误？SELECT job_id,last_name,AVG(salary)FROM employeesGROUP BY job_id;31home ba

18、ck first prev next last分组（聚集）函数分组（聚集）函数 27-12还可使用WHERE 子句排除一些行，然后再将剩余行分组。SELECT department_id,MAX(salary)FROM employeesWHERE last_name KingGROUP BY department_id;32home back first prev next last分组（聚集）函数分组（聚集）函数 27-13GROUP BY 原则使用GROUP BY 子句时，应遵循的重要原则有：如果SELECT 子句中不仅包含组函数（AVG、SUM、COUNT、MAX、MIN、STDDEV

19、、VARIANCE），还包含其它个体列，则这些个体列还必须出现在GROUP BY 子句中。不能在GROUP BY 子句中使用列别名。WHERE 子句可在对行进行分组之前排除某些行。33home back first prev next last分组（聚集）函数分组（聚集）函数 27-14组内组有时需要将组分为更小的组。例如，您可能希望根据部门对所有雇员进行分组；然后，在每个部门中，按职务进行分组。本示例说明每个部门中从事每项工作的雇员各有多少。SELECT department_id,job_id,count(*)FROM employeesWHERE department_id 40GROU

20、P BY department_id,job_id;34home back first prev next last分组（聚集）函数分组（聚集）函数 27-15嵌套组函数使用GROUP BY 时，可以嵌套两层组函数。SELECT max(avg(salary)FROM employeesGROUP by department_id;此查询将返回几个值？答案是一个此查询将找出每个部门的平均薪金，然后从该列表中选择最大值。35home back first prev next last分组（聚集）函数分组（聚集）函数 27-16HAVING 假设我们想找出每个部门的最高薪金，但仅针对有多个雇员的部

21、门进行查找，那该怎么做？此示例中有什么错误？SELECT department_id,MAX(salary)FROM employeesWHERE COUNT(*)1GROUP BY department_id;下一张幻灯片将解决此问题。36home back first prev next last分组（聚集）函数分组（聚集）函数 27-17组函数使用要点：不能在不能在WHERE 子句中使用组函数：子句中使用组函数：37home back first prev next last分组（聚集）函数分组（聚集）函数 27-18HAVING与使用WHERE 子句限定所选行一样，您可以使用HAVIN

22、G 子句来限定组。在使用GROUP BY 和HAVING 子句的查询中，将首先对行进行分组，然后应用组函数，这样将仅显示与HAVING 子句匹配的组。WHERE 子句用于限制行；HAVING 子句用于限制由GROUP BY 子句返回的组。SELECT department_id,MAX(salary)FROM employeesGROUP BY department_idHAVING COUNT(*)1;38home back first prev next last分组（聚集）函数分组（聚集）函数 27-19在SELECT 语句中，尽管可将HAVING 子句用于GROUP BY 子句之前，但

23、建议您按以下所示的顺序放置每条子句。如果使用ORDER BY 子句，请始终将其放在最后！SELECT 列,组函数FROM 表WHERE GROUP BY HAVING ORDER BY39home back first prev next last分组（聚集）函数分组（聚集）函数 27-20DISTINCT 关键字DISTINCT 用于仅返回查询中的非重复值或非重复值的组合。请看右边的查询。如果不使用关键字DISTINCT，该查询将返回D_CDS 表中的所有年份值。SELECT year as CD YearFROM d_cds;40home back first prev next last

24、分组（聚集）函数分组（聚集）函数 27-21DISTINCT要消除重复行，请按此处所示使用DISTINCT 关键字。使用DISTINCT 关键字只返回了所有CD 年份一次，而没有重复值。SELECT DISTINCT year AS CD YearFROM d_cds;41home back first prev next last分组（聚集）函数分组（聚集）函数 27-22DISTINCT关键字DISTINCT 可与所有组函数一起使用。使用DISTINCT 后，函数将仅考虑非重复值。为什么右侧的两个语句生成不同的结果？SELECT SUM(salary)FROM employeesWHERE

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

26、;43home back first prev next last分组（聚集）函数分组（聚集）函数 27-24COUNTCOUNT(表达式)返回表达式列中非空值的数量。COUNT(DISTINCT 表达式)返回表达式列中唯一且非空值的数量。SELECT COUNT(YEAR)FROM d_cdsWHERE year 2001;SELECT COUNT(DISTINCT year)FROM d_cdsWHERE year 2001;44home back first prev next last分组（聚集）函数分组（聚集）函数 27-25COUNT第一个查询为什么返回null 值？共列出了六条备

27、注，但count 函数只返回了5。为什么？因为COUNT 忽略列中的null 值。SELECT commentsFROM d_play_list_items;SELECT COUNT(comments)FROM d_play_list_items;45home back first prev next last分组（聚集）函数分组（聚集）函数 27-26COUNTCOUNT(*)返回表中满足返回表中满足SELECT 语句的条件的语句的条件的行数。行数。例如，要了解在表中例如，要了解在表中2001 年之前出品了多少年之前出品了多少张张CD，可在，可在SELECT 语语句中使用句中使用COUNT。

28、希望计算所有行（包括希望计算所有行（包括在一列或多列中有空值在一列或多列中有空值的行）时，可使用的行）时，可使用 COUNT(*)。SELECT COUNT(*)FROM d_cdsWHERE year 2001;46home back first prev next last分组（聚集）函数分组（聚集）函数 27-27 NVL有时需要在组函数中包括空值。例如，了解每天顾客所下订单的平均数量，可判断每个月所订的食品数。有几天餐厅未营业没有顾客，但店主发现相比较仅计算有顾客的天数的平均值而言，计算包括每一天的平均值反映的情况要准确。可使用以下语句开始编写包括空值的SELECT 语句SELECT AVG(NVL(customer_orders,0)