人大金仓分析型数据库WITH查询

目录

前言

WITH语句查询

WITH语句修改数据

---

前言

        WITH子句提供一个更大的SELECT查询集，使用子查询或执行数据修改操作。可以在INSERT, UPDATE或 DELETE 命令中使用WITH子句。

---

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子句编写查询，但需要两级嵌套子选择。相比较来说，WITH子句比较容易。递归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 ;

         对于某些查询，使用 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;

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

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

WITH语句修改数据

        对于SELECT命令，可以在 WITH子句中使用数据修改命令 INSERT, UPDATE, 或DELETE。允许在同一查询中执行几个不同的操作。WITH子句中的数据修改语句只执行一次，并且始终执行到完成，这与主查询是否读取所有（或任何）输出无关。这与在WITH子句中使用 SELECT 时的规则不同，只有在主查询要求输出时，才会继续执行 SELECT 。

        WITH子句中的 DELETE从产品中删除指定的行，并通过其返回子句返回其内容：

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

         WITH子句中的数据修改语句必须有RETURNING 子句，如前一个示例所示。它是RETURNING 子句的输出，而不是数据修改语句的目标表，形成了可以被查询的其余部分引用的临时表。如果 WITH中的数据修改语句缺少RETURNING子句，则返回错误。

        WITH子句中的子语句与主查询同时执行。因此，在WITH中使用数据修改语句时，将在快照中执行该语句。语句的效果在目标表上不可见。RETURNING的数据是在不同子语句和主查询之间传递更改的唯一方法。在本例中，外部SELECT返回WITH子句中UPDATE 操作之前的原始价格：

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

         不支持在单个语句中更新同一行两次。这种语句的效果是不可预测的。只有一个修改发了，但是无法预测哪一个发生修改。在WITH子句中用作数据修改语句目标的任何表都不能有条件规则、也不能有条件规则或扩展为多个语句的 INSTEAD规则。