Usage and example Analysis of rollup and cube grouping functions in SQL

SQL rollup and cube grouping function usage and example analysis, in response to this problem, this article describes the corresponding analysis and solution in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.

First, it calculates the standard aggregate value specified in the GROUP BY clause.

It then creates progressively higher level subtotals, moving from right to left in the grouped column list.

Finally, it creates a total.

N+1 sub-super aggregate combinations.

Select GROUPING(Owner), GROUPING(Object Type), OWNER, OBJECT_TYPE, COUNT(*) from gc.test.

Group by summary (OWNER, OBJECT_TYPE).

Ordered by owner;

First group owner,object_type, then group owner (i.e. subtotal), and finally total,

grouping You can see the subtotal level.

If rollup(a,b,c), then group a,b,c first, then group a,b, then group a, and finally add up.

cube

cube(a,b,c), order a,b,c then a, b then a,c then a then b,c then b then c then total

It produces n-th power possible superaggregate combinations of 2, if the columns and expressions are specified in the GROUP BY clause.

Note: The HAVING,GROUP BY clause conditions can't use aliases for the columns.

But ORDER BY clause can use aliases.

SQL example, subtotal by minute, then total by hour:

select grouping(TO_CHAR(CREATED,'yyyy-mm-dd hh34')),grouping(TO_CHAR(CREATED,'yyyy-mm-dd hh34:mi')),

TO_CHAR(CREATED,'yyyy-mm-dd hh34'),TO_CHAR(CREATED,'yyyy-mm-dd hh34:mi'),count(1) from dba_objects

group by rollup(TO_CHAR(CREATED,'yyyy-mm-dd hh34'),TO_CHAR(CREATED,'yyyy-mm-dd hh34:mi'))

order by TO_CHAR(CREATED,'yyyy-mm-dd hh34')

About SQL rollup and cube grouping function usage and sample analysis questions to share here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.