Oracle/SQL 分组/分析/聚合比较通俗深入的解析

最新推荐文章于 2024-04-10 11:51:16 发布

kevinmeanscool

最新推荐文章于 2024-04-10 11:51:16 发布

阅读量1.1k

点赞数

分类专栏： SQL 文章标签：分组分析函数聚合函数 Oracle SQL

本文链接：https://blog.csdn.net/kevinmeanscool/article/details/103381750

版权

SQL 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

引言

为什么会出现这个异常？

什么是SQL行源(SQL Row Source)生成?

什么是聚合(Aggregation)函数？

分组到底分的是什么组？

以上问题会如何解决最初的问题？

引言

在SQL实践的过程中，经常会出现一个异常。

“不是单组分组函数”，从字面意思理解起来似乎我们使用了一个无法对单个组进行分组的函数，而函数似乎就是我们经常使用的count、sum之类的封装接口。但其实，这样理解是100%不正确的，为何这样讲，如果读者真的迫切有问题想要解决，想了解这个事情，就请耐心的往下读，通俗，但需要仔细阅读，如果读者希望深入浅出，那再合适不过了。

为什么会出现这个异常？

我们拿到一个问题，不妨先给它一个明确的终极目标-----“为什么会出现这个异常？”，我们如果能清晰的把这个问题解决，也就自然明白如何修复此类异常。对于Oracle或者SQL，区别与Mysql最显著的特征就是不开源。不开源，就意味着我们无法Debug它的源码，从根本上梳理该异常的判断条件，执行逻辑。我们在求学的过程中一定听说过这样两句话：

于是，我们想搞清楚此类问题，就不得不读一下官方文档（点击这个超链接传送门直达）。

这里要提到一点，我们选择数据库，选择版本，进入文档后，会发现，是一个全英文的官方文档，即使拥有一定英语基础的读者，读起来也很难做到事半功倍。所以这篇文章作者利用时间阅读了相关的内容，并在资源站上找到了一份十分友好的中英对照Oracle 11g官方文档（文档将会放置在附件中）。

我们可以从官方文档中得知两件事情：

文档中说明了任何ORA异常，即被Oracle虚拟机捕捉到的异常，都来源于SQL行源生成的过程；
文档中并未提及分组函数这样的说法，只有group by关键词，以及分析函数与聚合函数。

也就是说，我们i想解决最初的问题，需要先搞清楚1、2点中提到的内容，比如：

什么是SQL行源(SQL Row Source)生成?
什么是分析(Analysis)函数？
什么是聚合(Aggregation)函数？
分组到底分的是什么组？
以上问题会如何解决最初的问题？

什么是SQL行源(SQL Row Source)生成?

这个是一种官方的说法，行源生成，其实行源生成，需要从一段SQL如何被编译器解析来定位。

我们平时写一段SQL，写好，执行，提交，似乎存储过程就这样被完成了。其实，它底层所作的事情远远超出我们的想象。上述过程翻译过来就是：

SQL解析

如图所示，我们的SQL提交后，首先会被编译器解析，其中包括:语法、语义、共享池。官方的说法其实也十分通俗易懂：

语法解析：检查关键词结构是否符合状态机的规范；

这个意思其实就是，我们使用的select、from、where这些关键词，是否存在，这个过程是去与数据库中系统关键字表进行比对，其次，还会确定是否满足一个子查询下select、from、where等关键词的逻辑顺序，比如 select * from dual;这样就是一个正确语法逻辑，而 The * selected from dual; 这样就是一个不符合语法规范的DDL语句，虽然两句话表达的意思相同。

语义解析：检查语法解析后的文本语义是否有意义；

这个意思其实就是，在语法解析通过后，会对关键词的前后名词（即非关键词外的名词，比如表名啦、字段名等等）是否有意义，有意义的意思就是说，是否存在，是否属于某个名词，最清晰的解释就是是否物理的存在。比如：select * from dual;这个语义解析就会通过，因为 * 代表着all，dual代表着实际存在的系统表。而select * from inexistentTable；这个表假设我们没有建过，也不是系统表，表物理的不存在，所以在语义解析时就会不通过，返回一个异常。

共享池检查：检查通过语义解析的名词资源，是否正在被占用；

这个很好理解，就是说，我们的资源如果存在临界情况，那么编译器在编译的时候会去查一下，这个资源允不允许被访问，允许访问的情况下，有无排他锁，是否正在被占用，如果被占用，我们可以选择等待或是放弃，一般默认是等待排他锁的释放。如果该资源没有被物理的访问，那么我们就把锁加上了，避免脏数据产生。

SQL优化器

什么是SQL优化器，可以对于许多读者，这是一个全新的名词。所谓SQL优化器，顾名思义就是SQL执行前，从时间代价、空间代价考量，生成执行方案的优化软件。许多SQL虚拟机中，内置的便是CBO(Cost-Based Optimizer)，基于成本代价的优化器。

从Oracle 6开始，优化器就支持下面4种表连接方式：

— 嵌套循环连接（Nested Loop Join）

— 群集连接（Cluster Join）

— 排序合并连接（Sort-Merge Join）

— 笛卡尔连接（Cartesian Join）

这里就不详细展开，有兴趣的读者可以阅读：https://blog.csdn.net/xstardust/article/details/81188972 这篇博文，十分的详细。我们在这里只需要了解，我们的SQL经过CBO后，会生成一个成本代价理论最小的执行树。

执行树被树物理的存储，指导查询器先查询哪个结果到内存上并且以什么样的查询方式，并且，根据执行树计划执行过后的每个子树，都会产生一个行源组。（这里终于引出了我们的主角--SQL行源）

行源树的生成

行源树就是SQL根据执行树计划地执行后生成的行集的组合，也就是说一个行源树有多个行源组。

举例：

select * from t1,t2 
 where t1.no = t2.no and
       t1.no < 100000;

如果我们自己去计算成本的话，那么我们肯定会先执行t1.no<100000,再去进行Hash关联，从而得到最终结果。也就是这样一个执行树：

由于我们是前序生成的，所以我们执行时，就用前序遍历。首先我们执行：

根据No索引在t1表中查询No小于100000的所有记录，并记录下每行对应的HashID。

假设，我们该执行查询出来的结果是：

我们这时候要注意了！一个查询结果始终对应一个行源组，也就是说，即使我们这里看着是18条记录，他们在物理上是属于一个行源组的，可以这么理解：

接下来它会根据执行树执行hash_join，所谓hash_join，简单的对于两个表来讲，hash-join就算讲两表中的小表（称S）作为hash表，然后去扫描另一个表(称M)的每一行数据，用得出来的行数据根据连接条件去映射建立的hash表，hash表是放在内存中的，这样可以很快的得到对应的S表与M表相匹配的行。

所以我们左子叶的节点执行过后产生一个行源组，作为关联查询的一个表，与另一个表关联，查询过后产生的结果根据“一个查询结果始终对应一个行源组”，就会产生一个具有1个或多个行记录的行集（行源组）。

由于我们没有进行分组操作，目前而言，即使有6条记录，它也是一个行源组。并且执行树遍历完成，此时返回查询结果，这个行源组便成为了查询结果。任何复杂的操作都可以通过这样的过程去单步它的执行过程以及每一步的行源组。

我们探究了SQL行源生成，明白了一段SQL在编译器中经历了怎么样的过程，这些基础知识将作为我们解决问题的关键。

什么是分析(Analysis)函数？

按官方的解释，所谓分析函数就是对行源组的每一行进行数学统计处理的过程。

其实有一种十分通俗的理解，就是对行维度进行处理的函数。

首先，我们可以明确一个点，partition by并不是函数，而是分组关键字之一，它会根据字段去分组，所以会把一个含有n个行记录的1个行源组，分解为含有y个行记录的z个行源组(如果分组列相同，将会归为一个行源组，不会去除)。这个概念与国内目前很多人的理解有很大的出入，即很多人都在错误的使用分析函数的称呼。其实像sum、count并不是一直所属聚合函数，相反它们也具有分析函数的特性。

许多分析函数同时也是聚合函数，比如sum()函数，这样使用就是聚合函数。

SQL> select deptno,sum(sal) sum_sal fromemp group by deptno;

而这样使用就是分析函数。

SQL> select distinct deptno,sum(sal)over(partition by deptno) sum_sal from emp;

它们得出的结果是相同的，但要注意，将sum函数作为分析函数时，使用了distinct关键词（去重）从而导致结果相同，否则会在查询结果上加上“每一”，这个概念，比如上述sql去除distinct关键词，寓意就变为：对每个雇员计算他/她所在的部门的薪金总数。

什么是聚合(Aggregation)函数？

按官方的解释，所谓聚合函数就是对行源组的每一列进行数学统计处理的过程。

其实与分析函数相对的，也有一种十分通俗的理解，就是对列维度进行处理的函数。

我们可以看到，一个行源组的记录被数学统计聚合为一个综合的结果，也可以明白，不论聚合函数还是分析函数，其API可以接收的对象始终是一个行源组。

分析函数、聚合函数也只是我们逻辑上使用时，一种区别操作的称呼而已。像sum、count、max底层等并没有对分析和聚合进行区分，像Oracle时使用关键词partition by/group by 来进行行源组拆解。所以接下来我们就要明白，分组到底分的是什么组？

分组到底分的是什么组？

其实根据我们上文提到的点，我们可以明白分组其实分的就是行源组，不是单单的结果，分组后的行源组使用得当也可以作为子查询，供上层使用，所以看到这里，是不是对分组有了一个更为清晰的认识了呢？

我们常见的分组关键字有两个 group by，partition by。

他们的区别其实在于：group by以所选字段去重的进行分组，而partition by以所选字段进行分组，并对相同列值的分为1组，赋予同样的组HashID。

对于分组，我特别要在这里提到一个大家经常会忽视的现象。就是 select * /select 某个字段，我们查询的结果是1个行源组还是多个行源组呢？我们不妨做个实验。

我们可以看到，返回了两个行记录，那么是1个行源组还是2个行源组呢？我们可以用函数去实验一下。

我们可以发现，这个sql在语义分析的时候被拦截下来，为什么呢，因为*在SQL中也是一个关键字，我们语义分析是不允许关键字作为语义的，只允许名词。所以，我们可以换成两个名词来尝试。

出现了参数个数无效的异常，我们说过，函数不论作为分析函数还是聚合函数去使用，API只允许接收一个行源组。那我们只用一个字段进行尝试。

我们可以发现聚合出了一个最大值，我们关键的问题来了：这就说明了，select * /select 字段返回的是一个行源组吗？ NONONO大错特错。我们要注意，这个sql 的执行树，我们首先会根据索引遍历t1的id，返回一个t1所有行的行源组，然后对这个行源组进行聚合。所以我们一定要清楚，结果和行源组并不是一回事！作者建议读者阅读sql的时候养成一个好习惯，就说从from开始阅读，因为select后的过程往往是最终执行的过程。

那么我们的问题依旧存在，select * /select 字段返回的是一个行源组吗？

我们可以这样写一种形式，就比如，我们想知道这个最大值的fname是谁。

终于我们遇到最初的问题，这个异常为什么出现呢？根据前文的阅读，读者应该似乎明白了一点。导致这个的原因是：max作为聚合函数时，接收的对象并不是1个行源组而是多个。怎么验证呢？请观察如下查询：

我们可以发现，这两种写法查询结果相同。其实后者的写法才是一个查询语句最完整的写法，当我们查询单字段时，SQL允许省略group by组合，但会在虚拟机内执行全字段分组，并返回所需查询的结果。也就是说，我们平时的select * /select 字段省略group by关键字，返回的结果也是一个多行源组结果。所以

select t.fanme,max(t.fmoney) from t1 t;

实际上是 select t.fanme,max(t.fmoney) from t1 t group by t.fanme,t.money;

在聚合前对检索t1的1个行源组进行了全字段分组，产生了2个行源组，再统一聚合，max表示吃不下，行源组太多，便反馈了一个这样的异常。

以上问题会如何解决最初的问题？

作者最近在阅读http://cs231n.stanford.edu/syllabus.html课程笔记时，发现斯坦福大学的思考方式往往习惯将一个问题的大的框架写出来，然后把这个问题分解为若干子问题，直到寻找到合适的解决方法变不再分解，从而最终将大问题解决。本文也是尝试用这样的一种思考方式解决最初遇到的问题，以及对沿子问题所用到的知识梳理进行总结。

十分感谢本文中涉及到的引用文章作者及译者，如有疏漏错误请读者指出。

附件：Oracle11g官方文档中英对照版.pdf https://download.csdn.net/download/u011433684/10001623