MySQL ——group by子句使用with rollup

     group by 子句使用with rollup关键字之后，具有分组加和的功能。即：在所有的分组记录之后，自动新增一条记录，从全局计算所有记录的数据。

0 问题描述

   求出每年的学生平均成绩，及历史至今的平均成绩，结果保留两位小数。

1 数据准备

create table rollup_test
(
  name varchar(8)   COMMENT'',
  year int COMMENT'',
  score int COMMENT''
)CHARACTER SET utf8 COLLATE utf8_general_ci;

insert into rollup_test
values ('a',2016, 85),
       ('b',2016, 45),
       ('c',2016, 90),
       ('a',2015, 75),
       ('b',2015, 90);

2 数据分析

  完成代码如下：

select `year`,
        round(avg(score) ,2) as avg_score
from rollup_test group by `year`
union all
select '历史至今' as `year`,
       round(avg(score) ,2) as avg_score
from rollup_test

方式二：利用group by with rollup 替换union all进行简化

select `year`,
        round(avg(score) ,2) as avg_score
from rollup_test
group by `year`
with rollup

由输出结果可知，with rollup 会自动新增一条记录：

year   avg_score
null      77.00

结果与下列代码等价

select  '历史至今' as `year`,
           round(avg(score) ,2) as avg_score
from rollup_test

再借助函数ifnull 或 coalesce() 对空字段赋值

上述代码继续优化为：

select  coalesce (`year`,'历史至今') as `year`,
        round(avg(score) ,2) as avg_score
from rollup_test
group by `year`
with rollup

 输出结果为：

3 小结

     高级分组聚合指的是：聚合时涉及到rollup、cube等。使用高级分组聚合函数的作用：将union多次的作业直接分到一个作业中执行，可以减少多作业对磁盘和网络IO额的消耗。