聚合分析和分组

SQL提供了一些列的聚集函数对表中的数据进行统计分析，在SQL中可以使用分组命令将列中的数据按照一定的条件进行分组。一般只将聚合函数作用在满足条件的分组上。因此聚合函数与分组命令一般结合使用.常见的聚合函数有sum（），max（），min( ),avg( ),count( )

1、sum( column_name)

求某列的所有非空数值之和

eg1：求所有女教师的工资总和

select sum(sal) as girl_sal from teacher where tsex='女' 

结果：

 

 

2、count（*）和count（column_name）

select count(*) as total_item, count(sal)as count_sal, count(tname+cast(sal as varchar(5)))as t_namesal from teacher

查询结果：

从这个结果分析：count(*)执行求出的是表的总行数（包括有null值的行），而count（column_name）计算的是这一列中非null数据的行数。此外count可以对进行连接操作后的列进行计数操作，有连接运算符一节可以知道只要参与连接的列中有一列为null，则连接的结果都为null。count对连接后的列进行计数，同样不包括列值为null的行在内。因此上述查询结果的后面两列的行数均为8.

 

2、最大/最小值函数max( )/min( )

注意：

SQL不支持如下的select语句：

from teacher --SQL语句不支持的select语句 select tname,dname,tsex,avg(age) from teacher --SQL语句不支持的select语句2 select tname,dname,tsex,age from teacher where age = max(age) 

理由：聚合函数处理的是数据组，在这里max（）和avg（）函数都是将这个表看成一组，而tname,dname,tsex都没有进行分组。上面两个不正确的例子中第二个例子可以改写为子查询的方式来返回最大值。代码如下：

select tname,dname,tsex,age from teacher where age = (select max(age)from teacher) 

 

说明：

• max( ),min( )函数还可以用于字符型数据，时间型数据。例如

--max()函数用于字符型数据 select max(tname)as maxname from teacher --max()函数用于时间型数据 select min(ctest)as early_date, max(ctest)as late_date from course  

• 确定最大值（最小值）时，max（）或者min（）函数都会忽略null值，但是如果在该列中，所有行的值都是null，则max（）或者min（）函数将返回null

3、avg（）函数

eg1：avg（）函数对null值的处理

从teacher表中查询所有教师的平均工资

select avg(sal)as avg_sal1, sum(sal)/count(*)as avg_sal2, sum(sal)/count(sal) as avg_sal3 from teacher 

查询结果：

由上述结果可以知道，avg（）函数将忽略null列的值，将总的数值数除以非空行数，得到平均值。

 

eg2：用where子句来限制用于计算平均值的行

从teacher表中查询所有计算机系的教师的平均年龄

select avg(age)as avgcomputer_age from teacher where dname='计算机' 

计算结果：

为什么能够有条件的计算平均值？

--------这与select语句的执行顺序是分不开的。where子句在select子句之前执行，排出了不符合条件的行，剩下的行才参与avg（）函数的计算。

 

4、组合查询

（1）分组的语法：

select  column ,sum(column)

from teacher

group by column

eg1:从teacher表中查询男教师和女教师的平均工资

select tsex+'教师'as teacher,avg(sal) as avg_sal from teacher group by tsex 

查询结果：

（2）group by 根据多个字段进行分组，

eg2：

select dname,tsex,count(*)as total_num from teacher group by dname,tsex order by dname 

查询结果：

 

（3）rollup运算符（略）

（4）cube运算符（略）

（5）group by 对null值的处理

group by子句将所有null值作为一组，也就是说在group by子句中认为null和null的值是“相等”的

eg：

--从teacher表中查询所有的工资数及各工资的人数 select sal,count(*)as total_num from teacher group by sal order by sal 

查询结果：

从结果可以看出，工资为null的被分为一个组做统计

 

（6）having子句

• 当having子句和group by子句在一起使用时，having子句用于对group by 分组后的各个组进行筛选，去除掉每个分组中不符合having子句条件的分组。

eg：从teacher表中查询至少有两位老师的系及教师人数。实现代码：

select dname,count(*)as teacher_num from teacher group by dname having count(*)>=2 order by dname

执行结果：

• having子句和where子句都是用于筛选，两者的异同

-------第一，如果查询语句中有group by 子句，则having子句只能作用于group by 分成的各个分组

   第二，如果没有group by 子句，有where子句的输出，并把这个输出作为一个分组

 第三，如果既没有group by子句，也没有where子句，那么having子句作用与from子句的输出，并将这个输出作为一个分组。

这三点和前面谈到的select语句执行顺序有关http://blog.csdn.net/qinyushuang/archive/2010/12/31/6109037.aspx。

综上，where子句只可以作用域select语句，而having子句可以作用于group by ，where子句，select子句的输出，具体作用于哪个子句，就上上面所说的三点说明。

eg1：

从teacher表中查询有女教师的系，及其拥有女教师的数量

--查询至少有2名女教师的系及各系拥有女教师的数量,并按女教师数量进行升序排序 select dname,count(tsex)as num_girl from teacher where tsex = '女' group by dname having count(tsex)>=2 order by num_girl 

执行结果：

 

eg2：一个错误的例子

--不能把单个的tsex应用于组 select dname ,count(tsex)as num_girl from teacher group by dname having tsex='女' 

执行出错，原因：包括在having子句中的列必须是分组的列

 

eg3：having子句作用where子句的一个例子

--having子句作用于where子句的例子 select count(tsex)as num_girl from teacher where tsex = '女' having count(tsex)>4 

执行结果：