人力成本统计分析1

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

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

3、 APEX 图表创建图表创建 参见下面的构建步骤8home back first prev next last 工资水平分析报表工资水平分析报表 创建页，选择创建页，选择 chart 页类型页类型9home back first prev next last 下一步选择下一步选择 Flash Chart 图表类型图表类型 下一步，下一步， page name 输入：输入： salary_analysis 下一步，选择下一步，选择 Do not use tabs 10home back first prev next last 下一步，选择下一步，选择 2D Line Chart Type在本页

4、，选择图表类型后，点击 Update 按钮可预览图表效果11home back first prev next last 下一步输入下一步输入 SQL 查询查询Build Query 按钮可激活查询构造向导，引导你构造查询语句12home back first prev next last 创建并运行页创建并运行页 编辑本页，添加报表区域编辑本页，添加报表区域13home back first prev next last 选择选择 SQL Report14home back first prev next last 输入查询语句，并创建区域，运行页输入查询语句，并创建区域，运行页15home

5、 back first prev next last 工资差异分析报表工资差异分析报表 重复创建图表步骤 选择 2D Column 图表类型 用下面 SQL 语句来创建SELECT null link, department_name label, VARIANCE(SALARY) 工资方差FROM employees e, departments dWHERE e.department_id = d.department_idGROUP BY department_name;16home back first prev next last 人力成本分析报表人力成本分析报表 重复创建图表步骤

6、选择 3D Pie 图表类型 用下面 SQL 语句来创建SELECT null link, department_name label, SUM(SALARY) 工资总额FROM employees e, departments dWHERE e.department_id = d.department_idGROUP BY department_name;17home back first prev next last 根据你所创建的图表，分析现有数据：根据你所创建的图表，分析现有数据： 哪个部门工资水平最高？ 哪个部门工资水平差异最大？ 哪个部门人力成本最高？18home back fir

7、st prev next last 计算平均提成计算平均提成 员工的提成为 salary*COMMISSION_PCT第一种方法：只计算有提成员工的平均提成第二种方法：计算所有员工的平均提成 计算员工总人数计算员工总人数 计算员工的不同工资数计算员工的不同工资数 计算员工的不同工作种类数目计算员工的不同工作种类数目(JOB_ID)19home back first prev next last 掌握分组函数使用方法掌握分组函数使用方法 掌握掌握 APEX 图表使用方法图表使用方法20home back first prev next last在在SQL 中，以下分组函数可对整个表或一组特定的行

8、执行中，以下分组函数可对整个表或一组特定的行执行运算。每个函数返回一个结果。运算。每个函数返回一个结果。 MIN 用于存储任何数据类型的列，返回最小值。 MAX 用于存储任何数据类型的列，返回最大值。 COUNT COUNT(表达式) 返回表达式列中非空值的数量。 COUNT(*) 返回表中满足SELECT 语句的条件的行数。 AVG 用于存储数值数据的列，计算平均值。 SUM 用于存储数值数据的列，计算值的总和。21home back first prev next lastVARIANCE： 用于存储数值数据的列，计算数用于存储数值数据的列，计算数据在平均值左右的分布情况。例据在平均值左右

9、的分布情况。例如，如果班级上次测验的平均分如，如果班级上次测验的平均分是是82%，学生的分数范围在，学生的分数范围在40% 到到100% 之间，则此分数的差异将比之间，则此分数的差异将比学生的分数范围在学生的分数范围在78% 到到88% 的的情况下大。情况下大。STDDEV： 与与variance 类似，标准偏差测量数类似，标准偏差测量数据的分布情况。对于平均值大致据的分布情况。对于平均值大致相同的两组数据，分布越广，标相同的两组数据，分布越广，标准偏差就越大。准偏差就越大。 例如，有四个数字例如，有四个数字: 1、7、4、 8最小值 MIN 是 1最大值 MAX 是 8总和 SUM 是 20

10、 = 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.1622home back first prev next last 组函数使用要点： 组函数会忽略空值。因为任何值和空值进行运算，结果还是空值。忽略空值可确保得到一个运算结果。 在下面的示例中，计算平均加班费率时没有使用(n

11、ull) 值。23home back first prev next last 组函数使用要点： SELECT 子句中可有多个基于相同或不同列的组函数。 还可使用WHERE 子句，将组函数限制在表的子集上。SELECT MAX(salary), MIN(salary), MIN(employee_id)FROM employeesWHERE department_id = 60;24home back first prev next last 组函数使用要点： MIN 和和MAX 两个组函数可用于任何数据类型。两个组函数可用于任何数据类型。 使用这些函数，可以找出列表中最后一个人的使用这些函数

12、，可以找出列表中最后一个人的姓名、最低薪金或最早的聘用日期。姓名、最低薪金或最早的聘用日期。 例如，很容易在按字母排序的雇员列表中找到例如，很容易在按字母排序的雇员列表中找到姓名列在第一行的雇员。姓名列在第一行的雇员。SELECT MIN(last_name)FROM employees;25home back first prev next last 组函数使用要点： 组函数忽略空值。组函数忽略空值。 MIN 和和MAX 可用于任何数据类型；可用于任何数据类型； SUM、AVG、STDDEV 和和VARIANCE 只能用于数值只能用于数值数据类型。数据类型。26home back first

13、 prev next last组函数使用语法SELECT col1, GROUP_FUNCTION(col2) ,FROM table1,WHERE conditionGROUP BY col1,HAVING group_conditionORDER BY col1,; 如果有如果有 WHERE 子句，先执行子句，先执行 WHERE ，根据其条件排除掉无关记，根据其条件排除掉无关记录录 如果有如果有 GROUP 子句，则先按其指定列的值将剩余记录分组；否则子句，则先按其指定列的值将剩余记录分组；否则所有记录被看作一组所有记录被看作一组 对每组记录计算分组函数对每组记录计算分组函数 如果有如果有

14、 HAVING 子句，则根据其中条件选择要显示哪些分组的结果子句，则根据其中条件选择要显示哪些分组的结果 执行执行 SELECT 子句，返回相应列子句，返回相应列 执行执行 ORDER BY子句，对返回结果进行排序子句，对返回结果进行排序27home back first prev next last GROUP BY 可以使用可以使用GROUP BY 子子句将表中的行分成较小句将表中的行分成较小的组。然后，可以使用的组。然后，可以使用组函数返回每个组的汇组函数返回每个组的汇总信息。总信息。 在所示的在所示的SELECT 语句语句中，根据中，根据“部门标识部门标识”对行进行分组，然后将对行进行

15、分组，然后将AVG 函数自动应用于每函数自动应用于每个组。个组。SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id;28home back first prev next last GROUP BY 如果想知道每个部门中雇员的最高薪金，该怎样做？可使用GROUP BY 子句指定依据哪一列对行进行分组？SELECT MAX(salary)FROM employeesGROUP BY department_id; 但如何区分哪个最高薪金属于哪个部门呢？ 29home back first prev next l

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

17、t prev next last 还可使用WHERE 子句排除一些行，然后再将剩余行分组。SELECT department_id, MAX(salary)FROM employeesWHERE last_name KingGROUP BY department_id;32home back first prev next last GROUP BY 原则 使用GROUP BY 子句时，应遵循的重要原则有：如果SELECT 子句中不仅包含组函数（AVG、SUM、COUNT、MAX、MIN、STDDEV、VARIANCE），还包含其它个体列，则这些个体列还必须出现在GROUP BY 子句中。不能

18、在GROUP BY 子句中使用列别名。WHERE 子句可在对行进行分组之前排除某些行。33home back first prev next last 组内组 有时需要将组分为更小的组。例如，您可能希望根据部门对所有雇员进行分组；然后，在每个部门中，按职务进行分组。 本示例说明每个部门中从事每项工作的雇员各有多少。 SELECT department_id, job_id, count(*)FROM employeesWHERE department_id 40GROUP BY department_id, job_id;34home back first prev next last 嵌套组

19、函数 使用GROUP BY 时，可以嵌套两层组函数。SELECT max(avg(salary)FROM employeesGROUP by department_id; 此查询将返回几个值？答案是一个此查询将找出每个部门的平均薪金，然后从该列表中选择最大值。35home back first prev next last HAVING 假设我们想找出每个部门的最高薪金，但仅针对有多个雇员的部门进行查找，那该怎么做？此示例中有什么错误？SELECT department_id, MAX(salary)FROM employeesWHERE COUNT(*) 1GROUP BY departme

20、nt_id;下一张幻灯片将解决此问题。36home back first prev next last 组函数使用要点： 不能在不能在WHERE 子句中使用组函数：子句中使用组函数：37home back first prev next last HAVING 与使用WHERE 子句限定所选行一样，您可以使用HAVING 子句来限定组。 在使用GROUP BY 和HAVING 子句的查询中，将首先对行进行分组，然后应用组函数，这样将仅显示与HAVING 子句匹配的组。 WHERE 子句用于限制行；HAVING 子句用于限制由GROUP BY 子句返回的组。 SELECT department_

21、id, MAX(salary)FROM employeesGROUP BY department_idHAVING COUNT(*) 1;38home back first prev next last 在SELECT 语句中，尽管可将HAVING 子句用于GROUP BY 子句之前，但建议您按以下所示的顺序放置每条子句。如果使用ORDER BY 子句，请始终将其放在最后！SELECT 列, 组函数FROM 表WHERE GROUP BY HAVING ORDER BY39home back first prev next last DISTINCT 关键字DISTINCT 用于仅返回查询中的

22、非重复值或非重复值的组合。 请看右边的查询。如果不使用关键字DISTINCT，该查询将返回D_CDS 表中的所有年份值。SELECT year as CD YearFROM d_cds;40home back first prev next last DISTINCT 要消除重复行，请按此处所示使用DISTINCT 关键字。 使用DISTINCT 关键字只返回了所有CD 年份一次，而没有重复值。SELECT DISTINCT year AS CD YearFROM d_cds;41home back first prev next last DISTINCT 关键字DISTINCT 可与所有组

23、函数一起使用。使用DISTINCT 后，函数将仅考虑非重复值。 为什么右侧的两个语句生成不同的结果？SELECT SUM(salary)FROM employeesWHERE department_id = 90;SELECT SUM(DISTINCT salary)FROM employeesWHERE department_id = 90;42home back first prev next last DISTINCT 将DISTINCT 与组函数（如COUNT）一起使用时，结果集将返回非重复列值的数量。 雇员们一共从事多少种不同的工作？ 有多少种不同的薪金？SELECT COUNT (DISTINCT job_id)FROM employees;SELECT COUNT (DISTINCT salary)FROM employees;43home back first prev next last COUNT COUNT(表达式) 返回表达式列中非空值的数量。 COUNT(DISTINCT 表达式) 返回表达式列中唯一且非空值的数量。