摘要：本文将围绕WITH，以及更高阶的WITH RECURSIVE表达式介绍其语法特征和具体使用规范，以及在GaussDB(DWS)中如何进行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为例：

with customer_total_return as(select sr_customer_sk as ctr_customer_sk,sr_store_sk as ctr_store_sk,sum(SR_FEE) as ctr_total_returnfrom store_returns,date_dimwhere sr_returned_date_sk = d_date_skand d_year =2000group by sr_customer_sk,sr_store_sk) select c_customer_idfrom customer_total_return ctr1,store,customerwhere ctr1.ctr_total_return > (select avg(ctr_total_return)1.2from customer_total_return ctr2where ctr1.ctr_store_sk = ctr2.ctr_store_sk)and s_store_sk = ctr1.ctr_store_skand s_state = 'TN'and ctr1.ctr_customer_sk = c_customer_skorder by c_customer_idlimit 100;该查询中定义了一个名称为customer_total_return的CTE，该CTE查询2000年退货的相关信息。
在主查询中该CTE被调用了两遍，如果不使用CTE，则customer_total_return定义的SQL需要在该查询中写两遍，使得查询更长更难以维护。
WITH表达式的语法如下：[WITH [RECURSIVE] with_query [,…] ] SELECT …其中，with_query的语法为：with_query_name [ ( column_name [, ...] ) ]AS ( {select | values | insert | update | delete} )关键要点如下：每个CTE的AS语句指定的SQL语句，必须是可以返回查询结果的语句，可以是普通的SELECT语句，也可以是INSERT、UPDATE、DELETE、VALUES等其它语句，需要通过RETURNING子句返回元组。
例如：WITH s AS (INSERT INTO t VALUES(1) RETURNING a) SELECT FROM s;单个WITH表达式表示一个SQL语句块中的CTE定义，可以同时定义多个CTE，每个CTE可以指定列名，也可以默认使用查询输出列的别名。
例如：WITH s1(a, b) AS (SELECT x, y FROM t1), s2 AS (SELECT x, y FROM t2) SELECT FROM s1 JOIN s2 ON s1.a=s2.x;该语句中定义了两个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表达式。
例如：WITH tmp AS (SELECT a FROM t) -- 1st tmpSELECT SUM(a) FROM(WITH tmp AS (SELECT a 2 AS a FROM tmp) -- 2nd tmpSELECT a FROM tmp t1 -- 3rd tmpWHERE EXISTS(SELECT a FROM tmp t2 WHERE t2.a=t1.a)); -- 4th tmp注：<1> 该语句中定义了两个同名CTE-tmp，一个定义在最外层主语句中，另一个定义在内层子查询中。
<2> 语句中一共引用了三次tmp，其中第三次和第四次的引用都是引用子查询中的tmp，而子查询tmp中使用的tmp（第二次的引用）则引用最外层的tmp。
（想想看，为什么？）特殊地，如果CTE出现在相关子查询中，也可以使用父层的列或表达式，此时引用CTE的地方都视为使用父层的列或表达式。
例如：update relate_table_010 set c_birth_month = (with tmp1 as (select s_store_sk, s_company_id, s_market_id from store where s_market_id = c_birth_day) select cc_mkt_id from call_center where cc_mkt_id + 1 in (select web_mkt_id from web_site inner join tmp1 on web_site_sk = s_store_sk where s_market_id = cc_mkt_id)) where c_birth_day = 9;该语句中，CTE tmp1中使用了外层relate_table_010的列c_birth_day。
二. With recursiveWITH表达式极大的方便了语句内相同SQL实现的复用，向高级编程语言迈进了一步，但相比高级编程语言而言，仍然缺少一个重要的语法支持，即循环。
SQL仍然无法像高级编程语言使用for, while一样，支持不确定循环次数的执行。
为此，SQL支持了with recursive语法，来解决这一问题，可以用在树和图的拓扑搜索上。
以下图的树为例：在GaussDB(DWS)中，可以使用表tree来存储所有节点及父子信息，表定义语句如下：CREATE TABLE tree(id INT, parentid INT);表中数据如下：通过以下WITH RECURSIVE语句，我们可以返回从顶层1号节点开始，整个树的节点，以及层次信息：WITH RECURSIVE nodeset AS(-- recursive initializing querySELECT id, parentid, 1 AS level FROM treeWHERE id = 1UNION ALL-- recursive join querySELECT tree.id, tree.parentid, level + 1 FROM tree, nodesetWHERE tree.parentid = nodeset.id)SELECT FROM nodeset ORDER BY id;上述查询中，我们可以看出，一个典型的WITH RECURSIVE表达式包含至少一个递归查询的CTE，该CTE中的定义为一个UNION ALL集合操作，第一个分支为递归起始查询，第二个分支为递归关联查询，需要自引用第一部分进行不断递归关联。
该语句执行时，递归起始查询执行一次，关联查询执行若干次并将结果叠加到起始查询结果集中，直到某一些关联查询结果为空，则返回。
上述查询的执行结果如下：起始查询结果包含level=1的结果集，关联查询执行了五次，前四次分别输出level=2,3,4,5的结果集，在第五次执行时，由于没有parentid和输出结果集id相等的记录，也就是再没有多余的孩子节点，因此查询结束。
从WITH RECURSIVE的执行过程来看，是典型的层次遍历（广度优先）的执行方式，因此WITH RECURSIVE也可以称为层次查询。
除了典型的树、图的拓扑查找应用，WITH RECURSIVE还可以用于模拟多数的复杂循环操作，只要我们正确定义起始条件、循环条件和终止条件。
例如：下例将整数1000-1001转化成二进制串。
WITH RECURSIVE integer AS(SELECT x AS orig, x, '' AS binary_text FROM GENERATE_SERIES(1000, 1010) AS set(x)UNION ALLSELECT orig, FLOOR(x/2)::int, CASE WHEN x % 2 = 1 THEN '1' ELSE '0' END || binary_text FROM INTEGER WHERE x > 0)SELECT orig, binary_text FROM integer WHERE x = 0 ORDER BY orig;执行结果如下：三. 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功能的基本语法如下：SELECT FROM tablename [START WITH <condition1>] CONNECT BY <condition2>;其中START WITH子句用于指定起始条件，即<condition1>，循环关联条件为<condition2>，其中可以使用PRIOR关键字来表示来自于上一循环的列。
例如上节中所述的树遍历的例子，使用Oracle的Connect By语法，语句如下：SELECT FROM tree START WITH id = 1 CONNECT BY PRIOR id = parentid;可以看出，Oracle的CONNECT BY实现了基本的树和图拓扑关系查找的功能，用法较简单，但相较于WITH RECURSIVE，不如其灵活，对于一些复杂的循环语句，尤其是起始语句和循环关联语句的输出列不相同的场景，无法支持。
但由于GaussDB(DWS)目前很多客户都是从Oracle系统迁移而来，因此面临着将Oracle的CONNECT BY语法改写为WITH RECURSIVE的需求。
对于基本语法，我们可以进行如下基本的改写以满足其功能：WITH RECURSIVE tmp_cte AS(SELECT FROM table WHERE <condition1>UNION ALLSELECT table. FROM table JOIN tmp_cte ON <condition2>)SELECT FROM tmp_cte;其中<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，此时会对输出行去重，自动终止循环，但要求输出行完全来自初始行，不能增加其它表达式，否则一并参与去重。
例如：WITH RECURSIVE nodeset AS(SELECT id, parentid, 1 AS level FROM treeWHERE id = 1UNIONSELECT tree.id, tree.parentid, level + 1 FROM tree, nodesetWHERE tree.parentid = nodeset.id)SELECT FROM nodeset ORDER BY id;注：此改写仍与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的过程中更进了一步。
点击下方，第一时间了解华为云新鲜技术~华为云博客_大数据博客_AI博客_云计算博客_开发者中心-华为云