GROUPING SETS

다음과 같이 국가별 휴대폰 사용자수에 대한 데이터가 있다고 할 때

국가, OS별 사용자수 합계를 구하기 위해서는 ROLLUP을 이용해 GROUP BY를 하는게 제일 빠른방법입니다.

WITH TEST AS
 
     (SELECT '2009' AS YEAR, 'KOR' AS country, 'IOS' AS os, 100 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'JPN' AS country, 'IOS' AS os, 60 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'USA' AS country, 'IOS' AS os, 700 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'KOR' AS country, 'ANDROID' AS os, 90 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'JPN' AS country, 'ANDROID' AS os, 500 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'USA' AS country, 'ANDROID' AS os, 190 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'KOR' AS country, 'WINDOWS' AS os, 40 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'JPN' AS country, 'WINDOWS' AS os, 60 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2009' AS YEAR, 'USA' AS country, 'WINDOWS' AS os, 80 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'KOR' AS country, 'IOS' AS os, 450 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'JPN' AS country, 'IOS' AS os, 50 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'USA' AS country, 'IOS' AS os, 90 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'KOR' AS country, 'ANDROID' AS os, 160 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'JPN' AS country, 'ANDROID' AS os, 40 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'USA' AS country, 'ANDROID' AS os, 270 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'KOR' AS country, 'WINDOWS' AS os, 110 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'JPN' AS country, 'WINDOWS' AS os, 300 AS users FROM DUAL
 
      UNION ALL
 
      SELECT '2010' AS YEAR, 'USA' AS country, 'WINDOWS' AS os,  50 AS users FROM DUAL)
 
SELECT   country, os, SUM (users)
 
    FROM TEST
 
GROUP BY ROLLUP (country, os)
 
ORDER BY country, os

이중에서 ROLLUP에 의해 구해지는 데이터중 국가별 합계는 필요없고, 전체합계만 필요하다면 아래와 같이 HAVING절과 GROUPING()함수를 통해 국가별 합계는 제거할수 있습니다.

SELECT   country, os, SUM (users)
 
    FROM TEST
 
GROUP BY ROLLUP (country, os)
 
  HAVING NOT (GROUPING (country) = '0' AND GROUPING (os) = '1')
 
ORDER BY country, os

이 방법보다 더 간단한 방법이 있는데 바로 아래와 같이 GROUPING SETS 절을 이용하는 것입니다. 

SELECT   country, os, SUM (users)
 
    FROM TEST
 
GROUP BY GROUPING SETS ((country, os), ())
 
ORDER BY country, os

GROUPING SET는 oracle 9.2부터 추가된 기능이며 다음표를 보면 동작원리를 이해할 수 있습니다.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/aggreg.htm#sthref1065

GROUPING SETS Statement	Equivalent GROUP BY Statement
GROUP BY GROUPING SETS(a, b, c)	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY c
GROUP BY GROUPING SETS(a, b, (b, c))	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY b, c
GROUP BY GROUPING SETS((a, b, c))	GROUP BY a, b, c
GROUP BY GROUPING SETS(a, (b), ())	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY ()
GROUP BY GROUPING SETS(a, ROLLUP(b, c))	GROUP BY a UNION ALL GROUP BY ROLLUP(b, c)