Hive OLAP多维聚合函数

共计 3823 个字符，预计需要花费 10 分钟才能阅读完成。

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;

：