MySQL ROLLUP和CUBE問題

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


(注，此表的表結構和資料與格式化聚合表formatting一致)
  1. CREATE TABLE rollup(
  2. orderid int NOT NULL,
  3. orderdate date NOT NULL,
  4. empid int NOT NULL,
  5. custid varchar(10) NOT NULL,
  6. qty int NOT NULL,
  7. PRIMARY KEY(orderid,orderdate));

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


首先做一個簡單的一維聚合
  1. SELECT YEAR(orderdate) year,
  2.         SUM(qty) sum
  3. FROM rollup
  4. GROUP BY YEAR(orderdate)
  5. WITH ROLLUP;



結果為



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


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


  1. SELECT empid, custid,
  2.         YEAR(orderdate) year,
  3.         SUM(qty) sum
  4. FROM rollup
  5. GROUP BY empid,custid,YEAR(orderdate)
  6. WITH ROLLUP;

結果為


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


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


  1. SELECT empid, custid, YEAR(orderdate) YEAR, SUM(qty) sum FROM rollup
  2. GROUP BY empid, custid, YEAR(orderdate)
  3. UNION
  4. SELECT empid, custid, NULL, SUM(qty) sum FROM rollup
  5. GROUP BY empid, custid
  6. UNION
  7. SELECT empid, NULL, NULL, SUM(qty) sum FROM rollup
  8. GROUP BY empid
  9. UNION
  10. 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操作：


  1. SELECT empid, custid, YEAR(orderdate), SUM(qty)
  2. FROM rollup
  3. GROUP BY empid, custid, YEAR(orderdate)
  4. WITH CUBE;
上述SQL語句會報錯：
-- ERROR 1235 (42000): This version of MySQL doesn't yet support 'CUBE'


可以通過rollup來模擬cube：

  1. SELECT
  2.     empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
  3. GROUP BY empid, custid, YEAR(orderdate)
  4. WITH ROLLUP
  5. UNION
  6. SELECT
  7.     empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
  8. GROUP BY empid, YEAR(orderdate), custid
  9. WITH ROLLUP
  10. UNION
  11. SELECT
  12.     empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
  13. GROUP BY custid, YEAR(orderdate),empid
  14. WITH ROLLUP
  15. UNION
  16. SELECT
  17.     empid, custid, YEAR(orderdate) year, SUM(qty) sum from rollup
  18. GROUP BY custid, empid, YEAR(orderdate)
  19. WITH ROLLUP
  20. UNION
  21. SELECT
  22.     empid,custid,YEAR(orderdate) year, SUM(qty) sum from rollup
  23. GROUP BY YEAR(orderdate), empid, custid
  24. WITH ROLLUP
  25. UNION
  26. SELECT
  27.     empid,custid,YEAR(orderdate) year, SUM(qty) sum from rollup
  28. GROUP BY YEAR(orderdate), custid, empid
  29. WITH ROLLUP;



產生的最終結果為：



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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1255419/，如需轉載，請註明出處，否則將追究法律責任。

相關文章