SQL Server中聚合函數的用法

聚合函數對一組值執行計算，並返回單個值。

除了 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 彙總運運算元

• CUBE 生成的結果集顯示了所選列中值的所有組合的聚合。
• 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)
)

到此這篇關於SQL Server聚合函數的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。