数据
name course score
小红,sql,85
小红,c#,90
小明,sql,93
小明,c#,82
小李,sql,88
小李,c#,0
建表
create table s(
name string,
course string,
score int
)row format delimited fields terminated by ',';
load data local inpath'/hive/s.txt' overwrite into table s
;
1.with cube 全局组合函数
with cube 是按照group by a,b,c的顺序聚合的
1.是按照(a,b,c)进行group by 2.(a,b) 3.(a,c) 4.(a) 5.(b,c) 6.(b) 7.© 8.对全表进行group by
一共是2x2x2=8种group by方式!
如果手手动写的话只能用 unuon all 函数来拼接,写很长很长的sql.
sql查询
select
name,
course,
score
from
s
group by
name,
course,
score
with cube
;
--表格实现
------- --------- --------
| name | course | score |
------- --------- --------
| NULL | NULL | NULL |
| NULL | NULL | 0 |
| NULL | NULL | 82 |
| NULL | NULL | 85 |
| NULL | NULL | 88 |
| NULL | NULL | 90 |
| NULL | NULL | 93 |
| NULL | c# | NULL |
| NULL | c# | 0 |
| NULL | c# | 82 |
| NULL | c# | 90 |
| NULL | sql | NULL |
| NULL | sql | 85 |
| NULL | sql | 88 |
| NULL | sql | 93 |
| 小明 | NULL | NULL |
| 小明 | NULL | 82 |
| 小明 | NULL | 93 |
| 小明 | c# | NULL |
| 小明 | c# | 82 |
| 小明 | sql | NULL |
| 小明 | sql | 93 |
| 小李 | NULL | NULL |
| 小李 | NULL | 0 |
| 小李 | NULL | 88 |
| 小李 | c# | NULL |
| 小李 | c# | 0 |
| 小李 | sql | NULL |
| 小李 | sql | 88 |
| 小红 | NULL | NULL |
| 小红 | NULL | 85 |
| 小红 | NULL | 90 |
| 小红 | c# | NULL |
| 小红 | c# | 90 |
| 小红 | sql | NULL |
| 小红 | sql | 85 |
------- --------- --------
2.grouping set 自由分组函数
通过上述的group by他会变成很长的一个包含很多null的表,
但是如果我只想要 group by a,(b,c)的聚合结果怎么办!
用grouping set 自由分组函数可以完美解决!
写sql
select
name,
course,
score
from
s
group by name,course,score
grouping sets (name,(course,score))
--with cube
;
--表格实现
------- --------- --------
| name | course | score |
------- --------- --------
| NULL | c# | 0 |
| NULL | c# | 82 |
| NULL | c# | 90 |
| NULL | sql | 85 |
| NULL | sql | 88 |
| NULL | sql | 93 |
| 小明 | NULL | NULL |
| 小李 | NULL | NULL |
| 小红 | NULL | NULL |
------- --------- --------
3.with rollup 递级分组函数
当我们只需要group by a,(a,b),(a,b,c)的数据时
with rollup 递级分组函数就可以完美解决!
select
name,
course,
score
from
s
group by name,course,score
with rollup
;
--表格实现
------- --------- --------
| name | course | score |
------- --------- --------
| NULL | NULL | NULL |
| 小明 | NULL | NULL |
| 小明 | c# | NULL |
| 小明 | c# | 82 |
| 小明 | sql | NULL |
| 小明 | sql | 93 |
| 小李 | NULL | NULL |
| 小李 | c# | NULL |
| 小李 | c# | 0 |
| 小李 | sql | NULL |
| 小李 | sql | 88 |
| 小红 | NULL | NULL |
| 小红 | c# | NULL |
| 小红 | c# | 90 |
| 小红 | sql | NULL |
| 小红 | sql | 85 |
------- --------- --------