mysql聚合函数rollup和cube
来源:互联网 发布:百度网站自然排名优化 编辑:程序博客网 时间:2024/05/22 06:26
一、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 rollupgroup by yere(orderdate);
然后对年份进行聚合:
select year(orderdate) year,sum(qty) sum from rollup
group by yere(orderdate)with rollup;
和普通的groupby差别不大,只是多了一个(null,220),表示对所有的year再做一次聚合,即订单数量总和。对单个唯独进行rollip操作只是可以在最后得到聚合的数据,对比groupby语句并没有非常大的优势。
4)对多个纬度进行聚合:
select empid,custid,year(orderdate) year,sum(qty) sumfrom rollup
group by empid,custid,year(orderdate)
with rollup;
对三次分组都进行了聚合,一个有三种聚合,其中:
- (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()函数;
【实例】
selectifnull(dept,'总计') as '部门',ifnull(phone,'小计') as '电话',sum(money) as '金额' fromgroupTable 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;
- mysql聚合函数rollup和cube
- MySQL ROLLUP和CUBE区别
- MySQL ROLLUP和CUBE问题
- ORACLE ROLLUP和CUBE函数
- rollup和cube函数区别
- Oracle的group by聚合函数扩展cube rollup和grouping sets
- Oracle的group by聚合函数扩展cube rollup和grouping sets
- Oracle的group by聚合函数扩展cube rollup和grouping sets
- Aggregation聚合函数--grouping,grouping_id,grouping set,cube,rollup
- CUBE ()函数和 ROLLUP()函数
- 关于rollup和cube等高级函数
- db2的rollup和cube函数
- mysql rollup&&cube
- rollup与cube函数
- oracle rollup,cube函数
- rollup与cube函数
- ROLLUP和CUBE
- ROLLUP和CUBE
- 工作日志内容
- excel文件导入数据库
- 得到音频文件的播放时间---GetAmrDuration
- centos 安装scipy
- Ethernet_II帧和802.3_Ethernet帧格式比较
- mysql聚合函数rollup和cube
- 单元测试
- dispose()与close()的区别 及pooling连接池相关
- OC中让键盘下去的方法
- java中Object类 源代码详解
- Unity3D开发者快速上手Unreal Engine 4指南
- Git基础使用教程:第一个Git项目
- mysql配置优化(windows下my.ini)
- BasicERP