SQL汇总和分组数据.doc

使用聚合函数进行汇总和分组
SQL提供一组聚合函数，它们能够对整个数据集合进行计算，将一组原始数据转换为有用的信息，以便用户使用。例如求成绩表中的总成绩、学生表中平均年龄等。
SQL的聚合函数如表1所示。
表1 聚合函数
聚合函数
支持的数据类型
功 能 描 述
Sum( )
数字
对指定列中的所有非空值求和
avg( )
数字
对指定列中的所有非空值求平均值
min( )
数字、字符、日期
返回指定列中的最小数字、最小的字符串和最早的日期时间
max( )
数字、字符、日期
返回指定列中的最大数字、最大的字符串和最近的日期时间
count（[distinct] *）
任意基于行的数据类型
统计结果集中全部记录行的数量，最多可达2 147 483 647行
count_big（[distinct] *）
任意基于行的数据类型
类似于count( )函数，但因其返回值使用了bigint数据类型，所以最多可以统计2^63-1行

1．SUM( )函数和AVG( )函数
两个函数都是对列式数字型的进行计算，只不过SUM( )是对列求和；而AVG( )是对列求平均值。
示例：
求“grade”表中每学期的课程成绩的总和。在查询分析器中输入的SQL语句如下：
use student
select sum(课程成绩) as 总成绩
from grade
实现的过程如图1所示。

图1 求课程成绩的总和
当与GROUP BY子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整个表生成一个值。
示例：
在“student”表中，按“性别”分别求年龄的平均值。在查询分析器中输入的SQL语句如下：
use student
select 性别 ,avg(年龄) as 平均年龄
from student group by 性别
实现的过程如图2所示。

图2 男女生的平均年龄
2．MIN( )函数和MAX( )函数
MIN( )和MAX( )函数分别查询列中的最小值和最大值。但列的数据包含数字、字符或日期/时间信息。MIN( )和MAX( )函数结果与列中数据的数据类型完全相同。
示例：
查询“student”表中最早出生的学生。在查询分析器中输入的SQL语句如下：
use student
select min(出生日期) as 最早出生
from Student
实现的过程结果如图3所示。

图3 学生表中年龄最小的学生信息
下面把GROUP BY子句和MAX( )函数结合使用。
示例：
在“student”表中，按“性别”分别求年龄的最大值。在查询分析器中输入的SQL语句如下：
use student
select 性别, max(年龄) as 最大年龄
from Student
group by 性别
实现的过程如图4所示。

图4 男女生中年龄的最大值
3．COUNT( )函数和COUNT_big( )函数
COUNT( )函数和COUNT_big( )函数两个函数都是对列中数据值的数目进行计数。它们返回的值总是一个整数，不管列的数据类型。
示例：
求“student”表中女生的人数。在查询分析器中输入的SQL语句如下：
use student
select count(年龄) as 女生记录总数
from student
where 性别='女'
实现的过程如图5所示。

图5 “Student”表中女生的记录总数
COUNT（*）就可以求整个表所有的记录数。例如，求“student”表中所有的记录数，SQL语句如下：
use student
select count(*) from student
4．消除重复记录（DISTINCT）
指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM( )、AVG( )和COUNT( )聚合函数时，可以从列中消除重复的值。DISTINCT关键字和聚合函数使用的格式是：聚合函数名称（DISTINCT 列名）。
示例：
在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：
use student
select count(学号)
from grade
实现的过程如图6所示。

图6 统计参加考试的学生
从上面的统计结果不难看出，实际上参加考试的学生是学号从B001～B005共5名，其中有重复的学号。这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCT。
示例：
在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：
use student
select count(distinct 学号)
from grade
实现的过程如图7所示。

图7 使用DISTINCT关键字统计参加考试的学生
注意：当使用DISTINCT关键字时，聚合函数的参数必须是一个简单的列名。

筛选分组结果
用GROUP BY可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进行分组，这时就需要使用HAVING子句来筛选分组。
示例：
在“grade”表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。在查询分析器中输入的SQL语句如下：
use student
select 课程代号, sum(课程成绩) as 课程总成绩
from grade
group by 课程代号
having count(*)>=2
实现的过程如图1所示。

图1 至少两个人对加同一门考试的课程成绩总和
1．SQL SELECT语句的执行顺序
下面给出SQL SELECT语句的执行顺序。
（1）FROM子句组装来自不同数据源的数据。
（2）WHERE子句基于指定的条件对记录行进行筛选。
（3）GROUP BY子句将数据划分为多个分组。
（4）使用聚集函数进行计算。
（5）使用HAVING子句筛选分组。
（6）计算所有的表达式。
（7）使用ORDER BY对结果集进行排序。
示例：
在“grade”表中，把“学号”内容不为空的记录按照“学号”分组，并且筛选分组结果，选出“课程成绩”大于92的学生信息。在查询分析器中输入的SQL语句如下：
use student
select 学号,avg(课程成绩) as 平均成绩
from grade
where 学号 is not null
group by 学号
having avg(课程成绩)>92
order by 平均成绩
实现的过程如图2所示。

图2 查询统计“student”表
下面给出上个示例中SQL语句的执行顺序。
（1）首先执行FROM子句，从“grade”表组装数据源的数据。
（2）执行WHERE子句，筛选“grade”表中所有数据不为NULL的数据。
（3）执行GROUP BY子句，把“grade”表按“学号”列进行分组。
（4）计算AVG( )聚集函数，按“课程成绩”求出平均成绩的具体数值。
（5）执行HAVING子句，筛选课程的平均成绩大于92分的学生信息。
（6）执行ORDER BY子句，把最后的结果按“平均成绩”进行排序。
2．HAVING子句在分组搜索条件上的限制
HAVING子句指定的搜索条件必须是作为一个整体应用于组而不是应用于各个记录。所以HAVING的搜索条件是有限制的，列举如下：
l l          一个常量。
l l          一个聚合函数，这个聚合函数生成一个值，该值汇总组中的记录。
l l          一个分组列，按照定义，这个分组字段在这个组的每一记录中有同样的值。
l l          一个包含上述各项组合的表达式。
示例：
在“grade”表中，按“学期”分组，求“学期”值不为空的课程成绩平均值。在查询分析器中输入的SQL语句如下：
use student
select avg(课程成绩) as 平均成绩
from grade
group by 学期
having 学期 is not null
实现的过程如图3所示。

图3 求“grade”表中按“学期”分组的平均成绩
示例：
在“grade”表中，按“课程类别”分组，并且查询“课程类别”不是“计算机类”的课程信息。在查询分析器中输入的SQL语句如下：
use student
select 课程类别
from course
group by 课程类别
having 课程类别<>'计算机类'
实现的过程如图4所示。

图4 “grade”表按“课程类别”分组统计
3．比较HAVING子句与WHERE子句
两个子句的相似之处。
（1）它们都是从结果表中筛选数据。
（2）它们都设置了某些数据能通过而其他数据不能通过的条件。
两个子句的不同之处。
（1）WHERE子句可以在进行任何处理之前从原表、原始数据中筛选行。
（2）HAVING子句可以在进行绝大部分处理之后筛选已分组和已总结的数据。
（3）WHERE子句不能在它设置的条件之中使用列函数。
（4）HAVING子句可以在它的条件中使用列函数。
理解HVING子句的最好方法就是记住SELECT语句中的哪些子句是按照明确的次序进行处理的。WHERE子句只能接收来自FROM子句的输入，而HAVING子句则可以接收来自GROUP BY、WHERE子句或FROM子句的输入。这是一个微妙但却重要的差别。
示例：
在“grade”表中，把“课程成绩”大于92分的按“学期”分组求平均成绩。在查询分析器中输入的SQL语句如下：
use student
select 学期,avg(课程成绩) as 平均成绩
from grade
where 课程成绩>92
group by 学期
实现的过程如图5所示。

图5 按学期求大于92分的课程的平均成绩
上个例子，首先挑选出“课程成绩”大于92分的学生信息，然后按“学期”再分组求课程成绩的平均值。下面把WHERE子句替换成HAVING子句，在查询分析器中运行的结果如图6所示。

图6 按“学期”分组用HAVING子句设置条件
SQL语句如下所示：
use student
select 学期,avg(课程成绩) as 平均成绩
from grade
group by 学期
having 课程成绩>92
由此可见，执行用HAVING子句替换WHERE子句的语句是错误的。因为“课程成绩”列既不包含在聚合函数中，也不包含在GROUP BY子句中。下面改变HAVING子句的条件，这个子句包括一个用了聚合函数的列。
示例：
在“grade”表中，按“学期”求课程成绩的平均值，并筛选出平均成绩大于92分的。在查询分析器中输入的SQL语句如下：
use student
select 学期,avg(课程成绩) as 平均成绩
from grade
group by 学期
having avg(课程成绩)>92
实现的过程如图7所示。

图7 按“学期”求成绩的平均值并用HAVING进行筛选
当按“学期”分完组后，HAVING子句就应用于这些结果。对于每一个组来说，都要求成绩的平均值，但只有平均成绩大于92分才能包括在结果中。
HAVING对分组后的数据可以进行筛选，并且可以使用AVG（）或SUM（）之类的设置功能，而这些是在WHERE子句中无法使用的。
4．使用ALL关键字
在GROUP BY子句中使用ALL关键字。只有在SQL语句还包括WHERE子句时，ALL关键字才有意义。
如果使用ALL关键字，那么查询结果将包括由 GROUP BY子句产生的所有组，即使某些组没有符合查询条件的行。没有ALL关键字，包含GROUP BY子句的SELECT语句将不显示没有符合条件的行的组。
示例：
在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值的行。在查询分析器中输入的SQL语句如下：
use student
select 课程代号,avg(课程成绩) as 平均成绩
from grade
where 学号!='b003'
group by 课程代号
having 课程代号 is not null
实现的过程如图8所示。

图8 分组查询成绩表
在上面的例子中使用ALL关键字。
示例：
在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值。在查询分析器中输入的SQL语句如下：
use student
select 课程代号,avg(课程成绩) as 平均成绩
from grade
where 学号!='b003'
group by all 课程代号
having 课程代号 is not null
实现的过程如图9所示。

图9 使用ALL关键字分组查询成绩表
5．在分组查询中使用CUBE运算符
CUBE运算符的主要作用是自动对GROUP BY子句中列出的字段进行分组汇总运算。
CUBE运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上，这些列被称为维。多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。
CUBE运算符在SQL语句的GROUP BY子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUP BY应指定维度列和关键字WITH CUBE。结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚集值。
下面举一个简单的例子，一个简单的表Inventory，其内容如表1所示：
表1 Inventory表结构
Item
Color
Quantity
Table
Blue
124
Table
Red
223
Chair
Blue
101
Chair
Red
210

在查询分析器中输入的SQL语句如下：
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
其查询结果如表2所示：
表2 查询结果集
Item
Color
QtySum
Chair
Blue
101.00
Chair
Red
210.00
Chair
NULL
311.00
Table
Blue
124.00
Table
Red
223.00
Table
NULL
347.00
NULL
NULL
658.00
NULL
Blue
225.00
NULL
Red
433.00

下面着重考查下列各行,如表3、表4、表5和表6所示。
表3 记录行1
Chair
NULL
311.00

这一行显示了Item维度中值为Chair的所有行的小计。对Color维度返回了NULL值，表示该行所显示的聚集包括Color维度为任意值的行。
表4 记录行2
Table
NULL
347.00

这一行类似，但显示的是Item维度中值为Table的所有行的小计。
表5 记录行3
NULL
NULL
658.00

这一行显示了多维数据集的总计。Item和Color维度的值都是NULL，表示两个维度中的所有值都汇总在该行中。
表6 记录行4
NULL
Blue
225.00
NULL
Red
433.00
这两行显示了Color维度的小计。两行中的Item维度值都是NULL，表示聚集数据来自Item维度为任意值的行。
示例：
在“grade”表中，按“学期”和“课程代号”分组求课程的平均成绩，并且用CUBE运算符进行小计。在查询分析器中输入的SQL语句如下：
use student
select 学期, 课程代号,avg(课程成绩) as 平均成绩
from grade
group by 学期,课程代号 with cube
having 课程代号 is not null
实现的过程如图10所示。

图10 对“grade”表统计小计
6．在分组查询中使用ROLLUP
在使用GROUP BY生成包含小计和合计的报表时，ROLLUP 运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。
CUBE和ROLLUP之间的区别在于：
（1）CUBE生成的结果集显示了所选列中值的所有组合的聚集。
（2）ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚集。
下面同样以一个简单表Inventory为例来介绍如何使用ROLLUP运算符，如表7所示。
表7 Inventory表
Item
Color
Quantity
Table
Blue
124
Table
Red
223
Chair
Blue
101
Chair
Red
210

在查询分析器中输入的SQL语句如下：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
其执行结果集如表7所示。
如果查询中的ROLLUP 关键字更改为CUBE，那么CUBE结果集与上述结果相同，只是在结果集的末尾还会返回下列两行，如表8所示。
表8 执行结果集1
Item
Color
QtySum
Chair
Blue
101.00
Chair
Red
210.00
Chair
ALL
311.00
Table
Blue
124.00
Table
Red
223.00
Table
ALL
347.00
ALL
ALL
658.00

表9 执行结果集2
ALL
Blue
225.00
ALL
Red
433.00
CUBE操作为Item和Color中值的可能组合生成行。例如，CUBE不仅报告与Item值Chair相组合的Color值的所有可能组合（Red、Blue和Red+Blue），而且报告与Color值Red相组合的Item值的所有可能组合（Chair、Table和Chair+Table）。
对于GROUP BY子句中右边的列中的每个值，ROLLUP操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，ROLLUP并不对每个Color值报告Item的所有可能组合。
ROLLUP操作的结果集具有类似于COMPUTE BY所返回结果集的功能，然而，ROLLUP具有下列优点：
（1）ROLLUP返回单个结果集；COMPUTE BY返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
（2）ROLLUP可以在服务器游标中使用；COMPUTE BY不可以。
（3）有时查询优化器为ROLLUP生成的执行计划比为COMPUTE BY生成的更为高效。
注意：WITH ROLLUP关键字主要对GROUP BY子句中列出的第一个分组字段进行汇总计算。GROUP BY子句如果有两个字段，那么字段位置不同，返回的结果集也不同。
示例：
在“grade”表中，按“学期”和“课程代号”分组求课程的平均成绩，并且用CUBE运算符进行小计。在查询分析器中输入的SQL语句如下：
use student
select 学期, 课程代号,avg(课程成绩) as 平均成绩
from grade
group by 学期,课程代号 with rollup
having 课程代号 is not null
实现的过程如图11所示。

图11 对成绩表统计并合计