SQL Server中聚合函數的用法

Posted on by

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

除瞭 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聚合函數的文章就介紹到這瞭。希望對大傢的學習有所幫助，也希望大傢多多支持WalkonNet。

推薦閱讀：