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

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

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

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

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

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

1、avg：平均值

avg( [ all | distinct ] expression )

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

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

2、min：最小值

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

3、max：最大值

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

4、sum：求和值

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

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

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

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

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

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

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

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

7、差值函數(shù)

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

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

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

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

3、var：計(jì)算方差

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

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

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

8、checksum_agg：計(jì)算組中各值的校驗(yàn)和

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

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

參數(shù)說(shuō)明：　

• ALL：對(duì)所有的值進(jìn)行聚合函數(shù)運(yùn)算。 ALL 為默認(rèn)值。
• DISTINCT ：指定 CHECKSUM_AGG 返回唯一校驗(yàn)值。
• expression ：一個(gè)整數(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é)果如下：

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

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

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

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

STRING_AGG ( input_string, separator ) [ order_clause ]

在這個(gè)語(yǔ)法中：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

11、cube或 rollup 匯總運(yùn)算符

• 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 運(yùn)算符添加時(shí)，該函數(shù)將導(dǎo)致附加列的輸出值為 1；當(dāng)行不由 CUBE 或 ROLLUP 運(yùn)算符添加時(shí)，該函數(shù)將導(dǎo)致附加列的輸出值為 0。

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

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

13、grouping_id：計(jì)算分組級(jí)別

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

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

很多聚合函數(shù)都可以用作窗口函數(shù)的運(yùn)算，如SUM,AVG,MAX,MIN。聚合開(kāi)窗函數(shù)只能使用PARTITION BY子句或都不帶任何語(yǔ)句，ORDER BY不能與聚合開(kāi)窗函數(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 語(yǔ)句的話，那么就是不對(duì)數(shù)據(jù)進(jìn)行分組，聚合函數(shù)計(jì)算所有的行的值。

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

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

最新評(píng)論