Java最新MySQL(五)：聚合和旋转操作（Pivoting技术），都2024年了，还不会Redis

ghfgjfg679

于 2024-05-11 14:25:35 发布

阅读量679

点赞数 27

分类专栏：程序员文章标签： java 面试学习

本文链接：https://blog.csdn.net/ghfgjfg679/article/details/138714535

版权

程序员专栏收录该内容

207 篇文章 0 订阅

订阅专栏

最后

无论是哪家公司，都很重视基础，大厂更加重视技术的深度和广度，面试是一个双向选择的过程，不要抱着畏惧的心态去面试，不利于自己的发挥。同时看中的应该不止薪资，还要看你是不是真的喜欢这家公司，是不是能真的得到锻炼。

针对以上面试技术点，我在这里也做一些分享，希望能更好的帮助到大家。

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

详细说明一下这些特性

DISTINCT 可以对GROUP_CONCAT里面的列进行去重
exprs 就是GROUP_CONCAT里面指定要拼接的列
ORDER BY {col_name} {ASC|DESC} 可以根据GROUP_CONCAT指定的列进行排序，ASC升序，DESC降序
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)

总结

本文从基础到高级再到实战，由浅入深，把MySQL讲的清清楚楚，明明白白，这应该是我目前为止看到过最好的有关MySQL的学习笔记了，我相信如果你把这份笔记认真看完后，无论是工作中碰到的问题还是被面试官问到的问题都能迎刃而解！

MySQL50道高频面试题整理：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

NOT NULL,

y2009 INT NULL,

y2010 INT NULL,

y2011 INT NULL,

PRIMARY KEY(custid)

总结

MySQL50道高频面试题整理：

[外链图片转存中…(img-nsY0v1MO-1715408712361)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

ghfgjfg679

关注

27
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
Java最新MySQL(五)：聚合和旋转操作（Pivoting技术），都2024年了，还不会Redis

本文从基础到高级再到实战，由浅入深，把MySQL讲的清清楚楚，明明白白，这应该是我目前为止看到过最好的有关MySQL的学习笔记了，我相信如果你把这份笔记认真看完后，无论是工作中碰到的问题还是被面试官问到的问题都能迎刃而解！MySQL50道高频面试题整理：本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录需要这份系统化的资料的朋友，可以点击这里获取NOT NULL,
复制链接

扫一扫