mysql聚合函数rollup和cube

你的名字 2021-05-12 11:51 659阅读 1赞

**一、with rollup：**

with rollup 通常和group by 语句一起使用，是根据维度在分组的结果集中进行聚合操作。——对group by的分组进行汇总。

假设用户需要对N个纬度进行聚合查询操作，普通的groupby语句需要N个查询和N次group by操作。而rollup的有点是一次可以去的N次groupby的结果，这样可以提高查询效率，同时大大减少网络的传输流量。

1、rollup演示：

1）创建表：

create table rollup(
    orderid int not null,
    orderdate date not null,
    empid int not null,
    custid varchar(10) not null,
    qty int not null,
    primary key(orderid,orderdate));

2）插入数据：

insert into rollup select 1,\'2010-01-02\',3,\'A\',10;
    insert into rollup select 2,\'2010-04-02\',2,\'B\',20;
    insert into rollup select 3,\'2010-05-02\',1,\'A\',30;
    insert into rollup select 4,\'2010-07-02\',3,\'D\',40;
    insert into rollup select 5,\'2011-01-02\',4,\'A\',20;
    insert into rollup select 6,\'2011-01-02\',3,\'B\',30;
    insert into rollup select 7,\'2011-01-02\',1,\'C\',40;
    insert into rollup select 8,\'2009-01-02\',2,\'A\',10;
    insert into rollup select 9,\'2009-01-02\',3,\'B\',20;

3）首先按照年份分组、统计销量；然后在对年份这一个纬度进行聚合

select year(orderdate) year, sum(qty) sum  from rollup group by yere(orderdate)；

**![Center][]**

然后对年份进行聚合：

select year(orderdate) year, sum(qty) sum  from rollup 
    group by yere(orderdate) with rollup;

![Center 1][]

和普通的groupby差别不大，只是多了一个(null,220)，表示对所有的year再做一次聚合，即订单数量总和。对单个唯独进行rollip操作只是可以在最后得到聚合的数据，对比groupby语句并没有非常大的优势。

4）对多个纬度进行聚合：

select empid,custid, year(orderdate) year,sum(qty) sum from rollup
    group by empid,custid,year(orderdate)
    with rollup;

![Center 2][]

对三次分组都进行了聚合，一个有三种聚合，其中：

*  (null,null,null)表示最后的聚合
 *  (empid,null,null)表示仅对(empid)一列进行分组的聚合结果
 *  (empid,custid,null)表示对(empid,custid)两列进行分组的聚合结果
 *  (empid,custid,year)表示对这3列进行分组的聚合结果——也就是group by本身聚集

所以，上面结果等价于：（未排序）

select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by empid,custid,year(orderdate)
    union
    select empid,custid,null,sum(qty) sum from rollup
    group by empid,custid
    union
    select empid,null,null,sum(qty) sum from rollup
    group by empid
    union
    select null,null,null,sum(qty) sum from rollup

2、rollup注意：

1）ORDER BY不能在rollup中使用，两者为互斥关键字；

2）如果分组的列包含NULL值，那么rollup的结果可能不正确，因为在rollup中进行的分组统计时，null具有特殊意义。因此在进行rollup时可以先将

null转换成一个不可能存在的值，或者没有特别含义的值，比如：IFNULL(xxx,0)

3）mysql中没有像oracle那样的grouping()函数；

【实例】

select ifnull(dept,'总计') as '部门',ifnull(phone,'小计') as '电话',sum(money) as '金额' from groupTable  
    group by dept,phone 
    with rollup;

\+--------+------+------+

| 部门  | 电话| 金额|

+--------+------+------+

| 财务部| 6001 |   10 |

|财务部| 6003 |  100 |

|财务部| 小计|  110 |

|营业部| 8001 |   20 |

|营业部| 8002 |   30 |

| 营业部| 小计|   50 |

| 总计  | 小计|  160 |

+--------+------+------+

**二、cube：**

rollup是cube的一种特殊情况，和rollup一样，cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。**具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作****。**

在mysql5.6.17版本中，只定义了cube，但是不支持cube操作。

2、rollup和cube的区别：

1）假设有n个维度，rollup会有n个聚合：

rollup(a,b)   统计列包含：(a,b)、(a)、()

rollup(a,b,c)统计列包含：(a,b,c)、(a,b)、(a)、()

……以此类推ing……

2）假设有n个纬度，cube会有2的n次方个聚合

cube(a,b)     统计列包含：(a,b)、(a)、(b)、()

cube(a,b,c)  统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()

……以此类推ing……

3、可以用rollup来模拟cube：

select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by empid,custid,year(orderdate)
    with rollup
    union
    select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by empid,year(orderdate),custid
    with rollup
    union
    select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by custid,year(orderdate),empid
    with rollup
    union
    select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by custid,empid,year(orderdate)
    with rollup
    union
    select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by year(orderdate),empid,custid
    with rollup
    union
    select empid,custid,year(orderdate) year,sum(qty) sum from rollup
    group by year(orderdate),empid,custid
    with rollup;

[Center]: /images/20210511/4d3566e755c14775a68f6a3c440f6fec.png
[Center 1]: /images/20210511/61c8d079330f44e09f2993b1b0550de3.png
[Center 2]: /images/20210511/1c1ea5e9bc8141179a920bdca3ed5184.png