GreenPlum中的WITH查询(公用表表达式)

0    92    1

Tags:

👉 本文共约3283个字,系统预计阅读时间或需13分钟。

简介

WITH子句提供在一个更大的SELECT查询中,使用子查询或执行数据修改操作的方式。你可以在INSERT, UPDATE, 或 DELETE 命令中使用WITH子句。

在WITH子句中的查询 在WITH子句中使用SELECT的相关信息

WITH子句中的数据修改语句, 在WITH子句中使用INSERT, UPDATE, or DELETE

Note: 这些是使用WITH子句的限制。

  • 对于包含WITH子句的SELECT命令,该子句最多只能包含一个修改表数据的子句(INSERT, UPDATE, 或 DELETE命令)。
  • 对于包含WITH子句的数据修改命令(INSERT, UPDATE, 或 DELETE),该子句只能包含SELECT命令,WITH子句不能包含数据修改命令。

默认情况下,将启用WITH子句的RECURSIVE关键字。通过将服务器配置参数gp“recursive”设置为false,可以禁用递归。By default, the RECURSIVE keyword for the WITH clause is enabled. 通过将服务器配置参数gp_recursive_cte 设置为false,WITH子句的RECURSIVE关键字被禁用。

Parent topic: 查询数据

在WITH子句中的查询

子查询通常被称为公共表表达式或CTE,可以认为是为查询定义临时表。这些示例显示了与SELECT命令一起使用的WITH子句。带WITH子句的示例可以以插入、更新或删除的相同方式使用。在每种情况下,WITH子句都有效地提供了可以在主命令中引用的临时表。

WITH子句中的SELECT命令在每次执行父查询时只计算一次,即使父查询或WITH子句的同级多次引用了该命令。因此,需要在多个地方进行的昂贵计算可以放在WITH子句中,以避免重复工作。另一个可能的应用是防止对具有副作用的函数进行不必要的多次计算。然而,这种情况的另一方面是,与普通的子查询相比,优化器无法将来自父查询的限制向下推送到WITH查询中。 WITH查询通常将按写入方式进行计算,而不禁止父查询随后可能丢弃的行。但是,如果对查询的引用只需要有限的行数,则计算可能会提前停止。

此功能的一个用途是将复杂的查询分解为简单的部分。此示例查询仅在顶部销售区域中显示每个产品的销售总额:

可以不使用WITH子句编写查询,但需要两级嵌套子选择。相比较来说,WITH子句比较容易。

当启用可选RECURSIVE关键字时,WITH子句可以完成标准SQL中不可能完成的事情。使用RECURSIVE,WITH子句中的查询可以引用自己的输出。这是一个简单的例子,它计算从1到100的整数之和:

递归WITH子句(使用WITH关键字的WITH子句)的一般形式是一个非递归术语,后跟一个 UNION(或 UNION ALL),然后是一个递归术语,其中只有递归术语可以包含对查询输出的引用。

包含 UNION [ ALL ]的递归WITH 查询执行如下:

  1. 计算非递归项。对于UNION (而不是UNION ALL),丢弃重复的行。包括递归查询结果中的所有剩余行,并将它们放在临时工作表中。
  2. 只要工作台不是空的,重复以下步骤:
    1. 计算递归项,将工作表的当前内容替换为递归自引用。对于 UNION(而不是UNION ALL),放弃重复的行和复制任何先前结果行的行。在递归查询的结果中包含所有剩余的行,并将它们放在临时中间表中。
    2. 将当前工作表的内容替换为中间表的内容,然后清空中间表

Note: 严格来说,过程是迭代而不是递归的,但是RECURSIVE是由SQL标准委员会选择的术语。

递归WITH查询通常用于处理层次结构或树结构数据。例如,该查询查找产品的所有直接和间接子部分,只给出一个显示直接包含内容的表:

使用递归WITH时,必须确保查询的递归部分最终不会返回元组,否则查询将无限期循环。 在计算整数和的示例中,工作表在每个步骤中包含一行,并在连续步骤中接受从1到100的值。 在第100步中,由于WHERE子句没有输出,查询终止。

对于某些查询,使用 UNION而不是 UNION ALL可以通过丢弃重复以前输出行的行来确保查询的递归部分最终不返回元组。然而,一个循环通常不涉及完全重复的输出行:只检查一个或几个字段就足够了,以查看以前是否达到了相同的点。处理这种情况的标准方法是计算访问值的数组。 例如,考虑使用链接字段搜索表图形的以下查询:

如果链接关系包含循环,则此查询将循环。因为查询需要深度输出,所以将 UNION ALL更改为UNION不会消除循环。 相反,查询需要在遵循特定的链接路径时,识别它是否再次到达同一行。此修改后的查询将path和cycle两列,添加到容易循环的查询中:

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复