MySQL(五)：聚合和旋转操作（Pivoting技术）

于 2021-04-23 22:53:03 发布
阅读量877 收藏 4
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/GDUT_Trim/article/details/116074552

聚合
聚合函数

MySQL数据库支持聚合（aggregate）操作，一般在GROUP BY分组后，对单个块进行聚合时使用，不过也可以对一个表来进行使用,按照分组对同一组内的数据聚合进行统计操作，目前支持的聚合函数有以下这些

  1. AVG() 平均值
  2. BIT_AND()
  3. BIT_OR()
  4. BIT_XOR()
  5. COUNT(DISTINCT)
  6. COUNT()
  7. GROUP_CONCAT()
  8. MAX() 最大值
  9. MIN() 最小值
  10. STD()
  11. STDDEV_POP()
  12. STDDEV_SAMP()
  13. STDDEV()
  14. SUM() 求和
  15. VAR_POP()
  16. VAR_SAMP()
  17. VARIANCE()

比较多是不常用的，这里主要介一下GROUP_CONCAT()

举个栗子，我们来看下面这几条SQL

CREATE TABLE z(
	a INT,
	b INT
)ENGINE=INNODB,CHARSET=utf8;

INSERT INTO z SELECT 1,200;
INSERT INTO z SELECT 1,100;
INSERT INTO z SELECT 1,100;
INSERT INTO z SELECT 2,400;
INSERT INTO z SELECT 2,500;
INSERT INTO z SELECT 3,NULL;

SELECT a,GROUP_CONCAT(b) FROM z GROUP BY a;

得到的结果如下

在这里插入图片描述
100，100，而a = 2这个分组得到的是400，500，a = 3这个分组只有一个NULL。

总的来说，他的作用就是将分组后的块对指定列所有值进行拼接，并使用逗号进行隔离。

此外GROUP_CONCAT聚合函数还有一些其他的特性，其语法如下

GROUP_CONCAT([DISTINCT] [exprs] [order by {col_name} {ASC|DESC} [SEPARATOR] [String]])

详细说明一下这些特性

  1. DISTINCT 可以对GROUP_CONCAT里面的列进行去重
  2. exprs 就是GROUP_CONCAT里面指定要拼接的列
  3. ORDER BY {col_name} {ASC|DESC} 可以根据GROUP_CONCAT指定的列进行排序，ASC升序，DESC降序
  4. SEPARATOR [String] 使用什么字符串来对其进行分隔，默认是逗号
SELECT a,GROUP_CONCAT(DISTINCT b ORDER BY b DESC SEPARATOR ":") FROM z GROUP BY a;

在这里插入图片描述

聚合的算法

MySQL仅仅支持流聚合，而其他的数据库可能会支持散列聚合，流聚合是依赖于获得的存储在GROUP BY列中的数据（也就是没有GROUP BY是用不了聚合的），如果一个SQL查询中包含的GROUP BY语句多于一行，流聚合会先根据GROUP BY对行进行排序。

下面是流聚合算法的伪代码

sort result according to group by column  //根据group by的列对结果进行排序
foreach row in result:  //遍历排序后的结果
	begin
		//如果当前行不匹配当前的分组，证明当前的分组已经处理完了，要到下一个分组
		if the row does not match the current group by columns
		begin
			//将当前分组的聚合结果打印出来
			print current aggreage results
			//清空当前分组的结果
			clear current aggreage results
			//将当前分组设为当前的输入行，也就是当前行
			set current group by columns to input row
		End
	//将聚合结果更新乘当前的输入行
	update aggregate results with input row
End

例如，为了计算MAX这个聚合，流聚合会考虑每个输入行的情况，如果输入行属于目前的分组（也就是在同一块中），则流聚合可通过判断当前分组的MAX值与该行的值来比较，从而更新当前分组的MAX值，如果输入行不属于目前的分组了，则证明，目前分组的所有数据已经全部处理完了，要到下一个分组了，就将目前分组的结果打印出来，并把输入行作为新的分组，同时将该分组的MAX值设为该行的值。

附加属性聚合

对于要聚合的属性加一些附加属性，比如权重之类的。

连续聚合

连续聚合是按时间顺序对有序数据进行聚合的操作。

累积聚合

累积聚合是指组合从序列内第一个元素到当前元素的数据，比如查询出每个员工从开始到现在累积的订单数量和平均订单数量。

针对上面的查询问题，行号问题的话有两个解决方案，分别为使用子查询和使用联接，子查询的方法通常显得比较直观，可读性强，但是在要求聚合时，根据聚合算法，子查询可能需要为每一个聚合（每一个组）扫描一次数据，而联接方法通常只需要扫描一次数据就可以得出结果

使用联接来实现

SELECT 
	a.empid,a.ordermonth,a.qty AS thismonth, 
	SUM(b.qty) AS total,
	CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg 
FROM EmpOrders a 
INNER JOIN EmpOrders b 
	ON a.empid = b.empid
	AND b.ordermonth <= a,ordermonth 
	GROUP BY a.empid,a.ordermonth,a.qty
	ORDER BY a.empid,a.ordermonth;

这条SQL，我们依然按SQL的执行步骤来分析，首先看FROM和后面的连接表，是一个同表内联接，排除条件1为a.empid = b.empid，让员工的数据是与自己的联接起来，排除了跟其他员工联接的数据，排除条件2为 b.ordermonth <= a.ordermonth，这个有点意思，这个是为了减去重复的数据，因为联接首先进行的是笛卡尔乘积，比如A表有a,b两条数据，那么A表进行笛卡尔乘积的话，会出现(a,a)(a,b)(b,a)(b,b)，可以发现出现了(a,b)和(b,a)，这两个是重复了的，所以需要进行去重，而去重的方式就是规定一个方向（这里规定的方向就是b.ordermonth <= a.ordermonth，舍弃了另一个方向，b.ordermonth > a.ordermonth），这样我们就得到了去掉重复，且正确匹配的数据，然后进行GROUP BY操作，就可以得到每个员工的块数据。

当然，也可以使用关联子查询来进行实现

SELECT empid,ordermonth,qry AS thismonth,
	(SELECT SUM(b.qty) FROM EmpOrders AS b WHERE a.empid = b.empid
    AND b.ordermonth <= a.ordermonth) AS total,
    CAST((SELECT AVG(b.qty) FROM EmpOrders AS b
         WHERE a.empid = b.empid AND b.ordermonth <= a.ordermonth) AS DECIMAL(5,2)) 
         AS avg 
FROM EmpOrders a 
GROUP BY a.empid,a.ordermonth

其实这里跟上面执行步骤的不同就是，这里先进行分块，分好块后，到执行列的时候再进行过滤。

滑动聚合

滑动聚合是按顺序对滑动窗口范围内的数据进行聚合的操作，与累积聚合不同，滑动聚合不是统计开始计算的位置到当前位置的数据，是规定一个范围来进行数据统计的。比如统计最近三个月中员工每月的订单情况。

Pivoting

Pivoting是一项可以把行旋转为列的技术，在执行Pivoting的过程中可能会使用到聚合。

开放架构

开放架构是一种用于频繁更改架构的一种设计模式，利用关系型数据库可以非常有效地处理数据操纵语句（DML），比如INSERY、SELECT、UPDATE和DELETE，但关系型数据库对架构更改时是不方便的，比如表结构进行添加，删除，更改列（DDL），所以此时就有了开放架构这种设计模式。

因此，在频繁更改架构的情况下，可以在一个表中存储所有的数据，每行存储一个属性的值(比如身高属性)，多用VARCHAR来存储，因为其可容纳各种类型的数据。

下面的SQL生成一张开放架构的表

CREATE TABLE t11(
	id INT,
	attribute VARCHAR(10),
	VALUE VARCHAR(20),
	PRIMARY KEY(attribute,id)
);

INSERT INTO t11 SELECT 1,'attr1','BMW';
INSERT INTO t11 SELECT 1,'attr2','100';
INSERT INTO t11 SELECT 1,'attr3','2010-01-01';
INSERT INTO t11 SELECT 2,'attr2','200';
INSERT INTO t11 SELECT 2,'attr3','2010-03-04';
INSERT INTO t11 SELECT 2,'attr4','M';
INSERT INTO t11 SELECT 2,'attr5','55.80';
INSERT INTO t11 SELECT 3,'attr1','SUV';
INSERT INTO t11 SELECT 3,'attr2','10';
INSERT INTO t11 SELECT 3,'attr3','2011-11-11';

在这里插入图片描述
那么此时，如果我们要对表的结构进行修改，比如说增加一种属性的时候，我们仅仅只需添加行，使用INSERT即可，不需要ALTER TABLE（因为一个属性使用行来储存，不再是列），id是标识同一个对象的属性，比如id=1，总共有3行，也就是有3个属性，代表这个对象有3个属性（attr1和attr2和attr3），当然，使用这种方法形成的缺陷也很多，比如可能会用不了关系型数据库的完整性约束和SQL优化，同时查询数据变得不如使用之前的SQL语句更加的值观，可读性变差了，所以，对于利用开放结构设计的表，一般使用Pivoting技术来查询数据。

Pivoting技术需要和聚合一起使用(比如上面那个栗子，根据id来进行聚合，那么属于同个对象的属性就会被分在一个块中，对块进行处理数据即可)，首先要确定结果的行数与表中的行数的关系（也就是要知道一个对象最多有可以有多少个属性），对于开放结构表t，应该有3行五列，这可以通过分组id来得到，因此可以通过下列Pivoting技术进行行列互转以得到数据。

具体的SQL如下

首先根据id进行分组，分组后，然后判断行里面的attribute属性是什么，将这行的value输出出来，再将这个列改为attribute属性值，这样就实现了行转为列了，至于为什么要使用MAX，因为这里使用的是GROUP BY，所以一定要用聚合函数来取值，当然如果确保数值是唯一的，也是可以使用MIN的。其实这里利用的另一个知识点就是使用了冗余列，就是生成其他列。

下面就讲讲MAX函数里面的过程，首先，表已经根据id分好组了，MAX函数里面放的应该是一个列，这里使用了语法CASE WHEN … THEN … END,第一个MAX遍历了attribute列，只将值为attr1的留下，然后返回的是对应的value，此时要记录最大值，方便下一次找到attr1对应的value（只不过这里只有一个attr1而已），然后继续向下找，知道将这组遍历完返回最大值，下面的MAX也是如此。

SELECT    
	id,
	MAX(CASE WHEN attribute = 'attr1' THEN value END) AS attr1,
	MAX(CASE WHEN attribute = 'attr2' THEN value END) AS attr2,
	MAX(CASE WHEN attribute = 'attr3' THEN value END) AS attr3,
	MAX(CASE WHEN attribute = 'attr4' THEN value END) AS attr4,
	MAX(CASE WHEN attribute = 'attr5' THEN value END) AS attr5 	
FROM t11 GROUP BY id;

在这里插入图片描述
这种旋转方式是非常高效的，因为对表只进行了一次扫描，另外，这是一种静态的Pivoting，即用户必先事先知道一共有多少个属性，然而对于一般的开放架构表，用户都会定义一个最大的属性个数，这样可以比较容易地进行Pivoting。

格式化聚合数据

Pivoting技术还可以用来格式化聚合数据，一般用于报表的展现。

举个栗子

CREATE TABLE t12(
	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)
)ENGINE=INNODB,CHARSET=utf8;

INSERT INTO t12 SELECT 1,'2010-01-02',3,'A',10;
INSERT INTO t12 SELECT 2,'2010-04-02',2,'B',20;
INSERT INTO t12 SELECT 3,'2010-05-02',1,'A',30;
INSERT INTO t12 SELECT 4,'2010-07-02',3,'D',40;
INSERT INTO t12 SELECT 5,'2011-01-02',4,'A',20;
INSERT INTO t12 SELECT 6,'2011-01-02',3,'B',30;
INSERT INTO t12 SELECT 7,'2011-01-02',1,'C',40;
INSERT INTO t12 SELECT 8,'2009-01-02',2,'A',10;
INSERT INTO t12 SELECT 9,'2009-01-02',3,'B',20;

生成的表

在这里插入图片描述
t是一张汇总表，显示了订单号、订单日期、员工编号、消费者编号和订单数量，要在此汇总上进一步统计每个消费者每年的订单数量。

第一想到的肯定是使用分组，根据顾客和年进行分组

SELECT custid,YEAR(orderdate),SUM(qty) FROM t12 GROUP BY custid,YEAR(orderdate);

在这里插入图片描述
查出来的数据没有什么问题，只是显示不够直观，要是想直观一点，让年份旋转呢？

SELECT custid,
SUM(CASE WHEN orderdate = 2009 THEN qty END) AS "2009",
SUM(CASE WHEN orderdate = 2010 THEN qty END) AS "2010",
SUM(CASE WHEN orderdate = 2011 THEN qty END) AS "2011" 
FROM (SELECT custid,YEAR(orderdate) AS orderdate, qty FROM t12) AS t GROUP BY custid;

结果为
在这里插入图片描述
同样使用pivoting技术即可，只不过这里要改为sum，因为qty需要进行叠加的，而且还要使用独立子查询，因为sum函数里面无法使用YEAR来进行格式化orderdate，要嵌一层子查询进去提前将orderdate格式化，而且这里不需要对orderdate进行分组，因为我们只需根据custid进行分组，拿到对应custid的每一年的所有数据，然后进行pivoting整理就可以了（遍历orderdate，是2009的就返回qty进行累加，将这列取名为2009，是2010的也是返回qty进行累加，将这列取名为2010，下面的同理，所以不需要根据orderdate进行分组）。

然后再进行简单的优化，就是将NULL改为0

这里可以使用IFNULL函数，格式为IFNULL(SQL,SQL为NULL时的代替值)

SELECT    
custid,
IFNULL(SUM(CASE WHEN orderdate = 2009 THEN qty END),0) AS "2009",
IFNULL(SUM(CASE WHEN orderdate = 2010 THEN qty END),0) AS "2010",
IFNULL(SUM(CASE WHEN orderdate = 2011 THEN qty END),0) AS "2011"
FROM (SELECT custid,YEAR(orderdate) AS orderdate,qty FROM t12) AS t GROUP BY custid;

在这里插入图片描述

Unpivoting

Unpivoting是Pivoting的反向操作，即将列旋转生成行

先生成我们想要的数据

CREATE TABLE p(
	custid VARCHAR(10) NOT NULL,
	y2009 INT NULL,
	y2010 INT NULL,
	y2011 INT NULL,
	PRIMARY KEY(custid)
)ENGINE=INNODB,CHARSET=utf8;

//将之前数据插入进p表
INSERT INTO p 
SELECT    
custid,
IFNULL(SUM(CASE WHEN orderdate = 2009 THEN qty END),0) AS "2009",
IFNULL(SUM(CASE WHEN orderdate = 2010 THEN qty END),0) AS "2010",
IFNULL(SUM(CASE WHEN orderdate = 2011 THEN qty END),0) AS "2011"
FROM (SELECT custid,YEAR(orderdate) AS orderdate,qty FROM t12) AS t GROUP BY custid;

生成的表是这样的

在这里插入图片描述
思路很简单，将表变成列向，首先要对没行数据复制三份，因为一行有三个不同年份的数据，所以要复制三份，并且每一个副本要加上属于自己年份的标识，这个使用交叉联接就可以实现了(不过需要一个只有单个列的派生表，刚好用三个年份来形成这个派生表)，然后，对列进行处理，遍历复制了三份的表，根据自己年份的标识来选要哪个列的数据

SELECT  * FROM p,(SELECT 2009 AS orderyear UNION ALL SELECT 2010 AS orderyear UNION ALL SELECT 2011 AS orderyear) AS p2;

在这里插入图片描述
我们需要的副本就生成了，然后下面的就简单了，根据orderyear里面标识的年份来选择我们要哪一列数据(从y2009、y2010、y2011里面选)

SELECT  
CASE orderyear 
	WHEN 2009 THEN y2009
	WHEN 2010 THEN y2010
	WHEN 2011 THEN y2011
	END AS qty,orderyear,custid 
 FROM p,(SELECT 2009 AS orderyear UNION ALL SELECT 2010 AS orderyear UNION ALL SELECT 2011 AS orderyear) AS p2;

在这里插入图片描述
当然还可以进行排除qty为0的数据，这里只需后面简单加个where过滤即可。

CUBE和ROLLUP

MySQL数据库支持CUBE和ROLLUP关键字，作为GROUP BY子句的选项，应用在对多个维度进行聚合的OLAP查询中。可以将ROLLUP看作CUBE的一种特殊情况，目前MySQL数据库仅支持CUBE关键字，但底层并没有实现，所以用不了，不过ROLLUP是实现了的。

ROLLUP

ROLLUP是根据维度在数据结果集中进行的聚合操作，假设用户需要对N个维度（N列，也就是GROUP BY指定的列数）进行聚合查询操作，普通的GROUP BY语句需要N个查询和N次GROUP BY操作（N个查询是对指定的列进行查询得到值，N次GROUP BY是指分组在分组，比如有GROUP BY指定了三个列，根据第一个列进行分组后，然后第一次分组后再进行根据第二个列分组，然后再根据），而ROLLUP的优点是一次可以取得N次GROUP BY的结果（即一次就可以完成），这样可以提高查询的效率，同时大大地减少了网络的传输流量。

SELECT  empid,custid,YEAR(orderdate)`year`,SUM(qty) qty FROM t12 GROUP BY empid,custid,YEAR(orderdate) WITH ROLLUP;

在这里插入图片描述
WITH ROLLUP与一般的GROUP BY形成的结果几乎一致，但就是最后面产生了(NULL,220)的数据，这表示了最后对每一组或块的SUM(qty)又进行了一次SUM(qty)的操作，也就是将每一组或块的SUM(qty)又叠加了起来。

ROLLUP的最后一行出现了(NULL,NULL,NULL,220)，这是表示最后的聚合，我们可以关注到倒数的四行，是不是感觉有点特殊，这里进行解释一下，(4,A,2011)表示对(empid,custid,year)对这三列进行分组的聚合结果，(4,A,NULL)是表示对(empid,custid,null)这三列进行分组的聚合结果，(4,null,null)是表示对(empid,null,null)进行聚合的结果，(null,null,null)就是最后的聚合结果了，其实前面的一些数据也是类似的规律，所以不难发现其实ROLLUP等同于下面这条SQL(要记得改SQL_MODE才能运行下面SQL)

SELECT empid,custid,YEAR(orderdate) `year`,SUM(qty) qty FROM t12 
GROUP BY empid,custid,YEAR(orderdate) 
UNION 
SELECT empid,custid,YEAR(orderdate) `year`,SUM(qty) qty FROM t12 
GROUP BY empid,custid 
UNION 
SELECT empid,custid,YEAR(orderdate) `year`,SUM(qty) qty FROM t12 
GROUP BY empid
UNION SELECT NULL,NULL,NULL,SUM(qty) qty FROM t12;

但执行却完全不一样，上面这条SQL需要查4次表，而ROLLUP只需要查一次表

ROLLUP只需1次表扫描操作即可得到全部的结果

使用ROLLUP要注意以下几个方面

  • ORDER BY
  • LIMIT
  • NULL

ORDER BY语句一般用来完成排序操作，但是在含有ROLLUP的SQL语句中不能使用ORDER BY关键字，可见这两者为互斥的关键字。

ROLLUP的SQL语句可以使用LIMIT，但因为不能用ORDER BY，LIMIT的结果阅读性挺差的，所以一般不用。

如果分组的列中有NULL值，那么ROLLUP的结果可能是不正确的，因为在ROLLUP中进行分组统计时值NULL是具有特殊意义的，因此在进行ROLLUP操作时，可以先将NULL转为一个不可能存在的值，或者没有特别含义的值。

比如使用

IFNULL(XXX,0)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值