MySQL8.0-分組函式ROLLUP的基本用法（GROUPING）

業餘磚家發表於2023-05-09
MySql函式

一、ROLLUP簡介

ROLLUP是GROUP BY子句的擴充套件。 ROLLUP選項允許包含表示小計的額外行，通常稱為超級聚合行，以及總計行。 透過使用ROLLUP選項，可以使用單個查詢生成多個分組集。

MySQL中的ROLLUP是用於 產生彙總輸出的修飾符，包括表示超級彙總(較高階別)彙總操作的額外行。它使我們能夠使用單個查詢在多個分析級別上彙總輸出。它主要用於為OLAP(線上分析處理)操作提供支援。

二、基本語法

以下是使用ROLLUP修飾符的語法:

SELECT 
    column1, column2, ...
FROM 
    table_name
GROUP BY
column1, column2,... WITH ROLLUP;

我們指定了 GROUP BY 子句，其中包括我們要基於其聚合資料的列名。最後，我們指定 WITH ROLLUP 修飾符以在另一行中獲得超級聚合輸出。

我們已經瞭解到GROUP BY查詢已與諸如 MAX ， MIN ， SUM ， COUNT ， AVG 等將輸出行按單列或多列進行分組。 ROLLUP修飾符是使用GROUP BY查詢的選項，該查詢包含用於表示小計的額外欄位。這些額外的行稱為超級彙總行，是總計行的總和。因此，ROLLUP修飾符使我們可以基於 GROUP BY子句中指定的列，在單個查詢中建立一組集合行。

三、ROLLUP的使用

例如，假設我們有一個包含以下資料的表 " sales" :

CREATE TABLE IF NOT EXISTS sales
(
id      BIGINT   COMMENT  'ID',
years   INT  COMMENT '年份',
country VARCHAR(20)  COMMENT '國家',
product VARCHAR(32)  COMMENT  '產品',
sales  INT  COMMENT '銷售額',
PRIMARY KEY(id)
);

insert into  sales VALUES
(1,2000,'Finland','Computer',1500),
(2,2000,'Finland','Phone',100),
(3,2000,'India','Calculator',150),
(4,2000,'India','Computer',1200),
(5,2000,'USA','Calculator',75),
(6,2000,'USA','Computer',1500),
(7,2001,'Finland','Phone',100),
(8,2001,'USA','Calculator',50),
(9,2001,'USA','Computer',2700),
(10,2001,'USA','TV',250);

如果要每年總結結果，我們將使用簡單的GROUP BY子句，如下所示:

SELECT
years,
SUM(sales) AS total_sales  
FROM  sales  
GROUP BY years;

它將給出以下輸出，顯示每年的總銷售額(總計):

在上面的查詢中，分組集由列名稱Year表示。如果我們需要在單個查詢中一起生成多個分組集，則可以使用UNION ALL運算子，如下所示:

SELECT
years,
SUM(sales) AS total_sales
FROM  sales  
GROUP BY years
UNION ALL
SELECT
null as years,
SUM(sales) AS  total_sales
FROM  sales ;

在此查詢中，我們可以看到NULL列。這是因為 UNION ALL子句要求所有查詢具有相同的列數。因此，為了滿足此要求，我們在第二個查詢的選擇列表中新增了NULL。

在執行查詢時，將獲得以下輸出:

年份列的輸出中的NULL表示超級總計的總計值。由於此查詢能夠產生每年的總銷售額以及總銷售額的總和，因此，它有兩個問題:

這使查詢很長。由於資料庫引擎在內部執行兩個單獨的查詢並將結果集組合為一個輸出，因此降低了查詢的效能。

為解決這些問題，MySQL允許我們使用ROLLUP子句，該子句在一個查詢中提供了兩種分析級別。 ROLLUP子句是GROUP BY子句的擴充套件，該子句產生另一行並顯示總計(超級聚合)值。

讓我們在向WITH BY ROLLUP修飾符新增後看到結果子句顯示所有年份的總計:

SELECT
years,
SUM(sales) AS total_sales
FROM sales
GROUP BY years WITH ROLLUP;

執行命令時，將得到如下輸出:

在此輸出中，我們可以在 Year列中看到 NULL 值，該值標識了超級彙總行。它清楚地表明ROLLUP子句不僅生成小計，而且給出了全年的總銷售額。

如果GROUP BY子句有多個列，則ROLLUP修飾符具有更多的列。複雜的效果。在這種情況下， ROLLUP修飾符假定GROUP BY子句中指定的列之間的層次結構。每次列值發生變化時，查詢都會在結果末尾生成一個額外的超級彙總摘要行。

例如，假設我們在GROUP BY子句中指定了三列為下方:

GROUP BY c1, c2, c3 WITH ROLLUP

ROLLUP修飾符采用以下層次結構:

c1 > c2 > c3

並生成以下分組集:

(c1, c2, c3)

(c1, c2)

(c1)

()

請參閱以下查詢以更清楚地說明它:

SELECT
years,
country,
product,
SUM(sales) AS total_sales
FROM sales
GROUP BY years, country, product ;

如果沒有ROLLUP，則基於GROUP BY子句中指定的多個列的銷售表摘要如下所示。在這裡，我們將僅在年份/國家/產品分析級別獲得摘要值。

新增ROLLUP後，查詢會產生一些額外的行:

SELECT
years,
country,
product,
SUM(sales) AS total_sales
FROM sales
GROUP BY years, country, product WITH ROLLUP;

請參見以下輸出:

以上輸出在以下四個級別生成資訊: 分析，說明如下:

首先，給定年份和國家/地區的每組產品行都會生成一個額外的超級彙總摘要行，該行顯示所有產品的總數。它將產品列設定為NULL。接下來，給定年份的每組行都會生成一個額外的超級彙總摘要行，該行顯示所有國家和產品的總計。它將"國家和地區"和"產品"列設定為NULL。最後，對於所有其他行，它會生成一個額外的超級彙總摘要行，該行顯示所有列的總計。它將Years，Country和Products列設定為NULL。

四、GROUPING()函式

GROUPING()函式用於檢查結果集中的NULL是否表示常規分組值，超彙總值或總計。當超級彙總行中出現NULL時，它返回1、否則，它將返回0。

我們可以在選擇列表， HAVING 子句和 ORDER BY 子句中使用GROUPING()函式。

請參閱以下查詢:

SELECT
years,
country,
product,
SUM(sales) AS total_sales,
GROUPING(years),
GROUPING(country),
GROUPING(product)
FROM sales
GROUP BY years, country, product WITH ROLLUP;

當超級集合行中 Years 列中的NULL出現時， GROUPING(Years)返回1，我們將得到以下輸出。否則，它將返回零。

類似地，當"超級彙總"行中"國家/地區"列中的NULL為空時， GROUPING(Country)將返回1、否則，它將返回零。

此外，當超級彙總行中"產品"列中的NULL出現時， GROUPING(produce)返回1、否則，它將返回零。

我們還可以使用GROUPING()函式來替代超級彙總NULL值的有意義的標籤，而不是直接顯示。

以下查詢說明了如何將 IF()函式與GROUPING()函式組合以替換標籤"年份"，"國家/地區"和"產品"列中的超級彙總NULL值:

SELECT
 IF(GROUPING(years), 'All years', years) AS years,
 IF(GROUPING(country), 'All countries', country) AS country,
 IF(GROUPING(product), 'All products', product) AS product,
 SUM(sales) AS Total_Sales
FROM sales
GROUP BY years, country, product WITH ROLLUP;

我們將得到如下輸出:

如果我們在其中有多個引數GROUPING()函式，它將返回代表位掩碼的輸出，該掩碼合併了每個表示式的結果。在這裡，最低位產生最右邊引數的結果。下面的示例將按以下方式進行評估:

示例: 分組(年份，國家/地區，產品)

  result for GROUPING(product)

+ result for GROUPING(country) << 1

+ result for GROUPING(years) << 2

使用多個引數GROUPING()函式示例：

SELECT
years,
country,
product,
SUM(sales) AS Total_Sales,
GROUPING(years,country,product) AS Grouping_Result
FROM sales
GROUP BY years, country, product WITH ROLLUP;

它將給出以下輸出:

如果任何引數具有超級聚合的NULL值，則此GROUPING()的結果將為非零。在這種情況下，它將僅返回超級聚集的行，並使用以下查詢過濾常規的分組行:

SELECT
years,
country,
product,
SUM(sales) AS Total_Sales
FROM sales
GROUP BY years, country, product WITH ROLLUP
HAVING GROUPING(years, country, product) <> 0;

它將給出以下輸出:

 

相關文章