MySQL with rollup的用法

當需要對數(shù)據(jù)庫數(shù)據(jù)進行分類統(tǒng)計的時候，往往會用上groupby進行分組。

而在groupby后面還可以加入withcube和withrollup等關(guān)鍵字對數(shù)據(jù)進行匯總。

mysql文檔

with rollup概述

with在sql語句中定義在group by之后。當需要對數(shù)據(jù)庫數(shù)據(jù)進行分類統(tǒng)計的時候，往往會用上groupby進行分組。

而在groupby后面還可以加入withcube和withrollup等關(guān)鍵字對數(shù)據(jù)進行匯總。

不過這個cube在mysql中并不適用。

應(yīng)用實例

現(xiàn)在有這樣一張學(xué)生表，里面的數(shù)據(jù)如下所示。

如果想對根據(jù)學(xué)生，對科目，分數(shù)求和，可以這樣寫。

如果想在這個的基礎(chǔ)上，求出學(xué)生的總分數(shù)，應(yīng)該怎么做。

使用 WITH ROLLUP，此函數(shù)是對聚合函數(shù)進行求和，注意 with rollup是對 group by 后的第一個字段，進行分組求和。

ORDER BY不能在rollup中使用，兩者為互斥關(guān)鍵字，如果使用，會拋出以下錯誤：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY。mysql5.7中是不支持的，在8.0以后支持。

相信大家已經(jīng)知道如何使用with rollup了，這個就是在group by分組之后，再次對聚合函數(shù)進行求和。

MySQL with rollup 聚合函數(shù)類似oracle cube操作

作用

在分組統(tǒng)計數(shù)據(jù)的基礎(chǔ)上再進行統(tǒng)計匯總 

題目示例

根據(jù)題目意思需要 求每個價格區(qū)間，時間的mark=0的和/mark=1的和

最后還有求個所有價格區(qū)間 按月份的mark=0的和/mark=1的和

SELECT
	PriceBand,
	YearMonth,
	sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) 
FROM
	test 
GROUP BY
	YearMonth,
	PriceBand UNION ALL
SELECT
	'Alll priceBand',
	YearMonth,
	sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) 
FROM
	test 
GROUP BY
	YearMonth

在不知道m(xù)ysql有 這種聚合函數(shù)的情況 我所想到的只能靠這樣拼接來實現(xiàn)

使用rollup后

SELECT
	ifnull( PriceBand, 'All priceBand' ),
	YearMonth,
	sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END ) 
FROM
	test 
GROUP BY
	YearMonth,
	PriceBand WITH ROLLUP 
HAVING
	YearMonth IS NOT NULL

注意

在mysql5.6.17版本中，只定義了cube，但是不支持cube操作。

cube也是一種對數(shù)據(jù)的聚合操作。但是rollup只在層次上對數(shù)據(jù)進行聚合，而cube對所有的維度進行聚合。具有N個維度的列，cube需要2的N次方次分組操作，而rollup只需要N次分組操作。

rollup和cube的區(qū)別：

1）假設(shè)有n個維度，rollup會有n個聚合：

• rollup(a,b) 統(tǒng)計列包含：(a,b)、(a)、()
• rollup(a,b,c)統(tǒng)計列包含：(a,b,c)、(a,b)、(a)、()

2）假設(shè)有n個緯度，cube會有2的n次方個聚合

• cube(a,b) 統(tǒng)計列包含：(a,b)、(a)、(b)、()
• cube(a,b,c) 統(tǒng)計列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、©、()

在mysql中 with rollup放的位置是有要求的

之前的group by 正常使用分組 加了 with rollup 在那個字段后就對所有價格區(qū)間進行統(tǒng)計 同時 with rollup 并不能放在 (group by a with rollup,b )中間

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論