16、SQL Server 汇总数据之聚合函数与分组 group by

汇总数据

主要用于对数据集的的数据进行汇总统计等操作，基本是聚合函数。

聚合的基本理念：不是返回所有指定的行，而是返回一行计算得到的值（前面指定的行

的某些数据的汇总）。它们汇总了原始数据集。

1、计算总数、平均值

2、统计分析

3、解决聚合问题

4、创建动态的交叉查询

一、简单聚合

在SQL查询的逻辑流程中，聚合函数是在From子句和Where子句之后执行的，这

意味着无须使用子查询就可以在汇总（使用聚合函数）前对数据进行组装和筛选。

基本聚合（函数）

聚合函数

支持的数据类型

描述

sum( )

Numeric

计算指定列中所有非空值的总和

avg()

numeric

计算指定列中所有非空值的平均值

min()

numeric、string、datetime

返回指定列中最小的数字或根据排序规则返回最前面的日期或字符串

max()

numeric、string、datetime

返回指定列中最大的数字或根据排序规则返回最前面的日期或字符串

count( [distinct]  *)

任何基于行的数据类型

计算结果集中的总行数，

count_big( [distinct]  *)

任何基于行的数据类型

与count类似，但是其返回类型是binint比count大

使用一般聚合函数时的规则：

1、由于现在SQL返回数据库中的信息，而不是建立一个由行组成的记录集，因此查询包含

聚合函数时，每一列（列列表、表达式、或order by中的列）都必须参与聚合函数的计算。

2、聚合选项distinct的作用与Select distinct 相同，但聚合选项中的distinct消除重复的

值而不是重复的行。

注：count( distinct * )是非法的，必须指定特定的列。

count(*)计算数据集的总行数，但count(clomun名)计算在指定列中有值的总行数

由于聚合函数属于表达式，因此结果中没有列名，最好指定列名

二、在结果集中分组（使用group by ）

group by 子句将根据特定列中的值，将数据集划分成子集。将数据划分成子集后，再

对每个子集执行聚合函数，最后由聚合函数生成数据（一般是每个子集占一行。）

如果group by 子句有多列，则是根据这些列的值完全相同的行分为一组，只要group gy

指定的任何一列的值不同，都不是同一分组。

1、简单分组

如：根据Category的值的不同分组，相同的为一值，每个分组根据聚合函数，会生成

一行汇总数据

Select Category,

  count(*) as  [COUNT],

  Sum(Amount)  as [Sum]

  Avg(Amount)   as [Avg]

  Min(Amount)   as [Min]

From RawData

group by Category

这上面是采用了分组的描述信息进行了分组，所以不需要另外添加分组的描述信息。

但是一般在大型关系数据库中很少直接使用分组的描述信息作为分组依据，这就需要

额外添加分组描述信息。这就需要使用子查询和联接来实现。

三、聚合查询（5种常用的聚合问题及解决方案）

1、包含分组依据描述

下面的实例试图返回一个没有在group by中出现的列。（也称百聚合描述列）

有两种解决方案：1、在group by 子句中包容额外的列（使用联接）

2、在子查询中执行聚合函数，并在外部查询中包含额外的列（使用联接）

其中这两个方案一般都会用到联接。。

方案1：

Select Category,Categoryname

sum(Amount) as [Sum]

avg(amount) as [Avg]

from RawData R 

inner join RowCategory C on R.CategoryID=C.RowCategoryID

group by  Category,C.Categoryname

order by  Category,C.Categoryname

方案2：在子查询中执行聚合函数，并在外部查询中包含额外的列

Select SQ.Category,Categoryname,SQ.[Sum],SQ.[Avg]

from 

(

Select Category

sum(Amount) as [Sum]

avg(amount) as [Avg]

from RawData R 

group by  Category

) as SQ

inner join RowCategory C on SQ.CategoryID=C.RowCategoryID

order by SQ.Category,C.Categoryname

2、包含所有的分组依据值

Group by 分组是在where子句之后进行的。

如果查询需要返回所有分组依据列的值，但如果要显示where过滤的行，

可使用group by all 选项返回所有分组依据值。

而不管where子句如何。

如：

select bmname 部门名称,count(bmname) 有工资的员工总数,

sum(A.basic_gz+A.jiaban_gz+A.jiangjin) 部门工资  from gongzi A 

inner join yuangong B  on A.ygid=B.id

inner join bumen C on B.bmID=C.id

where bmname='管理部'

group by all bmname

结果：

部门名称 有工资的员工总数 部门工资

管理部          1               702

技术部          0              NULL

客户部          0              NULL

销售部          0              NULL

注：如果不加all 结果为

部门名称 有工资的员工总数 部门工资

管理部        1                  702

如果没有where 条件结果为

部门名称 有工资的员工总数 部门工资

管理部         1                702

技术部         2                5469

客户部         1                1878

销售部         1                2200

3、嵌套聚合

对聚合过的结果集，再进行聚合查询

如：根据每种类别在每年/每季度的销售情况，求每年每季度销售最好的类别的销售

情况信息。

1、先根据种类以及每年/季度进行分组，并调用相关的聚合函数

这样就得到了不同种类在每年/每季度的销售情况

2、对上一个结果集，根据每年/每季度进行分组，并计算每个分组中销售最好的。

SQL语句：

--根据每年每季度分组，求出销售最多的一个分组。这里没有包含类别信息，如果需要

添加，可以通过再联接一个查询实现（下面会介绍到）

Select Y,Q,Max(SQ.[SUM]) as MaxSum

from

(--求出每年每季度不同类型的产品销售情况

select Category ,Year(SalesDate) as Y, DatePart(q,SalesDate) as Q,sum(Amount) as [SUM]

from RowData group by Category,Year(SalesDate),DatePart(q,SalesDate) 

) as SQ

group by Y,Q 

order by Y,Q

4、包含详细描述

一般通过子查询来实现（并使用联接），使用group by 会导致分组的不同，如果有多

列与一列分组效果是不一样的。多列必须这些列值全部相同才会成为一个分组，这样如果只

是为了添加显示依据单列分组中的某些信息，会使原来的分组发生变换，就得不到应有的信

息。

5、筛选分组结果

SQL Select 语句的执行顺序

1、From子句使用数据源组装数据集

2、Where子句根据条件限制返回的行

3、Group By 子句组装数据子集

4、对每个分组执行聚合函数

5、having 子句筛选数据子集

6、计算表达式

7、Order By 子句对结果进行排序

SQL Server使用Having 子句来筛选分组

如：

--根据每年每季度分组，求出销售最多的一个分组。这里没有包含类别信息，如果需要

添加，可以通过再联接一个查询实现

Select Y,Q,Max(SQ.[SUM]) as MaxSum

from

(--求出每年每季度不同类型的产品销售情况

select Category ,Year(SalesDate) as Y, DatePart(q,SalesDate) as Q,sum(Amount) 

as [SUM]

from RowData group by Category,Year(SalesDate),DatePart(q,SalesDate) 

) as SQ

group by Y,Q 

having avg(Amount)>25

order by Y,Q