LoginSignup
57
52

More than 5 years have passed since last update.

@q1701

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

Last updated at Posted 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_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 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 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_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

(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_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

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

57
52
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
57
52