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.

SQL一例，按分钟先小计，再按小时合计:
select grouping(TO_CHAR(CREATED,'yyyy-mm-dd hh24')),grouping(TO_CHAR(CREATED,'yyyy-mm-dd hh24:mi')),
TO_CHAR(CREATED,'yyyy-mm-dd hh24'),TO_CHAR(CREATED,'yyyy-mm-dd hh24:mi'),count(1) from dba_objects
group by rollup(TO_CHAR(CREATED,'yyyy-mm-dd hh24'),TO_CHAR(CREATED,'yyyy-mm-dd hh24:mi'))
order by TO_CHAR(CREATED,'yyyy-mm-dd hh24')

ITPub博客

[SQL] rollup & cube 分组函数用法及一例

最新文章