[SQL] rollup & cube 分組函式用法及一例

rollup

First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

n+1次superaggregate combinations

select grouping(owner),grouping(object_type),owner,object_type,count(*) from gc.test

group by rollup(owner,object_type)

order by owner;

先對owner,object_type分組，再對owner分組(也就是小計)，最後再合計，

grouping可看到小計級別。 

如果rollup(a,b,c)，那就先對a,b,c分組，再對a,b分組，再對a分組，最後再合計。

cube

cube(a,b,c)，順序先a,b,c 再a,b 再a,c 再a 再b,c 再b 再c 再合計

It produces 2的n次方 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.

注：The HAVING,GROUP BY clause conditions can’t use aliases for the columns.

        But ORDER BY clause can use aliases.