培训课件：人力成本统计分析任务.doc

人力成本统计分析任务人事管理任务背景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 报表构建9-4 下一步输入sql 查询报表构建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 类似，标准偏差测量数据的分布情况。对于平均值大致相同的两组数据，分布越广，标准偏差就越大。分组（聚集）函数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 函数自动应用于每个组。分组（聚集）函数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 kinggroup by department_id; 分组（聚集）函数27-13 group by 原则使用group by 子句时，应遵循的重要原则有：如果select 子句中不仅包含组函数（avg 、sum 、count 、max 、min 、stddev 、variance ），还包含其它个体列，则这些个体列还必须出现在group by 子句中。不能在group by 子句中使用列别名。where 子句可在对行进行分组之前排除某些行。分组（聚集）函数27-14 组内组有时需要将组分为更小的组。例如，您可能希望根据部门对所有雇员进行分组；然后，在每个部门中，按职务进行分组。本示例说明每个部门中从事每项工作的雇员各有多少。分组（聚集）函数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 子句返回的组。分组（聚集）函数27-19 在select 语句中，尽管可将having 子句用于group by 子句之前，但建议您按以下所示的顺序放置每条子句。如果使用order by 子句，请始终将其放在最后！select 列, 组函数from 表where group by having order by 分组（聚集）函数27-20 distinct 关键字distinct 用于仅返回查询中的非重复值或非重复值的组合。请看右边的查询。如果不使用关键字distinct ，该查询将返回d_cds 表中的所有年份值。分组（聚集）函数27-21 distinct 要消除重复行，请按此处所示使用distinct 关键字。使用distinct 关键字只返回了所有cd 年份一次，而没有重复值。分组（聚集）函数27-22 distinct 关键字distinct 可与所有组函数一起使用。使用distinct 后，函数将仅考虑非重复值。为什么右侧的两个语句生成不同的结果？分组（聚集）函数27-23 distinct 将distinct 与组函数（如count ）一起使用时，结果集将返回非重复列值的数量。雇员们一共从事多少种不同的工作？有多少种不同的薪金？分组（聚集）函数27-24 count count( 表达式) 返回表达式列中非空值的数量。count(distinct 表达式) 返回表达式列中唯一且非空值的数量。分组（聚集）函数27-25 count 第一个查询为什么返回null 值？共列出了六条备注，但count 函数只返回了5 。为什么？因为count 忽略列中的null 值。分组（聚集）函数27-26 count count(*) 返回表中满足select 语句的条件的行数。例如，要了解在表中2001 年之前出品了多少张cd ，可在select 语句中使用count 。希望计算所有行（包括在一列或多列中有空值的行）时，可使用count(*) 。分组（聚集）函数27-27 nvl 有时需要在组函数中包括空值。例如，了解每天顾客所下订单的平均数量，可判断每个月所订的食品数。有几天餐厅未营业没有顾客，但店主发现相比较仅计算有顾客的天数的平均值而言，计算包括每一天的平均值反映的情况要准确。可使用以下语句开始编写包括空值的select 语句select avg(nvl(customer_orders, 0) select department_id, job_id, count(*) from employees where department_id 40 group by department_id, job_id; select department_id, max(salary) from employees group by department_id ha