SQL学习之函数，基于Oracle下的HR用户（三）

五、 组函数(聚合函数)
1 组函数介绍
1.1 什么是组函数
组函数操作行集，给出每组的结果。组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。
1.2 组函数与单行函数区别
单行函数对查询到每个结果集做处理，而组函数只对分组数据做处理。
单行函数对每个结果集返回一个结果，而组函数对每个分组返回一个结果。
1.3 组函数的类型

• AVG 平均值
• COUNT 计数
• MAX 最大值
• MIN 最小值
• SUM 合计

1.4 组函数的语法

1.5 使用组函数的原则
• 用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
• 所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。
2 组函数的使用

2.1 使用 AVG 和 SUM 函数
AVG(arg)函数：`对分组数据做平均值运算。`
arg:参数类型只能是数字类型。
SUM(arg)函数：对分组数据求和。
arg:参数类型只能是数字类型。

2.1.1 示例
求雇员表中的的平均薪水与薪水总额。

select avg(salary) ,sum(salary) from employees;

2.2 使用 MIN 和 MAX 函数

MIN(arg)函数：求分组中最小数据。
arg:参数类型可以是字符、数字、日期。
MAX(arg)函数：求分组中最大数据。
arg:参数类型可以是字符、数字、日期。

2.2.1 示例
求雇员表中的最高薪水与最低薪水。

select min(salary),max(salary) from employees;

2.3 使用 COUNT 函数

COUNT 函数：返回一个表中的行数。

COUNT 函数有三种格式：

• COUNT(*) 
• COUNT(expr)
• COUNT(DISTINCT expr)

2.3.1COUNT()
返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果WHERE 子句包括在 SELECT 语句中，COUNT() 返回满足 WHERE 子句条件的行数。
2.3.1.1 示例一

--返回查询结果的总条数。
select count(*) from employees;

2.3.2COUNT(expr)函数
返回在列中的由 expr 指定的非空值的数。
2.3.2.1 示例二

--显示部门 80 中有佣金的雇员人数。
select count(commission_pct) from employees e where e.department_id = 80;

2.3.3COUNT(DISTINCT expr)：
使用 DISTINCT 关键字禁止计算在一列中的重复值。
2.3.3.1 示例三

--显示 EMPLOYEES 表中不重复的部门数。
select count(distinct department_id) from employees ;

2.4 组函数和 Null 值
所有组函数忽略列中的空值。
在组函数中使用 NVL 函数来处理空值。
2.4.1 示例一

--计算有佣金的员工的佣金平均值。
select avg(commission_pct) from employees;

2.4.2 示例二

--计算所有员工的佣金的平均值。
select avg(nvl(commission_pct,0)) from employees;

3 创建数据组(GROUP BY)
3.1 什么是创建数据组
可以根据需要将查询到的结果集信息划分为较小的组，用 GROUP BY 子句实现。
3.2GROUP BY 子句语法

GROUP BY 子句：GROUP BY 子句可以把表中的行划分为组。然后可以用组函数返回每一组的摘要信息。
3.3 使用分组原则

• 如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
• 使用 WHERE 子句，你可以在划分行成组以前过滤行。
• 在 GROUP BY 子句中必须包含列。
• 在 GROUP BY 子句中你不能用列别名。
• 默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。可以用 ORDER BY 子句覆盖这个默认值。

3.4GROUP BY 子句的使用
我们可以根据自己的需要对数据进行分组，在分组时，只要将需要做分组的列的列名添加到 GROUP BY 子句后侧就可以。GROUP BY 列不必在 SELECT 列表中。
3.4.1 示例一

--求每个部门的平均薪水。
select department_id , avg(salary) from
employees e group by e.department_id;

3.5 多于一个列的分组
3.5.1 示例一

--显示在每个部门中付给每个工作岗位的合计薪水的报告。
select department_id,job_id,
sum(salary)from employees group by
department_id,job_id order by department_id;

3.6GROUP BY 子句的执行顺序
先进行数据查询，在对数据进行分组，然后执行组函数。
3.7 非法使用 Group 函数的查询
• 在 SELECT 列表中的任何列必须在 GROUP BY 子句中。
• 在 GROUP BY 子句中的列或表达式不必在 SELECT 列表中。
3.8 约束分组结果
3.8.1 什么是 HAVING 子句
HAVING 语句通常与 GROUP BY 语句联合使用，用来过滤由 GROUP BY 语句返回的记录集。
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
3.8.2HAVING 子句语法
3.8.3 示例一

--显示那些最高薪水大于 $10,000 的部门的部门号和最高薪水。
select e.department_id,max(e.salary)
from employees e group by e.department_id
having max(e.salary) > 10000;

3.8.4 示例二

--查询那些最高薪水大于 $10,000 的部门的部门号和平均薪水。
select e.department_id,avg(e.salary)
from employees e group by e.department_id
having max(e.salary) > 10000;

3.9 嵌套组函数
在使用组函数时我们也可以根据需要来做组函数的嵌套使用。
3.9.1 示例

--显示部门中的最大平均薪水。
select max(avg(e.salary)) from
employees e group by e.department_id;

4 组函数小节练习
4.1 组函数在多行上计算，对每个组产生一个结果。True/False
True
4.2 组函数在计算中包含空值。True/False
False
组函数会忽略空值，如果需要空值参与计算，需要使用 nvl 函数处理空值。
4.3 在分组计算中，WHERE 子句对行的限制在计算的前面。 True/False
True
4.4 显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：
Maximum、Minimum、Sum 和 Average。四舍五入结果
为最近的整数。

select max(salary),min(salary),sum(salary),avg(salary) from employees;

4.5 修改上题显示每中工作类型的最低、最高、合计和平均薪
水。

select max(salary),min(salary),sum(salary),avg(salary) from employees group by job_id;

4.6 写一个查询显示每一工作岗位的人数。

select job_id, count(*) from employees group by job_id;

4.7 确定经理人数，不需要列出他们，列标签是 Number of Managers。

select count(distinct manager_id) from employees ;

4.8 写一个查询显示最高和最低薪水之间的差。列标签是DIFFERENCE。

select max(salary)-min(salary) from employees;

4.9 显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000的组。按薪水降序排序输出。

select e.manager_id ,min(e.salary) from employees e
where e.manager_id is not null group by e.manager_id having min(e.salary) > 6000 order by min(e.salary) desc;

4.10 写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

select d.department_name,d.location_id,count(*) ,avg(e.salary) from employees e ,departments d where
e.department_id = d.department_id group by d.department_name ,d.location_id;

4.11 创建一个查询显示雇员总数，和在 2001、2002、2003 和受雇的雇员人数。创建适当的列标题。

select count(*) total,
sum(decode(to_char(hire_date,'yyyy'),'2000',1,0))"2000",
sum(decode(to_char(hire_date,'yyyy'),'2001',1,0))"2001",
sum(decode(to_char(hire_date,'yyyy'),'2002',1,0))"2002",
sum(decode(to_char(hire_date,'yyyy'),'2003',1,0))"2003" from employees e;

4.12 创建一个混合查询显示工作岗位和工作岗位的薪水合计，并且合计部门 20、50、80 和90 的工作岗位的薪水。给每列一个恰当的列标题。

select job_id,sum(salary),
sum(decode(department_id,20,salary))"Dep 20",
sum(decode(department_id,50,salary))"Dep 50",
sum(decode(department_id,80,salary))"Dep 80",
sum(decode(department_id,90,salary))"Dep 90"
from employees group by job_id;