关于sql语句:关系代数和SQL语法

数据分析的语言接口

OLAP 计算引擎是一架机器，而操作这架机器的是编程语言。使用者通过特定语言通知计算引擎，须要读取哪些数据、以及须要进行什么样的计算。编程语言有很多种，任何人都能够设计出一门编程语言，而后设计对应的编译器做解析。编程语言从分类上来说，能够分为命令式，申明式。

命令式编程语言是咱们最常见的编程语言，C/C++/Java 等都是命令式编程语言，这类语言明确的通知机器应该执行什么样的指令，留给编译器优化的空间很小了。

申明式编程形容程序应该取得什么后果，至于如何做到，并不关注细节。SQL 就是一种申明式编程语言。例如 SQL 语句 select count(1) from department where kpi =3.25，指明计算 kpi=3.25 的人数，但不会具体指定如何实现计算。这给后续的优化器留下了很大的操作空间，优化器能够依据 SQL 的需要和理论的数据做各种各样的摸索，寻找到最佳的执行形式。

一个优良的剖析语言应该具备以下几个特色：

语言简略，门槛低
语意明确，无歧义
材料丰盛，不便学习
生态丰盛，工具多
不便扩大，可编排简单的逻辑

SQL 是一种历史悠久的，应用比拟宽泛的剖析语言。在关系型数据库时代就宽泛应用的一种语言。在 21 世纪初，呈现了 MapReduce 算法，数据分析师须要编写 MapReduce 程序来剖析数据。MapReduce 程序是一种命令式语言，编写过程十分麻烦，堪比写程序，这就须要数据分析师不仅具备算法能力，还要具备工程能力，应用体验十分蹩脚。这就须要两个团队来单干，BI 团队把剖析需要传递给开发团队，由开发团队去开发分析程序。为了改善剖析体验，呈现了 SQL on Hadoop 的解决方案，典型的如 Hive，提供 SQL 接口，并把用户输出的 SQL 转写成 MapReduce 执行打算，因此极大的晋升了数据分析的体验，实现了 BI 团队的自主剖析，升高了数据分析的门槛，大大增加了受众范畴。因而，SQL 的影响力是十分大的。从 Hive 开始，大数据的次要应用接口就转移到了 SQL 上。而工程师们能够在 SQL 这张皮之下，分心的优化性能，无缝的降级计算引擎，放弃应用接口的一致性。

SQL 的语法简略，逻辑清晰，理解了最简略的查问语句之后，就能够嵌套多层表白很简单的逻辑。SQL 基于关系代数，有实践根底，保障语意明确没有歧义。SQL 的倒退历史十分长远，因此学习材料也比拟多，不便新入门者学习。同时围绕 SQL 的生态也比拟丰盛，有很多工具应用 SQL 做剖析。

除了 SQL 之外，也有一些软件推出自定义的语言，例如 Elasticsearch 应用 Lucene 语法，Prometheus 推出了自定义的 PromQL，而 Splunk 推出了 SPL。每一种新的语法，对于新用户而言，都存在肯定的学习门槛。因此都不如 SQL 应用宽泛。能够说 SQL 是数据分析的事实标准。

数据模型

数据模型（DataModel）用于形容数据在数据库中的组织模式。常见的模型有关系模型（Relational），键值模型（Key/Value），图模型（Graph），文档模型（Document），列簇模型（Column-family）等。

关系型数据库采纳关系模型。Redis 采纳键值模型。图数据库采纳图模型。MongolDB 采纳文档模型。关系模型中的关系有点绕口，在英文中是 Relational，硬翻译成了关系，我的了解，关系指的是一些相互之间有关系的属性组成的一个实体，因为各个列属性之间存在关联关系，而被称为一个关系，其实指的是属性之间的相关性，这种相关性体现在：属于同一行；满足列之间的约束条件；满足行之间的约束条件；满足不同关系之间的约束条件。通过不同的约束条件，是全副的数据造成一种有组织的存在。

数据库通过关系模型，定义出一个个关系实体，确保内容之间满足肯定的束缚标间，并且提供编程接口去读写数据库内容。一个数据库蕴含一堆关系，每个关系是一个多行多列的表格。每一行的各个列之间是相干的，也可能会定义一些约束条件。行与行之间，也可能通过定义惟一键（Primary Key），定义排序形式来束缚行之间的关系。关系与关系之间，能够通过内部键来实现。

这种列之间和行之间的束缚关系，在 OLTP 场景中比拟实用，因为 OLTP 关注的数据自身，因而在存储数据时，更多关注数据的存储模式。而 OLAP 关注的数据的剖析，所以在数仓中，这些约束条件是弱化的，因而，在数仓中，咱们只需关注一张多行多列的表格即可，像 PK、排序这类束缚属性，更多只是用来做数据减速的伎俩。关系模型用来作为一种紧密的实践，给执行器的优化提供实践根底。然而这个名字毕竟太绕口，在后续文章中，除非波及到关系模型相干的实践，会应用关系这个词，个别状况下，会用表来指代一个关系。

关系代数(Relational Algebra)

关系模型和关系代数是 SQL 的实践根底。代数不仅是咱们所熟知的简略的加减乘除等数学计算。在计算机行业，咱们见到过多种 algebra，在神经网络中罕用的线性代数（linear algebra），在电路中用到的布尔代数（boolean algebra），香农把布尔代数带入到了逻辑电路设计中，为计算机二进制计算提供了理论依据。此外还有 N 多种 algebra，这里不一一列举。

关系代数，源自于汇合代数，讲述汇合之间的变换关系。关系代数中的一系列操作，承受一个或两个关系作为输出，产生一个新的关系作为后果。因为输出和输入都是一个关系，咱们能够串联多个算子，造成更加简单的算子。关系代数中蕴含的算子有：σ (select，从一个关系中筛选出局部行，造成一个新的关系),Π(projection，从一个关系中筛选出局部列，造成一个新的关系),∪(Union，合并两个关系), ∩(Intersection，取两个关系的交加局部), –(difference，取两个关系的差集局部), ×(Product，两个关系的笛卡尔积),⋈(Join，两个关系在满足某些条件下的连贯),ρ（Rename，重命名关系中的列）, ←(Assignments，把一个长期的查问命名成一个新的关系), δ(Duplicate Eliminating，去重), γ(Aggregation，对局部列做聚合计算，后果造成一个新关系), τ(Sorting，排序后果造成一个新关系)。这里定义了罕用的关系操作，名字曾经示意出了其操作的含意，在这里不再介绍每个操作的明细了。在语法解析和优化器阶段咱们会再次接触到关系代数，并且借助于关系代数的理论依据，来做一些语法树上的转换。在这里咱们只须要晓得在关系代数上有这些操作，并且在之后的 SQL 语法上看到如何用 SQL 语法来表白这些操作。

SQL

SQL 语言的倒退历史

SQL 的倒退历史，能够追溯到机械化数据分析的历史。在 20 世纪初，IBM 次要的业务是打孔卡业务，也就是应用卡上的孔来记录信息，而后利用电路的通断判断是否有孔，并通过电路驱动机械安装，累计计算结果。打孔卡相似咱们古代应用的答题卡，答题卡的每一个题目，都提供了四个选项，而后用铅笔涂黑对应的选项；打孔卡不同的中央在于，选中的局部穿透成孔，当搁置到电路板上时，有孔的局部会有电流通过，进而触发之后的动作。这是在过后是一项十分先进的数据分析办法，相较于古老的依赖人去计数，也让大数据的自动化剖析成为可能。在 20 世纪初，要统计数千万人口的信息，须要投入大量的人力资源，而打孔卡这种创世纪的创造，带来了数据分析行业的疾速倒退。因而能够说 IBM 的业务次要是提供数据分析的机器，次要的客户场景是联邦政府的人口普查，以及业机构做商业剖析。这个时候，数据存储以来打孔卡，而数据计算是机械安装，计算结果输入到打印机。

到 20 世纪 50 年代，随着电气化的倒退，磁带取代打孔卡成为新的存储设备，电气安装取代机械安装做计数。计算结果能够持续存储到磁带上。磁带的存储空间很大，不过磁带的毛病是只能程序读写，这导致数据处理的程序不得不适应这种个性依照程序解决。

到了 60、70 年代，磁盘被创造进去，磁盘能够被随机读写，这极大的扭转了数据处理形式。数据结构无需思考数据之间的程序，一些更加简单的数据模型被创造进去，例如网状模型或者层次化模型。1970 年，Edgar Codd 定义了关系模型，给出了非过程式的查问数据的办法，关系型数据库诞生了。关系模型十分简洁，并且提供了实践根底。非过程式的查询方法，屏蔽了实现的细节，使用者只须要申明所须要的后果即可，实现的过程则交给优化器给出最优的执行打算，这极大的升高了应用门槛。关系模型的发明者也因而取得了图灵奖。只管关系模型在学术上十分吸引人，然而在事实中，在性能上还比不上曾经存在的数据库。

到了 70 年代前期、80 年代，IBM 推出了一个突破性的我的项目 System R，在我的项目中研发了至关重要的可能使关系型数据库十分高效的技术。在 System R 中，IBM 推出了 SQL 的最晚期版本，称为 Sequal，起初演化成了 SQL（Structed Query Language 结构化查询语言）。这个我的项目尽管是个原型，然而它促成了之后 IBM 推出了第一个商用的关系模型的数据库产品 System/38(1979),SQL/DS(1981),DB2(1983)。其中 DB2 目前还是沉闷的商用数据库，在大学中也有 DB2 的应用课程。至此，SQL 语言呈现了，并且被其余的商用数据库系统所采纳，比方 Oracle 的数据库。在数十年内，SQL 语言凭借着其易用性，击败了其余须要关怀底层实现的数据库产品，成为了事实上的规范。

1986 年 ANSI 规范推出了 SQL 规范，称为 SQL86，就是咱们常说的 ANSI SQL。之后规范通过陆续补充，以增加新的个性，陆续呈现了 SQL89，SQL92，SQL1999（正则式，触发器，OO），SQL2003（XML, 窗口函数，Sequence，自增 ID），SQL2006，SQL2008（清空表语法，Fancy Sorting），SQL2011（长期表，管道式 DML），最近的是 SQL2016（Json，多态表）。

一般来说，一个数据分析系统，不肯定齐全遵循 SQL 的规范，这次要是由剖析零碎的特有个性所决定的，有些个性，在 SQL 规范里边是没有的，所以个别会在 SQL 规范上做一些拓展，号称是兼容 ANSI SQL。一个零碎须要反对的最小性能汇合是 SQL92 规范。

SQL 的性能

SQL 语法蕴含了几个类别的性能，别离是

Data Manipulation Language（DML）：数据操作语言，用于增删改查数据。
Data Definition Language（DDL）：数据定义语言，用于定义表的格局。
Data Control Language（DCL）: 数据管制语言，用于管制权限等。

尽管 DML 和 DCL 是 SQL 零碎的根底性能，本文的关注重点更多是数据处理的技术，以及如何放慢数据处理的技术，因而更多关注 DDL。在 DDL 中，也有增删改查，在这几项中，本文更多关注查的局部内容，即如何放慢数据的读取和计算。而数据的写入、存储局部的优化伎俩，也是为了满足减速数据计算的目标。

SQL 的处理过程

SQL 全称 Structed Query Language(结构化查询语言)。SQL 语法简略，易学易用，是数据分析畛域最通用的语言。SQL 是数据分析的操作工具，对于用户而言 SQL 代表浙用户的操作语义，然而对于程序而言，只是接管到一串字符串。程序须要了解 SQL 的意义，要通过词法剖析、语法分析、语义剖析、结构成形象语法树。词法剖析、语法分析是十分根底的操作。大学的计算机的编译原理课程应该蕴含了本局部内容，词法剖析和语法分析的模式是固定的，玩不出花色，无助于晋升计算速度。不过作为 OLAP 引擎中必不可少的第一环，还是有必要对词法剖析和语法分析做出简略的介绍，有助于理解后续章节中的查问打算和优化器，然而本章不会占用太多篇幅，本文的重点是对于计算速度的内容。

开发者也能够研发自定义的剖析语言，只有语言合乎肯定的规定，没有歧义，在语义上残缺，也能过称为一种语言。不过开发一个新的语言十分艰难，大多数的新语言采纳程序式编程，每一个短语示意一个简略的操作；或者采纳管道式申明语法，每一部分代表输出，计算和输入，然而要定义一种可能有限扩大而没有歧义的语法是很难的。在语义残缺水平上是不能和 SQL 相比拟的。无论是开发一门新的语言，还是采纳 SQL，流程都和下图相似。OLAP 引擎解析 SQL，生成形象语法树，再转化成逻辑执行打算，通过优化后，生成高性能的算子组合。这就是编译和优化的过程。

图 2 -1 程序编译和 SQL 编译

在理解编译之前，咱们首先理解一下 SQL 的构造定义。SQL 是围绕着关系进行的。能够在关系上定义各种操作，也能够定义多个关系的操作。

关系

SQL 操作的对象是结构化数据。SQL 语法的根底语法以及嵌套扩大，都是围绕着“关系”进行的。“关系”能够设想成数据库中的表，由多行多列组成。一个 SQL，承受一个或多个“关系”的输出，并输入一个“关系”。在嵌套查问时，外部查问输入一个两头“关系”，并作为外层查问的输出“关系”，相似于 Linux 命令行中的管道语法。在下文中，用“表”来示意“关系”。

SQL 语法

单表上的操作

在一个表上，能够进行过滤(WHERE)、转换(scalar 函数)、聚合(聚合或分组聚合)、聚合后过滤（HAVING）、排序（ORDER BY）、投影（SELECT）、截断行数（LIIMIT）等操作。各个操作之间的执行工夫存在先后顺序。一个典型的 SQL 语法如：

[WITH with_query [,...]]
SELECT expr
   FROM TABLE
  WHERE bool_expr
  GROUP BY columns 
  HAVING Condition 
  ORDER BY expr 
  LIMIT count

在执行程序上，首先从表中 select 出须要的列；而后执行 WHERE 语句；过滤完后，执行 GROUP BY 聚合计算；聚合后的后果执行 HAVING 执行二次过滤；而后执行 ORDER BY 排序后果；最初依据 LIMIT 限定输入的行数。

图 2 -2 SQL 执行程序

通过以上步骤，实现对一个表的操作，并且输入一个新的表。当须要嵌套查问时，把外部的后果表用括号蕴含起来，即可视同外部查问为一个一般表，而后执行上述雷同的操作。因此，SQL 的语法能够有限的嵌套。对于嵌套查问，除了用括号把子查问蕴含起来作为子表，另一种做法是用 with 语句定义子查问。下文予以具体介绍。

SELECT 子句

最简略的 SELECT 操作是 SELECT select_expr from TABLE。示意从表中获取数据，也容许在数据之上减少一些列的计算。在 select 可跟的表达式有：

• SELECT 列名. 示意从表中读取列的原始数据。
• SELECT scalar_function(列名)，示意读取列的原始数据，并且通过 scalar_function 逐行转换每一行原始数据，输入转换后后果。Scalar Function 是转换函数，示意 1 行到 1 行的转换。通过转换后的数据行数不会产生扭转。一个典型的转换函数是 round 函数，示意把原始数据截断后保留几个小数位。
• SELECT aggregate_function(列名)，示意读取原始数据，并且对所有的原始数据做聚合计算，输入聚合后的后果，后果只蕴含一行一列数据。

SELECT 后的表达式有能够有 1 个或者多个，可用逗号来连贯多个表达式，如果是第 1 或第 2 种状况，两种表达式能够混合应用，例如 SELECT column1, scalar_function(column2)，能够并列呈现有限多个列名或者转换函数。对于第 3 种状况，在没有 group by 语句的状况下，聚合函数只能和其余聚合函数混合应用，例如 SELECT aggretate_function1(column1), aggregate_function2(column2)，在同级别不能呈现 1 或者 2 的状况，当然聚合函数内是能够嵌套转换函数的，例如 SELECT aggregate_function(scalar_function(column))。对于有 group by 的状况，group by 的列名以及列名之上的转换函数能够呈现在 select 中。原理也很简略，因为 case 1 和 2 不扭转后果行数，case 3 聚合计算只输入一行后果，所以是不能在同级别混用的。

转换函数（scalar function）

如上文所说，转换函数对每一行输出，通过计算后，输入对应一行的后果，即，转换函数不会扭转输出数据的行数。scalar function 的 scalar 就代表是对原有数据的线性伸缩，不扭转原数据的维度空间。转换函数的输出参数能够是 0 个或者多个；输入只有 1 个，即无论输出多少列参数，输入只有一列。如果心愿输入多列，则须要把输入后果整合到一个简单类型里，例如数组 array 或者字典 map，再通过嵌套查问开展后果。

因为转换函数不扭转后果的行数，因而能够有限嵌套调用转换函数，例如 fun1(fun2(fun3(fun4(fun5(key)))))，只管大多数状况下有限档次的嵌套并不是必要的，一到两层的嵌套是常见的场景。

转换函数定义好了输入输出模式，函数实现并不属于执行框架的内容，执行框架无需关注函数的外部实现，只须要调用该函数，并且把对应的参数传入函数，而后取得输入后果并传递给后续的算子即可。

基于这套机制，用户能够开发更多自定义的 UDF，并且注册到执行引擎中。开发者在开发 UDF 的过程中，只须要关怀 UDF 的格局定义，而无需关注执行引擎外部简单的实现逻辑。

转换函数的一个样例，key 列取一位小数输入：

SELECT round(key,1) FROM table

图 2 -3

聚合函数

聚合函数和转换函数的不同点在于：聚合函数无论承受多少行输出数据，输入数据都只有一个值，即一行一列；如果是依照窗口聚合（group by 某些列），那么每个窗口内的输出数据只会产生一个输入数据。例如求均值的函数 avg，无论输出的数据有多少行，最终都只输入一个均值。另一个不同点在于，转换函数没有外部状态，输出数据后能够立马失去输入后果；而聚合函数，要在内存中保留一个状态，直到全副数据都输出完结后，能力拿到最终的后果。例如 avg 函数，在内存中保留一个 sum 和一个 count 这两个值作为状态，别离示意输出数据的求和值以及输出行数，每输出一个新的数据，都更新状态，最终输入时才把两者相除，取得均值。

聚合函数也是一种 UDAF(用户自定义聚合函数)。用户能够开发本人的 UDAF，并且注册到执行引擎中供调用。

聚合函数的一个样例，求拜访日志的均匀延时：

SELECT status，avg(dValue) FROM accesslog group by status

依照 status 划分窗口，别离有 200 和 500 两个窗口，每个窗口内的数据别离计算 avg 这个汇合函数，产生一个聚合后果。

图 2 - 4 聚合函数

选择性聚合

如果在 SQL 里边只有一个聚合函数，咱们只冀望对局部数据做聚合计算，那么只须要把过滤条件放在 where 中，先过滤出本人想要的数据即可。然而，如果有多个聚合函数呢，每个聚合函数须要的过滤条件不一样呢？对于 count 算子，有对应的 count_if 函数能够附加过滤条件。对于其余的聚合函数，也能够应用 case when 先过滤出来须要的数据，而后再执行聚合计算，例如 avg(case when status=200 then latency end)。不过 case when 并不是专门用来做过滤的，语法应用起来也不叫简单，也不是所有聚合函数都满足这种过滤的语意。除了 case when，还有一种专门的选择性聚合算子，能够对每个聚合函数附加一个过滤条件。具体语法如：

SELECT
  key,
  AGG1(x) FILTER (WHERE condition1),
  AGG2(y) FILTER (WHERE condition2),
  AGG3(z) FILTER (WHERE condition3),
  ...
FROM

每个聚合算子后都跟着一个 filter(where bool 表达式)，满足 bool 表达式的内容才会参加对应的聚合。在同一层的的各个聚合函数，能够指定各自的过滤条件，也能够不指定过滤条件，每个聚合函数对应的过滤条件之间没有任何关系，能够雷同，也能够不同。这就是选择性聚合，在语法层面给多样化的聚合提供了不便。

Distinct 聚合

在聚合函数中，所有的输出都会参加到聚合中。然而有一种场景，先把数据做去重，再做聚合。最常见的应用场景是 count(distinct key)，先对 key 做去重，在计算 count 值。除了 count，其余的聚合函数也能够应用该语法，例如 avg(distinct key)，先去重再聚合。

聚合函数内的残缺语法是:

aggregate_function(all key)
aggregate_function(distinct key)

第一种语法不做去重，全副数据参加计算。第二种语法先做去重，再做聚合计算。默认是第一种语法，因而 all 关键字不是必须的。

聚合中的 Null 值

在聚合函数的输出参数中，如果参数值是 null，那么不参加计算。例如 sum(key)，只统计非 null 值的和。count(key)只统计非 null 的个数。此处有个例外，就是 count()，因为 并不是具体的列，不存在 null 或非 null 的差异，因而所有的行都会统计在内。

如果聚合函数的所有输出，排除掉 null 值后，只有 0 行无效数据，那么聚合函数的返回后果是 null，因为没有任何无效数据参加计算。以 sum 为例，如果全都是 null，或者只有 0 行输出，返回 0 或者其余非凡值是不适合的，因为没有非凡值能够惟一代表这种场景，只有返回 null 才适合。在所有的聚合函数中，除了 count 之外，都合乎这一定义，count 0 行输出的后果是 0。

GROUP BY 分组聚合

只有聚合函数的场景，所有的输出都聚合成一个后果。如果要把输出分到多个分组中，每个分组别离生成聚合后果，则须要用 group by 指定分组。Group by 后跟一列或者多列、或者有某些列通过转换函数计算后的后果。Group by 子句是配合聚合算子应用的。没有 group by 的状况下，聚合算子承受所有的输出数据，产生一个计算结果；有 group by 的状况，称为分组聚合，各行数据先依照 group by 中指定的列或列的转换后果，计算所属分组，每个分组内无论有多少行数据，都会计算产生一行聚合后果。图 2 - 4 是一个 group by 分组聚合的样例，依照 status 分组，总共有 2 个分组，每个分组产生一行聚合后果，即共两行聚合后果。

Group by 的一个样例，求拜访日志中每个站点的均匀延时：SELECT avg(latency), host from accesslog GROUP BY host 在一个分组内，能够执行多个聚合函数，每个聚合函数产生一列聚合后果。即分组的数量决定后果行数，聚合函数的数量决定后果的列数。

在有 group by 的场景下，select 中指定的表达式，除了聚合函数外，还能够 select 某些列，或者某些列通过转换函数计算后的后果，这些列是有限度条件的，只能是 group by 中呈现的列。如果是非 group by 的列，就会呈现一个难以抉择的问题，因为分组是依照 group by 的列分组的，每个分组只输入一行后果，如果 select 非 group by 的列，那么在分组中，会有多行数据进入同一分组，在输入时到底抉择哪一行作为解决呢？这没有明确的答案。有几种可能性，第一种是随机的抉择一行；第二种是抉择第一行；第三种是抉择最初一行；第四种是全副输入。可能性太多，如果用户不明确的通知 SQL 抉择哪一种选项，就会造成误判，输入后果不肯定满足用户预期。每一种选项都会有对应的聚合函数实现。当然在 mysql 零碎中，是依照第一种选项输入的。

对于须要在分组内产生多行聚合后果的应用场景，能够参考窗口函数。

如果要分组的列是 null 值，则 null 值会作为一个独自的分组。

个别的场景下，一个原始数据只会在一个分组内参加聚合计算，不会同时呈现在多个分组中。但也有一些高级用法就是 grouping set 操作，在下文具体介绍。

Grouping sets 操作

上文介绍的 group by 子句，是比较简单的一种分组聚合操作。全量的数据，会依照分组条件分到不同的组里边，每一行数据，都只会在一个分组中参加聚合。还有一种更加简单的分组聚合操作是 grouping sets 操作。相干关键字是 grouping sets, cube, rollup。该算子能够容许在一次查问中，依照不同的分组条件，屡次分组。每一条数据，都会依照不同的分组条件屡次参加聚合。

例如，如果你心愿依照多个分组聚合(grade, class), (grade),(class), 如果应用 group by，那么要别离执行三次 group by 操作。应用 grouping sets 则能够在一次查问中实现，语法是 select grade,class,count(1) from log group by grouping sets((grade, class), (grade),(class))。在输入的后果中，grade class 两列都会输入，然而在后两个汇合中，只 group by 了一列，另一列以 null 呈现在后果中。

Rollup 语法是一种非凡的 grouping sets 语法，roll up 后跟的汇合，会依照层级聚合的形式，枚举出所有的前缀汇合。例如 group by rollup(grade, class), 相当于 group by grouping sets ((grade, class),(grade),())。最初一个分组条件是空分组，也就是不分组，相当于没有 group by 的场景。

Cube 语法也是一种非凡的 grouping sets 语法，cube 和 roll up 不同之处在于，cube 会枚举所有可能的汇合。例如 group by cube(grade,class)，相当于 group by grouping sets((grade,class),(grade),(class),())。

窗口函数

转换函数输出一行数据，输入一行数据。聚合函数把多行数据聚合成一行。有没有一种聚合函数，实现聚合，然而不扭转输出行数呢？答案是窗口函数。

窗口函数在表白后果上相似于转换函数，针对每一行输出，都会产生一行输入，不会扭转后果的行数。但在应用上，在窗口函数外部，能够应用聚合计算函数。窗口函数依据某些列分成多个桶，把每一行数据散发到对应的桶中去，而后在每一个桶上执行聚合函数，并且把后果写回到每一行。因而，相当于窗口函数把聚合函数当成了转换函数来应用。转换函数是把一行输出转换成一行输入；窗口函数是把窗口内的若干行聚合后生成一个后果，然而每一行都会有一个后果。

窗口函数的逻辑如图 2 - 4 所示，有窗口列，排序列，参加聚合的列。在每个窗口内对指定的若干行进行聚合计算，并且写入到以后行的后果中。输入的后果行数和输出的行数雷同。

图 2 -5 窗口函数示意图

窗口函数最简略的场景，例如：avg(key2) over()，示意把所有数据当成一个分组做 avg 聚合，并且写回每条数据中，尽管后果中的每行数字都雷同，然而没有扭转后果行数。如下图中的 out3 的后果所示，所有行的均值为 3，3 就是每一行对应的后果。

再简单一点的窗口函数场景，例如：avg(key2) over(partition by key1)，示意依照 key1 作为分组，每个分组内别离执行 avg 聚合计算，并且更新到每个分组的每条数据中。如下图的 out1 所示，a 这个窗口的均值是 1.5，窗口内所有的后果都填充为 1.5。b 这个窗口内均值是 4，窗口内所有的后果都填充成 4。

更加简单一点的窗口函数样例如：avg(key2) over(partition by key1 order by key2)，示意依照 key1 作为分组，在每个分组内再依照 key2 排序，计算窗口内从第一行到以后行为止的数据的 avg 聚合后果，也就是分组内每一行的后果可能是不一样的。参考下图中的 out2 这个后果，a 这个窗口，第一行数据是 1，均值就是 1；第二行数据是 2，第二行对应的窗口均值就是第一行和第二行的均值，也就是 1.5。因而后果中，第一行的后果是 1，第二行的后果是 1.5。这个和 out1 的比照比拟显著，out1 的后果中，每个窗口内的后果都是一样的。

上边的样例还不是最简单的，前 2 个样例，都是在分组内的所有数据上执行聚合；加上 order by 之后，是聚合从第一行到以后行的数据。那有没有一种办法，只聚合以后行左近的几行呢？是否更加灵便的指定窗口内哪些行参加聚合计算呢？答案是能够的。窗口函数能够指定以后行的前后若干行参加聚合计算，例如 avg(key2) over(partition by key1 order by key2 range between unbounded preceding and current row)，示意从第一行到以后行。range between 1 precedingand 2 following，示意蕴含前一行、以后行、后两行总共 4 行组成的数据进行聚合，更新到以后行的后果。参加聚合的行称为一个 frame，一个 frame 内的数据聚合生成一个后果。

图 2 - 6 窗口函数的输入

在窗口函数中，除了一般的聚合函数，还有一些非凡的、专门用于窗口运算的聚合函数。例如：rank()用于窗口内的排序，输入排序后的序号，雷同的值序号雷同，然而雷同的值会占用计数值，例如 100、102、102、103，输入序号是 1、2、2、4，留神最初一个序号是 4。如果冀望输入的须要是去重排序后的序号，则应该用 dense_rank 函数，针对上述例子，输入序号为 1、2、2、3。此外还有 row_number 输入行号。cume_dist 排序后从窗口第一行开始的累积百分比，和 rank 相似，雷同的值输入雷同的后果，输入后果为 rank()/total。percent_rank 输入(rank()-1)/total-1)。cume_dist 和 percent_rank 的差异在于，后者从 0 开始累积。

运算符和函数

在外部实现和表白成果上中，运算符和函数是雷同的。两者的区别在于语法模式不同，函数有明确的函数名，蕴含 0 个或者多个参数的参数列表；运算符则是通过常见的符号来表白意义，例如 +-*/ 等符号。运算符蕴含 1 个或者 2 个参数。双目运算符蕴含两个参数，例如 + 运算符，须要左右参数。单目运算符蕴含一个参数，例如 - 运算符，代表符号的取反操作。运算符须要在语法文件中显式定义语法模式。而函数名是不须要定义在语法文件中的，在语法文件中只是一个一般的字符串而已，直到语意查看阶段才须要查看函数是否存在。

表达式

表达式是一种有一个或多个函数、运算符、连接符组成的一个残缺表达式（Expression）。表达式的作用等同于转换函数，输出 0 个或多个字段，输入一行一列后果。常见的表达式有 bool 表达式，逻辑表达式，比拟表达式，函数调用，lambda 表达式等。

比拟表达式

比拟表达式通过比拟运算符 >,>=,<,<=,=,<> 等连贯两个表达式，用于断定两个表达式的大小关系。左右的表达式不肯定是根底类型，也可能是简单的表达式，例如函数调用表达式。根底类型的数据包含 integer、bigint 等数值类型，也可能是 varchar，char 等字符串类型。除了上述比拟算法，还有 between 关键字，key between x to y，等价于 key >=x and key <=y，是一个闭区间。

Bool 表达式

bool 表达式指的是返回后果为 bool 类型的一类表达式。Bool 表达式宽泛的利用于各种过滤条件中，例如 WHERE，HAVING，ON 等。一些转换函数能够返回 bool 类型后果，还有一些比拟运算符能够返回 bool 后果。例如 >,< 等比拟运算符。

逻辑表达式

函数能够代表一个简略的表达式，如果要表白简单的逻辑，除了函数嵌套函数，还能够用逻辑链接符号组合多个表达式，造成一个简单的 bool 表达式。逻辑表达式由逻辑运算符 AND、OR、NOT 连贯 1 个或者 2 个 bool 表达式，并且返回 bool 后果。其中 AND 和 OR 是双目运算符，NOT 是单目运算符。

Lambda 表达式

Lambda 表达式又称为是匿名函数，没有函数名称，只有参数列表和计算表达式。Lambda 表达式能够用于让用户自定义解决逻辑，相当于一种 UDF。通常在应用中，lambda 表白也能够作为函数的参数传入函数，而后在函数内调用该 lambda 表达式迭代解决数据。

一个简略的 lambda 表达式如：x -> x + 1，示意承受一个参数 x，返回 x +1。

WHERE 子句

Where 子句后跟一个 bool 表达式，示意从表中读取数据后，会对每一行数据评估该 bool 表达式的后果。如果表达式评估后果为 true，则该行数据就会传递后给后续的算子做进一步计算；如果评估后果为 false 或者地位状态，则抛弃改行数据，不再参加后续计算。

Bool 表达式能够是一个简略的表达式，例如 a =1；也能够是嵌套多层转换函数组成的 bool 表达式，例如 a%10=1；或者由逻辑运算符连接起来的逻辑表达式，例如 a AND b。Bool 表达式中的函数都是转换函数，不能是聚合函数。

Where 子句的操作产生在聚合计算之前。Where 子句十分重要，能够帮忙缩小读取和计算的数据量，经常用于减速计算。在优化器中，有一些规定帮忙把过滤条件尽可能的下推到叶子结点。filter 下推是一种十分罕用且无效的减速伎俩。

Where 子句的一个样例，获取学生中所有的男生信息：SELECT * FROM student where gender =‘male’

HAVING 子句

Having 子句经常追随 group by 子句呈现。Having 子句相似于 where，是一个 bool 表达式。但 Having 利用于 group by 聚合计算之后，每个分组的计算结果会用来持续评估 Having 表达式的后果，只有满足 having 子句为 true 的分组，能力输入到后续的算子。

Having 和 where 的区别在于：1，where 在 group by 之前实现，having 在 group by 之后执行；2，where 利用于每条原始数据上，having 利用于 group by 分组聚合后果上。

实践上而言，即使没有 group by 计算，只有一个全局聚合操作，可能应用 having，然而全局聚合的后果只有一样，那么这个时候 having 的作用就是判断这一行后果是否满足条件。例如 select avg(latency) as avg_latency from log having avg_latency > 100

即使没有 group by 没有任何聚合函数，select 中只有原始列或者转换函数的后果时，也能够用 having，但这时候 having 就没有意义了，因为 having 中的条件是能够合并到 where 中的。例如 select from log where latency > 10000000 having status>200，齐全能够写成 select from log where latency > 10000000 and status>200。

总而言之，having 子句个别和 group by 语句联结应用，用于过滤分组聚合后的后果，筛选出分组聚合后果满足特定条件的某些分组。

Having 子句的一个样例，求拜访日志中均匀延时大于 10 秒的站点及其延时：SELECT avg(latency), host from accesslog GROUP BY host HAVING avg(latency) > 10

having 子句的执行产生在 group by 之后，order by 之前。程序参考图 2 -2。

Order By 子句

Order by 子句蕴含一个或多个表达式，用于排序输入的后果。在 order by 中能够指定多个表达式，每个表达式指定排序形式，能够升序，也能够降序，默认是升序排列。排序时多个表达式从左到右顺次评估。当左侧表达式评估进去的多个行后果一样时，会评估右侧表达式的值用于排序。例如 order by key1 asc, key2 desc 示意依照 key1 升序排列，当 key1 雷同时，依照 key2 降序排列。

Order by 子句的一个样例，学生依照分数排序：Select * from student order by score asc

Limit 子句

Limit 子句用于限度返回后果的行数。当之前的算子输入行数超出了 limit 指定的行数时，会抛弃超出的局部。因为 Limit 算子能够缩小传递给上游的数据量。因此在优化中十分有用。例如 order by 和 limit 算子合并，在排序阶段就大大减少用于排序的数据量；把 limit 算子尽可能向叶子结点的方向下推。通常而言，limit 算子会和 order by 联结应用。如果独自应用 limit 算子，输入后果不保障程序，也就是说每次执行会取得不同的后果。和 order by 联结应用时，能力保障每次查问后果的一致性。

一个查问样例：SELECT * FROM student limit 100，示意获取 100 个学生信息。

通常而言，limit 限定最多的返回行数。在 MySQL 中，还能够通过 limit offset,line 这个翻页语法，从第 offset 行开始，读取 line 行后果。而对于 OLAP 引擎而言，反对翻页并不事实，因为每次提交翻页申请，都是要计算一遍 SQL，取得后果后再翻页返回，因此代价十分大。除非 OLAP 引擎把计算结果缓存在内存中，期待下次翻页获取。MySQL 之所以可能反对翻页，一方面是因为 MySQL 的查问个别是事务性查问，另一方面数据量比拟小，翻页的代价不大。

多个表间操作

在一层 SQL 查问中，数据源能够是一个表，也能够是多个表。对多个表进行操作并产出一个新的表。表之前的操作包含连贯操作（join），汇合操作（set）。

Join

Join 能够把多个表（左右）连接成一个表，依据肯定的条件让多个表对应的行输入到同一行，左表的一行数据和右表的一行数据连接成一行，左表和右表的列都会呈现在后果中。Join 的操作类型包含 Inner Join、Left Join、Right Join、Full Join、Cross Join。各种 Join 的策略参考下图所示，Inner Join 输入左右两表的交加，即满足连贯条件的行，输入后果中，左表和右表的列都不为 null。Left Join 不论左表是否满足条件都输入，而右表只输入满足条件的行，其余行以 null 输入。Right Join 和 Left Join 相同。Full Join 同时输入左右表，对于满足条件的行，输入对应的左右表连贯后的后果，对于不满足的行，输入左表 (右表) 的行，同时右表 (左表) 以 null 输入，相当于汇合了 Left Join 和 Right Join 的个性。Cross Join 没有链接条件，输入两个表的笛卡尔积。

Join 操作是 SQL 所有算子中，计算复杂度最高的算子之一。针对 Join 的优化是 SQL 中一个十分重要的课题，Join 的执行形式、执行程序，左右表的大小等因素都会影响 Join 的性能。在后续章节中，会介绍基于规定的优化器和基于代价的优化器来优化 Join 算子。

图 2 -7 不同的 Join 类型

Set

Set 操作是一种汇合操作，汇合的元素是行，用于把多个表前后拼接成一个表。拼接后不扭转列的个数，原表中的一行，原样输入到后果中，参加 set 操作的左右表的列个数和类型必须保持一致。set 操作和 join 操作的差异在于，join 是左右表列与列依照连贯条件拼接成一行，set 操作是行与行拼接成更多行，不扭转原始一行的内容。Set 操作包含 Union、Intersect、Except。别离代表并集、交加、差集。汇合的实践根底是汇合代数，默认场景下，汇合是不蕴含反复元素的。

汇合运算符后能够增加 distinct 或者 all 关键字，别离示意后果去重和不去重。默认是去重的后果。例如 table1 union table2，输入两个表去重后的后果。

嵌套查问

在一个最简略的查问中，from 语句指定了要从什么表中读取数据。在 from 中，最简略的状况是指定一个表，从这一个表中读取数据进去；略微简单的状况是 from 多张表的 join 后果；再简单一点，from 的起源，基本不是一张表，而是另一个查问的输入后果。咱们都晓得，一个 SQL 查问的后果也能成为一个新的表，这个新的表能够作为另一个查问的输出。这就是关系模型的优良之处，任何关系通过计算后，造成第二个关系，再通过第二次计算，则造成了第三个关系。实践上，表活着关系能够通过有数轮计算，组成一个单向流动的链表。这就是嵌套查问。嵌套查问的后果，能够像一张一般的表一样，参加上游的计算、join、union 等。

在 SQL 中，写嵌套查问有两种模式，第一种，最直观的就是 from 后写一个子查问，并且把子查问用 () 蕴含起来，造成一个残缺的整体，例如:select abc from ( select abc from table）

()外部的即为一个残缺的子查问。

第二种是 with 语法：

with temp_table1 as (select abc from table),
     temp_table2 as (select abc from temp_table1)
select * from temp_table2

通过 with 语法，能够定义多个视图，视图用括号左右蕴含起来。多个长期表之间，用逗号分隔。with 语句的最初不须要加逗号，间接跟 select 语句。

with 语句比拟简洁，能够做到每一行只定义一个视图，视图的定义和应用能够离开到不同的中央，在应用时只须要援用视图的表名即可。定义一次视图甚至能够屡次援用。而嵌套式查问，长期表的定义和应用放在一起，每应用一次就须要定义一次。外层的查问语句外部是一个子查问，from 关键字在整个 SQL 语句的两头地位，导致外层查问的前半部分在子查问前边，后半局部在子查问后边，同一层查问语意被一个简单的字查问分隔开，不利于对 SQL 语意了解。因而在应用前套查问时，举荐应用 with 语法。

with 查问中定义一个视图，在后续能够援用屡次该视图。视图并不是物化视图，如果援用屡次视图，会开展执行屡次。

子查问表达式

子查问除了作为一种关系在 from 语句中被援用。还有一种用处是作为表达式被援用，例如 where 语句中的援用子查问后果作为一个汇合，判断某个值和这个汇合的关系。子查问表达式和嵌套查问的区别在于，子查问表达式在 plan 中表演一个表达式，而嵌套查问表演一个视图。在子查问中，能够援用外层查问的属性，而外层查问中，不能引用子查问的属性。

除了 from 后，嵌套子查问能够呈现在 SQL 的简直每个地位。

1. 呈现在 select 输入后果中，select (select 1) as one from student。
2. 呈现在 where 中，select name from student where id in (select id from applied)。

对于判断外层查问属性和内层子查问后果之间关系的断定形式，有几种形式：

1. ALL 示意外层表达式要满足子查问的所有后果。
2. ANY 示意外层表达式须要满足子查问的至多一个后果。
3. IN 等同于 ANY。
4. EXISTS 示意至多有一行后果返回。

依照输入后果，子查问包含三种类型：

1. 标量子查问（scalar subquery）：只返回一行一列后果。
2. 多行输入子查问：输入多行一列，或多行多列。
3. exists 子查问：输入后果是 bool 类型。

按是否援用外层查问的属性，分为：

1. 关联子查问：子查问中援用到了外层查问的属性。
2. 无关联子查问：子查问没有援用外层查问的属性。

标量子查问表达式

标量子查问的后果只有一行一列一个值。针对这个个性，能够有很多优化伎俩。在后续的优化器章节会给出介绍。实践上来说，对于外层查问的每一行数据，都须要去执行一次子查问表达式。然而这里还有些不同点，对于相干子查问和不相干子查问的解决是不同的。对于不相干子查问，子查问没有援用内部的任何列，因而对于内部的每一行数据，子查问的执行后果都是雷同的，因而执行一次即可。这种场景下，子查问只会执行一次。

标量子查问能够用于 case 表达式、select 子句、where 子句、order by 子句、函数的参数等。因为标量子查问只返回一行一列，因而能够当成单个值应用。

scalar 子查问在被应用之处，只能要求呈现一个后果，但并未在语法上束缚子查问返回一个后果。用户能够写一个聚合子查问只返回一个后果，或者用 limit 1 限定返回一个后果；也能够写一个可能返回多行数据的 SQL，只有在执行时，如果理论返回多行后果则会报错。

例如 select count(1) from log where latency >= (select avg(latency) from log)，子查问中时聚合函数，肯定会返回一行后果，因此能够失常执行。但退出用户写这样一个子查问 select count(1) from log where latency >= (select (latency) from log)，则存在三种可能，返回 0 行后果，返回 1 行后果，返回大于 1 行后果。如果返回 0 行后果，则以 null 作为子查问的输入，如果返回大于 1 行后果，则运行报错。因为标量子查问的外层须要一行一列输出。或者说，标量子查问之所以称为是标量子查问，是因为外层查问要求子查问输入一行一列，而不是子查问自身通过语法或者理论运行只能失去一行一列后果。

除了 where 中，还能够在 select 中，例如 select , (select max(latency) from log )from log，在每一行都输入最大的 latency 值。如果写成 select , (select latency from log)from log 则会报错。

也能够作为函数参数:select *, abs((select max(latency) from log) )from log。基本上，在须要单个值的中央就能够应用标量子查问。

子查问用于判断汇合从属关系

in 和 not in 用于断定外层查问的属性是否属于内层子查问后果的汇合内。例如：select * from course where student_id in (select student_id from student where apply_year=’2018′)

in 和 not in 除了用于子查问，还能够指定一个 list 常量，例如：select * from course where student_id in（1，2，3）

Exists 子查问用于断定是否是空集合

Exists 子查问查看子查问是否有输入后果，如果有至多一行后果，则断定为 true，否则断定为 false。通常 Exists 子查问被用于关联子查问，也就是说针对外层查问的每一行数据，断定 Exists 子查问的后果。如果是非关联子查问，则对于外层查问的每一行数据，Exists 的后果都是一行的后果，这样做没有意义。

例如，SELECT name FROM websites WHERE EXISTS (select count from access_log WHERE websites.id = access_log.site_id and count > 100) 示意输入拜访日志中 count > 100 的网站的名字。

not exists 是相同的语意，示意子查问的后果为空集合。

Exists 查问也能够用 in 语法来表白，in 语法示意判断某一列的每一行是否在子查问的输入后果中。例如上述的逻辑，能够用 in 语法来表白：SELECT name FROM websites WHERE id in (SELECT site_id from access_log where count > 100)。显然，在 in 查问中，子查问是不相干查问，因而，子查问只须要执行一次即可，因此查问效率较高。

子查问用于比较级和数值大小关系

外层查问能够和子查问的后果进行比照，比照的运算符包含 <,>, <=, >=, =, <>。子查问的后果能够蕴含修饰符 SOME，ANY，ALL。外层表的每一行会一一和子查问后果的每一行进行比照，返回 true 或者 false。如果是 SOME 或者 ANY 修饰符，那么只须要至多 1 行比照为 true 即可。如果是 ALL 修饰符，那么就须要所有的行比照后果都为 true 才行。=ANY 的语义和 IN 雷同。<>ALL 的意义和 NOT IN 雷同。

一个样例：SELECT Num FROM Test2 WHERE Num > ANY (SELECT Num FROM Test1)示意 Test2 这张表中的 Num，如果在 Test1 表中存在比之小的值，则该行数据满足条件，会输入到上游算子中。

量化子查问会在优化器章节进行深刻的介绍其优化办法。

子查问用于断定汇合是否蕴含反复值

和 exists 相似，还有一个 unique 关键字，用于判断子查问的所有行是否蕴含反复值，如果蕴含反复值，那么返回 false；如果不蕴含反复值，则返回 true。

例如：select * from log where unique (select projectName from log)

子查问的理论运行形式

一般来说，上述几种子查问，如果是非关联子查问，每一行断定后果都一样，意义不是很大。所以，通常上边的这些子查问都会是关联子查问，这样才会每一行后果不一样。而关联子查问个别会在 plan 优化阶段，转化为 join 计算。

子查问是一种语法示意模式，在物化 plan 中，是没有子查问这种执行形式的，个别须要须要转化为等价的关系代数表达形式。

除了惯例的几种 join（left，right，full，cross），还有两种非凡的 join 模式，semijon 和 antijoin。semijoin 用于 in 或 exists 查问，示意只有满足条件，就输入左表的数据，每行数据只输入一次。antijoin 用于 not in 或 not exists，示意只有不满足条件，就输入左表的数据，每行数据只输入一次。尽管 semejoin 和 antijoin 有等价的示意模式，然而这两种特化的表达形式能够取得更好的执行性能。

Null 解决

对于惯例的数据处理是很简略的，然而往往有一些非法的 case 须要解决。null 就是一个典型的场景。一个非法值，或者不晓得具体值的值就用 null 示意。

在聚合函数中，输出 null 值的解决在上文曾经形容过了。在这个章节中，次要思考转换函数输出 null 的状况。

对于一个转换函数或者转换表达式，如果返回值是非 boolean 的状况，例如代数运算，如果输出是 null，那么输入也是 null。

如果转换函数或者转换表达式返回值是 boolean 的状况，例如一个比拟表达式，失常状况输入只有 true、false 两种场景，如果输出的一个参数是 null，无奈明确断定是 true 还是 false 的状况下，则须要第三种状态，即 unkonwn 状态用于判断。为什么简略粗犷的输入 null 呢？这是因为，unknown 代表的信息量要大于 null。在后续的计算中，即使存在 unkonwn 状态，也能过推断出后果。

针对 and、or、not 逻辑表达式，当呈现 unkonwn 时，甚至能够借助短路求值的思维，取得最终后果，无需关怀 unknown 到底是 true 还是 false。

• AND：如果是 true and unknown，后果取决于 unkonwn，那么后果就是 unkonwn；如果是 false and unkonwn，无论 unkonwn 是 true 还是 false，后果都是 false。
• OR：如果是 true or unkonwn，无论 unknown 是 true 还是 false，后果都是 true；如果是 false or unknown，后果取决于 unknown，后果仍为 unknown。
• NOT：not unknown，后果仍是 unknown。

Is null 语法和 is not null 语法：is null 能够判断一个表达式是否是 null，is not null 正好相同。同时在 SQL 规范中，还有 is unknown 语法和 is not unknown 语法，不过这两个对于 unknown 的语法并不是所有的 SQL 引擎都反对。

在用于分组操作时，例如 group by，distinct，union 等，如果指定的列中蕴含 null，那么所有对应 null 的行都会作为一个分组。这一点和计算表达式中的体现是不同的，例如判断 null=null，那么输入将是 unknown，而不是 true。

Unnest 语法

在 SQL 中，生成新的数据依赖于表达式或者函数，在上文中提到，函数分成两种类型，别离是标量转换函数，另一种是聚合计算函数。标量计算函数把一行输出数据转换成一行一列输入后果；聚合计算函数把多行输出数据转换成一行一列输入后果。如果咱们要输入一列转换成多列，那么能够通过多个表达式实现。如果咱们须要把一行转化成多行，该怎么解决呢？在这种需要场景下，就用到了 Unnest 语法。

Unnest 语法能够把一行数据转换成多行数据。例如输出数据是一个数组类型，那么能够把数组中的每一个元素作为一行后果输入。语法如：

SELECT element FROM (VALUES ( ARRAY[1,2,3]) ) as t(element)。输入后果为 3 行，别离是 1、2、3.。

其余 SQL 语法

除了 SELECT 语法，还有其余的语法例如 INSERT/CREATE 等 DDL 语句。

小结

本文介绍了 SQL 和查问相干的一些外围语法规定，有助于读者理解 SQL 可能实现哪些方面的计算。在后续的章节中，咱们持续理解 SQL 语言如何转化成形象语法树的。

原文链接

本文为阿里云原创内容，未经容许不得转载。