結構化查詢語言/彙總立方體
在 分組 一章中，我們已經看到，關鍵字 GROUP BY 在結果集中建立了行的分組。此外，聚合函式 如 SUM() 計算這些組中每個組的壓縮值。
由於 GROUP BY 可以按多個列進行彙總，因此通常需要計算“超級組”的彙總值，這些“超級組”是透過從 GROUP BY 規範中依次省略一列而產生的。
為了說明這種情況，我們提供一個示例表和針對此類表的典型問題。
CREATE TABLE car_pool (
-- define columns (name / type / default value / nullable)
id DECIMAL NOT NULL,
producer VARCHAR(50) NOT NULL,
model VARCHAR(50) NOT NULL,
yyyy DECIMAL NOT NULL CHECK (yyyy BETWEEN 1970 AND 2020),
counter DECIMAL NOT NULL CHECK (counter >= 0),
CONSTRAINT car_pool_pk PRIMARY KEY (id)
);
--
INSERT INTO car_pool VALUES ( 1, 'VW', 'Golf', 2005, 5);
INSERT INTO car_pool VALUES ( 2, 'VW', 'Golf', 2006, 2);
INSERT INTO car_pool VALUES ( 3, 'VW', 'Golf', 2007, 3);
INSERT INTO car_pool VALUES ( 4, 'VW', 'Golf', 2008, 3);
INSERT INTO car_pool VALUES ( 5, 'VW', 'Passat', 2005, 5);
INSERT INTO car_pool VALUES ( 6, 'VW', 'Passat', 2006, 1);
INSERT INTO car_pool VALUES ( 7, 'VW', 'Beetle', 2005, 1);
INSERT INTO car_pool VALUES ( 8, 'VW', 'Beetle', 2006, 2);
INSERT INTO car_pool VALUES ( 9, 'VW', 'Beetle', 2008, 4);
INSERT INTO car_pool VALUES (10, 'Toyota', 'Corolla', 2005, 4);
INSERT INTO car_pool VALUES (11, 'Toyota', 'Corolla', 2006, 3);
INSERT INTO car_pool VALUES (12, 'Toyota', 'Corolla', 2007, 2);
INSERT INTO car_pool VALUES (13, 'Toyota', 'Corolla', 2008, 4);
INSERT INTO car_pool VALUES (14, 'Toyota', 'Prius', 2005, 1);
INSERT INTO car_pool VALUES (15, 'Toyota', 'Prius', 2006, 1);
INSERT INTO car_pool VALUES (16, 'Toyota', 'Hilux', 2005, 1);
INSERT INTO car_pool VALUES (17, 'Toyota', 'Hilux', 2006, 1);
INSERT INTO car_pool VALUES (18, 'Toyota', 'Hilux', 2008, 1);
--
COMMIT;
在該表中，有兩個不同的汽車生產商、6 個型號和 4 年。針對此類表的典型問題是
- 每個生產商或每個型號的汽車數量。
- 一些標準組合的汽車數量，例如：生產商加型號或生產商加年份。
- 汽車總數（不含任何標準）。
正如我們所見，關鍵字 GROUP BY 為完全一個分組級別提供壓縮資料，在本例中為生產商加型號。
SELECT producer, model, sum(counter) as cnt
FROM car_pool
GROUP BY producer, model
ORDER BY producer, cnt desc;
--
Toyota Corolla 13
Toyota Hilux 3
Toyota Prius 2
VW Golf 13
VW Beetle 7
VW Passat 6
在這種情況下，人們還希望知道更高分組的相應值：每個生產商或整個表的值。這可以透過提交略微不同的 SELECT 來實現。
SELECT producer, sum(counter) as cnt
FROM car_pool
GROUP BY producer
ORDER BY producer, cnt desc;
--
Toyota 18
VW 26
--
--
SELECT sum(counter) as cnt
FROM car_pool;
--
44
原則上，可以使用 UNION 組合這些 SELECT，或者依次提交它們。但由於這是一個標準要求，SQL 提供了一個更優雅的解決方案，即用 ROLLUP 關鍵字擴充套件 GROUP BY。基於 GROUP BY 的結果，它為每個上級組提供額外的行，這些上級組是透過依次省略分組標準而產生的。
SELECT producer, model, sum(counter) as cnt
FROM car_pool
GROUP BY ROLLUP (producer, model); -- the MySQL syntax is: GROUP BY producer, model WITH ROLLUP
--
Toyota Corolla 13
Toyota Hilux 3
Toyota Prius 2
Toyota 18 <-- the additional row per first producer
VW Beetle 7
VW Golf 13
VW Passat 6
VW 26 <-- the additional row per next producer
44 <-- the additional row per all producers
簡單的 GROUP BY 語句在生產商加型號級別建立行。ROLLUP 關鍵字會導致建立額外的行，其中首先省略型號，然後省略型號和生產商。
ROLLUP 關鍵字提供了層次結構檢視適合的解決方案。但在資料倉庫應用程式中，人們希望自由地在聚合資料中導航，而不僅僅是從上到下。為了支援此要求，SQL 標準提供了關鍵字 CUBE。它是 ROLLUP 的擴充套件，它為GROUP BY 列的所有可能組合提供額外的行。
在我們的上述示例中，有兩個列生產商和型號，ROLLUP 為“生產商-only”和“no criteria”（= 完整表）建立了行。除此之外，CUBE 為“型號-only”建立了行。（如果不同的生產商使用相同的型號名稱，則此類行將只導致 1 個額外的行。）
SELECT producer, model, SUM(counter) AS cnt
FROM car_pool
GROUP BY CUBE (producer, model); -- not supported by MySQL
--
Toyota Corolla 13
Toyota Hilux 3
Toyota Prius 2
Toyota - 18
VW Beetle 7
VW Golf 13
VW Passat 6
VW - 26
- Beetle 7 <--
- Corolla 13 <--
- Golf 13 <-- additional rows for 'model-only'
- Hilux 3 <--
- Passat 6 <--
- Prius 2 <--
- - 44
如果存在三個分組列 c1、c2 和 c3，則這些關鍵字會導致以下分組。
| GROUP BY | (c1, c2, c3) |
| GROUP BY ROLLUP | (c1, c2, c3), (c1, c2), (c1) 和 () |
| GROUP BY CUBE | (c1, c2, c3), (c1, c2), (c1, c3), (c2, c3), (c1), (c2), (c3) 和 () |