ROLLUPとCUBE (Oracle, SQLServer, GROUP BY, GROUPING, GROUPING SETS, 小計, 総合計)

便利な機能なのは、分かっているのについつい忘れてしまうので、メモメモ。
意外に結構しらない人も多いのではないのでしょうかこれ。

SQLで小計や総合計を求める時に、GROUP BYを利用することが多いと思いますが
GROUP BYには更に小計と総合計を求めるオプションがあります。

• ROLLUP
• CUBE

手元のSQLポケットリファレンスをみると、何気にこれANSI標準です。
使い方は、サンプル見ていただいたら大体分かると思います。

記述方法が、各ＤＢによってバラバラです。
以下、Oracleでの書き方。

GROUP BY ROLLUP(Col1, Col2...ColN)
GROUP BY CUBE(Col1, Col2...ColN)
GROUP BY GROUPING SETS ((グループ列パターン), (グループ列パターン)...)

以下、SQLServerでの書き方。

GROUP BY Col1, Col2...ColN WITH ROLLUP
GROUP BY Col1, Col2...ColN WITH CUBE

以下、サンプルです。SQLServer 2008でNorthwindデータベースを利用しています。

USE Northwind;

/* GROUP BY XXX WITH ROLLUP, CUBEのサンプル */
SELECT
	  Orders.OrderID
	 ,Details.ProductID
	 ,ROUND(SUM(Details.UnitPrice * Details.Quantity * (1 - Details.Discount)), 0) AS Price
          /* GROUPING関数は集計行か否かを判定できる。 0:集計行ではない.  1:集計行 */
	 ,GROUPING(Orders.OrderID) AS IsOrderIDGrouping
	 ,GROUPING(Details.ProductID) AS IsProductIDGrouping
FROM
	Orders
		INNER JOIN [Order Details] Details
		ON
			Orders.OrderID = Details.OrderID
		INNER JOIN Products
		ON
			Details.ProductID = Products.ProductID
WHERE
	Orders.OrderID BETWEEN 10260 AND 10265
GROUP BY
	/*
		ROLLUPでは、OrderID毎の合計と総合計が表示される。
		CUBEでは、OrderID毎の合計、ProductID毎の合計と総合計が表示される。
		
		Oracleで利用できるGROUPING SETSはSQL Serverでは指定出来ない。
		
		Oracleでの指定方法
			ROLLUP: GROUP BY ROLLUP(OrderID, ProductID) 
			CUBE  : GROUP BY CUBE(OrderID, ProductID)
			または、以下のようにGROUPING SETSを用いる.
				ROLLUP: GROUP BY GROUPING SETS ( (OrderID, ProductID), (OrderID), ())
				CUBE  : GROUP BY GROUPING SETS ( (OrderID, ProductID), (OrderID), (ProductID), ())
	*/
	Orders.OrderID, Details.ProductID WITH ROLLUP
 --	Orders.OrderID, Details.ProductID WITH CUBE
ORDER BY
	IsOrderIDGrouping, IsProductIDGrouping, Orders.OrderID, Details.ProductID

実行すると以下のようになります。

OrderID	ProductID	Price	IsOrderIDGrouping	IsProductIDGrouping
10260	41	92	0	0
10260	57	780	0	0
10260	62	443	0	0
10260	70	189	0	0
10261	21	160	0	0
10261	35	288	0	0
10262	5	163	0	0
10262	7	360	0	0
10262	56	61	0	0
10263	16	626	0	0
10263	24	101	0	0
10263	30	932	0	0
10263	74	216	0	0
10264	2	532	0	0
10264	41	164	0	0
10265	17	936	0	0
10265	70	240	0	0
10260	NULL	1505	0	1
10261	NULL	448	0	1
10262	NULL	584	0	1
10263	NULL	1874	0	1
10264	NULL	696	0	1
10265	NULL	1176	0	1
NULL	NULL	6282	1	1

ProductID列がNULLの行は、OrderID毎の集計行です。
また、OrderID, ProductIDがNULLの行は、総合計行です。