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

數(shù)數(shù)，

和，

平均數(shù)，

或獲取最小值或最大值。

但還有更多。

 

 

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

那么，這有多容易呢？

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

讓我們開始吧。

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

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

何時(shí)使用

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

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

計(jì)算表格中的記錄

學(xué)習(xí) COUNT 的第一個(gè)也是最簡(jiǎn)單的方法是計(jì)算表中的所有記錄。這包括 NULL 和重復(fù)項(xiàng)。

計(jì)數(shù)非空值

 

 

 

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

 

 

 

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

 

計(jì)數(shù)唯一值

 

 

 

如果有 2 個(gè)或更多Smith或Cruz中間名，則將其計(jì)為 1 個(gè)。

 

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

 

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

 

 

 

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

 

 

 

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

 

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

 

 

 

使用 GROUP BY 生成此查詢比使用 OVER 子句更快。我們?cè)趺粗滥?？通過使用此處介紹的執(zhí)行計(jì)劃和 STATISTICS IO  。您可以稍后查看。

 

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

 

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

 

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

 

但是，速度有問題。

 

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

 

SQL SUM 函數(shù)

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

 

何時(shí)使用

 

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

 

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

一段時(shí)間的格式化總和

 

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

 

 

 

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

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

 

 

 

 

 

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

 

SQL 平均函數(shù)

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

 

何時(shí)使用

 

您可以使用它來計(jì)算以下各項(xiàng)的平均值：

 

每月銷售額，

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

日常客戶投訴，

等等。

 

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

使用帶條件的 AVG()

 

這是使用 AVG 的一個(gè)簡(jiǎn)單直接的示例。

 

 

 

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

 

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

 

 

 

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

 

 

 

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

 

SQL MIN 函數(shù)

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

 

何時(shí)使用

 

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

 

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

將 MIN 與子查詢一起使用

 

 

 

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

 

 

 

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

SQL MAX 函數(shù)

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

 

何時(shí)使用

 

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

 

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

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

 

您可以將MAX 與 SQL Server 中的 MIN 函數(shù)一起使用，并在一個(gè)查詢中使用 COUNT。看看下面的例子。

 

 

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

 

 

 

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

使用 MAX 和日期

 

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

 

 

 

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

 

 

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

 

 

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

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

 

何時(shí)使用

 

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

 

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

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

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

等等。

方差的結(jié)果往往被夸大。因此，測(cè)量變異性的流行選擇是標(biāo)準(zhǔn)偏差。稍后會(huì)詳細(xì)介紹標(biāo)準(zhǔn)偏差。

 

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

對(duì)于此項(xiàng)目，我們只有 1 個(gè)示例，但需要對(duì)其進(jìn)行一些解釋才能使其實(shí)用。

 

數(shù)據(jù)準(zhǔn)備

 

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

 

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

 

問題的簡(jiǎn)要背景

 

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

 

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

 

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

 

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

 

失望的程度可以使用統(tǒng)計(jì)方差來衡量。當(dāng)需要更長(zhǎng)的時(shí)間時(shí)，方差會(huì)增加。

 

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

 

帶有 VAR 和 VARP 的示例 SQL 代碼

 

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

 

 

 

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

 

 

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

 

分析

在 Client1 支付服務(wù)費(fèi)用的上下文中，請(qǐng)注意結(jié)果中的這 4 件事。注意事項(xiàng)在圖 7 中編號(hào)。

 

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

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

2021 年 3 月的差異最大。方差越大，我們?cè)秸f“糟透了！” 當(dāng)然，這取決于您可以容忍不付款的時(shí)間。您能想象最多需要 90 到 120 天嗎？

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

 

您可能會(huì)問，為什么要同時(shí)使用樣本方差和總體方差？重點(diǎn)是什么？

 

在我們的例子中，重點(diǎn)是展示一個(gè)例子。但在現(xiàn)實(shí)世界中，您必須選擇一個(gè)。我們的數(shù)據(jù)包含 1 個(gè)客戶和 1 個(gè)服務(wù)提供商?？雌饋硭谴笮蛿?shù)據(jù)集的子集。如果是這種情況，樣本方差就有意義了。如果我們只對(duì) Client 1 和 Service Provider 1 感興趣，那么這個(gè)子集就是我們唯一需要的信息。因此，這使得總體方差適用。然而，如果這是唯一的數(shù)據(jù)，那么總體方差更有意義。

 

但是當(dāng)您說方差是 29.36 時(shí)，涉及的單位是什么？此外，等待付款的最長(zhǎng)期限僅為17天。這就是為什么方差令人困惑而標(biāo)準(zhǔn)偏差更有意義。

 

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

 

STDEV() 返回?cái)?shù)字?jǐn)?shù)據(jù)集中代表或樣本的統(tǒng)計(jì)標(biāo)準(zhǔn)偏差。同時(shí)，如果數(shù)據(jù)來自完整或整體人口，則使用 STDEVP()。

 

與方差不同，標(biāo)準(zhǔn)差處理用于計(jì)算平均值或平均值的單位。這些單位可以是天、小時(shí)、美元、點(diǎn)、米或任何需要的計(jì)量單位。

 

標(biāo)準(zhǔn)偏差是方差的平方根。它還說明一個(gè)值與平均值的距離。

 

何時(shí)使用

 

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

 

以下是更多用例：

 

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

研究 45 至 65 歲男性心臟病風(fēng)險(xiǎn)標(biāo)志物

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

 

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

 

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

 

 

 

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

 

 

圖 8. 具有標(biāo)準(zhǔn)偏差和方差的結(jié)果集。

 

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

 

STRING_AGG SQL 函數(shù)

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

 

何時(shí)使用

 

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

 

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

 

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

 

 

 

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

 

 

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

 

CHECKSUM_AGG SQL 函數(shù)

CHECKSUM_AGG 返回?cái)?shù)據(jù)集中值的校驗(yàn)和。

 

何時(shí)使用

 

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

 

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

 

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

 

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

 

 

 

這是在做任何更改之前 2 的校驗(yàn)和：

 

 

 

圖 10. Products 表的原始和副本的校驗(yàn)和。校驗(yàn)和是一樣的。

 

現(xiàn)在讓我們從臨時(shí)表中刪除一些記錄。然后，我們?cè)俅蔚玫叫ｒ?yàn)和。

 

 

 

刪除記錄后校驗(yàn)和的變化見圖11。

 

 

 

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

 

但是請(qǐng)注意，計(jì)算出的校驗(yàn)和幾乎不會(huì)發(fā)生變化。

 

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

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

 

同時(shí)， GROUPING_ID 計(jì)算分組級(jí)別。

 

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

何時(shí)使用

 

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

 

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

 

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

 

 

 

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

 

 

 

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

 

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

 

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

 

讓我們?cè)谙旅娴拇a中使用它們。

 

 

 

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

 

 

 

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

 

注意到圖 13 中的方框部分了嗎？我們?cè)谇懊娴拇a中使用了指示的分組級(jí)別和組標(biāo)志。這些級(jí)別和標(biāo)志用于格式化輸出、刪除空值并添加更好的標(biāo)題。

 

這好多了。

 

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

 

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

 

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

 

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

 

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

 

像這樣？然后請(qǐng)?jiān)谀钕矚g的社交媒體平臺(tái)上分享。

 

 

Edwin Sanchez

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