7.8. WITH的查询（公用表表达式）

WITH提供了一种在更大的SELECT查询中编写子查询的方式。 这个通常称为公共表表达式或CTEs的子查询可以认为是定义存在于查询中的临时表。 这个特性的一个应用是用于分解复杂查询为简单的部分。下面是一个例子：

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来编写，但是我们必须需要两个隔离的SELECT嵌套语句。该方法比其它方法更容易理解。

可选的RECURSIVE修饰符从仅有的语法便利性到一个完成事情的特性的改变。 使用RECURSIVE，一个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语句总是一个non-recursive term，然后是UNION (或UNION ALL)，那么一个recursive term， 它们只可以包含参考查询的输出。这样的一个查询执行如下：

在上面的例子中，在每一步中仅有一个工作表行，并且在后续的步骤中它的值将从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
    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去除掉前面输出重复的行可以实现这个。 然而，通常一个周期不涉及那些完全复制的输出行：检查一个或几个字段来查看是否存在事先达成的 相同点可能是必要的。 处理这种情况的标准方式是计算一个访问队列。 例如，请考虑下面的查询，使用link字段搜索一个表graph：

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;

如果link关系包含循环那么这个查询将会循环。 因为我们需要一个"深度"输出，仅改变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[g.id]，
          false
        FROM graph g
      UNION ALL
        SELECT g.id， g.link， g.data， sg.depth + 1，
          path || g.id，
          g.id = ANY(path)
        FROM graph g， search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

除了防止循环，该数组值通常是有用的，在它的右边作为代表采取的得到任何特定行的"路径"。

在一般情况下，使用一个行数组多于一个字段需要检查到一个循环。 例如，如果我们需要对比字段f1和f2：

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;

当你不能确定它们在设置了一个LIMIT父查询后是否会循环的时候，这是一个 对于测试查询有用的技巧。 例如，这个循环将在没有LIMIT的情况下循环：

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

它能工作是因为PostgreSQL的实现评估只有当许多实际上是通过父 查询获取的WITH查询行。 在实际的生产环境下不推荐使用该技巧，因为其它的系统可以以不同的方式工作。 同样，如果你使用外部查询将递归查询结果或将它们加入到别的表中分类，那么它通常是不工作的。

一个有用的WITH查询属性是每个父查询执行一次它们做一次评估，即使指定它们 不止一次地通过父查询或WITH查询。 所以，复杂的需要在多个地方放置的计算可以通过设置WITH查询来避免冗余工作。 另一个可能的应用是防止不必要的多副作用函数的评估。 然而，另一方面，比起普通的子查询，优化器是不能够避开父查询拆分为一个WITH查询的限制。 通常将WITH查询评估如上，没有行限制的父查询可能丢失。（但是，正如上面所说， 如果查询参考查询数量有限的行，评估可能会很早终止。）