?聚合函數(shù)對一組值執(zhí)行計算，并返回單個值。

除了 COUNT 外，聚合函數(shù)都會忽略 Null 值。 聚合函數(shù)經(jīng)常與 SELECT 語句的 GROUP BY 子句一起使用。

OVER 子句可以跟在除 STRING_AGG、GROUPING 或 GROUPING_ID 函數(shù)以外的所有聚合函數(shù)后面。

只能在以下位置將聚合函數(shù)作為表達(dá)式使用：

• SELECT 語句的選擇列表（子查詢或外部查詢）。
• HAVING 子句。

T-SQL提供的聚合函數(shù)一共有13個之多。

1、avg：平均值

avg( [ all | distinct ] expression )

AVG函數(shù)用于計算精確型或近似型數(shù)據(jù)類型的平均值，bit類型除外，忽略null值。AVG函數(shù)計算時將計算一組數(shù)的總和，然后除以為null的個數(shù)，得到平均值

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

2、min：最小值

MIN函數(shù)用于計算最小值，MIN函數(shù)可以適用于numeric、char、varchar或datetime、money或smallmoney列，但不能用于bit列。不允許使用聚合函數(shù)和子查詢，忽略null值。

3、max：最大值

MAX函數(shù)用于計算最大值，忽略null值。max函數(shù)可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。不允許使用聚合函數(shù)和子查詢。

4、sum：求和值

SUM函數(shù)用于求和，只能用于精確或近似數(shù)字類型列(bit類型除外)，忽略null值，不允許使用聚合函數(shù)和子查詢。

5、count：統(tǒng)計項數(shù)值

count函數(shù)用于計算滿足條件的數(shù)據(jù)項數(shù)，返回int數(shù)據(jù)類型的值。這里的表達(dá)式是除text、image或ntext以外任何數(shù)據(jù)類型的表達(dá)式。但不允許使用聚合函數(shù)和子查詢。

• count(*) ： 返回所有的項數(shù)，包括null值和重復(fù)項。而除了count(*)外，其他任何形式的count()函數(shù)都會忽略Null行。
• count(all 表達(dá)式)：返回非空的項數(shù)。
• count(distinct 表達(dá)式)：返回唯一非空的項數(shù)

注意：count(字段名)，如果字段名為NULL，則count函數(shù)不會統(tǒng)計。例如count(name)，如果name為空，則不會統(tǒng)計到結(jié)果

select count(distinct age) from person    -- 查詢person表里的年齡唯一且非空的項數(shù)

6、count_big：統(tǒng)計項數(shù)量

返回組中的項數(shù)。 COUNT_BIG 的用法與 COUNT 函數(shù)類似。 兩個函數(shù)唯一的差別是它們的返回值。 COUNT_BIG 始終返回 bigint 數(shù)據(jù)類型值。 COUNT 始終返回 int 數(shù)據(jù)類型值。

7、差值函數(shù)

1、stdev：計算標(biāo)準(zhǔn)偏差值　　

這里的expression必須是一個數(shù)值表達(dá)式，不允許使用聚合函數(shù)和子查詢。表達(dá)式的值是精確或近似數(shù)值類型，但不包括bit數(shù)據(jù)類型。將忽略null值。

2、stdevp：計算總體標(biāo)準(zhǔn)偏差

返回指定表達(dá)式中所有值的總體標(biāo)準(zhǔn)偏差。

3、var：計算方差

VAR函數(shù)用于計算指定表達(dá)式中所有值的方差?！∵@里的expression表達(dá)式必須是一個數(shù)值表達(dá)式，不允許使用聚合函數(shù)和子查詢。表達(dá)式的值是精確或近似數(shù)值類型，但不包括bit數(shù)據(jù)類型，將忽略null值。

4、varp：計算總體統(tǒng)計方差

返回指定表達(dá)式中所有值的總體統(tǒng)計方差。

8、checksum_agg：計算組中各值的校驗和

返回組中各值的校驗和。 將忽略 Null 值。CHECKSUM_AGG 可用于檢測表中的更改。表中行的順序不影響 CHECKSUM_AGG 的結(jié)果。此外，CHECKSUM_AGG 函數(shù)還可與 DISTINCT 關(guān)鍵字和 GROUP BY 子句一起使用。如果表達(dá)式列表中的某個值發(fā)生更改，則列表的校驗和通常也會更改。但只在極少數(shù)情況下，校驗值會保持不變。

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

參數(shù)說明：　

• ALL：對所有的值進(jìn)行聚合函數(shù)運算。 ALL 為默認(rèn)值。
• DISTINCT ：指定 CHECKSUM_AGG 返回唯一校驗值。
• expression ：一個整數(shù)表達(dá)式。 不允許使用聚合函數(shù)和子查詢。

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

顯示結(jié)果如下：

可見隨著表的更改，該系統(tǒng)函數(shù)返回的值也變了。此函數(shù)的作用正在于此，檢測表的更改。

9、string_agg：串聯(lián)字符串

MS SQL Server的2017新增了STRING_AGG()是一個聚合函數(shù)，它將由指定的分隔符分隔將字符串行連接成一個字符串。 它不會在結(jié)果字符串的末尾添加分隔符。

以下是STRING_AGG()函數(shù)的語法：

STRING_AGG ( input_string, separator ) [ order_clause ]

在這個語法中：

• input_string是串聯(lián)時可以轉(zhuǎn)換為VARCHAR和NVARCHAR的類型。
• separator是結(jié)果字符串的分隔符。它可以是文字或變量。
• order_clause使用WITHIN GROUP子句指定連接結(jié)果的排序順序：

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

STRING_AGG()忽略NULL，并且在執(zhí)行連接時不會為NULL添加分隔符。

下面將使用示例數(shù)據(jù)庫中的sales.customers表進(jìn)行演示：

此示例使用STRING_AGG()函數(shù)生成城市客戶的電子郵件列表：

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

執(zhí)行上面查詢語句，得到以下結(jié)果：

要對email列表進(jìn)行排序，請使用WITHIN GROUP子句：

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

執(zhí)行上面查詢語句，得到以下結(jié)果：

注意：STRING_SPLIT（）函數(shù)：一個表值函數(shù)，它根據(jù)指定的分隔符將字符串拆分為子字符串行。

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

10、approx_count_distinct：唯一非空值的近似數(shù)

SQL Server 2019引入了新函數(shù)Approx_Count_distinct以提供行的近似計數(shù)。Count（distinct（））函數(shù)提供實際的行數(shù)。

該函數(shù)APPROX_COUNT_DISTINCT應(yīng)該使用較少的內(nèi)存和CPU資源，以便可以獲取數(shù)據(jù)結(jié)果而不會出現(xiàn)任何問題，例如溢出到磁盤或CPU峰值。這對于數(shù)十億行的需求很有用。

11、cube或 rollup 匯總運算符

• CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。
• ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。

查詢出插入的全部數(shù)據(jù)：

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 列：

當(dāng)行由 CUBE 或 ROLLUP 運算符添加時，該函數(shù)將導(dǎo)致附加列的輸出值為 1；當(dāng)行不由 CUBE 或 ROLLUP 運算符添加時，該函數(shù)將導(dǎo)致附加列的輸出值為 0。

僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相關(guān)聯(lián)的選擇列表中才允許分組。

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

13、grouping_id：計算分組級別

僅當(dāng)指定了 GROUP BY 時，GROUPING_ID 才能在 SELECT 列表、HAVING 或 ORDER BY 子句中使用。 使用 GROUPING_ID 標(biāo)識分組級別下面的示例返回按 AdventureWorks2012 數(shù)據(jù)庫的 Name 和 Title 匯總的雇員計數(shù)以及 Name, 和公司總計。 GROUPING_ID() 用于為 Title 列中的每行創(chuàng)建一個值以標(biāo)識聚合級別。

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 :聚合開窗函數(shù)

很多聚合函數(shù)都可以用作窗口函數(shù)的運算，如SUM,AVG,MAX,MIN。聚合開窗函數(shù)只能使用PARTITION BY子句或都不帶任何語句，ORDER BY不能與聚合開窗函數(shù)一同使用。例如，查詢雇員的定單總數(shù)及定單信息。

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

如果窗口函數(shù)不使用PARTITION BY 語句的話，那么就是不對數(shù)據(jù)進(jìn)行分組，聚合函數(shù)計算所有的行的值。

WITH OrderInfo AS
(
  SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)

到此這篇關(guān)于SQL Server聚合函數(shù)的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評論