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 小结