WITH 子句
WITH 子句定义了一个或多个已命名公用表表达式 (CTE)，其中每个 CTE 均封装了一个结果集，该结果集可以被同一个 WITH 子句中的另一个 CTE 引用或被主要查询引用。Vertica 可以对每个引用执行 CTE（内联展开），或将结果集实体化作为临时表，以供其所有引用重复使用。在这两种情况下，WITH 子句都可以帮助简化复杂查询并避免语句重复。
语法
WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ ] [ RECURSIVE ] {
cte‑identifier [ ( column-aliases ) ] AS (
[ subordinate-WITH-clause ]
query-expression )
} [,...]
参数
/*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/- 启用当前 WITH 子句中所有查询的实体化。否则，实体化由配置参数 WithClauseMaterialization 设置，默认设置为 0（禁用）。如果禁用 WithClauseMaterialization，则 WITH 子句的主查询返回时会自动清除实体化。有关详细信息，请参阅WITH 子句的实体化。
RECURSIVE- 指定通过重复执行嵌入的 UNION 或 UNION ALL 语句来迭代 WITH 子句的结果集。请参阅下面的递归查询。
-
cte‑identifier - 标识 WITH 子句中的公用表表达式 (CTE)。此标识符可用于同一 WITH 子句中的 CTE，也可用于父 WITH 子句和子 WITH 子句（如果有）中的 CTE。最外层（主）WITH 子句的 CTE 标识符也可用于主要查询。
同一 WITH 子句的所有 CTE 标识符必须是唯一的。例如，以下 WITH 子句定义了两个 CTE，因此它们需要唯一的标识符：
regional_sales和top_regions：WITH -- query sale amounts for each region regional_sales AS (SELECT ... ), top_regions AS ( SELECT ... ) ) -
column-aliases - 结果集列别名的逗号分隔列表。别名列表必须映射到 CTE 查询中的所有列表达式。如果忽略，则只有查询中使用的名称才能引用结果集列。
在以下示例中，
revenueCTE 指定了两个列别名：vkey和total_revenue。这两个别名分别映射到列vendor_key和聚合表达式SUM(total_order_cost)。主要查询将引用这些别名：WITH revenue ( vkey, total_revenue ) AS ( SELECT vendor_key, SUM(total_order_cost) FROM store.store_orders_fact GROUP BY vendor_key ORDER BY vendor_key) SELECT v.vendor_name, v.vendor_address, v.vendor_city, r.total_revenue FROM vendor_dimension v JOIN revenue r ON v.vendor_key = r.vkey WHERE r.total_revenue = (SELECT MAX(total_revenue) FROM revenue ) ORDER BY vendor_name; -
subordinate‑WITH‑clause - 嵌套在当前 WITH 子句中的 WITH 子句。此 WITH 子句中的 CTE 只能引用同一子句中的 CTE，或者父 WITH 子句和子 WITH 子句中的 CTE。
重要
主要查询只能引用最外层 WITH 子句中的 CTE。它不能引用任何嵌套的 WITH 子句中的 CTE。 -
query-expression - 给定 CTE 的查询。
限制
WITH 子句仅支持 SELECT 和 INSERT 语句。它们不支持 UPDATE 或 DELETE 语句。
递归查询
包含 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 中设置。
限制
存在以下限制：
-
非递归项的 SELECT 列表不能包含通配符
*（星号）或函数 MATCH_COLUMNS。 -
递归项只能引用目标 CTE 一次。
-
递归引用不能出现在外联接中。
-
递归引用不能出现在子查询中。
-
WITH 子句不支持 UNION 选项 ORDER BY、LIMIT 和 OFFSET。
示例
注意
有关显示递归 WITH 子句用法的示例，请参阅 WITH 子句递归。包含一个 CTE 的单个 WITH 子句
下面的 SQL 定义了一个包含单个 CTE 的 WITH 子句 revenue，该子句将聚合表 store.store_orders_fact 中的数据。主要查询将引用两次 WITH 子句结果集：在其 JOIN 子句和谓词中：
-- define WITH clause
WITH revenue ( vkey, total_revenue ) AS (
SELECT vendor_key, SUM(total_order_cost)
FROM store.store_orders_fact
GROUP BY vendor_key ORDER BY 1)
-- End WITH clause
-- primary query
SELECT v.vendor_name, v.vendor_address, v.vendor_city, r.total_revenue
FROM vendor_dimension v JOIN revenue r ON v.vendor_key = r.vkey
WHERE r.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)
包含多个 CTE 的单个 WITH 子句
在以下示例中，WITH 子句包含两个 CTE：
-
regional_sales每个地区的销售总量 -
top_regions使用regional_sales的结果集确定销售总量最高的三个地区：
主要查询在 top_regions 结果集中按地区和部门聚合销量：
WITH
-- query sale amounts for each region
regional_sales (region, total_sales) AS (
SELECT sd.store_region, SUM(of.total_order_cost) AS total_sales
FROM store.store_dimension sd JOIN store.store_orders_fact of ON sd.store_key = of.store_key
GROUP BY store_region ),
-- query previous result set
top_regions AS (
SELECT region, total_sales
FROM regional_sales ORDER BY total_sales DESC LIMIT 3
)
-- primary query
-- aggregate sales in top_regions result set
SELECT sd.store_region AS region, pd.department_description AS department, SUM(of.total_order_cost) AS product_sales
FROM store.store_orders_fact of
JOIN store.store_dimension sd ON sd.store_key = of.store_key
JOIN public.product_dimension pd ON of.product_key = pd.product_key
WHERE sd.store_region IN (SELECT region FROM top_regions)
GROUP BY ROLLUP (region, department) ORDER BY region, product_sales DESC, GROUPING_ID();
region | department | product_sales
---------+----------------------------------+---------------
East | | 1716917786
East | Meat | 189837962
East | Produce | 170607880
East | Photography | 162271618
East | Frozen Goods | 141077867
East | Gifts | 137604397
East | Bakery | 136497842
East | Liquor | 130410463
East | Canned Goods | 128683257
East | Cleaning supplies | 118996326
East | Dairy | 118866901
East | Seafood | 109986665
East | Medical | 100404891
East | Pharmacy | 71671717
MidWest | | 1287550770
MidWest | Meat | 141446607
MidWest | Produce | 125156100
MidWest | Photography | 122666753
MidWest | Frozen Goods | 105893534
MidWest | Gifts | 103088595
MidWest | Bakery | 102844467
MidWest | Canned Goods | 97647270
MidWest | Liquor | 97306898
MidWest | Cleaning supplies | 90775242
MidWest | Dairy | 89065443
MidWest | Seafood | 82541528
MidWest | Medical | 76674814
MidWest | Pharmacy | 52443519
West | | 2159765937
West | Meat | 235841506
West | Produce | 215277204
West | Photography | 205949467
West | Frozen Goods | 178311593
West | Bakery | 172824555
West | Gifts | 172134780
West | Liquor | 164798022
West | Canned Goods | 163330813
West | Cleaning supplies | 148776443
West | Dairy | 145244575
West | Seafood | 139464407
West | Medical | 126184049
West | Pharmacy | 91628523
| | 5164234493
(43 rows)
包含 WITH 子句的 INSERT 语句
以下 SQL 使用 WITH 子句将 JOIN 查询中的数据插入到表 total_store_sales 中：
CREATE TABLE total_store_sales (store_key int, region VARCHAR(20), store_sales numeric (12,2));
INSERT INTO total_store_sales
WITH store_sales AS (
SELECT sd.store_key, sd.store_region::VARCHAR(20), SUM (of.total_order_cost)
FROM store.store_dimension sd JOIN store.store_orders_fact of ON sd.store_key = of.store_key
GROUP BY sd.store_region, sd.store_key ORDER BY sd.store_region, sd.store_key)
SELECT * FROM store_sales;
=> SELECT * FROM total_store_sales ORDER BY region, store_key;
store_key | region | store_sales
-----------+-----------+-------------
2 | East | 47668303.00
6 | East | 48136354.00
12 | East | 46673113.00
22 | East | 48711211.00
24 | East | 48603836.00
31 | East | 46836469.00
36 | East | 48461449.00
37 | East | 48018279.00
41 | East | 48713084.00
44 | East | 47808362.00
49 | East | 46990023.00
50 | East | 47643329.00
9 | MidWest | 46851087.00
15 | MidWest | 48787354.00
27 | MidWest | 48497620.00
29 | MidWest | 47639234.00
30 | MidWest | 49013483.00
38 | MidWest | 48856012.00
42 | MidWest | 47297912.00
45 | MidWest | 48544521.00
46 | MidWest | 48887255.00
4 | NorthWest | 47580215.00
39 | NorthWest | 47136892.00
47 | NorthWest | 48477574.00
8 | South | 48131455.00
13 | South | 47605422.00
17 | South | 46054367.00
...
(50 rows)