DataBaser.Net: Grouping Sets

1 기본적인 사용방법
2 데이터 뻥튀기
3 참고자료

1 기본적인 사용방법 #

ROLLUP과 CUBE를 이용한 쿼리는 다음과 같이 결과를 뿌렸다. 2005 버전 이하에서는 이랬다.

--Grouping Set
use tempdb
go

--drop table grp_set_test
create table grp_set_test(
	group1 int
,	group2 char(1)
,	val int
);

insert grp_set_test values 
	(1, 'a', 10)
,	(1, 'a', 20)
,	(1, 'a', 30)
,	(1, 'b', 60)
,	(2, 'a', 70)
,	(2, 'a', 80)
,	(3, 'c', 70)
,	(3, 'c', 90);
go

select
	group1
,	group2 
,	SUM(val) as total
from grp_set_test
group by 
	group1
,	group2 with rollup;
/*
group1      group2 total
----------- ------ -----------
1           a      60
1           b      60
1           NULL   120
2           a      150
2           NULL   150
3           c      160
3           NULL   160
NULL        NULL   430
*/

select
	group1
,	group2 
,	SUM(val) as total
from grp_set_test
group by 
	group1
,	group2 with cube;
/*
group1      group2 total
----------- ------ -----------
1           a      60
2           a      150
NULL        a      210
1           b      60
NULL        b      60
3           c      160
NULL        c      160
NULL        NULL   430
1           NULL   120
2           NULL   150
3           NULL   160
*/

2008버전에서는 다음과 같이 할 수 있다. 두 쿼리는 같은 결과를 뿌린다.

--sql server 2008 new syntax
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	rollup(group1, group2); --new syntax
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
total  total  3            430
*/

select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets --new syntax
	(
		(group1, group2)
	,	(group1)
	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
total  total  3            430
*/

위의 결과에서 소계와 총계를 제거해 보자.

--소계 제거
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets
	(
		(group1, group2)
--	,	(group1)
	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
2      a      0            150
3      c      0            160
total  total  3            430
*/

--총계 제거
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets
	(
		(group1, group2)
	,	(group1)
--	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
*/

결과에 대한 제어가 좀 더 쉬워졌다.

참고:

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

--is the same as
SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year

[edit]

2 데이터 뻥튀기 #

데이터를 뻥튀기 시키는 일반적인 방법은 dummy 테이블과 cross join하는 거였다. 하지만 경우에 따라서는 grouping sets를 이용할 수도 있다. 다음의 예제를 보면 데이터 뻥튀기하는 것을 볼 수 있다.

;with dummy
as
(
        select 0 seq
        union all
        select seq + 1 from dummy
        where seq + 1 <= 10
)
select
    width
,   cnt
from (
    select 
        isnull((case when seq between 1 and 3 then '1~3' end)
    ,   isnull((case when seq between 1 and 6 then '1~6' end)
    ,   (case when seq between 1 and 9 then '1~9' end))) width
    ,    count(*) cnt
    from dummy
    group by
        grouping sets
        (
            (case when seq between 1 and 3 then '1~3' end)
        ,   (case when seq between 1 and 6 then '1~6' end)
        ,   (case when seq between 1 and 9 then '1~9' end)
        )
) t
where width is not null
option (maxrecursion 0);

/* result 

width cnt
----- -----------
1~9   9
1~6   6
1~3   3

*/

[edit]

3 참고자료 #

Generating SubTotals using GROUPING

Contents

1 기본적인 사용방법 #

2 데이터 뻥튀기 #

3 참고자료 #