关于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的简直每个地位。

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

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

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

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

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

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

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

标量子查问表达式

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

标量子查问能够用于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语言如何转化成形象语法树的。

原文链接

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