SQL Rollup - tw511教學網

在本教學中，您將學習如何使用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通過單個查詢執行多級分析。