3.8 SQL Server 數據分組 · 軟件測試

SQL Server中分組查詢通常用于配合聚合函數，實現分類匯總統計的信息。而其分類匯總的本質實際上就是先將信息排序，排序后相同類別的信息會聚在一起，然后通過需求進行統計計算。 SQL Server中分組查詢通常用于配合聚合函數，實現分類匯總統計的信息。而其分類匯總的本質實際上就是先將信息排序，排序后相同類別的信息會聚在一起，然后通過需求進行統計計算。 SQL Server中常用的數據分組相關查詢如下： >[danger] ## GROUP BY- 根據指定列表達式列表中的值對查詢結果進行分組。 ## SQL Server GROUP BY子句簡介 `GROUP BY`子句用于按分組排列查詢的行。這些分組由在`GROUP BY`子句中指定的列確定。以下是`GROUP BY`子句的語法： ~~~sql SELECT select_list FROM table_name GROUP BY column_name1, column_name2 ,...; ~~~ 在此查詢語法中，`GROUP BY`子句為列中的每個值組合生成一個組。請考慮以下示例： ~~~sql SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/4b/fb/4bfbf2dbf75f7f94efb75bb3abd06b2e_599x527.png) 在此示例中，檢索了客戶ID為`1`，列是：`customer_id`和`order_date`。從輸出中可以清楚地看到，ID為`1`的客戶在`2016`年下了一個訂單，在`2018`年下了兩個訂單。ID為`2`的客戶在`2017`年下了兩個訂單，在`2018`年下了一個訂單。在查詢中添加一個`GROUP BY`子句來查看效果： ~~~sql SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/d7/af/d7af39c70760b274519c4cab1b53c21d_597x526.png) `GROUP BY`子句將前三行分為兩組，接下來的三行分為另外兩組，具有客戶ID和訂單年份的唯一組合。從功能上講，上面查詢中的`GROUP BY`子句產生的結果與使用`DISTINCT`子句的以下查詢的結果相同： ~~~sql SELECT DISTINCT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/3b/da/3bda72f677862ce4388a827ea32ffa48_598x526.png) #### 1\. GROUP BY子句和聚合函數實際上，`GROUP BY`子句通常與聚合函數一起用于生成摘要報告。聚合函數對組執行計算并返回每個組的唯一值。例如，`COUNT()`函數返回每個組中的行數。其他常用的聚合函數是：`SUM()`，`AVG()`，`MIN()`，`MAX()`。 `GROUP BY`子句將行排列成組，聚合函數返回每個組的摘要(總數量，最小值，最大值，平均值，總和等)。例如，以下查詢返回客戶按年度下達的訂單數： ~~~sql SELECT customer_id, YEAR (order_date) order_year, COUNT (order_id) 訂單數量 FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/a9/05/a90512a664b5dc203b786ff24d9d748f_596x527.png) 如果要引用`GROUP BY`子句中未列出的任何列或表達式，則必須使用該列作為聚合函數的輸入。否則，數據庫系統將會提示錯誤，因為無法保證列或表達式將為每個組返回單個值。例如，以下查詢將失敗： ~~~sql SELECT customer_id, YEAR (order_date) order_year, order_status FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; ~~~ > 這是因為`order_status`列未在`GROUP BY`子句中。 #### 2\. 更多GROUP BY子句示例下面再舉幾個例子來理解`GROUP BY`子句的工作原理。 **2.1. 帶有COUNT()函數示例的GROUP BY子句** 以下查詢返回每個城市的客戶數量： ~~~sql SELECT city, COUNT (customer_id) customer_count FROM sales.customers GROUP BY city ORDER BY city; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/7e/eb/7eeba16a6602b95fce4c0e8957ecc135_598x522.png) 在此示例中，`GROUP BY`子句按城市將客戶分組，`COUNT`函數返回每個城市的客戶數。同樣，以下查詢按州和城市返回客戶數量。 ~~~sql SELECT city, state, COUNT (customer_id) customer_count FROM sales.customers GROUP BY state, city ORDER BY city, state; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/e2/d8/e2d8447aebfdeb223d0dc47e461be55a_599x528.png) **2.2. GROUP BY子句帶有MIN和MAX函數示例** 以下聲明返回所有型號年份為`2018`的最低和最高價產品： ~~~sql SELECT brand_name, MIN (list_price) min_price, MAX (list_price) max_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/b4/6d/b46d0a93f2d860c131d6b5185015a3d5_596x527.png) 在此示例中，`WHERE`子句在`GROUP BY`子句之前。 **2.3. 帶有AVG()函數示例的GROUP BY子句** 以下語句使用`AVG()`函數返回型號年份為`2018`年的所有產品的平均價格： ~~~sql SELECT brand_name, AVG (list_price) avg_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/47/ea/47ea415c5bbd08e3f5cae5f30c0d1e84_597x523.png) **2.4. 帶有SUM函數示例的GROUP BY子句** 請參閱以下`order_items`表： ![](https://www.yiibai.com/uploads/article/2019/02/20/171200_52615.png) 以下查詢使用`SUM()`函數獲取每個訂單的凈值： ~~~sql SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/8b/a2/8ba2bde750aa9b5f4994320a3d57d039_596x525.png) >[danger] ## HAVING - 指定組或聚合的搜索條件。在本教程中，將學習如何使用SQL Server `HAVING`子句根據指定的條件篩選組。 `HAVING`子句通常與GROUP BY子句一起使用，以根據指定的條件列表過濾分組。以下是`HAVING`子句的語法： ~~~sql SELECT select_list FROM table_name GROUP BY group_list HAVING conditions; ~~~ 在此語法中，`GROUP BY`子句將行匯總為分組，`HAVING`子句將一個或多個條件應用于這些每個分組。只有使條件評估為`TRUE`的組才會包含在結果中。換句話說，過濾掉條件評估為`FALSE`或`UNKNOWN`的組。因為SQL Server在`GROUP BY`子句之后處理`HAVING`子句，所以不能通過使用列別名來引用選擇列表中指定的聚合函數。以下查詢將失敗： ~~~sql SELECT column_name1, column_name2, aggregate_function (column_name3) column_alias FROM table_name GROUP BY column_name1, column_name2 HAVING column_alias > value; ~~~ 必須明確使用`HAVING`子句中的聚合函數表達式，如下所示： ~~~sql SELECT column_name1, column_name2, aggregate_function (column_name3) alias FROM table_name GROUP BY column_name1, column_name2 HAVING aggregate_function (column_name3) > value; ~~~ ## SQL Server HAVING示例下面舉一些例子來理解`HAVING`子句的工作原理。 #### 1\. HAVING子句與COUNT函數示例請參閱示例數據庫中的以下`orders`表： ![](https://www.yiibai.com/uploads/article/2019/02/21/093953_42725.png) 以下聲明查找每年至少下過兩個訂單的客戶： ~~~sql SELECT customer_id, YEAR (order_date), COUNT (order_id) order_count FROM sales.orders GROUP BY customer_id, YEAR (order_date) HAVING COUNT (order_id) >= 2 ORDER BY customer_id; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/14/91/1491653564436703a94ddf616eefbe3e_595x527.png) 在上面查詢示例中， * 首先，`GROUP BY`子句按客戶和訂單年份對銷售訂單進行分組。 `COUNT()`函數返回每個客戶每年下達的訂單數。 * 其次，`HAVING`子句篩選出訂單數至少為`2`的所有客戶。 #### 2\. HAVING子句與SUM()函數的例子請考慮以下`order_items`表： ![](https://www.yiibai.com/uploads/article/2019/02/21/094308_46588.png) 以下語句查找凈值大于`20000`的銷售訂單： ~~~sql SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id HAVING SUM ( quantity * list_price * (1 - discount) ) > 20000 ORDER BY net_value; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/47/c5/47c502ffff416cbf5099bab6f6944c98_640x530.png) 在這個例子中： * 首先，`SUM`函數計算銷售訂單的凈值。 * 其次，`HAVING`子句過濾凈值小于或等于`20000`的銷售訂單。 #### 3\. HAVING子句與MAX和MIN函數的示例請參閱以下`products`表： ![](https://www.yiibai.com/uploads/article/2019/02/21/094541_69955.png) 以下語句首先查找每個產品類別中的最大和最小價格。然后，它篩選出最大價格大于`4000`或最小價格小于`500`的類別： ~~~sql SELECT category_id, MAX (list_price) max_list_price, MIN (list_price) min_list_price FROM production.products GROUP BY category_id HAVING MAX (list_price) > 4000 OR MIN (list_price) < 500; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/f4/9a/f49a207e49385f1ed986796538efa25e_639x528.png) #### 4\. HAVING子句與AVG()函數示例以下語句查找平均價格介于`500`和`1000`之間的產品類別： ~~~sql SELECT category_id, AVG (list_price) avg_list_price FROM production.products GROUP BY category_id HAVING AVG (list_price) BETWEEN 500 AND 1000; ~~~ 執行上面查詢語句，得到以下結果： ![](https://www.yiibai.com/uploads/article/2019/02/21/095420_36238.png) >[danger] ## GROUPING SETS - 生成多個分組集。 #### 設置銷售摘要表為了方便演示，下面創建一個名為`sales.sales_summary`的新表。 ~~~sql SELECT b.brand_name AS brand, c.category_name AS category, p.model_year, round( SUM ( quantity * i.list_price * (1 - discount) ), 0 ) sales INTO sales.sales_summary FROM sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY b.brand_name, c.category_name, p.model_year ORDER BY b.brand_name, c.category_name, p.model_year; ~~~ 在此查詢中，按品牌和類別檢索銷售額數據，并將其填充到`sales.sales_summary`表中。以下查詢從`sales.sales_summary`表返回數據： ![](https://img.kancloud.cn/04/7e/047e71b09504f85d38552e291fdd695c_639x524.png) #### SQL Server GROUPING SETS入門根據定義，分組集是分組的一組列。通常，具有聚合的單個查詢定義單個分組集。例如，以下查詢定義了一個分組集，其中包括品牌和類別，表示為(品牌，類別)。查詢返回按品牌和類別分組的銷售額： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, category ORDER BY brand, category; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/cb/45/cb454564fd26d9529828e929da5ecc7a_639x532.png) 以下查詢按品牌返回銷售額。它定義了一個分組集(品牌)： ~~~sql SELECT brand, SUM (sales) sales FROM sales.sales_summary GROUP BY brand ORDER BY brand; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/8f/c1/8fc133d4e4878226b2c6c4f7ed00f708_642x527.png) 以下查詢按類別返回銷售額。它定義了一個分組集(類別)： ~~~sql SELECT category, SUM (sales) sales FROM sales.sales_summary GROUP BY category ORDER BY category; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/01/2e/012ec51e2833d49db3cc47632d37181f_634x497.png) 以下查詢定義空分組集。它返回所有品牌和類別的銷售額。 ~~~sql SELECT SUM (sales) sales FROM sales.sales_summary; ~~~ 執行上面查詢語句，得到以下結果： ![](https://www.yiibai.com/uploads/article/2019/02/21/100254_38181.png) 上面的四個查詢返回四個結果集，其中包含四個分組集： ~~~shell (brand, category) (brand) (category) () ~~~ 要使用所有分組集的聚合數據獲得統一的結果集，可以使用`UNION ALL`運算符。由于UNION ALL運算符要求所有結果集具有相同數量的列，因此需要將NULL添加到查詢的選擇列表中，如下所示： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, category UNION ALL SELECT brand, NULL, SUM (sales) sales FROM sales.sales_summary GROUP BY brand UNION ALL SELECT NULL, category, SUM (sales) sales FROM sales.sales_summary GROUP BY category UNION ALL SELECT NULL, NULL, SUM (sales) FROM sales.sales_summary ORDER BY brand, category; ~~~ 執行上面查詢語句，得到以下結果： ![](https://www.yiibai.com/uploads/article/2019/02/21/100720_22578.png) 該查詢生成了一個結果，其中包含了我們所期望的所有分組集的聚合。但是，它有以下兩個主要問題： * 查詢非常冗長(看起來是不是很累？) * 查詢很慢，因為SQL Server需要執行四個查詢并將結果集合并為一個查詢。為了解決這些問題，SQL Server提供了一個名為`GROUPING SETS`的`GROUP BY`子句的子句。 `GROUPING SETS`在同一查詢中定義多個分組集。以下是`GROUPING SETS`的一般語法： ~~~sql SELECT column1, column2, aggregate_function (column3) FROM table_name GROUP BY GROUPING SETS ( (column1, column2), (column1), (column2), () ); ~~~ 此查詢創建四個分組集： ~~~shell (column1,column2) (column1) (column2) () ~~~ 使用此`GROUPING SETS`重寫獲取銷售數據的查詢，如下所示： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY GROUPING SETS ( (brand, category), (brand), (category), () ) ORDER BY brand, category; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/1c/cf/1ccf9de2555b3fbb62f5067b923c6ee2_638x431.png) 如上所示，查詢產生的結果與使用`UNION ALL`運算符的結果相同。但是，此查詢更具可讀性，當然也更有效。 **GROUPING函數** `GROUPING`函數指示是否聚合`GROUP BY`子句中的指定列。它是聚合則返回`1`，或者為結果集是未聚合返回`0`。請參閱以下查詢示例： ~~~sql SELECT GROUPING(brand) grouping_brand, GROUPING(category) grouping_category, brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY GROUPING SETS ( (brand, category), (brand), (category), () ) ORDER BY brand, category; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/7b/9e/7b9ee912c64e43c6a102d221b041fbf5_635x428.png) `grouping_brand`列中的值表示該行是否已聚合，`1`表示銷售額按品牌匯總，`0`表示銷售金額未按品牌匯總。相同的概念應用于`grouping_category`列。 >[danger] ## CUBE - 生成包含維列的所有組合的分組集。 ## SQL Server CUBE簡介分組集在單個查詢中指定數據分組。例如，以下查詢定義表示為(品牌)的單個分組集： ~~~sql SELECT brand, SUM(sales) FROM sales.sales_summary GROUP BY brand; ~~~ 如果您沒有學習過GROUPING SETS的使用，可使用以下查詢創建`sales.sales_summary`表： ~~~sql SELECT b.brand_name AS brand, c.category_name AS category, p.model_year, round( SUM ( quantity * i.list_price * (1 - discount) ), 0 ) sales INTO sales.sales_summary FROM sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY b.brand_name, c.category_name, p.model_year ORDER BY b.brand_name, c.category_name, p.model_year; ~~~ 即使以下查詢不使用GROUP BY子句，它也會生成一個空的分組集，表示為`()`。 ~~~sql SELECT SUM(sales) FROM sales.sales_summary GROUP BY brand; ~~~ `CUBE`是`GROUP BY`子句的子句，用于生成多個分組集。以下是`CUBE`的一般語法： ~~~sql SELECT d1, d2, d3, aggregate_function (c4) FROM table_name GROUP BY CUBE (d1, d2, d3); ~~~ 在此語法中，`CUBE`根據在`CUBE`子句中指定的維度列：`d1`，`d2`和`d3`生成所有可能的分組集。上面的查詢返回與以下查詢相同的結果集，該查詢使用`GROUPING SETS`： ~~~sql SELECT d1, d2, d3, aggregate_function (c4) FROM table_name GROUP BY GROUPING SETS ( (d1,d2,d3), (d1,d2), (d1,d3), (d2,d3), (d1), (d2), (d3), () ); ~~~ 如果在多維數據集中指定了`N`維列，則將具有`2N`個分組集。通過部分使用`CUBE`可以減少分組集的數量，如以下查詢所示： ~~~sql SELECT d1, d2, d3, aggregate_function (c4) FROM table_name GROUP BY d1, CUBE (d2, d3); ~~~ 在這種情況下，查詢生成四個分組集，因為在`CUBE`中只指定了兩個維列。 ## SQL Server CUBE示例以下語句使用`CUBE`生成四個分組集： ~~~shell (brand, category) (brand) (category) () ~~~ 參考以下查詢語句： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY CUBE(brand, category); ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/ef/44/ef44275f2064b064602b0aa130e41806_638x525.png) 在此示例中，在`CUBE`子句中指定了兩個維列，因此，我們總共有四個分組集。以下示例說明如何執行部分`CUBE`以減少查詢生成的分組集的數量： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, CUBE(category); ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/e5/64/e5648ef71b8b28b5f3b0b98bfe050685_639x523.png) >[danger] ## ROLLUP - 生成分組集，假設輸入列之間存在層次結構。 ## SQL Server ROLLUP簡介 SQL Server `ROLLUP`是`GROUP BY`子句的子句，它提供了定義多個分組集的簡寫。與CUBE子句不同，`ROLLUP`不會根據維度列創建所有可能的分組集; `CUBE`是其中的一部分。生成分組集時，`ROLLUP`假定維度列之間存在層次結構，并且僅基于此層次結構生成分組集。 `ROLLUP`通常用于生成小計和總計來生成報告目的。考慮一個例子。以下`CUBE(d1，d2，d3)`定義了八個可能的分組集： ~~~sql (d1, d2, d3) (d1, d2) (d2, d3) (d1, d3) (d1) (d2) (d3) () ~~~ 并且`ROLLUP(d1，d2，d3)`僅創建四個分組集，假設層次結構`d1> d2> d3`，如下所示： ~~~sql (d1, d2, d3) (d1, d2) (d1) () ~~~ `ROLLUP`通常用于計算分層數據的聚合，例如：按年>季度>月的銷售額。 ## SQL Server ROLLUP語法 SQL Server `ROLLUP`的一般語法如下： ~~~sql SELECT d1, d2, d3, aggregate_function(c4) FROM table_name GROUP BY ROLLUP (d1, d2, d3); ~~~ 在此語法中，`d1`，`d2`和`d3`是維列。該語句將根據層次結構`d1> d2> d3`計算列`c4`中的值的聚合。還可以執行部分匯總以減少使用以下語法生成的小計： ~~~sql SELECT d1, d2, d3, aggregate_function(c4) FROM table_name GROUP BY d1, ROLLUP (d2, d3); ~~~ ## SQL Server ROLLUP示例在這個示例中將重用在GROUPING SETS教程中創建的`sales.sales_summary`表進行演示。如果尚未創建`sales.sales_summary`表，則可以使用以下語句創建它。 ~~~sql SELECT b.brand_name AS brand, c.category_name AS category, p.model_year, round( SUM ( quantity * i.list_price * (1 - discount) ), 0 ) sales INTO sales.sales_summary FROM sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY b.brand_name, c.category_name, p.model_year ORDER BY b.brand_name, c.category_name, p.model_year; ~~~ 以下查詢使用`ROLLUP`按品牌(小計)以及品牌和類別(總計)計算銷售額。 ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY ROLLUP(brand, category); ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/fe/03/fe03766d614a68327bb92f8233ec3fb6_640x528.png) 在此示例中，查詢假定品牌和類別之間存在層次結構，即：品牌>類別。請注意，如果更改品牌和類別的順序，結果將會有所不同，如以下查詢所示： ~~~sql SELECT category, brand, SUM (sales) sales FROM sales.sales_summary GROUP BY ROLLUP (category, brand); ~~~ 在此示例中，層次結構是類別>品牌，執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/a9/2a/a92a8fc557a9e165e5ff9183c21c9f7a_640x528.png) 以下語句顯示了如何執行部分匯總： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, ROLLUP (category); ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/e1/0d/e10d6946d7786cfabfa7b95fddddca70_641x527.png)