Group by后加rollup、cube、grouping sets的用法和区别

Group by后加rollup、cube、grouping sets的用法和区别

一、相关分析

通常当聚合率和数据量没有大于一定程度时，对于不涉及Rollup、Cube、Grouping_Sets这三种操作的聚合很少出现GC问题。对于Rollup、Cube、Grouping_Sets操作可采用如下优化方法避免GC。

Rollup / Cube / Grouping_Sets时，某些场景下，如果多维度的字段比较多，内存或者GC会造成性能问题。特别的， 在实现这三种操作 时， 记录数会出现倍数的膨胀， 调优的时候请务必关注 GC 情况。 如果 GC性能情况表现不加， 建议用手动改动的方式调优， 通常是把这三种操作等价的用 UNION 多个子查询 SQL 的方式实现。 对 SQL 改写相当于是对它们计算内容的同语义翻译。

1. Rollup的改写

对它等价的拆分改写结果如下，上下两个语句的结果相同：

2. Cube改写

对它等价的拆分改写结果如下(全部内容非红色框内容)，上下两个语句的结果相同：

可以看出前三个的Union块的结果(红色框部分)等同于一个rollup，所以Cube语法还可以改写为

3. Grouping Sets的改写

对它等价的拆分改写结果如下，上下两个语句的结果相同：

总结：可以按照以上所示的对三种操作的改写形式对语句展开优化，尽可能的减少因内存和GC引发的性能问题。但是，一般情况下，如果GC问题不是特别严重，就不用改写，否则会导致性能更差。

二、对比Group by、Cube、Rollup

Rollup运算符生成的结果集类似于Cube运算符生成的结果集。
CUBE和Rollup之间的具体区别：
1、CUBE生成的结果集显示了所选列中值的所有组合的聚合
2、Rollup生成的结果集显示了所选列中值的某一层次结构的聚合。

Rollup优点：
1、Rollup返回单个结果集，而compute by返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
2、Rollup可以在服务器游标中使用，而compute by则不可以。
3、查询优化器为Rollup生成的执行计算比为compute by生成的更为高效。
原文链接请点击：原文链接
小知识：
GROUPING（COLUMN）使用
使用GROUPING()可以判断该行是数据库本来的行，还是统计产生的行。GROUPING值为0时说明这个值是数据库中本来的值，为1说明是统计的结果（也可以说该列为空时是1，不空时是0），根据这一特性，可以使显示结果更加人性化。