読者です 読者をやめる 読者になる 読者になる

いろいろ備忘録日記

主に .NET と Python絡みのメモを公開しています。

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

C# SQL oracle SQLServer

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


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

  • ROLLUP
  • CUBE


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


記述方法が、各DBによってバラバラです。
以下、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の行は、総合計行です。