DB2 多种GROUP BY的使用

平常经常使用GROUP BY对数据进行分组运算，但是还有其他的分组运行情况。

GROUP BY CUBE()、GROUP BY  ROLLUP()、GROUP BY GROUPING SETS()

以原始数据表数据为例：

【1】GROUP BY CUBE()的分组情况

结果集相当于

SELECT SUM(SALARY) FROM TEST_SALARY  @

SELECT NAME, SUM(SALARY) FROM TEST_SALARY GROUP BY NAME@

SELECT DEPART, SUM(SALARY) FROM TEST_SALARY GROUP BY DEPART@

SELECT NAME,DEPART,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME,DEPART@

这四个结果集合集。

【2】GROUP BY ROLLUP()

结果集相当于 

SELECT SUM(SALARY) FROM TEST_SALARY  @

SELECT NAME,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME@

SELECT NAME,DEPART,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME,DEPART@

这三种结果集的合集。

【3】GROUPING SETS()

结果集相当于 

SELECT SUM(SALARY) FROM TEST_SALARY  @

SELECT NAME,SUM(SALARY) FROM TEST_SALARY GROUP BY NAME@

SELECT DEPART,SUM(SALARY) FROM TEST_SALARY GROUP BY DEPART@

这三种结果集的合集。

 

总结：

【2】group by cube(A,B) = all + group by A + group by B + group by A,B
         group by cube(A,B,C) = all + group by A + group by B + group by C + group by A,B + group by A,C + group by B,C    + group by A,B,C
        ==> group by cube 的结果是2的n次幂个组合
      

【3】group by rollup(A,B) = all + group by A + group by A,B
         group by rollup(A,B,C) = all + group by A + group by A,B + group by A,B,C
        ==> group by rollup 的结果是n+1个组合
        
 【4】group by grouping sets(A,B,()) = group by A + group by B + all
         group by grouping sets(A,B,C,()) = group by A + group by B + group by C +all
        ==> group by grouping sets 的结果是 n个组合