データ・ウェアハウスにおける集計のためのSQL

rollup
cube
grouping sets
grouping
grouping_id

MySQL

(8.0.32)
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

rollup、grouping、grouping_id -> 使用可能
cubeとgrouping sets -> 使用不可★

※grouping_id関数はgrouping関数と同じ名前

drop table tab1 ;

create table tab1
(
col1 varchar(4)
, col2 varchar(4)
, col3 varchar(4)
, col4 bigint
);

insert into tab1 values('AAA','AA','A',1);
insert into tab1 values('AAA','AA','B',2);
insert into tab1 values('AAA','BB','A',3);
insert into tab1 values('AAA','BB','B',4);
insert into tab1 values('BBB','AA','A',5);
insert into tab1 values('BBB','AA','B',6);
insert into tab1 values('BBB','BB','A',7);
insert into tab1 values('BBB','BB','B',8);

select * from tab1 order by col1,col2,col3;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, col2 with rollup
order by col1, col2
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by col1, col2, col3 with rollup
order by col1, col2, col3
;

Oracle

(19c)

https://docs.oracle.com/cd/F19136_01/dwhsg/sql-aggregation-data-warehouses.html#GUID-E051A04E-0C53-491D-9B16-B71BA00B80C2
https://qiita.com/q1701/items/2321b9a8674d9796af5b

drop table tab1 purge;

create table tab1
(
col1 varchar2(4)
, col2 varchar2(4)
, col3 varchar2(4)
, col4 int
);

commit;

select * from tab1 order by col1,col2,col3;

select grouping(col1) g1
, grouping(col2) g2
, grouping_id(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, col2
order by col1, col2
;

select grouping(col1) g1
, grouping(col2) g2
, grouping_id(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, rollup(col2)
order by col1, col2
;

※grouping_idは、行ごとに、該当するgrouping関数を使用した場合に生成される1と0のセットを取り、それを連結してビット・ベクトルを形成します

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping_id(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by col1, rollup(col2, col3)
order by col1, col2, col3
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping_id(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by cube(col1, col2, col3)
order by col1, col2, col3
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping_id(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by grouping sets( col2, (col1, col3), () )
order by col1, col2, col3
;

PostgreSQL

(15)

https://www.postgresql.jp/document/14/html/queries-table-expressions.html#QUERIES-GROUPING-SETS

※grouping_id関数はgrouping関数と同じ名前

drop table tab1 ;

create table tab1
(
col1 varchar(4)
, col2 varchar(4)
, col3 varchar(4)
, col4 bigint
);

select * from tab1 order by col1,col2,col3;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, col2
order by col1, col2
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, rollup(col2)
order by col1, col2
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by col1, rollup(col2, col3)
order by col1, col2, col3
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by cube(col1, col2, col3)
order by col1, col2, col3
;

select grouping(col1) g1
, grouping(col2) g2
, grouping(col3) g3
, grouping(col1, col2, col3) gid
, col1
, col2
, col3
, count(*)
, sum(col4)
from tab1
group by grouping sets( col2, (col1, col3), () )
order by col1, col2, col3
;

SQL Server

(2022)

https://learn.microsoft.com/ja-jp/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16
https://devlights.hatenablog.com/entry/20090830/p1

drop table tab1 ;

create table tab1
(
col1 varchar(4)
, col2 varchar(4)
, col3 varchar(4)
, col4 bigint
);

select * from tab1 order by col1,col2,col3;

select grouping(col1) g1
, grouping(col2) g2
, grouping_id(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, col2
order by col1, col2
;

select grouping(col1) g1
, grouping(col2) g2
, grouping_id(col1, col2) gid
, col1
, col2
, count(*)
, sum(col4)
from tab1
group by col1, rollup(col2)
order by col1, col2
;