MySql 技术内幕 (第6章 聚合和旋转操作)

6.1　聚合

6.1.1　聚合函数

MySQL支持的聚合操作：

AVG()

BIT_AND() 位运算，与

BIT_OR() 位运算，或

BIT_XOR() 位运算，异或

COUNT()

GROUP_CONCAT() 返回由属于一组的列值连接组合而成的结果

MAX()

MIN()

STD() 返回给定表达式中所有值的标准差

STDDEV_POP()

STDDEV_SAMP()

STDDEV() 返回给定表达式中所有值的标准差

SUM()

VAR_POP()

VAR_SAMP()

VARIANCE() 返回给定表达式中所有值的方差

MySQL仅支持流聚合，流聚依赖于获得的存储再GROUP BY列中的数据，

流聚合会先根据GROUP BY对行进行排序;

 

这里看一下group_concat函数,语法如下;

create table z
(
    a int null,
    b int null
);

INSERT INTO test.z (a, b) VALUES (1, 200);
INSERT INTO test.z (a, b) VALUES (1, 100);
INSERT INTO test.z (a, b) VALUES (1, 100);
INSERT INTO test.z (a, b) VALUES (2, 400);
INSERT INTO test.z (a, b) VALUES (2, 500);
INSERT INTO test.z (a, b) VALUES (3, null);
INSERT INTO test.z (a, b) VALUES (4, 444);
INSERT INTO test.z (a, b) VALUES (4, null);

使用函数:

select a, group_concat(b)
from z
group by a;

             

去重,排序,拼接:

select a, group_concat(distinct b order by b desc SEPARATOR ':')
from z
group by a;

             

 

6.1.2　聚合的算法

mysql只支持流聚合。

流聚合依赖于获得的存储在GROUP BY列中的数据，如果SQL查询中包含的GROUP BY语句多于一行，流聚合会先根据GROUP BY对行进行排列。

explain
select  distinct  name  from    demo  ;

explain
select     name  from     demo  group by name  ;

上面两种执行计划是一样的;

6.2　附加属性聚合

省略...

 

6.3　连续聚合

按时间顺序对有序数据进行 聚合的操作;

6.3.1　累积聚合

从序列内第一个元素到当前元素的数据,如为每个员工返回每月开始到现在累计的订单数量和平均订单数量;

6.3.2　滑动聚合

按顺序对滑动窗口范围内的数据进行聚合的操作;

6.3.3　年初至今聚合

统计的仅为当前一年的聚合;下限的开始位置为概念的第一天;

 

6.4　Pivoting

6.4.1　开放架构

开放架构是一种用于频繁更改架构的设计模式。对于利用开放架构设计的表，一般使用Pivoting技术来查询。

已知属性个数，可以用静态Pivoting。

Pivoting还可以用来做格式化聚合函数，一般用于报表输出，直观。

6.4.2　关系除法

6.4.3　格式化聚合数据

6.5　Unpivoting

Unpivoting，Pivoting的反向操作。把行转为列。

6.6　CUBE 和 ROLLUP

mysql仅支持CUBE，不支持ROLLUP。ROLLUP是根据维度在数据结果集中进行的聚合操作。ROLLUP的优点是一次可以取得N次GROUP BY的结果，提高查询效率。单个维度没啥优势，第一个维度优势就大了。

如果分组的列包含NULL值,那么ROLLUP的结果可能是不正确的;

6.6.1　ROLLUP

根据维度在数据结果集中进行的聚合操作;

ROLLUP不能和ORDER BY一起使用，用LIMIT的话阅读性差，没有实际意义。

6.6.2　CUBE

ROLLUP是CUBE的一种特殊情况, 都是对数据的聚合操作;

ROLLUP只是层次上对数据进行聚合;

CUBE对所有维度进行聚合，具有N维度的列，需要2^N次分组操作。

6.7　小结