写点什么

SQL 操作:WITH 表达式及其应用

  • 2022 年 6 月 21 日
  • 本文字数:5452 字

    阅读完需:约 18 分钟

本文分享自华为云社区《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 recursive


WITH 表达式极大的方便了语句内相同 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 的过程中更进了一步。


点击关注,第一时间了解华为云新鲜技术~

发布于: 刚刚阅读数: 4
用户头像

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算! 传送门:https://bbs.huaweicloud.com/

评论

发布
暂无评论
SQL操作:WITH表达式及其应用_数据库_华为云开发者联盟_InfoQ写作社区