SQL 操作:WITH 表达式及其应用
本文分享自华为云社区《GaussDB(DWS) SQL进阶之SQL操作之WITH表达式》,作者: 两杯咖啡 。
SQL 标准 1999 中,在传统 SQL 语法的基础上增加了 with 表达式的使用,使得 SQL 语句的编程可以更加灵活和具备可扩展性。本文将围绕 with,以及更高阶的 with recursive 表达式介绍其语法特征和具体使用规范,以及在 GaussDB(DWS)中如何进行 with 表达式的调优。同时,对 Oracle 的 connect by 语法进行探讨,研究其使用 with recursive 进行迁移改写的方法。
一. WITH 表达式及其应用
WITH 表达式用于定义查询中公用语句块,每个语句块称为 CTE,即 common table expr,可以理解为一个带名称的子查询,之后该查询可以以其名称在查询中被多次引用,类似于高级编程语言中的函数。TPC-DS benchmark 测试集中有很多包含 WITH 表达式的 SQL 语句,99 个查询中有 24 个相关语句。对于查询复杂的 AP 场景,WITH 表达式的应用场景非常广泛,很多客户现场都在使用 WITH 表达式,尤其对于多年维护的应用程序,使用 WITH 表达式是进行 SQL 编写演进的一个优秀实践。
以 TPC-DS Q1 为例:
该查询中定义了一个名称为 customer_total_return 的 CTE,该 CTE 查询 2000 年退货的相关信息。在主查询中该 CTE 被调用了两遍,如果不使用 CTE,则 customer_total_return 定义的 SQL 需要在该查询中写两遍,使得查询更长更难以维护。
WITH 表达式的语法如下:
其中,with_query 的语法为:
关键要点如下:
每个 CTE 的 AS 语句指定的 SQL 语句,必须是可以返回查询结果的语句,可以是普通的 SELECT 语句,也可以是 INSERT、UPDATE、DELETE、VALUES 等其它语句,需要通过 RETURNING 子句返回元组。例如:
单个 WITH 表达式表示一个 SQL 语句块中的 CTE 定义,可以同时定义多个 CTE,每个 CTE 可以指定列名,也可以默认使用查询输出列的别名。例如:
该语句中定义了两个 CTE,s1 和 s2,其中 s1 指定了列名为 a, b,s2 未指定列名,则列名为输出列名 x, y。
每个 CTE 可以在主查询中引用 0 次、1 次或多次。
同一个语句块中不能出现同名的 CTE,即不支持高级语言的重载。但不同语句块中可以出现同名的 CTE。此时,语句中引用的 CTE 则是距离引用位置最近的语句块中的 CTE。
除非使用 WITH RECURSIVE,否则 CTE 不允许自引用,即 CTE 的定义中引用当前 CTE。
由于 SQL 语句中可能包含多个 SQL 语句块,每个语句块都可以包含一个 WITH 表达式,每个 WITH 表达式中的 CTE 可以在当前语句块、当前语句块的后续 CTE 中,以及子层语句块中引用,但不能在父层语句块中引用。由于每个 CTE 的定义也是个语句块,因此也支持在该语句块中定义 WITH 表达式。例如:
注:
<1> 该语句中定义了两个同名 CTE-tmp,一个定义在最外层主语句中,另一个定义在内层子查询中。
<2> 语句中一共引用了三次 tmp,其中第三次和第四次的引用都是引用子查询中的 tmp,而子查询 tmp 中使用的 tmp(第二次的引用)则引用最外层的 tmp。(想想看,为什么?)
特殊地,如果 CTE 出现在相关子查询中,也可以使用父层的列或表达式,此时引用 CTE 的地方都视为使用父层的列或表达式。例如:
该语句中,CTE tmp1 中使用了外层 relate_table_010 的列 c_birth_day。
二. With recursive
WITH 表达式极大的方便了语句内相同 SQL 实现的复用,向高级编程语言迈进了一步,但相比高级编程语言而言,仍然缺少一个重要的语法支持,即循环。SQL 仍然无法像高级编程语言使用 for, while 一样,支持不确定循环次数的执行。为此,SQL 支持了 with recursive 语法,来解决这一问题,可以用在树和图的拓扑搜索上。以下图的树为例:
在 GaussDB(DWS)中,可以使用表 tree 来存储所有节点及父子信息,表定义语句如下:
表中数据如下:
通过以下 WITH RECURSIVE 语句,我们可以返回从顶层 1 号节点开始,整个树的节点,以及层次信息:
上述查询中,我们可以看出,一个典型的 WITH RECURSIVE 表达式包含至少一个递归查询的 CTE,该 CTE 中的定义为一个 UNION ALL 集合操作,第一个分支为递归起始查询,第二个分支为递归关联查询,需要自引用第一部分进行不断递归关联。该语句执行时,递归起始查询执行一次,关联查询执行若干次并将结果叠加到起始查询结果集中,直到某一些关联查询结果为空,则返回。
上述查询的执行结果如下:
起始查询结果包含 level=1 的结果集,关联查询执行了五次,前四次分别输出 level=2,3,4,5 的结果集,在第五次执行时,由于没有 parentid 和输出结果集 id 相等的记录,也就是再没有多余的孩子节点,因此查询结束。
从 WITH RECURSIVE 的执行过程来看,是典型的层次遍历(广度优先)的执行方式,因此 WITH RECURSIVE 也可以称为层次查询。除了典型的树、图的拓扑查找应用,WITH RECURSIVE 还可以用于模拟多数的复杂循环操作,只要我们正确定义起始条件、循环条件和终止条件。
例如:下例将整数 1000-1001 转化成二进制串。
执行结果如下:
三. GaussDB(DWS)的实现
在 PG 中,CTE 的扫描使用了专门的执行算子 WorkTableScan,用于将数据集中缓存起来,供其它引用使用,做到了一次扫描,多次使用的效果。对于 GaussDB(DWS),不下推的计划继承了 PG 的计划。TPC-DS Q1 的计划,如下图所示:
第 15 号算子即 CTE Scan,对 CTE customer_total_return 的结果进行缓存,供第 8 号和第 14 号 CTE scan 算子使用。
对于 GaussDB(DWS)分布式系统,数据是分布存储在各个 DN 的,因此这样的做法是不适合的。在 GaussDB(DWS)中,目前将 CTE 的实现 inline 到各个调用的地方进行,保证计划的分布式下推执行。TPC-DS Q1 的计划,如下图所示:
红框中的两个计划即是两个 CTE 的执行部分。
GaussDB(DWS)嵌入的执行方式,对于 CTE 多次执行,根据不同的过滤条件可以生成不同的计划,某些场景是适合的。后续需要结合 PG 的共享执行机制,对过滤条件相同的执行语句块进行一次执行,结果共享的改进,减少数据处理和运算量。
对于 WITH RECURSIVE 表达式,GaussDB(DWS)也支持其分布式执行,计划如下所示:
同时,由于 WITH RECURSIVE 涉及到循环运算,在语句写得不好的时候,可能出现循环次数过多导致数据库执行异常,因此 GaussDB(DWS)引入了参数 max_recursive_times,用于控制 WITH RECURSIVE 的最大循环次数,默认值为 200,超过该次数则报错。
四. Oracle CONNECT BY 的迁移
读到这里,可能细心的读者已经发现了,WITH RECURSIVE 和 Oracle 支持的 CONNECT BY 特性功能很相似,都是用于进行不定次数的循环运算,但语法不同。
Oracle CONNECT BY 功能的基本语法如下:
其中 START WITH 子句用于指定起始条件,即<condition1>,循环关联条件为<condition2>,其中可以使用 PRIOR 关键字来表示来自于上一循环的列。例如上节中所述的树遍历的例子,使用 Oracle 的 Connect By 语法,语句如下:
可以看出,Oracle 的 CONNECT BY 实现了基本的树和图拓扑关系查找的功能,用法较简单,但相较于 WITH RECURSIVE,不如其灵活,对于一些复杂的循环语句,尤其是起始语句和循环关联语句的输出列不相同的场景,无法支持。
但由于 GaussDB(DWS)目前很多客户都是从 Oracle 系统迁移而来,因此面临着将 Oracle 的 CONNECT BY 语法改写为 WITH RECURSIVE 的需求。对于基本语法,我们可以进行如下基本的改写以满足其功能:
其中<condition2>需要对 Oracle 的 PRIOR 表达式进行改写,明确 PRIOR 修饰的列为 table 表的列,非 PRIOR 修饰的列为 tmp_cte 对应的列。
为了更准确地表示遍历的层次关系,Oracle 的 CONNECT BY 功能还支持一些伪列和其它表达式,其基本语义和改写方式如下表所示,请读者下来思考具体的改写方法。
终止循环嵌套选项
【语法】CONNECT BY NO CYCLE <condition>
【语义】通过在循环关联条件前指定 NO CYCLE,在遇到循环嵌套重复行时,主动终止重复行的重复循环。
【示例】SELECT * FROM tree START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = parentid;
【改写方式】GaussDB(DWS)中支持在 WITH RECURSIVE 表达式定义的语句块中使用 UNION,而非 UNION ALL,此时会对输出行去重,自动终止循环,但要求输出行完全来自初始行,不能增加其它表达式,否则一并参与去重。例如:
注:此改写仍与 Oracle 有区别,即 Oracle 可以重复输出重复行一次,而本改写自动跳过;另外本改写不能增加其它伪列及表达式,例如:level 等。
层次排序选项
【语法】ORDER SIBLINGS BY <column>[, …]
【语义】CONNECT BY 默认深度递归遍历并输出,此选项修改排序顺序为层次,<column>。
【示例】SELECT * FROM tree START WITH id = 1 CONNECT BY PRIOR id = parentid ORDER SIBLINGS BY id;
【改写方式】可以在 WITH RECUSIVE 的语句块输出列增加伪列 LEVEL(见下方说明), path_array(),然后按照该两列排序。其中 path_array()的入参为排序列,含义为从根到当前节点的值。
伪列 LEVEL/ CONNECT_BY_ISLEAF/CONNECT_BY_ISCYCLE
【语义】LEVEL 表示当前行的遍历层次/CONNECT_BY_ISLEAF 表示当前行是否为遍历终止节点(叶子节点)/ CONNECT_BY_ISCYCLE 表名当前行是否为循环重复行,与 NO CYCLE 搭配使用才有意义;
【示例】SELECT id, parentid, LEVEL, CONNECT_BY_IS_LEAF, CONNECT_BY_IS_CYCLE FROM tree START WITH id = 1 CONNECT BY NO CYCLE PRIOR id = parentid;
【改写方式】LEVEL 可以通过增加伪列实现,例如上文示例。CONNECT_BY_ISLEAF 则需要与输出结果集的递归 join 列关联,根据关联结果判断。由于不支持 NO CYCLE,CONNECT_BY_ISCYCLE 不支持改写。
操作符 CONNECT_BY_ROOT(column)
【语义】返回遍历开始行对应的 column 值;
【示例】SELECT id, parentid, CONNECT_BY_ROOT(id) FROM tree START WITH id = 1 CONNECT BY PRIOR id = parentid;
【改写方式】可以在 WITH RECUSIVE 的语句块输出列增加标识起始行的列,在嵌套过程中该列值始终继承第一行的值。
函数 SYS_CONNECT_BY_PATH(column, char)
【语义】返回从起始行到当前行嵌套的所有 column 的值,以 char 分隔。
【示例】SELECT id, parentid, SYS_CONNECT_BY_PATH(id, ‘/’) FROM tree START WITH id = 1 CONNECT BY PRIOR id = parentid;
【改写方式】可以在 WITH RECUSIVE 的语句块输出列增加标识起始行到当前行的相应列的字符串,在嵌套过程中通过字符串连接增加当前行的值。
五. 总结
本文中所讲到的 WITH 表达式及 WITH RECURSIVE 表达式的用法,涉及很多 SQL 中复杂的操作,当然掌握其语法也在熟练掌握 SQL 的过程中更进了一步。
版权声明: 本文为 InfoQ 作者【华为云开发者联盟】的原创文章。
原文链接:【http://xie.infoq.cn/article/9a5c3c108577f30342e97cbc9】。文章转载请联系作者。
评论