SQL Rollup

在本教程中，您將學習如何使用SQL ROLLUP生成多個分組集。

1. SQL ROLLUP簡介

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

注意，分組集是一組用於分組的列。 例如，一個由倉庫返回庫存的查詢，分組集是(warehouse)。

SELECT
    warehouse, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse;

有關GROUPING SETS的更多信息，請查看分組集教程。

以下是SQL ROLLUP的基本語法：

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);

ROLLUP假定輸入列之間存在層次結構。 例如，如果輸入列是(c1，c2)，則層次結構c1> c2。 ROLLUP生成考慮此層次結構有意義的所有分組集。 這就是爲什麼我們經常使用ROLLUP來生成小計和總計以用於報告目的。

在上面的語法中，ROLLUP(c1，c2)生成以下三個分組集：

(c1,c2)
(c1)
()

Oracle，Microsoft SQL Server和PostgreSQL支持此語法。 但是，MySQL的語法略有不同，如下所示：

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table_name
GROUP BY c1, c2 WITH ROLLUP;

2. SQL ROLLUP示例

我們將使用在GROUPING SETS教程中設置的inventory表進行演示。

mysql> select * from inventory;
+---------------+---------+----------+----------+
| warehouse     | product | model    | quantity |
+---------------+---------+----------+----------+
| San Jose      | iPhone  | 6s       |      100 |
| San Fransisco | iPhone  | 6s       |       50 |
| San Jose      | iPhone  | 7        |       50 |
| San Fransisco | iPhone  | 7        |       10 |
| San Jose      | iPhone  | X        |      150 |
| San Fransisco | iPhone  | X        |      200 |
| San Jose      | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Note 8   |      100 |
| San Jose      | Samsung | Note 8   |      150 |
+---------------+---------+----------+----------+
10 rows in set

2.1. SQL ROLLUP有一個列示例

以下語句使用GROUP BY子句和SUM()函數按倉庫查找總庫存：

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY warehouse;
+---------------+---------------+
| warehouse     | SUM(quantity) |
+---------------+---------------+
| San Fransisco | 560           |
| San Jose      | 650           |
+---------------+---------------+
2 rows in set

要檢索所有倉庫中的總產品數，請將ROLLUP添加到GROUP BY子句，如下所示：

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP(warehouse);

執行上面示例代碼，得到以下結果：

正如在結果中看到的那樣，warehouse列中的NULL值指定了總計超級聚合行。 在此示例中，ROLLUP選項使查詢生成另一行，顯示所有倉庫中的總產品數量。
要使輸出更具可讀性，可以使用COALESCE()函數將NULL值替換All值，如下所示：

SELECT 
    COALESCE(warehouse, 'All warehouses') AS warehouse,
    SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);

執行上面示例代碼，得到以下結果：

2.2. SQL ROLLUP有多列示例

以下語句按warehouse和product計算庫存：

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, product;

執行上面示例代碼，得到以下結果：

將ROLLUP添加到GROUP BY子句：

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse , product);

執行上面示例代碼，得到以下結果：

請注意，輸出包含兩個分析級別的摘要信息，而不僅僅是一個：

• 在指定倉庫的每組產品行之後，將顯示一個額外的摘要行，顯示總庫存。 在這些行中，product列中的值設置爲NULL。
• 在所有行之後，將顯示一個額外的摘要行，顯示所有倉庫和產品的總庫存。 在這些行中，warehouse 和 product 列中的值設置爲NULL。

2.3. SQL ROLLUP帶有部分彙總的示例

可以使用ROLLUP執行部分彙總，以減少計算的小計數，如以下示例所示：

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, ROLLUP (product);

執行上面示例代碼，得到以下結果：

在此示例中，ROLLUP僅爲product列而不是warehouse列生成超級聚合摘要。

在本教程中，您學習瞭如何使用SQL ROLLUP通過單個查詢執行多級分析。