1.6 常用聚合函数

聚合函数对一组值执行计算后返回单个值。除了COUNT（统计项数）函数以外，其他的聚合函数在计算时都会忽略空值（Null）。

所有聚合函数均为确定性函数。即任何时候使用一组相同的输入值调用聚合函数执行后的返回值都是相同的，无二义性。

聚合函数通常会在下列场合使用。

● SELECT语句的选择列表，包括子查询和外部查询。

● 使用COMPUTE或COMPUTE BY产生汇总列时。

● HAVING子句对分组的数据记录进行条件筛选。

下面介绍常用的聚合函数的语法及其使用方法。

1.6.1 计算平均值AVG

AVG函数用于计算精确型或近似型数据类型的平均值，bit数据类型除外，忽略Null值。

AVG函数在计算时将计算一组值的总和，然后除以非NULL值的个数，得到平均值。

1．语法结构

        AVG ( [ ALL | DISTINCT ] expression )

● ALL：为默认值，表示对所有的数据都计算平均值。

● DISTINCT：每个值的唯一值计算平均值，不管相同的值出现多次。比如学生成绩表中，语文课程的成绩可能相同，如果指定DISTINCT，那么相同的值仅出现一次作为计算。

● Expression：精确或近似值的表达式。表达式内部不允许使用子查询和其他聚合函数。

2．实例

AdventureWorks数据库的HumanResources架构下有一个存储雇员信息的表Employee，该表存储的是某个公司的雇员信息。

● VacationHours字段：smallint数据类型，NOT NULL，以小时来表示雇员可以获得的可持续的假期。

● Title字段：nvarchar（50）数据类型，NOT NULL，代表雇员的职位。Vice President代表副总经理。

下面的代码获得不同类型的雇员职位所可以获得的平均假期时间。

        USE AdventureWorks;
        GO
        SELECT E.TITLE as ’雇员职位’, AVG(VacationHours)as ’平均假期时间’
        FROM HumanResources.Employee E
        GROUP BY E.Title ;

1.6.2 计算最小值MIN

MIN函数用于计算最小值，MIN函数可以适用于numeric、char、varchar或datetime、money或smallmoney列，但不能用于bit列。不允许使用聚合函数和子查询，忽略NULL值。

1．语法结构

        MIN ( [ ALL | DISTINCT ] expression )

如果表达式返回的是字符数据，MIN函数返回排序序列的最低值。

2．实例

AdventureWorks数据库的Sales架构下有一个存储雇员信息的表SalesTaxRate，该表存储的是各个地区的税率。

TaxRate字段：smallmoney数据类型，NOT NULL，代表不同地区的税率。

下面的代码获得最低税率的地区和税率值。

        USE AdventureWorks;
        GO
        --定义临时变量
        DECLARE @MinTaxRate smallmoney;
        --查找最小税率值，存放在临时变量中
        SELECT @MinTaxRate=MIN(S.TaxRate)
        FROM Sales.SalesTaxRate S;
        --连接两个表查询到地区名称
        SELECT S.Name as  ’税名’
              ,S.StateProvinceID as ’地区ID'
              ,S.TaxRate as ’税率’
              ,P.Name as ’地区名’
        FROM Sales.SalesTaxRate S, Person.StateProvince P
        WHERE S.TaxRate=@MinTaxRate
        AND S.StateProvinceID=P.StateProvinceID;
        GO;

1.6.3 计算最大值MAX

MAX函数用于计算最大值，忽略NULL值。MAX函数可以适用于numeric、char、varchar、money、smallmoney或datetime列，但不能用于bit列。不允许使用聚合函数和子查询。

1．语法结构

        MAX ( [ ALL | DISTINCT ] expression )

如果表达式返回的是字符数据，MIN函数返回排序序列的最高值。

2．实例

下面的代码获得最高税率的地区和税率值。

        USE AdventureWorks;
        GO
        --定义临时变量
        DECLARE @MaxTaxRate smallmoney;
        --查找最小税率值，存放在临时变量中
        SELECT @MaxTaxRate=MAX(S.TaxRate)
        FROM Sales.SalesTaxRate S;
        --连接两个表查询到地区名称
        SELECT S.Name as  ’税名’
              ,S.StateProvinceID as ’地区ID'
              ,S.TaxRate as ’税率’
              ,P.Name as ’地区名’
        FROM Sales.SalesTaxRate S, Person.StateProvince P
        WHERE S.TaxRate=@MaxTaxRate
        AND S.StateProvinceID=P.StateProvinceID;
        GO

1.6.4 计算求和值SUM

SUM函数用于求和，只能用于精确或近似数字类型列（bit类型除外），忽略NULL值。不允许使用聚合函数和子查询。

1．语法结构

   MAX([ALL|DISTINCT]expression)

2．实例

AdventureWorks数据库的Production架构下有一个存储产品信息的表Product，该表存储的是产品的信息。

● Name字段：nvarchar(50)数据类型，NOT NULL，产品名称。

● Color字段：nvarchar(50)数据类型，可以为NULL，产品颜色。

● ListPrice字段：money数据类型，NOT NULL，销售价格。

● StandardCost字段：money数据类型，NOT NULL，标准成本。

下面的代码获得产品名称类似Mountain的、销售价格大于0、颜色不为空的产品，按照颜色分组后的销售价格总和、成本价格总和，查询结果按照颜色升序排序。

        USE AdventureWorks;
        GO
        SELECT P.Color as ’产品颜色’, SUM(P.ListPrice) as ’销售价格总和’, SUM(P.
    StandardCost) as ’成本价格总和’
        FROM Production.Product P
        WHERE P.Color IS NOT NULL
        AND P.ListPrice ! = 0.00
        AND P.Name LIKE ' Mountain%'
        GROUP BY P.Color
        ORDER BY P.Color;
        GO

1.6.5 计算项数值COUNT（COUNT_BIG）

COUNT函数用于计算满足条件的数据项数，返回int数据类型的值。

1．语法结构

        COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

这里的表达式是除text、image或ntext以外任何数据类型的表达式。但不允许使用聚合函数和子查询。

2．常见使用方法

● COUNT(*)：返回所有的项数，包括Null值和重复项。

● COUNT(ALL表达式)：返回非空的项数。

● COUNT(DISTINCT表达式)：返回唯一非空的项数。

COUNT_BIG的语法和COUNT完全一样，只不过返回值为Bigint数据类型，这样返回的数值范围就可以大大超过COUNT。

3．实例

AdventureWorks数据库的Sales架构下有一个存储销售人员信息的表SalesPerson。

● Bonus字段：money数据类型，not null，完成销售额后的奖金。

● SalesQuota字段：money数据类型，可以为null，销售额。

下面的代码获得年度销售额大于25000的人员总数、平均奖金数。

        USE AdventureWorks;
        GO
        SELECT COUNT(*) as ’人员总数’, AVG(S.Bonus) as ’平均奖金’
        FROM Sales.SalesPerson S
        WHERE S.SalesQuota > 25000;
        GO

1.6.6 计算标准偏差值STDEV

STDEV函数用于计算指定表达式中所有值的标准偏差。

1．语法结构

        STDEV ( [ ALL | DISTINCT ] expression )

这里的expression表达式必须是一个数值表达式，不允许使用聚合函数和子查询。表达式的值是精确或近似数值类型，但不包括bit数据类型。将忽略Null值。

2．标准偏差

标准偏差（Standard Deviation）是统计学上的专业术语。用于衡量数据值偏离算术平均值的程度。标准偏差越小，这些值偏离平均值就越少，反之亦然。标准偏差的大小可通过标准偏差与平均值的倍率关系来衡量。

标准偏差的计算公式：S = Sqr(∑(xn-x拨)^2 /(n-1))

● ∑：代表总和。

● x拨：代表x的算术平均值。

● ^2代表二次方。

● Sqr代表平方根。

例：有一组数字分别是200、50、100、200，求它们的标准偏差。

标准偏差S = Sqr(5625) = 75。

3．实例

下面的代码计算所有销售人员距离平均奖金值的标准偏差。

        USE AdventureWorks;
        GO
        SELECT STDEV(S.Bonus) as ’平均偏差值’, AVG(S.Bonus) as ’平均值’
        FROM Sales.SalesPerson S;
        GO

1.6.7 计算方差VAR

VAR函数用于计算指定表达式中所有值的方差。

1．语法结构

        VAR ( [ ALL | DISTINCT ] expression )

这里的expression表达式必须是一个数值表达式，不允许使用聚合函数和子查询。表达式的值是精确或近似数值类型，但不包括bit数据类型。将忽略Null值。

2．方差

样本中各数据与样本平均数的差的平方和的平均数叫做样本方差；样本方差的算术平方根叫做样本标准差。样本方差和样本标准差都是衡量一个样本波动大小的量，样本方差或样本标准差越大，样本数据的波动就越大。数学上一般用E{[X-E(X)]^2}来度量随机变量X与其均值E(X)的偏离程度，称为X的方差。

设X是一个随机变量，若E{[X-E(X)]^2}存在，则称E{[X-E(X)]^2}为X的方差，记为D(X)或DX。即D(X)=E{[X-E(X)]^2}，而σ(X)=D(X)^0.5（与X有相同的量纲）称为标准差或均方差。

由方差的定义可以得到以下常用计算公式：

D(X)=E(X^2) - [E(X)]^2

3．实例

下面的代码计算所有销售人员奖金的方差。

        USE AdventureWorks;
        GO
        SELECT VAR(S.Bonus) as ’方差’
        FROM Sales.SalesPerson S;
        GO