SQL Server（00）：聚合函数



聚合函数对一组值执行计算，并返回单个值。

除了 COUNT 外，聚合函数都会忽略 Null 值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。

OVER 子句可以跟在除 STRING_AGG、GROUPING 或 GROUPING_ID 函数以外的所有聚合函数后面。

只能在以下位置将聚合函数作为表达式使用：

• SELECT 语句的选择列表（子查询或外部查询）。
• HAVING 子句。

T-SQL提供的聚合函数一共有13个之多。

1、avg：平均值

avg( [ all | distinct ] expression )

AVG函数用于计算精确型或近似型数据类型的平均值，bit类型除外，忽略null值。AVG函数计算时将计算一组数的总和，然后除以为null的个数，得到平均值

select avg(distinct age) from person    -- 查询person表里的年龄的平均值，相同值只计算一次

2、min：最小值

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

3、max：最大值

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

4、sum：求和值

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

5、count：统计项数值

count函数用于计算满足条件的数据项数，返回int数据类型的值。这里的表达式是除text、image或ntext以外任何数据类型的表达式。但不允许使用聚合函数和子查询。

• count(*) ： 返回所有的项数，包括null值和重复项。而除了count(*)外，其他任何形式的count()函数都会忽略Null行。
• count(all 表达式)：返回非空的项数。
• count(distinct 表达式)：返回唯一非空的项数

注意：count(字段名)，如果字段名为NULL，则count函数不会统计。例如count(name)，如果name为空，则不会统计到结果

select count(distinct age) from person    -- 查询person表里的年龄唯一且非空的项数

6、count_big：统计项数量

返回组中的项数。 COUNT_BIG 的用法与 COUNT 函数类似。 两个函数唯一的差别是它们的返回值。 COUNT_BIG 始终返回 bigint 数据类型值。 COUNT 始终返回 int 数据类型值。

7、差值函数

1、stdev：计算标准偏差值　　

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

2、stdevp：计算总体标准偏差

返回指定表达式中所有值的总体标准偏差。

3、var：计算方差

VAR函数用于计算指定表达式中所有值的方差。　这里的expression表达式必须是一个数值表达式，不允许使用聚合函数和子查询。表达式的值是精确或近似数值类型，但不包括bit数据类型，将忽略null值。

4、varp：计算总体统计方差

返回指定表达式中所有值的总体统计方差。

8、checksum_agg：计算组中各值的校验和

返回组中各值的校验和。 将忽略 Null 值。CHECKSUM_AGG 可用于检测表中的更改。表中行的顺序不影响 CHECKSUM_AGG 的结果。此外，CHECKSUM_AGG 函数还可与 DISTINCT 关键字和 GROUP BY 子句一起使用。如果表达式列表中的某个值发生更改，则列表的校验和通常也会更改。但只在极少数情况下，校验值会保持不变。

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

参数说明：　

• ALL：对所有的值进行聚合函数运算。 ALL 为默认值。
• DISTINCT ：指定 CHECKSUM_AGG 返回唯一校验值。
• expression ：一个整数表达式。 不允许使用聚合函数和子查询。

SELECT CHECKSUM_AGG(Account_Age) FROM Account
GO
UPDATE Account SET Account_Age = 30 WHERE Account_Id = 6
GO
SELECT CHECKSUM_AGG(Account_Age) FROM Account

显示结果如下：

　　可见随着表的更改，该系统函数返回的值也变了。此函数的作用正在于此，检测表的更改。

9、string_agg：串联字符串

MS SQL Server的2017新增了STRING_AGG()是一个聚合函数，它将由指定的分隔符分隔将字符串行连接成一个字符串。 它不会在结果字符串的末尾添加分隔符。

以下是STRING_AGG()函数的语法：

STRING_AGG ( input_string, separator ) [ order_clause ]

在这个语法中：

• input_string是串联时可以转换为VARCHAR和NVARCHAR的类型。
• separator是结果字符串的分隔符。它可以是文字或变量。
• order_clause使用WITHIN GROUP子句指定连接结果的排序顺序：

  WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )

STRING_AGG()忽略NULL，并且在执行连接时不会为NULL添加分隔符。

下面将使用示例数据库中的sales.customers表进行演示：

此示例使用STRING_AGG()函数生成城市客户的电子邮件列表：

SELECT  city,   STRING_AGG(email,';') email_list FROM sales.customers GROUP BY city;

执行上面查询语句，得到以下结果：

要对email列表进行排序，请使用WITHIN GROUP子句：

SELECT city, STRING_AGG(email,';') WITHIN GROUP (ORDER BY email) email_list FROM  sales.customers GROUP BY city;

执行上面查询语句，得到以下结果：

注意：STRING_SPLIT（）函数：一个表值函数，它根据指定的分隔符将字符串拆分为子字符串行。

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

10、approx_count_distinct：唯一非空值的近似数

SQL Server 2019引入了新函数Approx_Count_distinct以提供行的近似计数。Count（distinct（））函数提供实际的行数。

该函数APPROX_COUNT_DISTINCT应该使用较少的内存和CPU资源，以便可以获取数据结果而不会出现任何问题，例如溢出到磁盘或CPU峰值。这对于数十亿行的需求很有用。

11、cube或 rollup 汇总运算符

1. CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
2. ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

查询出插入的全部数据：

select * from dbo.PeopleInfo

select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb             //用group by 
select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb with cube;  //用with cube
select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb with rollup //用with rollup

12、grouping：指示是否聚合GROUP BY 列：

当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。

select [name],numb,grouping(numb) from dbo.PeopleInfo group by [name],numb with rollup

13、grouping_id：计算分组级别

仅当指定了 GROUP BY 时，GROUPING_ID 才能在 SELECT 列表、HAVING 或 ORDER BY 子句中使用。 使用 GROUPING_ID 标识分组级别下面的示例返回按 AdventureWorks2012 数据库的 Name 和 Title 汇总的雇员计数以及 Name, 和公司总计。 GROUPING_ID() 用于为 Title 列中的每行创建一个值以标识聚合级别。SELECT D.Name ,CASE WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:' ELSE N'Unknown' END AS N'Job Title' ,COUNT(E.BusinessEntityID) AS N'Employee Count' FROM HumanResources.Employee E INNER JOIN HumanResources.EmployeeDepartmentHistory DH ON E.BusinessEntityID = DH.BusinessEntityID INNER JOIN HumanResources.Department D ON D.DepartmentID = DH.DepartmentID WHERE DH.EndDate IS NULL AND D.DepartmentID IN (12,14) GROUP BY ROLLUP(D.Name, E.JobTitle);14、partition by :聚合开窗函数很多聚合函数都可以用作窗口函数的运算，如SUM,AVG,MAX,MIN。聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，ORDER BY不能与聚合开窗函数一同使用。例如，查询雇员的定单总数及定单信息WITH OrderInfo AS ( SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) ) SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount From OrderInfo ORDER BY EmployeeID如果窗口函数不使用PARTITION BY 语句的话，那么就是不对数据进行分组，聚合函数计算所有的行的值WITH OrderInfo AS ( SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) )