Group by后加rollup、cube、Grouping_Sets的用法区别
1、相关分析服务器
一般当聚合率和数据量没有大于必定程度时，对于不涉及Rollup、Cube、Grouping_Sets这三种操做的聚合不多出现GC问题。对于Rollup、Cube、Grouping_Sets操做可采用以下优化方法避免GC。性能
一、Rollup / Cube / Grouping_Sets时，某些场景下，若是多维度的字段比较多，内存或者GC会形成性能问题。特别的， 在实现这三种操做 时， 记录数会出现倍数的膨胀， 调优的时候请务必关注 GC 状况。 若是 GC性能状况表现不加， 建议用手动改动的方式调优， 一般是把这三种操做等价的用 UNION 多个子查询 SQL 的方式实现。 对 SQL 改写至关因而对它们计算内容的同语义翻译。优化
一、1Rollup的改写spa
对它等价的拆分改写结果以下，上下两个语句的结果相同：翻译
一、2Cube改写3d
能够看出前三个的Union块的结果等同于一个Cube，因此还能够改写为code
一、3Grouping Sets的改写blog
对它等价的拆分改写结果以下，上下两个语句的结果相同：内存
总结：能够按照以上所示的对三种操做的改写形式对语句展开优化，尽量的减小因内存和GC引起的性能问题。可是，通常状况下，若是GC问题不是特别严重，就不用改写，不然会致使性能更差。io
2、对比Group by、Cube、Rollup
Rollup运算符生成的结果集相似于Cube运算符生成的结果集。
CUBE和Rollup之间的具体区别：
v一、CUBE生成的结果集显示了所选列中值的全部组合的聚合
v二、Rollup生成的结果集显示了所选列中值的某一层次结构的聚合。
Rollup优势：
v一、Rollup返回单个结果集，而compute by返回多个结果集，而多个结果集会增长应用程序代码的复杂性。
v二、Rollup能够在服务器游标中使用，而compute by则不能够。
v三、查询优化器为Rollup生成的执行计算比为compute by生成的更为高效。
3、实例
-1、建立表 CREATE TABLE employee_part(department STRING,name STRING,salary int) CLUSTERED BY (department) INTO 7 BUCKETS STORED AS ORC tblproperties('transactional'='true'); --2、入数据 insert into employee_part values('A','ZHANG',100); insert into employee_part values('A','LI',200); insert into employee_part values('A','WANG',300); insert into employee_part values('A','DUAN',500); insert into employee_part values('B','DUAN',600 ); insert into employee_part values('B','DUAN',700); insert into employee_part values('A','ZHAO',400);
--3、Group by SELECT department,name,sum(salary)AS sum FROM employee_part GROUP BY department,name;
--4、Rollup SELECT department,name,sum(salary)AS sum FROM employee_part GROUP BY Rollup(department,name); 等价于 SELECT department,name,sum(salary)AS sum FROM employee_part GROUP BY department,name union SELECT department,'NULL',SUM(salary)AS sum FROM employee_part GROUP BY department union SELECT 'NULL','NULL',SUM(salary)AS sum FROM employee_part;
--5、CUBE SELECT department,name,sum(salary)AS sum FROM employee_part GROUP BY Cube(department,name); 等价于 SELECT department,name,sum(salary)AS sum FROM employee_part GROUP BY department,name union SELECT department,'NULL',SUM(salary)AS sum FROM employee_part GROUP BY department union SELECT 'NULL','NULL',SUM(salary)AS sum FROM employee_part UNION SELECT 'NULL', name, SUM(Salary) AS sum FROM employee_part GROUP BY name; 等价于 SELECT department,name,sum(salary)AS sum FROM employee_part GROUP BY Rollup(department,name) UNION SELECT 'NULL', name, SUM(Salary) AS sum FROM employee_part GROUP BY name;
能够看出CUBE的结果集在Rollup结果集上多出了5行，这5行至关于在Rollup结果集上再union上以员工名字为group by 的结果。