合 PG中的CTE(with语句、公共表表达式)
简介
WITH
提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE(Common Table Expressions),它们可以被看成是定义只在一个查询中存在的临时表。在WITH
子句中的每一个辅助语句可以是一个SELECT
、INSERT
、UPDATE
或DELETE
,并且WITH
子句本身也可以被附加到一个主语句,主语句也可以是SELECT
、INSERT
、UPDATE
或DELETE
。
使用PostgreSQL CTE(common table expressions)可以简化复杂查询,PostgreSQL CTE(common table expressions) 是临时结果,可以在其他SQL中引用,如SELECT, INSERT, UPDATE 和 DELETE,其仅存在于查询执行期间。
在 PostgreSQL 中,WITH 子句提供了一种编写辅助语句的方法,以便在更大的查询中使用。
WITH 子句有助于将复杂的大型查询分解为更简单的表单,便于阅读。这些语句通常称为通用表表达式(Common Table Express, CTE),也可以当做一个为查询而存在的临时表。
WITH 子句是在多次执行子查询时特别有用,允许我们在查询中通过它的名称(可能是多次)引用它。
WITH 子句在使用前必须先定义。
下面是创建CTE的语法:
1 2 3 4 5 | WITH cte_name (column_list) AS ( CTE_query_definition ) statement; |
首先,指定CTE的名称,接着是可选的列字段列表
其次,在with子句体内,指定查询作为返回结果集,如果没有显示指定列字段列表,则CTE_query_definition 的select 字段列表将作为CTE的字段列表。
第三,在其他SQL语句中可以向使用表或视图一样使用CTE,语句包括SELECT, INSERT, UPDATE 和 DELETE。
CTE一般用于简化复杂join和子查询。
WITH
中SELECT
的基本价值是将复杂的查询分解称为简单的部分。一个例子:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
,其中regional_sales
的输出用在top_regions
中而top_regions
的输出用在主SELECT
查询。这个例子可以不用WITH
来书写,但是我们必须要用两层嵌套的子SELECT
。使用这种方法要更简单些。
可选的RECURSIVE
修饰符将WITH
从单纯的句法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE
,一个WITH
查询可以引用它自己的输出。一个非常简单的例子是计算从1到100的整数合的查询:
1 2 3 4 5 6 | WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; |
一个递归WITH
查询的通常形式总是一个非递归项,然后是UNION
(或者UNION ALL
),再然后是一个递归项,其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被这样执行:
递归查询求值
- 计算非递归项。对
UNION
(但不对UNION ALL
),抛弃重复行。把所有剩余的行包括在递归查询的结果中,并且也把它们放在一个临时的工作表中。 - 只要工作表不为空,重复下列步骤:
- 计算递归项,用当前工作表的内容替换递归自引用。对
UNION
(不是UNION ALL
),抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中,并且也把它们放在一个临时的中间表中。 - 用中间表的内容替换工作表的内容,然后清空中间表。
- 计算递归项,用当前工作表的内容替换递归自引用。对
注意
严格来说,这个处理是迭代而不是递归,但是RECURSIVE
是SQL标准委员会选择的术语。
在上面的例子中,工作表在每一步只有一个行,并且它在连续的步骤中取值从1到100。在第100步,由于WHERE
子句导致没有输出,因此查询终止。
递归查询通常用于处理层次或者树状结构的数据。一个有用的例子是这个用于找到一个产品的直接或间接部件的查询,只要给定一个显示了直接包含关系的表:
1 2 3 4 5 6 7 8 9 10 | 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
的查询:
1 2 3 4 5 6 7 8 9 | 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
关系包含环,这个查询将会循环。因为我们要求一个“depth”输出,仅仅将UNION ALL
改为UNION
不会消除循环。反过来在我们顺着一个特定链接路径搜索时,我们需要识别我们是否再次到达了一个相同的行。我们可以项这个有循环倾向的查询增加两个列path
和cycle
:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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; |
除了阻止环,数组值对于它们自己的工作显示到达任何特定行的“path”也有用。
在通常情况下如果需要检查多于一个域来识别一个环,请用行数组。例如,如果我们需要比较域f1
和f2
:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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; |