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

  • 21
    Like
  • 2
    Comment

サンプルデータ

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

複数列を組み合わせた小計(1)

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

複数列を組み合わせた小計(2)

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