Hive OLAP多维聚合函数

OLAP多维聚合函数

Hive OLAP多维聚合函数是增强版的Group By语句。一般和Group By同时使用，用来进行多维分析。

1
2

-- 建表
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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

With Cube 任意维度聚合

1
2

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

-- 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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

Grouping Sets 指定维度聚合

1
2

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

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

With Rollup 层级维度聚合

1
2

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

-- 下钻
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;