SQL 聚合函數(shù) 是執(zhí)行計算并返回匯總結(jié)果的函數(shù)。有了這些，可以很容易地對數(shù)據(jù)集進行這些計算：

數(shù)數(shù)，

和，

平均數(shù)，

或獲取最小值或最大值。

但還有更多。

 

 

標準偏差和方差等統(tǒng)計計算也是工具的一部分。將它與分組和排序相結(jié)合，您的新報告就會有一些奇妙的東西。

那么，這有多容易呢？

今天，提高您的技能，使用 SQL Server 支持的 SQL 聚合函數(shù)編寫 SQL 查詢。了解它們中的每一個以及何時可以使用它們。而且，當然，我們會得到實際的例子。

讓我們開始吧。

SQL 計數(shù)函數(shù)

SQL COUNT就是函數(shù)名所暗示的。這很重要。

何時使用

對于您希望計入數(shù)據(jù)的任何內(nèi)容。它可以是員工數(shù)量、銷售產(chǎn)品數(shù)量或經(jīng)過的天數(shù)。你說出它的名字。

SQL 中 COUNT 函數(shù)的實際示例

計算表格中的記錄

學習 COUNT 的第一個也是最簡單的方法是計算表中的所有記錄。這包括 NULL 和重復項。

計數(shù)非空值

 

 

 

也可以用ALL關(guān)鍵字表示。

 

 

 

如果去掉 ALL 關(guān)鍵字，SQL Server 假定您的意思是 ALL。這是默認設置。

 

計數(shù)唯一值

 

 

 

如果有 2 個或更多Smith或Cruz中間名，則將其計為 1 個。

 

按分區(qū)或分組計數(shù)

 

之前的所有 COUNT 示例都返回 1 行。現(xiàn)在，讓我們嘗試對結(jié)果進行分區(qū)。

 

 

 

這會計算所有以 A、B、C 等開頭的中間名。您可以在下面的圖 1 中看到結(jié)果。

 

 

 

圖1所示。 COUNT with OVER…PARTITION BY的結(jié)果集。

 

還有另一種寫法可以返回相同的結(jié)果。

 

 

 

使用 GROUP BY 生成此查詢比使用 OVER 子句更快。我們怎么知道呢？通過使用此處介紹的執(zhí)行計劃和 STATISTICS IO  。您可以稍后查看。

 

其他用于計數(shù)的 SQL 聚合函數(shù)

 

COUNT 返回一個 INT 數(shù)據(jù)類型。如果您需要計算高達 9,223,372,036,854,775,807 的大數(shù)據(jù)集，那么COUNT 將不起作用。您需要 COUNT_BIG。它返回一個適合計算非常大的數(shù)據(jù)集的 BIGINT 數(shù)據(jù)類型。它的功能與 COUNT 相同，但不同之處在于返回的數(shù)據(jù)類型。

 

COUNT(DISTINCT expression) 對于您的大數(shù)據(jù)集是否太慢？然后，使用 APPROX_COUNT_DISTINCT。它比 COUNT DISTINCT 使用更少的內(nèi)存占用。它適用于具有許多不同值的大型數(shù)據(jù)集。

 

但是，速度有問題。

 

APPROX_COUNT_DISTINCT 返回近似計數(shù)。如果您需要計算大的不同的值，速度比精度更重要，就是它了。 它以 97% 的概率保證最高 2% 的錯誤率。

 

SQL SUM 函數(shù)

SUM 用于對數(shù)據(jù)集中的數(shù)值求和。

 

何時使用

 

您可以使用它來計算每月總銷售額等。

 

SQL中SUM函數(shù)的實際例子

一段時間的格式化總和

 

下面是在條件下使用 SUM 的示例。結(jié)果用逗號分隔符格式化。

 

 

 

一段時間的組總數(shù)

此示例將添加 JOIN、GROUP BY 和 HAVING 子句的使用。請注意，您可以在 SELECT 列表和 HAVING 子句中使用 SQL 聚合函數(shù)，如 SUM。

 

 

 

 

 

圖 2. 將 SUM 與 GROUP BY 和 HAVING 子句一起使用的結(jié)果集。

 

SQL 平均函數(shù)

SQL AVG 函數(shù)用于獲取數(shù)據(jù)集中數(shù)值的平均值。

 

何時使用

 

您可以使用它來計算以下各項的平均值：

 

每月銷售額，

產(chǎn)品退貨數(shù)量，

日?？蛻敉对V，

等等。

 

SQL中AVG函數(shù)的實際例子

使用帶條件的 AVG()

 

這是使用 AVG 的一個簡單直接的示例。

 

 

 

條件與另一個 SQL 聚合函數(shù)混合的組平均值

 

在本例中，您可以將 AVG 與 SUM 混合使用。結(jié)果也被分組和排序。

 

 

 

數(shù)字使用數(shù)字分隔符進行格式化。查看下面的圖 3。

 

 

 

圖 3. 使用 AVG() 的查詢中的示例結(jié)果集。

 

SQL MIN 函數(shù)

使用 MIN 從一組值中獲取最小值。要求和的數(shù)據(jù)可以是數(shù)字、字符串或日期。

 

何時使用

 

有利于查找最便宜的產(chǎn)品，當前的月薪，最早的航班等等。

 

SQL中MIN函數(shù)的實際例子

將 MIN 與子查詢一起使用

 

 

 

將 MIN 與 GROUP BY 和 ORDER BY 一起使用

 

 

 

圖 4. 使用 MIN 獲取每個產(chǎn)品子類別的最低價格的查詢結(jié)果集。

SQL MAX 函數(shù)

使用 MAX 獲取一組值中的最大值或最后一個值。與 MIN 一樣，您可以使用它來聚合數(shù)字、字符串和日期。

 

何時使用

 

這有利于獲得有史以來票房最高的電影、最多的選票、最高的收入等等。

 

SQL MAX 函數(shù)的實際例子

將 MAX 與其他 SQL 聚合函數(shù)一起使用

 

您可以將MAX 與 SQL Server 中的 MIN 函數(shù)一起使用，并在一個查詢中使用 COUNT?？纯聪旅娴睦?。

 

 

查看下面圖 5 中每個子類別的產(chǎn)品定價的更好視圖。

 

 

 

圖 5. 結(jié)果集顯示每個子類別產(chǎn)品的最低和最高價格。

使用 MAX 和日期

 

除了數(shù)字之外，您還可以將 MAX 與日期一起使用，如下例所示。

 

 

 

上面的示例通過EmployeePayHistory 表中員工的最后一次費率變化獲取最新的工資率 。查看下面圖 6 中的結(jié)果。

 

 

圖 6. 對日期使用 MAX 函數(shù)的查詢的示例結(jié)果集。

 

 

SQL 中的 VAR 和 VARP 函數(shù)

使用 SQL VAR() 獲取 給定數(shù)字數(shù)據(jù)集中的代表或樣本的統(tǒng)計方差 。同時， VARP() 是針對 數(shù)字數(shù)據(jù)集的 整個群體，因此我們在VAR之后有P。

 

何時使用

 

與 SUM 或 AVG 相比，獲得方差不太常見。但在業(yè)務中，您可以在以下場景中使用它：

 

要知道股票是波動的還是穩(wěn)定的，

如果交付產(chǎn)品的時間越來越短，

如果客戶填寫網(wǎng)絡表單所需的時間比以前更好，

等等。

方差的結(jié)果往往被夸大。因此，測量變異性的流行選擇是標準偏差。稍后會詳細介紹標準偏差。

 

SQL 中 VAR 和 VARP 函數(shù)的實際示例

對于此項目，我們只有 1 個示例，但需要對其進行一些解釋才能使其實用。

 

數(shù)據(jù)準備

 

讓我們先準備數(shù)據(jù)。稍后我們將在方差和標準差示例中使用。

 

CREATE TABLE ServiceProviderTransactions

(

TransactionID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

ServiceProviderID INT,

ServiceID TINYINT,

ClientID INT,

TransactionDate DATETIME,

DatePaid DATETIME,

ServiceAmount DECIMAL(6,2)

)

GO

 

INSERT INTO ServiceProviderTransactions

(ServiceProviderID, ServiceID, ClientID, TransactionDate, DatePaid, ServiceAmount)

VALUES

(1,1,1,'05/29/2020 00:00','06/05/2020 16:47',350.00),

(1,1,1,'06/12/2020 00:00','06/24/2020 19:22',350.00),

(1,1,1,'06/24/2020 00:00','07/02/2020 18:20',350.00),

(1,1,1,'07/14/2020 00:00','07/14/2020 22:44',350.00),

(1,1,1,'07/31/2020 00:00','08/06/2020 21:53',350.00),

(1,1,1,'08/17/2020 00:00','09/02/2020 23:11',350.00),

(1,1,1,'09/14/2020 00:00','09/14/2020 22:51',350.00),

(1,1,1,'10/02/2020 00:00','10/09/2020 23:11',350.00),

(1,1,1,'10/15/2020 00:00','10/21/2020 23:13',350.00),

(1,1,1,'10/23/2020 00:00','10/29/2020 23:58',350.00),

(1,1,1,'10/30/2020 00:00','11/09/2020 23:44',350.00),

(1,1,1,'11/06/2020 00:00','11/11/2020 15:58',350.00),

(1,1,1,'11/11/2020 00:00','11/11/2020 15:58',350.00),

(1,1,1,'11/20/2020 00:00','11/27/2020 18:13',350.00),

(1,1,1,'12/04/2020 00:00','12/15/2020 01:52',350.00),

(1,1,1,'12/11/2020 00:00','12/23/2020 01:39',350.00),

(1,1,1,'12/18/2020 00:00','12/23/2020 20:30',350.00),

(1,1,1,'01/22/2021 12:43','01/28/2021 01:02',350.00),

(1,1,1,'02/04/2021 22:23','02/09/2021 17:33',350.00),

(1,1,1,'02/05/2021 15:48','02/13/2021 00:02',350.00),

(1,1,1,'02/12/2021 13:21','02/17/2021 08:03',350.00),

(1,1,1,'02/19/2021 22:58','02/24/2021 01:27',350.00),

(1,1,1,'02/26/2021 18:26','03/09/2021 20:23',350.00),

(1,1,1,'02/26/2021 18:44','03/09/2021 20:23',350.00),

(1,1,1,'03/05/2021 13:53','03/22/2021 20:27',350.00),

(1,1,1,'03/12/2021 13:41','03/22/2021 20:27',350.00),

(1,1,1,'03/19/2021 17:32','03/24/2021 19:49',350.00),

(1,1,1,'03/26/2021 14:29','04/01/2021 18:46',350.00),

(1,1,1,'03/31/2021 19:35','04/01/2021 21:54',350.00),

(1,1,1,'04/16/2021 21:23','04/19/2021 19:44',350.00),

(1,1,1,'04/23/2021 19:04','04/27/2021 20:43',350.00),

(1,1,1,'04/30/2021 20:00','05/11/2021 21:20',350.00),

(1,1,1,'05/07/2021 21:12','05/11/2021 21:20',350.00),

(1,1,1,'05/14/2021 14:56','05/18/2021 09:38',350.00),

(1,1,1,'05/21/2021 10:12','06/01/2021 16:52',350.00)

GO

 

問題的簡要背景

 

在我們進入編碼部分之前，我們會問一些與這些數(shù)據(jù)相關(guān)的問題。

 

您的客戶付錢給您，但他們需要多長時間才能付錢給您？當您被問到這樣的問題時，您會回答“我平均需要 10.3542 天才能得到付款”嗎？當然不是！通常的回答是，“有時我會在同一天收到報酬。但在其他日子，需要 60 天。當這種情況發(fā)生時，真是糟透了！”

 

當類似的情況發(fā)生在您身上時，您有同樣的感覺嗎？

 

這證明我們感覺不到平均水平。在產(chǎn)品交付等其他問題上也是如此。每當有交易時，您都需要 MIN 時間。但是您討厭MAX。當交貨或付款需要更長的時間時，會有一定程度的失望。

 

失望的程度可以使用統(tǒng)計方差來衡量。當需要更長的時間時，方差會增加。

 

您現(xiàn)在能理解我的意思嗎？然后，讓我們開始編碼。

 

帶有 VAR 和 VARP 的示例 SQL 代碼

 

我們將檢查客戶 1 每月向服務提供商 1 付款所需的天數(shù)的差異。這是代碼。

 

 

 

除了 VAR 和 VARP 之外，我們還使用了幾個 SQL 聚合函數(shù)。下面的圖 7 將幫助我們理解結(jié)果。

 

 

圖 7. 計算方差的查詢結(jié)果集。

 

分析

在 Client1 支付服務費用的上下文中，請注意結(jié)果中的這 4 件事。注意事項在圖 7 中編號。

 

數(shù)據(jù)中 0 或 null 的方差很簡單。僅存在 1 個事務。因此，也不存在變異。

2021 年 2 月和 3 月平均為 7 天。但是請注意它們對于樣本方差和總體方差的方差有多大。這就是為什么平均日子感覺不對勁的原因。

2021 年 3 月的差異最大。方差越大，我們越說“糟透了！” 當然，這取決于您可以容忍不付款的時間。您能想象最多需要 90 到 120 天嗎？

如果將#3 與#4 進行比較，您就會明白為什么 2021 年 3 月的差異最大。數(shù)據(jù)高度分散在 1 到 17 之間。

 

您可能會問，為什么要同時使用樣本方差和總體方差？重點是什么？

 

在我們的例子中，重點是展示一個例子。但在現(xiàn)實世界中，您必須選擇一個。我們的數(shù)據(jù)包含 1 個客戶和 1 個服務提供商。看起來它是大型數(shù)據(jù)集的子集。如果是這種情況，樣本方差就有意義了。如果我們只對 Client 1 和 Service Provider 1 感興趣，那么這個子集就是我們唯一需要的信息。因此，這使得總體方差適用。然而，如果這是唯一的數(shù)據(jù)，那么總體方差更有意義。

 

但是當您說方差是 29.36 時，涉及的單位是什么？此外，等待付款的最長期限僅為17天。這就是為什么方差令人困惑而標準偏差更有意義。

 

SQL 中的 STDEV SQL 和 STDEVP 函數(shù)

 

STDEV() 返回數(shù)字數(shù)據(jù)集中代表或樣本的統(tǒng)計標準偏差。同時，如果數(shù)據(jù)來自完整或整體人口，則使用 STDEVP()。

 

與方差不同，標準差處理用于計算平均值或平均值的單位。這些單位可以是天、小時、美元、點、米或任何需要的計量單位。

 

標準偏差是方差的平方根。它還說明一個值與平均值的距離。

 

何時使用

 

與方差一樣，標準差也是可變性的度量。因此，通常的用例將起作用。不過，在其他情況下，方差更有意義，但它們超出了本文的范圍。

 

以下是更多用例：

 

解讀民意調(diào)查數(shù)據(jù)。

研究 45 至 65 歲男性心臟病風險標志物

了解全國調(diào)查中年齡的變化。

 

STDEV SQL 和 STDEVP SQL 函數(shù)的實際示例

 

我們的標準差示例將只是用方差擴展前面的示例。這樣，我們可以將 2 與數(shù)據(jù)進行比較。

 

 

 

下面的圖 8 顯示了結(jié)果。

 

 

圖 8. 具有標準偏差和方差的結(jié)果集。

 

由于標準差是方差的平方根，所以結(jié)果是一個較小的值。然而，這更接近于我們的最小值、最大值和平均值。2021 年 3 月的標準偏差（圖 8 中的方框）也是最高的。其余部分講述了與方差相同的故事。

 

STRING_AGG SQL 函數(shù)

STRING_AGG() 連接行中的字符串，每行之間有一個分隔符。分隔符不會添加到最后一個字符串的末尾。

 

何時使用

 

為數(shù)據(jù)集成等制作文本。

 

SRTING_AGG SQL 函數(shù)的實際例子

 

我們的示例是根據(jù)姓名、出生日期和職位名稱形成以豎線分隔的文本數(shù)據(jù)。

 

 

 

查看下面圖 9 中的結(jié)果。它使用 SQL Server Management Studio 中的 數(shù)據(jù)查看器。

 

 

圖 9. 使用 STRING_AGG 生成以豎線分隔的文本數(shù)據(jù)的結(jié)果。

 

CHECKSUM_AGG SQL 函數(shù)

CHECKSUM_AGG 返回數(shù)據(jù)集中值的校驗和。

 

何時使用

 

您可以使用它來比較 2 個表是否相同。

 

SQL中CHECKSUM_AGG函數(shù)的實際例子

 

要使用它來比較 2 個表中的數(shù)據(jù)，讓我們 從AdventureWorks 數(shù)據(jù)庫中創(chuàng)建Products表 的臨時副本。然后我們得到原件和副本的校驗和。

 

除了 CHECKSUM_AGG()，我們還將使用 BINARY_CHECKSUM 來獲取行的校驗和。

 

 

 

這是在做任何更改之前 2 的校驗和：

 

 

 

圖 10. Products 表的原始和副本的校驗和。校驗和是一樣的。

 

現(xiàn)在讓我們從臨時表中刪除一些記錄。然后，我們再次得到校驗和。

 

 

 

刪除記錄后校驗和的變化見圖11。

 

 

 

圖 11. 從 Products 表副本中刪除記錄后 CHECKSUM_AGG 的結(jié)果。

 

但是請注意，計算出的校驗和幾乎不會發(fā)生變化。

 

SQL 中的 GROUPING 和 GROUPING_ID 函數(shù)

如果 GROUP BY 子句中使用的指示表達式被聚合，則 GROUPING 返回 1。否則，它返回 0。

 

同時， GROUPING_ID 計算分組級別。

 

這兩個函數(shù)都需要使用 GROUP BY。如果 GROUP BY 子句中沒有 ROLLUP、CUBE 或 GROUPING SETS，兩者都將返回零。因此，如果 GROUP BY 子句中存在這些關(guān)鍵字中的任何一個，則結(jié)果將是有意義的。

何時使用

 

當您的 SELECT 語句具有 GROUP BY 子句，并且您需要結(jié)果中的小計和總計時。

 

SQL 中 GROUPING 和 GROUPING_ID 函數(shù)的實際示例

 

要了解這是如何工作的，讓我們創(chuàng)建一個沒有這些SQL GROUPING 和 GROUPING_ID 函數(shù)的查詢。

 

 

 

就是這樣。我們在語句中有 GROUP BY ROLLUP?，F(xiàn)在，讓我們檢查下面圖 12 中的結(jié)果。

 

 

 

圖 12. 使用 GROUP BY ROLLUP 而沒有 GROUPING 和 GROUPING_ID 的查詢結(jié)果。

 

圖 12 中的那些空值是什么？

 

如果沒有 ROLLUP，這些將不會出現(xiàn)。它們是總計和小計。圖 12 中的方框部分是指示符。這些是頭盔子類別的小計和配件類別的總計。不太像樣，不是嗎？GROUPING 和 GROUPING_ID 將允許您格式化這些總計和小計。

 

讓我們在下面的代碼中使用它們。

 

 

 

我在查詢中添加了 GROUPING 和 GROUPING_ID 的輸出。這些輸出對于代碼中的 CASE WHEN 子句很有用。讓我們看看圖 13 中的輸出。

 

 

 

圖 13. 使用 GROUP BY ROLLUP 和 GROUPING 和 GROUPING_ID 的查詢結(jié)果集。

 

注意到圖 13 中的方框部分了嗎？我們在前面的代碼中使用了指示的分組級別和組標志。這些級別和標志用于格式化輸出、刪除空值并添加更好的標題。

 

這好多了。

 

使用 SQL 聚合函數(shù)的最重要內(nèi)容

 

那么，您對 SQL 聚合函數(shù)的了解如何？

 

這些函數(shù)適用于報告中的匯總計算。盡管其中一些也有其他目的。您還可以使用 GROUP BY 和 OVER PARTITION BY 對輸出進行分類。然后使用 ORDER BY 進行排序。

 

現(xiàn)代 SQL 工具使與聚合函數(shù)相關(guān)的所有任務都更加簡單明了。特別是，用于 SSMS的Devart SQL Complete 插件會自動計算 MIN、MAX、AVG、SUM、COUNT 和 DISTINCT 結(jié)果。您只需選擇SSMS 結(jié)果網(wǎng)格中的單元格即可查看準確的結(jié)果。

 

我希望對每個函數(shù)的討論及其實際示例對您有所幫助。

 

像這樣？然后請在您最喜歡的社交媒體平臺上分享。

 

 

Edwin Sanchez

軟件開發(fā)人員和項目經(jīng)理，擁有超過 20 年的軟件開發(fā)經(jīng)驗。他最近的技術(shù)偏好包括 C#、SQL Server BI Stack、Power BI 和 Sharepoint。Edwin 將他的技術(shù)知識與他最近的內(nèi)容寫作技巧相結(jié)合，以幫助新一代的技術(shù)愛好者。