SQL能完成哪方面的计算？一文详解关系代数和SQL语法

1. 语言简单，门槛低
2. 语意明确，无歧义
3. 资料丰富，方便学习
4. 生态丰富，工具多

5. 方便扩展，可编排复杂的逻辑

[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 ，是一个闭区间。
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能够完成哪些方面的计算。


            

            

            

                

                    0
                    0
                    投票数
                
文章评分
                

            
                                                        
本文转载自马云雷 阿里开发者，原文链接：https://mp.weixin.qq.com/s/D8Rv-E_gSYFhnscVMK1WGg。