PostgreSQL中WITH查询公用表表达式

zxrhhm

于 2024-10-09 10:30:31 发布

阅读量630

点赞数 8

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/zxrhhm/article/details/142768634

版权

在PostgreSQL中，WITH查询（也称为公用表表达式，Common Table Expressions，简称CTE）是一种用于定义临时结果集的语句，这个结果集可以在后续的查询中被引用。WITH查询可以简化复杂的SQL查询，使其更加可读和易于维护。

基本语法

WITH查询的基本语法如下：

WITH cte_name (column1, column2, ...) AS (
    -- 这是一个普通的SQL查询
    SELECT ...
)
-- 这是一个引用前面定义的CTE的查询
SELECT ...
FROM cte_name
-- 还可以有其他JOIN、WHERE等子句

SELECT in WITH

WITH中SELECT的基本价值是将复杂的查询分解成更简单的部分。如下示例:

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;

在这个例子中，它只显示顶级销售地区的每个产品销售总额。VITH子句定义了两个辅助语句:region sales和top regions，其中region sales的输出用于top region, top region的输出用于主SELECT查询。
本例可以不使用WITH编写，但是我们需要两层嵌套的sub- select。这样做更容易一些。

递归CTE(Recursive Queries)

PostgreSQL还支持递归CTE，这允许你定义一个递归查询，这在处理层次结构数据时非常有用，比如组织结构、目录树等。

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

递归WITH查询的一般形式总是一个非递归项，然后是UNION(或UNION ALL)，然后是递归项，其中只有递归项可以包含对查询自身输出的引用。这样的查询执行如下:
递归查询评估

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

在上面的示例中，工作表在每个步骤中只有一行，并且在连续的步骤中获取从1到100的值。在第100步中，由于存在WHERE子句，因此没有输出，因此查询终止。

递归查询通常用于处理分层或树状结构的数据。下面这个查询是一个有用的例子，它查找产品的所有直接和间接子部件，只给出一个显示直接包含项的表:

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 * pr.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

示例：递归查询组织结构

假设我们有一个名为employees的表，其中包含employee_id、name和manager_id（表示该员工的直接上级）。

WITH RECURSIVE employee_hierarchy AS (
    -- 基础情况：选择所有没有上级的员工（即顶层员工）
    SELECT employee_id, name, manager_id, CAST(name AS VARCHAR(1000)) AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归情况：选择所有有上级的员工，并将他们加入到层次结构中
    SELECT e.employee_id, e.name, e.manager_id, CONCAT(eh.hierarchy_path, ' -> ', e.name) AS hierarchy_path
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- 查询结果
SELECT * FROM employee_hierarchy;

在这个例子中，employee_hierarchy是一个递归CTE，它首先选择所有没有上级的员工作为基础情况，然后递归地选择所有有上级的员工，并将他们加入到层次结构中。hierarchy_path列显示了从顶层员工到当前员工的完整路径。

搜索顺序Search Order

在使用递归查询计算树遍历时，您可能希望按照深度优先或宽度优先的顺序对结果进行排序。这可以通过与其他数据列一起计算排序列并使用该列在最后对结果进行排序来实现。注意，这实际上并不控制查询求值访问行的顺序;这在SQL实现中始终是依赖的。这种方法只是提供了一种方便的方法来排序之后的结果。

为了创建深度优先顺序，我们为每个结果行计算到目前为止访问过的行数组。例如，考虑以下使用link字段搜索表树的查询:

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;

要添加深度优先排序信息，你可以这样写:
To add depth-first ordering information, you can write this:

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[t.id]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || t.id
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

一般情况下在需要使用多个字段来标识一行的，请使用行数组。例如，如果我们需要跟踪字段f1和f2:

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

在只需要跟踪一个字段的常见情况下，省略ROW()语法。这允许使用简单数组而不是复合类型数组，从而提高效率。

周期检测 Cycle Detection

在使用递归查询时，重要的是要确保查询的递归部分最终不会返回元组，否则查询将无限循环。有时，使用UNION而不是UNION ALL可以通过丢弃重复前一个输出行的行来实现这一点。然而，一个循环通常不涉及完全重复的输出行:可能需要检查一个或几个字段，以查看之前是否到达了相同的点。处理这种情况的标准方法是计算已经访问过的值的数组。例如，再次考虑下面的查询，它使用链接字段搜索表图:

When working with recursive queries it is important to be sure that the recursive part of the query will eventually return no tuples, or else the query will loop indefinitely. Sometimes, using UNION instead of UNION ALL can accomplish this by discarding rows that duplicate previous output rows. However, often a cycle does not involve output rows that are completely duplicate: it may be necessary to check just one or a few fields to see if the same point has been reached before. The standard method for handling such situations is to compute an array of the already-visited values. For example, consider again the following query that searches a table graph using a link field:

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 0
    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并不能消除循环。相反，我们需要识别在遵循特定的链接路径时是否再次到达了同一行。在循环的查询中添加两列is_cycle和path:

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[g.id]
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      g.id = ANY(path),
      path || g.id
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
SELECT * FROM search_graph;

除了防止循环之外，数组值本身通常也很有用，因为它表示到达任何特定行的“路径”。
Aside from preventing cycles, the array value is often useful in its own right as representing the “path” taken to reach any particular row.

在一般情况下，需要检查多个字段以识别循环，请使用行数组。例如，如果我们需要比较f1和f2字段:
In the general case where more than one field needs to be checked to recognize a cycle, use an array of rows. For example, if we needed to compare fields f1 and f2:

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[ROW(g.f1, g.f2)]
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      ROW(g.f1, g.f2) = ANY(path),
      path || ROW(g.f1, g.f2)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
SELECT * FROM search_graph;

在通常情况下，只需检查一个字段就可以识别一个循环，因此省略ROW()语法。这允许使用简单数组而不是复合类型数组，从而提高效率。
Omit the ROW() syntax in the common case where only one field needs to be checked to recognize a cycle. This allows a simple array rather than a composite-type array to be used, gaining efficiency.

有内置语法来简化循环检测。上面的查询也可以这样写:
There is built-in syntax to simplify cycle detection. The above query can also be written like this:

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
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

它会在内部被改写成上面的形式。CYCLE子句首先指定要跟踪循环检测的列列表，然后指定显示是否检测到循环的列名，最后指定将跟踪路径的另一个列的名称。循环和路径列将隐式地添加到CTE的输出行中。
and it will be internally rewritten to the above form. The CYCLE clause specifies first the list of columns to track for cycle detection, then a column name that will show whether a cycle has been detected, and finally the name of another column that will track the path. The cycle and path columns will implicitly be added to the output rows of the CTE.

循环路径列的计算方式与前一节中显示的深度优先排序列相同。查询可以同时具有SEARCH和CYCLE子句，但是深度优先搜索规范和循环检测规范会产生冗余计算，因此仅使用CYCLE子句并按路径列排序会更有效。如果需要宽度优先排序，那么同时指定SEARCH和CYCLE可能很有用。
The cycle path column is computed in the same way as the depth-first ordering column show in the previous section. A query can have both a SEARCH and a CYCLE clause, but a depth-first search specification and a cycle detection specification would create redundant computations, so it’s more efficient to just use the CYCLE clause and order by the path column. If breadth-first ordering is wanted, then specifying both SEARCH and CYCLE can be useful.

WITH子句和limit一起使用

当您不确定查询是否可能循环时，测试查询的一个有用技巧是在父查询中放置LIMIT。例如，这个查询将在没有LIMIT的情况下永远循环:
A helpful trick for testing queries when you are not certain if they might loop is to place a LIMIT in the parent query. For example, this query would loop forever without the LIMIT:

WITH RECURSIVE t(id) AS (
    SELECT 1
  UNION ALL
    SELECT id+1 FROM t
)
SELECT id,now() as v1 FROM t LIMIT 10;
-- run result
 id |              v1
----+-------------------------------
  1 | 2024-10-09 08:46:46.959977+08
  2 | 2024-10-09 08:46:46.959977+08
  3 | 2024-10-09 08:46:46.959977+08
  4 | 2024-10-09 08:46:46.959977+08
  5 | 2024-10-09 08:46:46.959977+08
  6 | 2024-10-09 08:46:46.959977+08
  7 | 2024-10-09 08:46:46.959977+08
  8 | 2024-10-09 08:46:46.959977+08
  9 | 2024-10-09 08:46:46.959977+08
 10 | 2024-10-09 08:46:46.959977+08
(10 rows)

在With子句中修改数据Data-Modifying Statements in WITH

可以在WITH中使用大多数数据修改语句(INSERT、UPDATE或DELETE，但不能使用MERGE)。这允许您在同一个查询中执行几个不同的操作。一个例子是:

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

上面示例的一个优点是，WITH子句附加到INSERT，而不是INSERT中的子select。这是必要的，因为数据修改语句只允许出现在附加到顶级语句的WITH子句中。但是，正常的WITH可见性规则仍然适用，因此可以从子select中引用WITH语句的输出。

WITH中的数据修改语句通常有return子句(参见 https://www.postgresql.org/docs/16/dml-returning.html)，如上面的例子所示。是RETURNING子句的输出，而不是数据修改语句的目标表，形成了查询的其余部分可以引用的临时表。如果WITH中的数据修改语句缺少RETURNING子句，那么它不会形成临时表，并且不能在查询的其余部分中引用。这样的语句仍然会被执行。如下不是特别有用的例子是:

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

这个示例将从表foo和bar中删除所有行。报告给客户端的受影响行数将只包括从bar中删除的行。

不允许在数据修改语句中使用递归自引用。在某些情况下，可以通过引用递归WITH的输出来绕过此限制，例如:

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

该查询将删除产品的所有直接和间接子部件。

WITH中的数据修改语句只执行一次，并且总是完成，与主查询是否读取所有(或任何)输出无关。注意，这与WITH中的SELECT规则不同:如前一节所述，SELECT的执行只在主查询需要输出时执行。

WITH中的子语句彼此并发执行，并与主查询并发执行。因此，在WITH中使用数据修改语句时，指定的更新实际发生的顺序是不可预测的。所有语句都在同一个快照中执行(参见第13章)，因此它们不能“看到”彼此对目标表的影响。这减轻了行更新的实际顺序不可预测性的影响，并且意味着返回数据是在不同的WITH子语句和主查询之间通信更改的唯一方法。一个例子是

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

外部SELECT将返回UPDATE操作之前的原始价格

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.

注意事项

性能：虽然WITH查询可以使SQL查询更加清晰，但在某些情况下，它可能会影响性能。因此，在优化查询时，需要权衡可读性和性能。
作用域：CTE在定义它们的查询块内是可见的，并且不能在其他查询块或会话中重用。
递归深度：在使用递归CTE时，需要注意递归的深度，以避免因过深的递归而导致的性能问题或栈溢出错误。

通过合理使用WITH查询，可以显著提高SQL查询的可读性和可维护性，同时处理一些复杂的查询需求。

zxrhhm

关注

8
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫