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

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的反向操作，即将列旋转生成行