SQL Rollup

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

1. SQL ROLLUP簡介

ROLLUPGROUP 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> c2ROLLUP生成考慮此層次結構有意義的所有分組集。 這就是爲什麼我們經常使用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);

執行上面示例代碼,得到以下結果:

SQL Rollup

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

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

執行上面示例代碼,得到以下結果:

SQL Rollup

2.2. SQL ROLLUP有多列示例

以下語句按warehouseproduct計算庫存:

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

執行上面示例代碼,得到以下結果:

SQL Rollup

ROLLUP添加到GROUP BY子句:

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

執行上面示例代碼,得到以下結果:

SQL Rollup

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

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

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

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

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

執行上面示例代碼,得到以下結果:

SQL Rollup

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

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