Hive OLAP多维聚合函数

OLAP多维聚合函数

Hive OLAP多维聚合函数是增强版的Group By语句。一般和Group By同时使用，用来进行多维分析。 本文总结Hive三种常用的多维聚合函数:With Cube 任意维度聚合、Grouping Sets 指定维度聚合、With Rollup 层级维度聚合。

测试数据

-- 建表 create table student_scores( id int, studentId int, language int, math int, english int, classId string, departmentId string ); -- 写入数据 insert into table student_scores values   (1,111,68,69,90,'class1','department1'),   (2,112,73,80,96,'class1','department1'),   (3,113,90,74,75,'class1','department1'),   (4,114,89,94,93,'class1','department1'),   (5,115,99,93,89,'class1','department1'),   (6,121,96,74,79,'class2','department1'),   (7,122,89,86,85,'class2','department1'),   (8,123,70,78,61,'class2','department1'),   (9,124,76,70,76,'class2','department1'),   (10,211,89,93,60,'class1','department2'),   (11,212,76,83,75,'class1','department2'),   (12,213,71,94,90,'class1','department2'),   (13,214,94,94,66,'class1','department2'),   (14,215,84,82,73,'class1','department2'),   (15,216,85,74,93,'class1','department2'),   (16,221,77,99,61,'class2','department2'),   (17,222,80,78,96,'class2','department2'),   (18,223,79,74,96,'class2','department2'),   (19,224,75,80,78,'class2','department2'),   (20,225,82,85,63,'class2','department2');

With Cube 任意维度聚合

语法:GROUP BY a,b WITH CUBE。 作用:根据Group BY维度的所有可能组合进行聚合。类似于Apache Kylin的Cube多维立方体概念。n个维度会有2的n次方种组合。

select departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,classid with cube;

结果 departmentid    classid summath uv NULL            NULL    1654    20 NULL            class1  930    11 NULL            class2  724    9 department1    NULL    718    9 department1    class1  410    5 department1    class2  308    4 department2    NULL    936    11 department2    class1  520    6 department2    class2  416    5

等价于union all:

-- 0个维度-没有维度 select null as departmentid,null classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores union all -- 1个维度-classid select null as departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by classid union all -- 1个维度-departmentid select departmentid,null as classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid union all -- 2个维度-departmentid、classid select departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,classid;

Grouping Sets 指定维度聚合

语法:GROUP BY a,b GROUPING SETS (a,b) 作用:根据GROUPING SETS指定维度组合进行聚合。是Cube的一部分。Grouping Sets 分组(Grouping) 集(Sets)，是多个分组的并集。等价于Union ALL单个分组结果。如grouping sets(A,B) 等价于...group by null,B union all ...group by A,null。

select departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,classid grouping sets((departmentid,classid),departmentid);

结果: departmentid    classid summath uv department1    NULL    718    9 department1    class1  410    5 department1    class2  308    4 department2    NULL    936    11 department2    class1  520    6 department2    class2  416    5

等价于union all: select departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,classid union all select departmentid,null as classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,null;

With Rollup 层级维度聚合

语法:GROUP BY a,b,c WITH ROLLUP 作用:以GROUP BY最左侧的维度为主，从该维度的角度去上卷、下钻。是Cube的一部分。

select departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,classid with rollup;

结果 departmentid    classid summath uv NULL            NULL    1654    20 department1    NULL    718    9 department1    class1  410    5 department1    class2  308    4 department2    NULL    936    11 department2    class1  520    6 department2    class2  416    5

等价于union all: -- 下钻 select departmentid,classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,classid union all select departmentid,null as classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by departmentid,null -- 上卷 union all select null as departmentid,null as classid,sum(math) as sumMath,count(distinct studentid) as uv from student_scores group by null,null;