hive 增强聚合函数 with rollup

hive 增强聚合函数 with rollup

现有一张课程选修表score_rollup，共有7名同学，他们选修的课程和课程所考成绩如下

c_name	s_name	s_sex	s_score
数学	赵雷	男	90
数学	钱电	男	60
数学	孙风	男	80
数学	李云	男	30
数学	周梅	女	87
数学	郑竹	女	89
英语	赵雷	男	99
英语	钱电	男	80
英语	孙风	男	80
英语	李云	男	20
英语	吴兰	女	34
英语	郑竹	女	98
语文	赵雷	男	80
语文	钱电	男	70
语文	孙风	男	80
语文	李云	男	50
语文	周梅	女	76
语文	吴兰	女	31

数据&建表

create table score_rollup
(
    c_name  string,
    s_name  string,
    s_sex   string,
    s_score int
);

insert overwrite table score_rollup
values ('数学', '赵雷', '男', 90),
       ('数学', '钱电', '男', 60),
       ('数学', '孙风', '男', 80),
       ('数学', '李云', '男', 30),
       ('数学', '周梅', '女', 87),
       ('数学', '郑竹', '女', 89),
       ('英语', '赵雷', '男', 99),
       ('英语', '钱电', '男', 80),
       ('英语', '孙风', '男', 80),
       ('英语', '李云', '男', 20),
       ('英语', '吴兰', '女', 34),
       ('英语', '郑竹', '女', 98),
       ('语文', '赵雷', '男', 80),
       ('语文', '钱电', '男', 70),
       ('语文', '孙风', '男', 80),
       ('语文', '李云', '男', 50),
       ('语文', '周梅', '女', 76),
       ('语文', '吴兰', '女', 31);

需求：求每门课程男生和女生各有多少人选修，以及每门课程男生和女生的平均分；每门课程共有多少人选修、以每门课程及平均分；所有课程选修总人数，以及所有课程总平均分（不考虑此指标是否有意义）

如果不用with rollup，怎么求解？

1. 求各门课程男生和女生各有多少人选修，以及该门课程男生和女生的平均分

select c_name,
       s_sex,
       count(1)     as cnt,
       avg(s_score) as avg
from score_rollup
group by c_name, s_sex
order by c_name

c_name	s_sex	cnt	avg
数学	男	4	65
数学	女	2	88
英语	女	2	66
英语	男	4	69.75
语文	女	2	53.5
语文	男	4	70

2. 每门课程共有多少人选修、以每门课程及平均分

select c_name,
       count(1) as cnt,
       round(avg(s_score),2) as avg
from score_rollup
group by c_name

c_name	cnt	avg
数学	6	72.67
英语	6	68.5
语文	6	64.5

3. 所有课程选修总人数，以及所有课程总平均分

select count(c_name) as cnt,
       round(avg(s_score)) as avg
from score_rollup

如果用with rollup，怎么求解？

select c_name,
       s_sex,
       count(1) as cnt_num,
       avg(s_score)  as avg_score
from score_rollup
group by c_name, s_sex
with rollup
order by c_name,s_sex

总结：with rollup主要和分组的深度有关，若group by c_name, s_sex是2级深度，聚合时先按2级深度（group by c_name, s_sex）进行聚合，在按1级深度（group by c_name）进行聚合， 最后按按0级深度（group 空）进行聚合（我的用词可能不准确）