这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

WITH 子句

WITH 子句是较大的主要查询中的伴随查询。Vertica 可以通过两种方式评估 WITH 子句：

  • 内联扩展（默认）：当主要查询每次引用每个 WITH 子句时，Vertica 才会对它进行评估。

  • 实体化：Vertica 对每个 WITH 子句进行一次评估，将结果存储在临时表中，然后在查询需要时引用此表。

有关语法选项和要求的详细信息，请参阅 WITH 子句。

1 - WITH 子句的内联展开

默认情况下，Vertica 使用内联展开评估 WITH 子句。当主要查询每次引用每个 WITH 子句时，Vertica 才会对它进行评估。内联展开通常在查询没有多次引用同一个 WITH 子句时或在内联展开之后可以开展某些本地优化时运行效果最好。

示例

以下示例显示适合用于内联展开的 WITH 子句。在获取 2007 年 12 月 1 日至 7 日发货的所有订单的订单信息的查询中，使用 WITH 子句。


-- Begin WITH
WITH store_orders_fact_new AS(
   SELECT * FROM store.store_orders_fact WHERE date_shipped between '2007-12-01' and '2007-12-07')
-- End WITH
-- Begin primary query
SELECT store_key, product_key, product_version, SUM(quantity_ordered*unit_price) AS total_price
FROM store_orders_fact_new
GROUP BY store_key, product_key, product_version
ORDER BY total_price DESC;

 store_key | product_key | product_version | total_price
-----------+-------------+-----------------+-------------
       232 |        1855 |               2 |       29008
       125 |        8500 |               4 |       28812
       139 |        3707 |               2 |       28812
       212 |        3203 |               1 |       28000
       236 |        8023 |               4 |       27548
       123 |       10598 |               2 |       27146
        34 |        8888 |               4 |       27100
       203 |        2243 |               1 |       27027
       117 |       13932 |               2 |       27000
        84 |         768 |               1 |       26936
       123 |        1038 |               1 |       26885
       106 |       18932 |               1 |       26864
        93 |       10395 |               3 |       26790
       162 |       13073 |               1 |       26754
        15 |        3679 |               1 |       26675
        52 |        5957 |               5 |       26656
       190 |        8114 |               3 |       26611
         5 |        7772 |               1 |       26588
       139 |        6953 |               3 |       26572
       202 |       14735 |               1 |       26404
       133 |        2740 |               1 |       26312
       198 |        8545 |               3 |       26287
       221 |        7582 |               2 |       26280
       127 |        9468 |               3 |       26224
        63 |        8115 |               4 |       25960
       171 |        2088 |               1 |       25650
       250 |       11210 |               3 |       25608
...

Vertica 处理查询，如下所示：

  1. 在主要查询中展开对 store_orders_fact_new 的 WITH 子句引用。

  2. 展开 WITH 子句后，评估主要查询。

2 - WITH 子句的实体化

实体化启用之后，Vertica 会对每个 WITH 子句进行一次评估，将结果存储在临时表中，然后在查询需要时引用此表。主要查询执行完成之后，Vertica 会将临时表删除。

如果 WITH 子句非常复杂，例如当 WITH 子句包含 JOIN 和 GROUP BY 子句，并且在主要查询中多次被引用时，实体化可帮助提高性能。

如果实体化已启用，WITH 语句会自动提交用户事务。即使将 EXPLAIN 与 WITH 语句一起使用，也是如此。

启用 WITH 子句实体化

WITH 实体化由配置参数 WithClauseMaterialization 设置，默认设置为 0（禁用）。您可以分别使用 ALTER DATABASE 和 ALTER SESSION，通过在数据库和会话级别设置 WithClauseMaterialization 来启用和禁用实体化：

  • 数据库：

    => ALTER DATABASE db-spec SET PARAMETER WithClauseMaterialization={ 0 | 1 };
=> ALTER DATABASE db-spec CLEAR PARAMETER WithClauseMaterialization;

  • 会话：参数设置将继续有效，直到以显式方式清除它，或者会话结束。

    => ALTER SESSION SET PARAMETER WithClauseMaterialization={ 0 | 1 };
=> ALTER SESSION CLEAR PARAMETER WithClauseMaterialization;

您还可以使用提示 ENABLE_WITH_CLAUSE_MATERIALIZATION 为单个查询启用 WITH 实体化。如果查询返回，则会自动清除实体化。例如：


=> WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ revenue AS (
      SELECT vendor_key, SUM(total_order_cost) AS total_revenue
      FROM store.store_orders_fact
      GROUP BY vendor_key ORDER BY 1)
     ...

使用 EE5 临时关系处理 WITH 子句

默认情况下，当重用 WITH 子句查询时，Vertica 将这些 WITH 子句查询输出保存在 EE5 临时关系中。不过，可以更改此选项。可以通过以下设置方式，使用配置参数 EnableWITHTempRelReuseLimit 来设置 EE5 临时关系支持：

  • 0：禁用此功能。

  • 1：将所有 WITH 子句查询强制保存到 EE5 临时关系中，无论它们是否被重用。

  • 2（默认值）：仅将重用的 WITH 子句查询保存到 EE5 临时关系中。

  • 3 及 3 以上：仅当使用 WITH 子句查询至少达到此次数时才将其保存到 EE5 临时关系中。

可以分别使用 ALTER DATABASE 和 ALTER SESSION，在数据库和会话级别设置 EnableWITHTempRelReuseLimit。当 WithClauseMaterialization 设置为 1 时，该设置将覆盖任何 EnableWITHTempRelReuseLimit 设置。

请注意，对于具有复杂类型的 WITH 查询，禁用临时关系。

示例

以下示例显示适合用于实体化的 WITH 子句。查询获取在所有订单中拥有最高合并订单成本的供应商的数据：

-- Enable materialization

=> ALTER SESSION SET PARAMETER WithClauseMaterialization=1;

-- Define WITH clause
=> WITH revenue AS (
      SELECT vendor_key, SUM(total_order_cost) AS total_revenue
      FROM store.store_orders_fact
      GROUP BY vendor_key ORDER BY 1)
-- End WITH clause

-- Primary query
=> SELECT vendor_name, vendor_address, vendor_city, total_revenue
FROM vendor_dimension v, revenue r
WHERE v.vendor_key = r.vendor_key AND total_revenue = (SELECT MAX(total_revenue) FROM revenue )
ORDER BY vendor_name;
   vendor_name    | vendor_address | vendor_city | total_revenue
------------------+----------------+-------------+---------------
 Frozen Suppliers | 471 Mission St | Peoria      |      49877044
(1 row)

Vertica 按以下方式处理此查询：

  1. WITH 子句 revenue 通过 store.store_orders_fact 表评估 SELECT 语句。

  2. revenue 子句的结果存储在本地临时表中。

  3. 无论何时引用 revenue 子句，都会使用表中存储的结果。

  4. 查询执行完成之后，临时表会删除。

3 - WITH 子句递归

包含 RECURSIVE 选项的 WITH 子句可以重复执行 UNION 或 UNION ALL 查询，从而迭代其自身的输出。递归查询在处理分层结构（例如，经理下属关系）或树状结构数据（如分类法）等自引用数据时十分有用。

配置参数 WithClauseRecursionLimit（默认设置为 8）将设置递归的最大深度。您可以分别使用 ALTER DATABASE 和 ALTER SESSION 在数据库和会话范围内设置此参数。递归将会继续，直到达到配置的最大深度为止，或者直到最后一次迭代返回没有数据为止。

可以按如下方式指定递归 WITH 子句：

WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ ] RECURSIVE
   cte‑identifier [ ( column-aliases ) ] AS (
     non-recursive-term
     UNION [ ALL ]
     recursive-term
   )

非递归项和递归项由 UNION 或 UNION ALL 分隔：

  • non-recursive-term 查询将其结果集设置在 cte-identifier，在 recursive-term 中递归。

  • UNION 语句的 recursive-term 以递归方式迭代其自身输出。当递归完成时，所有迭代的结果均会编译并在 cte-identifier 中设置。

例如：


=> ALTER SESSION SET PARAMETER WithClauseRecursionLimit=4; -- maximum recursion depth = 4
=> WITH RECURSIVE nums (n) AS (
   SELECT 1 -- non-recursive (base) term
   UNION ALL
     SELECT n+1 FROM nums -- recursive term
  )
SELECT n FROM nums; -- primary query

此简单查询按如下方式执行：

  1. 执行 WITH RECURSIVE 子句：

    • 评估非递归项 SELECT 1，并将结果集 (1) 放入 nums。

    • 迭代 UNION ALL 查询 (SELECT n+1)，直到迭代次数大于配置参数 WithClauseRecursionLimit。

    • 合并所有 UNION 查询的结果，并将结果集设置在 nums 中，然后退出到主要查询。

  2. 执行主要查询 SELECT n FROM nums：

    
 n
---
 1
 2
 3
 4
 5
(5 rows)

在本例中，根据 WithClauseRecursionLimit，WITH RECURSIVE 子句在四次迭代后退出。如果将 WithClauseRecursionLimit 还原为其默认值 8，则子句在八次迭代后退出：


=> ALTER SESSION CLEAR PARAMETER WithClauseRecursionLimit;
=> WITH RECURSIVE nums (n) AS (
   SELECT 1
   UNION ALL
     SELECT n+1 FROM nums
  )
SELECT n FROM nums;
 n
---
 1
 2
 3
 4
 5
 6
 7
 8
 9
(9 rows)

限制

存在以下限制：

  • 非递归项的 SELECT 列表不能包含通配符 *（星号）或函数 MATCH_COLUMNS。

  • 递归项只能引用目标 CTE 一次。

  • 递归引用不能出现在外联接中。

  • 递归引用不能出现在子查询中。

  • WITH 子句不支持 UNION 选项 ORDER BY、LIMIT 和 OFFSET。

示例

一家小型软件公司维护以下有关员工及其经理的数据：

=> SELECT * FROM personnel.employees ORDER BY emp_id;
 emp_id |   fname   |   lname   | section_id |    section_name     |  section_leader  | leader_id
--------+-----------+-----------+------------+---------------------+------------------+-----------
      0 | Stephen   | Mulligan  |          0 |                     |                  |
      1 | Michael   | North     |        201 | Development         | Zoe Black        |         3
      2 | Megan     | Berry     |        202 | QA                  | Richard Chan     |        18
      3 | Zoe       | Black     |        101 | Product Development | Renuka Patil     |        24
      4 | Tim       | James     |        203 | IT                  | Ebuka Udechukwu  |        17
      5 | Bella     | Tucker    |        201 | Development         | Zoe Black        |         3
      6 | Alexandra | Climo     |        202 | QA                  | Richard Chan     |        18
      7 | Leonard   | Gray      |        203 | IT                  | Ebuka Udechukwu  |        17
      8 | Carolyn   | Henderson |        201 | Development         | Zoe Black        |         3
      9 | Ryan      | Henderson |        201 | Development         | Zoe Black        |         3
     10 | Frank     | Tucker    |        205 | Sales               | Benjamin Glover  |        29
     11 | Nathan    | Ferguson  |        102 | Sales Marketing     | Eric Redfield    |        28
     12 | Kevin     | Rampling  |        101 | Product Development | Renuka Patil     |        24
     13 | Tuy Kim   | Duong     |        201 | Development         | Zoe Black        |         3
     14 | Dwipendra | Sing      |        204 | Tech Support        | Sarah Feldman    |        26
     15 | Dylan     | Wijman    |        206 | Documentation       | Kevin Rampling   |        12
     16 | Tamar     | Sasson    |        207 | Marketing           | Nathan Ferguson  |        11
     17 | Ebuka     | Udechukwu |        101 | Product Development | Renuka Patil     |        24
     18 | Richard   | Chan      |        101 | Product Development | Renuka Patil     |        24
     19 | Maria     | del Rio   |        201 | Development         | Zoe Black        |         3
     20 | Hua       | Song      |        204 | Tech Support        | Sarah Feldman    |        26
     21 | Carmen    | Lopez     |        204 | Tech Support        | Sarah Feldman    |        26
     22 | Edgar     | Mejia     |        206 | Documentation       | Kevin Rampling   |        12
     23 | Riad      | Salim     |        201 | Development         | Zoe Black        |         3
     24 | Renuka    | Patil     |        100 | Executive Office    | Stephen Mulligan |         0
     25 | Rina      | Dsouza    |        202 | QA                  | Richard Chan     |        18
     26 | Sarah     | Feldman   |        101 | Product Development | Renuka Patil     |        24
     27 | Max       | Mills     |        102 | Sales Marketing     | Eric Redfield    |        28
     28 | Eric      | Redfield  |        100 | Executive Office    | Stephen Mulligan |         0
     29 | Benjamin  | Glover    |        102 | Sales Marketing     | Eric Redfield    |        28
     30 | Dominic   | King      |        205 | Sales               | Benjamin Glover  |        29
     32 | Ryan      | Metcalfe  |        206 | Documentation       | Kevin Rampling   |        12
     33 | Piers     | Paige     |        201 | Development         | Zoe Black        |         3
     34 | Nicola    | Kelly     |        207 | Marketing           | Nathan Ferguson  |        11
(34 rows)

您可以通过 WITH RECURSIVE 查询此数据以了解员工与经理的关系。例如，以下查询的 WITH RECURSIVE 子句获取员工 Eric Redfield（包括他的所有直接下属和间接下属员工）的员工-经理关系：

WITH RECURSIVE managers (employeeID, employeeName, sectionID, section, lead, leadID)
 AS (SELECT emp_id, fname||' '||lname, section_id, section_name, section_leader, leader_id
      FROM personnel.employees WHERE fname||' '||lname = 'Eric Redfield'
 UNION
    SELECT emp_id, fname||' '||lname AS employee_name, section_id, section_name, section_leader, leader_id FROM personnel.employees e
      JOIN managers m ON m.employeeID = e.leader_id)
 SELECT employeeID, employeeName, lead AS 'Reports to', section, leadID from managers ORDER BY sectionID, employeeName;

WITH RECURSIVE 子句定义了 CTE managers，然后分两个阶段执行：

  1. 非递归项使用从 personnel.employees 查询的数据填充 managers。

  2. 递归项的 UNION 查询迭代它自己的输出，直到在第四个循环中找不到更多数据。然后，所有迭代结果进行编译并设置在 managers 中，WITH CLAUSE 退出到主要查询。

主要查询从 managers 返回三个级别的数据（每个递归迭代一个级别）：

类似地，以下查询遍历相同的数据以获取员工 Richard Chan（他在公司管理层中比 Eric Redfield 低一级）的所有员工-经理关系：

WITH RECURSIVE managers (employeeID, employeeName, sectionID, section, lead, leadID)
 AS (SELECT emp_id, fname||' '||lname, section_id, section_name, section_leader, leader_id
      FROM personnel.employees WHERE fname||' '||lname = 'Richard Chan'
 UNION
    SELECT emp_id, fname||' '||lname AS employee_name, section_id, section_name, section_leader, leader_id FROM personnel.employees e
      JOIN managers m ON m.employeeID = e.leader_id)
 SELECT employeeID, employeeName, lead AS 'Reports to', section, leadID from managers ORDER BY sectionID, employeeName;

WITH RECURSIVE 子句像以前一样执行，但这次它在两次迭代后找不到更多数据而退出。因此，主要查询从 managers 返回两个级别的数据：

WITH RECURSIVE 实体化

默认情况下，实体化处于禁用状态。在这种情况下，Vertica 将 WITH RECURSIVE 查询重写为子查询，其数量与所需的递归级别一样多。

如果递归非常深，则大量查询重写可能会产生相当大的开销，从而对性能产生不利影响并耗尽系统资源。在这种情况下，请考虑使用配置参数 WithClauseMaterialization 或提示 ENABLE_WITH_CLAUSE_MATERIALIZATION 来启用实体化。在任何一种情况下，来自所有递归级别的中间结果集都将写入本地临时表。当递归完成时，所有临时表中的中间结果都进行编译并传递给主要查询。