人力成本统计分析PPT课件.ppt

人力成本统计分析任务 人事管理 任务背景2 1 你们项目组现在负责开发一个人事管理系统 你负责人力成本统计分析报表生成任务 项目经理告诉你 报表涉及的两个数据表DEPARTMENTS EMPLOYEES已经设计好 可通过dept emp txt脚本创建报表应提供如下信息 各部门人数 工资总额 平均工资 最高工资 最低工资 工资差距指数 标准方差 除报表外 应采取饼图 柱状图 曲线图等直观方式来显示统计结果 任务背景2 2 项目经理告诉你通过你开发的报表 图表 人力资源部经理可容易的了解 哪些部门工资水平高 哪些部门工资水平低 哪些部门工资差距比较大 哪些部门工资成本最高 参考下面的报表设计 有助于你完成任务 报表设计3 1 工资水平分析报表 报表设计3 2 工资差异分析报表 报表设计3 3 人力成本分析报表 关键技术 分组 聚集 函数分组函数介绍APEX图表创建参见下面的构建步骤 报表构建9 1 工资水平分析报表创建页 选择chart页类型 报表构建9 2 下一步选择FlashChart图表类型下一步 pagename输入 salary analysis下一步 选择Donotusetabs 报表构建9 3 下一步 选择2DLineChartType 在本页 选择图表类型后 点击Update按钮可预览图表效果 报表构建9 4 下一步输入SQL查询 BuildQuery按钮可激活查询构造向导 引导你构造查询语句 报表构建9 5 创建并运行页编辑本页 添加报表区域 报表构建9 6 选择SQLReport 报表构建9 7 输入查询语句 并创建区域 运行页 报表构建9 8 工资差异分析报表重复创建图表步骤选择2DColumn图表类型用下面SQL语句来创建SELECTnulllink department namelabel VARIANCE SALARY 工资方差FROMemployeese departmentsdWHEREe department id d department idGROUPBYdepartment name 报表构建9 9 人力成本分析报表重复创建图表步骤选择3DPie图表类型用下面SQL语句来创建SELECTnulllink department namelabel SUM SALARY 工资总额FROMemployeese departmentsdWHEREe department id d department idGROUPBYdepartment name 图表测试 根据你所创建的图表 分析现有数据 哪个部门工资水平最高 哪个部门工资水平差异最大 哪个部门人力成本最高 作业与练习 计算平均提成员工的提成为salary COMMISSION PCT第一种方法 只计算有提成员工的平均提成第二种方法 计算所有员工的平均提成计算员工总人数计算员工的不同工资数计算员工的不同工作种类数目 JOB ID 总结 掌握分组函数使用方法掌握APEX图表使用方法 分组 聚集 函数27 1 在SQL中 以下分组函数可对整个表或一组特定的行执行运算 每个函数返回一个结果 MIN用于存储任何数据类型的列 返回最小值 MAX用于存储任何数据类型的列 返回最大值 COUNTCOUNT 表达式 返回表达式列中非空值的数量 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子句 将组函数限制在表的子集上 SELECTMAX salary MIN salary MIN employee id FROMemployeesWHEREdepartment id 60 分组 聚集 函数27 5 组函数使用要点 MIN和MAX两个组函数可用于任何数据类型 使用这些函数 可以找出列表中最后一个人的姓名 最低薪金或最早的聘用日期 例如 很容易在按字母排序的雇员列表中找到姓名列在第一行的雇员 SELECTMIN last name FROMemployees 2020 1 27 25 分组 聚集 函数27 6 组函数使用要点 组函数忽略空值 MIN和MAX可用于任何数据类型 SUM AVG STDDEV和VARIANCE只能用于数值数据类型 分组 聚集 函数27 7 组函数使用语法SELECT col1 GROUP FUNCTION col2 FROMtable1 WHEREcondition GROUPBYcol1 HAVINGgroup condition ORDERBYcol1 如果有WHERE子句 先执行WHERE 根据其条件排除掉无关记录如果有GROUP子句 则先按其指定列的值将剩余记录分组 否则所有记录被看作一组对每组记录计算分组函数如果有HAVING子句 则根据其中条件选择要显示哪些分组的结果执行SELECT子句 返回相应列执行ORDERBY子句 对返回结果进行排序 分组 聚集 函数27 8 GROUPBY可以使用GROUPBY子句将表中的行分成较小的组 然后 可以使用组函数返回每个组的汇总信息 在所示的SELECT语句中 根据 部门标识 对行进行分组 然后将AVG函数自动应用于每个组 SELECTdepartment id AVG salary FROMemployeesGROUPBYdepartment id 分组 聚集 函数27 9 GROUPBY如果想知道每个部门中雇员的最高薪金 该怎样做 可使用GROUPBY子句指定依据哪一列对行进行分组 SELECTMAX salary FROMemployeesGROUPBYdepartment id 但如何区分哪个最高薪金属于哪个部门呢 分组 聚集 函数27 10 GROUPBY通常 我们采用的方法是在SELECT列表中包含GROUPBY列 SELECTdepartment id MAX salary FROMemployeesGROUPBYdepartment id 分组 聚集 函数27 11 组函数要求 在SELECT子句中所列出的任何不属于组函数组成部分的列 都必须在GROUPBY子句中列出 此示例中有什么错误 SELECTjob id last name AVG salary FROMemployeesGROUPBYjob id 分组 聚集 函数27 12 还可使用WHERE子句排除一些行 然后再将剩余行分组 SELECTdepartment id MAX salary FROMemployeesWHERElast name King GROUPBYdepartment id 分组 聚集 函数27 13 GROUPBY原则使用GROUPBY子句时 应遵循的重要原则有 如果SELECT子句中不仅包含组函数 AVG SUM COUNT MAX MIN STDDEV VARIANCE 还包含其它个体列 则这些个体列还必须出现在GROUPBY子句中 不能在GROUPBY子句中使用列别名 WHERE子句可在对行进行分组之前排除某些行 分组 聚集 函数27 14 组内组有时需要将组分为更小的组 例如 您可能希望根据部门对所有雇员进行分组 然后 在每个部门中 按职务进行分组 本示例说明每个部门中从事每项工作的雇员各有多少 SELECTdepartment id job id count FROMemployeesWHEREdepartment id 40GROUPBYdepartment id job id 分组 聚集 函数27 15 嵌套组函数使用GROUPBY时 可以嵌套两层组函数 SELECTmax avg salary FROMemployeesGROUPbydepartment id 此查询将返回几个值 答案是一个 此查询将找出每个部门的平均薪金 然后从该列表中选择最大值 分组 聚集 函数27 16 HAVING假设我们想找出每个部门的最高薪金 但仅针对有多个雇员的部门进行查找 那该怎么做 此示例中有什么错误 SELECTdepartment id MAX salary FROMemployeesWHERECOUNT 1GROUPBYdepartment id 下一张幻灯片将解决此问题 分组 聚集 函数27 17 组函数使用要点 不能在WHERE子句中使用组函数 分组 聚集 函数27 18 HAVING与使用WHERE子句限定所选行一样 您可以使用HAVING子句来限定组 在使用GROUPBY和HAVING子句的查询中 将首先对行进行分组 然后应用组函数 这样将仅显示与HAVING子句匹配的组 WHERE子句用于限制行 HAVING子句用于限制由GROUPBY子句返回的组 SELECTdepartment id MAX salary FROMemployeesGROUPBYdepartment idHAVINGCOUNT 1 分组 聚集 函数27 19 在SELECT语句中 尽管可将HAVING子句用于GROUPBY子句之前 但建议您按以下所示的顺序放置每条子句 如果使用ORDERBY子句 请始终将其放在最后 SELECT列 组函数FROM表WHEREGROUPBYHAVINGORDERBY 分组 聚集 函数27 20 DISTINCT关键字DISTINCT用于仅返回查询中的非重复值或非重复值的组合 请看右边的查询 如果不使用关键字DISTINCT 该查询将返回D CDS表中的所有年份值 SELECTyearas CDYear FROMd cds 分组 聚集 函数27 21 DISTINCT要消除重复行 请按此处所示使用DISTINCT关键字 使用DISTINCT关键字只返回了所有CD年份一次 而没有重复值 SELECTDISTINCTyearAS CDYear FROMd cds 分组 聚集 函数27 22 DISTINCT关键字DISTINCT可与所有组函数一起使用 使用DISTINCT后 函数将仅考虑非重复值 为什么右侧的两个语句生成不同的结果 SELECTSUM salary FROMemployeesWHEREdepartment id 90 SELECTSUM DISTINCTsalary FROMemployeesWHEREdepartment id 90 分组 聚集 函数27 23 DISTINCT将DISTINCT与组函数 如COUNT 一起使用时 结果集将返回非重复列值的数量 雇员们一共从事多少种不同的工作 有多少种不同的薪金 SELECTCOUNT DISTINCTjob id FROMemployees SELECTCOUNT DISTINCTsalary FROMemployees 分组 聚集 函数27 24 COUNTCOUNT 表达式 返回表达式列中非空值的数量 COUNT DISTINCT表达式 返回表达式列中唯一且非空值的数量 SELECTCOUNT YEAR FROMd cdsWHEREyear 2001 SELECTCOUNT DISTINCTyear FROMd cdsWHEREyear 2001 分组 聚集 函数27 25 COUNT第一个查询为什么返回null值 共列出了六条备注 但count函数只返回了5 为什么 因为COUNT忽略列中的null值 SELECTcommentsFROMd play list items SELECTCOUNT comments FROMd play list items 分组 聚集 函数27 26 COUNTCOUNT 返回表中满足SELECT语句的条件的行数 例如 要了解在表中2001年之前出品了多少