rollup 是根據維度在資料結果集中進行的聚合操作。
假設使用者需要對N個唯獨進行聚合查詢操作，普通的group by語句需要N個查詢和N次group by操作。
而rollup的有點是一次可以去的N次group by的結果，這樣可以提高查詢效率，同時大大減少網路的傳輸流量。

(注，此表的表結構和資料與格式化聚合表formatting一致)

CREATE TABLE rollup(
orderid int NOT NULL,
orderdate date NOT NULL,
empid int NOT NULL,
custid varchar(10) NOT NULL,
qty int NOT NULL,
PRIMARY KEY(orderid,orderdate));

INSERT INTO rollup SELECT 1,'2010-01-02',3,'A',10;
INSERT INTO rollup SELECT 2,'2010-04-02',2,'B',20;
INSERT INTO rollup SELECT 3,'2010-05-02',1,'A',30;
INSERT INTO rollup SELECT 4,'2010-07-02',3,'D',40;
INSERT INTO rollup SELECT 5,'2011-01-02',4,'A',20;
INSERT INTO rollup SELECT 6,'2011-01-02',3,'B',30;
INSERT INTO rollup SELECT 7,'2011-01-02',1,'C',40;
INSERT INTO rollup SELECT 8,'2009-01-02',2,'A',10;
INSERT INTO rollup SELECT 9,'2009-01-02',3,'B',20;

首先做一個簡單的一維聚合

SELECT YEAR(orderdate) year,

SUM(qty) sum

FROM rollup

GROUP BY YEAR(orderdate)

WITH ROLLUP;

結果為

和普通的group by差別不大，只是多了一個(null,220)，表示對所有的year再做一次聚合，即訂單數量總和。
對單個唯獨進行rollip操作只是可以在最後得到聚合的資料，對比group by語句並沒有非常大的優勢。

對多個維度進行rollup才能體現出rollup的優勢：
(對3列進行層次的維度操作)

SELECT empid, custid,
YEAR(orderdate) year,
SUM(qty) sum
FROM rollup
GROUP BY empid,custid,YEAR(orderdate)
WITH ROLLUP;

結果為

其中(null,null,null)表示最後的聚合
(empid,custid,year)表示對這3列進行分組的聚合結果
(empid,custid,null)表示對(empid,custid)兩列進行分組的聚合結果
(empid,null,null)表示僅對(empid)一列進行分組的聚合結果

所以上述語句等同於（但未排序）

SELECT empid, custid, YEAR(orderdate) YEAR, SUM(qty) sum FROM rollup
GROUP BY empid, custid, YEAR(orderdate)
UNION
SELECT empid, custid, NULL, SUM(qty) sum FROM rollup
GROUP BY empid, custid
UNION
SELECT empid, NULL, NULL, SUM(qty) sum FROM rollup
GROUP BY empid
UNION
SELECT NULL, NULL, NULL, SUM(qty) sum FROM rollup

雖然兩者得到相同的結果，但是執行計劃卻不同
rollup只需要一次表掃描操作就能得到全部結果，因此查詢效率在此得到了極大的提升。

P.S.
在使用rollup需要注意以下幾方面
1.
ORDER BY不能在rollup中使用，兩者為互斥關鍵字，如果使用，會丟擲以下錯誤：
Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY

2.
可以使用LIMIT，但是因為不能使用order by，所以閱讀性下降，故大多數情況下無實際意義。

3.
如果分組的列包含NULL值，那麼rollup的結果可能不正確
因為在rollup中進行的分組統計時，null具有特殊意義
因此在進行rollup時可以先將null轉換成一個不可能存在的值，或者沒有特別含義的值，比如：
IFNULL(xxx,0)

【關於cube】
rollup是cube的一種特殊情況，和rollup一樣，cube也是一種對資料的聚合操作
但是rollup只在層次上對資料進行聚合，而cube對所有的維度進行聚合
具有N個維度的列，cube需要2的N次方次分組操作，而rollup只需要N次分組操作

在mysql 5.6.17版本中，只定義了cube，但是不支援cube操作：

SELECT empid, custid, YEAR(orderdate), SUM(qty)
FROM rollup
GROUP BY empid, custid, YEAR(orderdate)
WITH CUBE;

上述SQL語句會報錯：
-- ERROR 1235 (42000): This version of MySQL doesn't yet support 'CUBE'

可以透過rollup來模擬cube：

SELECT

    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup

GROUP BY empid, custid, YEAR(orderdate)

WITH ROLLUP

UNION

SELECT

    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup

GROUP BY empid, YEAR(orderdate), custid

WITH ROLLUP

UNION

SELECT

    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup

GROUP BY custid, YEAR(orderdate),empid

WITH ROLLUP

UNION

SELECT

    empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup

GROUP BY custid, empid, YEAR(orderdate)

WITH ROLLUP

UNION

SELECT

    empid,custid,YEAR(orderdate) year, SUM(qty) sum from rollup

GROUP BY YEAR(orderdate), empid, custid

WITH ROLLUP

UNION

SELECT

    empid,custid,YEAR(orderdate) year, SUM(qty) sum from rollup

GROUP BY YEAR(orderdate), custid, empid

WITH ROLLUP;

產生的最終結果為：

更多請參考姜承堯《mysql技術內幕：SQL程式設計》

MySQL ROLLUP和CUBE問題

相關文章