合 GreenPlum中的WITH查询（公用表表达式）

2023年3月28日

0 104 1

Tags： GreenPlum WITH

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

简介
在WITH子句中的查询
WITH子句中的数据修改语句
参考

简介

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 regional_sales AS (
     SELECT region, SUM(amount) AS total_sales
     FROM orders
     GROUP BY region
  ), top_regions AS (
     SELECT region
     FROM regional_sales
     WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
  )
SELECT region,
    product,
    SUM(quantity) AS product_units,
    SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH regional_sales AS (

SELECT region, SUM(amount) AS total_sales

FROM orders

GROUP BY region

), top_regions AS (

SELECT region

FROM regional_sales

WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)

)

SELECT region,

product,

SUM(quantity) AS product_units,

SUM(amount) AS product_sales

FROM orders

WHERE region IN (SELECT region FROM top_regions)

GROUP BY region, product;

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

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

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

WITH RECURSIVE t(n) AS (

VALUES (1)

UNION ALL

SELECT n+1 FROM t WHERE n < 100

)

SELECT sum(n) FROM t;

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

non_recursive_term UNION [ ALL ] recursive_term

1	non_recursive_term UNION [ ALL ] recursive_term

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

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

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

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

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part ;

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (

SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'

UNION ALL

SELECT p.sub_part, p.part, p.quantity

FROM included_parts pr, parts p

WHERE p.part = pr.sub_part

)

SELECT sub_part, SUM(quantity) as total_quantity

FROM included_parts

GROUP BY sub_part ;

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

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

WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth) AS (

SELECT g.id, g.link, g.data, 1

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1

FROM graph g, search_graph sg

WHERE g.id = sg.link

)

SELECT * FROM search_graph;

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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： GreenPlum WITH

小麦苗

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

发表回复取消回复

要发表评论，您必须先登录。

一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 GreenPlum中的WITH查询（公用表表达式）

简介

在WITH子句中的查询

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合 GreenPlum中的WITH查询（公用表表达式）

简介

在WITH子句中的查询

相关文章

您可能还喜欢...

PG和GreenPlum中的依赖查询

GreenPlum优化参数之数据库参数gp_interconnect_tcp_listener_backlog和操作系统内核参数net.core.somaxconn调优 SetupTCPInterconnect: too many expected incoming connections(352), Interconnect setup might possibly fail

GreenPlum数据库不定时报错interconnect error connection closed prematurely、timed out waiting for response from motion receiver during TCP interconnect teardown

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复