WITH 查询(公共表表达式)
WITH
子句提供了一种在主查询中编写辅助语句的方式。这些语句通常被称为公共表表达式(Common Table Expressions,简称 CTE),可以理解为只在当前查询中临时存在的表。
在 Apache Cloudberry 中使用 WITH
子句时有以下限制:
- 如果
WITH
子句出现在一个SELECT
命令中,则该子句中最多只能包含一个用于修改表数据的命令(如INSERT
、UPDATE
或DELETE
)。 - 如果
WITH
子句出现在一个修改数据的命令中(如INSERT
、UPDATE
或DELETE
),那么该子句只能包含SELECT
命令,不能再嵌套其他数据修改语句。
在 Apache Cloudberry 中,WITH
子句默认启用了 RECURSIVE
关键字。可以通过将服务器配置参数 gp_recursive_cte
设为 false
来禁用递归功能。
WITH 子句中的 SELECT 查询
CTE 的常见用途之一是将复杂查询拆分为多个更简单的部分。本节示例展示了如何在 SELECT
命令中使用 WITH
子句。这些示例中的 WITH
子句也可以用于 INSERT
、UPDATE
或 DELETE
语句中。
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
和 top_regions
。其中 top_regions
使用了 regional_sales
的结果,而主查询又使用了 top_regions
。虽然这个查询也可以不用 WITH
来写,但那样就需要使用两层嵌套的子查询。
如 果指定了可选的 RECURSIVE
关键字,WITH
子句可以实现标准 SQL 中无法实现的递归操作。启用递归后,一个 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
查询的一般结构是:一个非递归部分,后接一个 UNION