More than 5 years have passed since last update.

SQL: GROUP BY, ROLLUP, CUBEの実行例

Last updated at 2017-07-10Posted at 2014-05-18

サンプルデータ

SELECT *
FROM sample_data
ORDER BY key1, key2, key3, key4

KEY1	KEY2	KEY3	KEY4	VAL
AAAA	AAA	AA	A	100
AAAA	AAA	AA	B	110
AAAA	AAA	BB	A	120
AAAA	AAA	BB	B	130
AAAA	BBB	AA	A	140
AAAA	BBB	AA	B	150
AAAA	BBB	BB	A	160
AAAA	BBB	BB	B	170
BBBB	AAA	AA	A	180
BBBB	AAA	AA	B	190
BBBB	AAA	BB	A	200
BBBB	AAA	BB	B	210
BBBB	BBB	AA	A	220
BBBB	BBB	AA	B	230
BBBB	BBB	BB	A	240
BBBB	BBB	BB	B	250

/* データ型に特に意味はない */
CREATE TABLE sample_data
(
  key1 CHAR(4),
  key2 CHAR(3),
  key3 CHAR(2),
  key4 CHAR(1),
  val INTEGER
);

INSERT INTO sample_data VALUES('AAAA','AAA','AA','A',100);
INSERT INTO sample_data VALUES('AAAA','AAA','AA','B',110);
INSERT INTO sample_data VALUES('AAAA','AAA','BB','A',120);
INSERT INTO sample_data VALUES('AAAA','AAA','BB','B',130);
INSERT INTO sample_data VALUES('AAAA','BBB','AA','A',140);
INSERT INTO sample_data VALUES('AAAA','BBB','AA','B',150);
INSERT INTO sample_data VALUES('AAAA','BBB','BB','A',160);
INSERT INTO sample_data VALUES('AAAA','BBB','BB','B',170);
INSERT INTO sample_data VALUES('BBBB','AAA','AA','A',180);
INSERT INTO sample_data VALUES('BBBB','AAA','AA','B',190);
INSERT INTO sample_data VALUES('BBBB','AAA','BB','A',200);
INSERT INTO sample_data VALUES('BBBB','AAA','BB','B',210);
INSERT INTO sample_data VALUES('BBBB','BBB','AA','A',220);
INSERT INTO sample_data VALUES('BBBB','BBB','AA','B',230);
INSERT INTO sample_data VALUES('BBBB','BBB','BB','A',240);
INSERT INTO sample_data VALUES('BBBB','BBB','BB','B',250);

KEY1, KEY2 で集計する

SELECT key1, key2, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, key2
ORDER BY key1, key2

KEY1	KEY2	COUNT(*)	SUM(VAL)
AAAA	AAA	4	460
AAAA	BBB	4	620
BBBB	AAA	4	780
BBBB	BBB	4	940

KEY1, KEY2 で集計しつつ KEY2 の小計をとる

小計を取りたい列を ROLLUP() 演算子に指定する。

SELECT key1, key2, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2)
ORDER BY key1, key2

KEY2がNULLの行が、KEY2の小計行である。

KEY1	KEY2	COUNT(*)	SUM(VAL)
AAAA	AAA	4	460
AAAA	BBB	4	620
AAAA		8	1080
BBBB	AAA	4	780
BBBB	BBB	4	940
BBBB		8	1720

複数列を組み合わせた小計（１）

KEY1ごとのKEY2, KEY3の小計をとる。KEY2, KEY3の小計については更にKEY2ごとのKEY3の小計をとる。

SELECT key1, key2, key3, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2, key3)
ORDER BY key1, key2, key3

KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
AAAA	AAA	AA	2	210
AAAA	AAA	BB	2	250
AAAA	AAA		4	460
AAAA	BBB	AA	2	290
AAAA	BBB	BB	2	330
AAAA	BBB		4	620
AAAA			8	1080
BBBB	AAA	AA	2	370
BBBB	AAA	BB	2	410
BBBB	AAA		4	780
BBBB	BBB	AA	2	450
BBBB	BBB	BB	2	490
BBBB	BBB		4	940
BBBB			8	1720

ここで ROLLUP() に指定する順序には意味がある。

KEY2とKEY3を入れ替えると以下の通り。

SELECT key1, key2, key3, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key3, key2)
ORDER BY key1, key3, key2

KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
AAAA	AAA	AA	2	210
AAAA	BBB	AA	2	290
AAAA		AA	4	500
AAAA	AAA	BB	2	250
AAAA	BBB	BB	2	330
AAAA		BB	4	580
AAAA			8	1080
BBBB	AAA	AA	2	370
BBBB	BBB	AA	2	450
BBBB		AA	4	820
BBBB	AAA	BB	2	410
BBBB	BBB	BB	2	490
BBBB		BB	4	900
BBBB			8	1720

複数列を組み合わせた小計（２）

KEY1, KEY2ごとに、KEY3, KEY4の小計をとる。

SELECT key1, key2, key3, key4,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, key2, ROLLUP( (key3, key4) )
ORDER BY key1, key2, key3, key4

KEY1	KEY2	KEY3	KEY4	COUNT(*)	SUM(VAL)
AAAA	AAA	AA	A	1	100
AAAA	AAA	AA	B	1	110
AAAA	AAA	BB	A	1	120
AAAA	AAA	BB	B	1	130
AAAA	AAA			4	460
AAAA	BBB	AA	A	1	140
AAAA	BBB	AA	B	1	150
AAAA	BBB	BB	A	1	160
AAAA	BBB	BB	B	1	170
AAAA	BBB			4	620
BBBB	AAA	AA	A	1	180
BBBB	AAA	AA	B	1	190
BBBB	AAA	BB	A	1	200
BBBB	AAA	BB	B	1	210
BBBB	AAA			4	780
BBBB	BBB	AA	A	1	220
BBBB	BBB	AA	B	1	230
BBBB	BBB	BB	A	1	240
BBBB	BBB	BB	B	1	250
BBBB	BBB			4	940

GROUPING, GROUPING_ID

カラムにNULLが格納されている場合、ROLLUPされた「超集合行」を判別するには GROUPING または GROUPING_ID 関数を使用する。

関数	仕様
GROUPING(列)	1: 指定した列の超集合行 / 0: それ以外
GROUPING_ID(列1, 列2, ...)	上記の組み合わせ(2進数に見立てる)

SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2, key3)
ORDER BY key1, key2, key3

（ただし今回のサンプルデータにはNULLが含まれない。）

GRP_2	GRP_3	GRPID_1X2X3	KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
0	0	0	AAAA	AAA	AA	2	210
0	0	0	AAAA	AAA	BB	2	250
0	1	1	AAAA	AAA		4	460
0	0	0	AAAA	BBB	AA	2	290
0	0	0	AAAA	BBB	BB	2	330
0	1	1	AAAA	BBB		4	620
1	1	3	AAAA			8	1080
0	0	0	BBBB	AAA	AA	2	370
0	0	0	BBBB	AAA	BB	2	410
0	1	1	BBBB	AAA		4	780
0	0	0	BBBB	BBB	AA	2	450
0	0	0	BBBB	BBB	BB	2	490
0	1	1	BBBB	BBB		4	940
1	1	3	BBBB			8	1720

クロス集計

ROLLUPと異なり、値の組み合わせで小計が計算される。

(1) CUBE(KEY1, KEY2, KEY3)

以下の組み合わせをとる。

(KEY1), (KEY2), (KEY3), (KEY1, KEY2), (KEY1, KEY3), (KEY2, KEY3), (KEY1, KEY2, KEY3)

SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY CUBE(key1, key2, key3)
ORDER BY key1, key2, key3

GRP_1	GRP_2	GRP_3	GRPID_1X2X3	KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
0	0	0	0	AAAA	AAA	AA	2	210
0	0	0	0	AAAA	AAA	BB	2	250
0	0	1	1	AAAA	AAA		4	460
0	0	0	0	AAAA	BBB	AA	2	290
0	0	0	0	AAAA	BBB	BB	2	330
0	0	1	1	AAAA	BBB		4	620
0	1	0	2	AAAA		AA	4	500
0	1	0	2	AAAA		BB	4	580
0	1	1	3	AAAA			8	1080
0	0	0	0	BBBB	AAA	AA	2	370
0	0	0	0	BBBB	AAA	BB	2	410
0	0	1	1	BBBB	AAA		4	780
0	0	0	0	BBBB	BBB	AA	2	450
0	0	0	0	BBBB	BBB	BB	2	490
0	0	1	1	BBBB	BBB		4	940
0	1	0	2	BBBB		AA	4	820
0	1	0	2	BBBB		BB	4	900
0	1	1	3	BBBB			8	1720
1	0	0	4		AAA	AA	4	580
1	0	0	4		AAA	BB	4	660
1	0	1	5		AAA		8	1240
1	0	0	4		BBB	AA	4	740
1	0	0	4		BBB	BB	4	820
1	0	1	5		BBB		8	1560
1	1	0	6			AA	8	1320
1	1	0	6			BB	8	1480
1	1	1	7				16	2800

(2) KEY1, CUBE(KEY2, KEY3)

SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, CUBE(key2, key3)
ORDER BY key1, key2, key3

GRP_2	GRP_3	GRPID_1X2X3	KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
0	0	0	AAAA	AAA	AA	2	210
0	0	0	AAAA	AAA	BB	2	250
0	1	1	AAAA	AAA		4	460
0	0	0	AAAA	BBB	AA	2	290
0	0	0	AAAA	BBB	BB	2	330
0	1	1	AAAA	BBB		4	620
1	0	2	AAAA		AA	4	500
1	0	2	AAAA		BB	4	580
1	1	3	AAAA			8	1080
0	0	0	BBBB	AAA	AA	2	370
0	0	0	BBBB	AAA	BB	2	410
0	1	1	BBBB	AAA		4	780
0	0	0	BBBB	BBB	AA	2	450
0	0	0	BBBB	BBB	BB	2	490
0	1	1	BBBB	BBB		4	940
1	0	2	BBBB		AA	4	820
1	0	2	BBBB		BB	4	900
1	1	3	BBBB			8	1720

(3) CUBE(KEY1,(KEY2, KEY3))

SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY CUBE(key1, (key2, key3))
ORDER BY key1, key2, key3

GRP_1	GRP_2	GRP_3	GRPID_1X2X3	KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
0	0	0	0	AAAA	AAA	AA	2	210
0	0	0	0	AAAA	AAA	BB	2	250
0	0	0	0	AAAA	BBB	AA	2	290
0	0	0	0	AAAA	BBB	BB	2	330
0	1	1	3	AAAA			8	1080
0	0	0	0	BBBB	AAA	AA	2	370
0	0	0	0	BBBB	AAA	BB	2	410
0	0	0	0	BBBB	BBB	AA	2	450
0	0	0	0	BBBB	BBB	BB	2	490
0	1	1	3	BBBB			8	1720
1	0	0	4		AAA	AA	4	580
1	0	0	4		AAA	BB	4	660
1	0	0	4		BBB	AA	4	740
1	0	0	4		BBB	BB	4	820
1	1	1	7				16	2800

(4) KEY1, CUBE((KEY2, KEY3))

SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, CUBE((key2, key3))
ORDER BY key1, key2, key3

GRP_2	GRP_3	GRPID_1X2X3	KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
0	0	0	AAAA	AAA	AA	2	210
0	0	0	AAAA	AAA	BB	2	250
0	0	0	AAAA	BBB	AA	2	290
0	0	0	AAAA	BBB	BB	2	330
1	1	3	AAAA			8	1080
0	0	0	BBBB	AAA	AA	2	370
0	0	0	BBBB	AAA	BB	2	410
0	0	0	BBBB	BBB	AA	2	450
0	0	0	BBBB	BBB	BB	2	490
1	1	3	BBBB			8	1720

GROUPING SETS

小計をとる列の組み合わせを直接指定する。

key2, (key1, key3), 全体

SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_3,
       GROUPING(key3)                AS grp_4,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY GROUPING SETS(key2, (key1, key3), ())
ORDER BY key1, key2, key3

GRP_1	GRP_3	GRP_4	GRPID_1X2X3	KEY1	KEY2	KEY3	COUNT(*)	SUM(VAL)
0	1	0	2	AAAA		AA	4	500
0	1	0	2	AAAA		BB	4	580
0	1	0	2	BBBB		AA	4	820
0	1	0	2	BBBB		BB	4	900
1	0	1	5		AAA		8	1240
1	0	1	5		BBB		8	1560
1	1	1	7				16	2800

参考URL

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up