PostgreSQL技术问答02 - CTE

站长

2024年06月11日 14:26 · 阅读数 65

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是CTE。

什么是CTE

CTE是英文Common Table Express，即公共表表达式的缩写。这个名称听起来过于专业和学术化，其实理解起来非常简单，就是可以给一个子查询进行命名，然后在后续的查询语句中向一个数据表(记录集)一样引用和使用。

在PG中，CTE也被称为With Query(With 查询)，因为对于CTE的实现，PostgreSQL中使用了一个专门关键字-With来结合查询来创建CTE的记录集。我们可以可以查看PostgreSQL技术文档的Select语句章节，它的一般范式是：


[ WITH [ RECURSIVE ] with_query [, ...] ]
and with_query is:
with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ]
( select | values | insert | update | delete )
[ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
[ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] 
USING cycle_path_col_name ]
TABLE [ ONLY ] table_name [ * ]

CTE使用With作为声明，然后需要使用或者多个一个with_query子句。这个子句的特性和结构可能比较复杂，我们后面会结合实例并选择重点进行说明。这里先说明几个结构和使用上的要点：

With和As关键字是不能缺少的
CTE的名称(with_query_name)也是不能缺失，并且在整个SQL语句中是不能重复的
CTE内容可以来自select、values、insert、updata、delete等多种形式，应该是结果记录集的形式就可以

为什么要用CTE？

笔者理解，在SQL中，使用CTE，应该更多的是方便开发和软件工程等方面的考虑。虽然，逻辑上SQL可以使用子查询进行复杂查询的嵌套。但实际开发工作中，大型复杂查询的嵌套数量太多，会造成阅读、理解和维护的困难。

CTE可以帮助开发者使用更加清晰的思维和逻辑，来对复杂查询进行拆解和简化，并且有逻辑和结构的分步骤分层次的组织数据，在在某种程度上改善了SQL没有结构和流程控制能力的缺陷。然后，生成和创建的SQL语句，就比较方便的可以进行逐步的调试，也方便后续的使用和维护。

PG提供的CTE还提供了很多高级功能，比如可以通过values构造数据，实体化、递归操作等等，这些都大大扩展了CTE的使用场景和适应性。

什么是values构造

CTE操作涉及到构造记录集。除了常规的使用select进行查询来构造之外，当然也包括使用update、delete配合returning子句返回修改相关数据的记录集。除此之外，还有一种通过外部输入的数据来进行记录集构造的方式，这时就需要使用Values语句来实现。

在PostgreSQL中，对一单条数据记录，也可以直接使用select数值的方式，来构造一条记录，对于需要同时通过输入外部数据，构造多条记录构成的记录集，也可以使用select union all的方式，但更好的方式，应该就是使用Values语句。下面的示例应该能够方便我们理解这一点：

// 单条记录
select 1 as id, 20 as ivalue;

// Union all
select 1 as id, 20 as ivalue
union all
select 2 , 20;

// 使用select values
 select * from  (values 
(1,20),
(2,30)
) as V ;

// CTE
With V(id,ivalue) as (values
(1,20),
(2,30)
) select * from V;

举几个例子

技术文档中的CTE语法，其实过于复杂了，日常工作中都不会用到这种程度。它本来主要就是为了简化思维而存在的，下面举几个简单的例子来进行说明：

// 简单CTE
with O1 as (select id, ivalue from udata where id > 1) 
select * from O1;

// CTE values 
with O2(id, ivalue) as (values (20, 35), (21, 36)) 
select * from O2;

// 多个CTE关联
with 
O1 as (select id, ivalue from udata where id > 1),
O2(id, ivalue) as (values (20, 35), (21, 36))
select * from O1 join O2 on O1.id = O2.id;

// CTE 嵌套
with 
O1 as (select id, ivalue from udata ),
O2(id, ivalue) as (values (20, 35), (21, 36)),
O3 as (select id, ivalue from O1 where id > 1)
select * from O2 join O3 on O3.id = O2.id;

// 数据修改 CTE
with
I as (insert into udata(id, ivalue) select id+100, ivalue+200 from udata returing 1)
select count(1) from I;

这几个例子展示了CTE的一些特性和使用方式，应该能够包含到大多数日常使用的场景了，包括：

本质而言，CTE是单一SQL语句，但可以带有结构和逻辑顺序，也是单一事务
可以使用一个查询来构造CTE
也可以使用values，引用外部数据来构造CTE
可以构造多个CTE(使用逗号分隔CTE语句)，并在后续引用
CTE可以嵌套，就是在后续的CTE中，使用前面的CTE
构成CTE，可以使用任意执行结果为记录集的语句，包括Select、Insert、Update、Delete和Values等，其中IUD需要使用returning子句

由此我们可以看到，CTE的使用是非常灵活的，开发者可以根据业务和理解需求，构建CTE结构层次，更像是一种编程方式。

什么叫CTE递归

CTE作为一种记录集的生成模式，其实是支持递归操作的。还是先举例说明：

WITH RECURSIVE FB(n, a, b) AS (
  SELECT 0, 0, 1   -- 基础查询
  UNION ALL
  SELECT n+1, b, a+b  FROM FB WHERE n < 10 -- 递归查询 + 终止条件
) SELECT * FROM FB;

上面的例子来自mistral，笔者觉得这个例子举得比官方技术文档要好啊？

递归，是计算机软件中一个有点抽象而神奇的概念，它一般指一个程序方法，可以在执行过程中调用自己的一种操作。有点自己抓住头发提起自己的感觉？但实际上没那么复杂，递归调用自己，就像俄罗斯套娃一样，但每次调用需要传递的参数是不一样的，只需要设置一个终止条件，就可以控制递归结束，然后在从结束的那个地方，一层层的返回结果，直到最外面那一层，就能够返回最终的结果。

所以，笔者理解，递归的要素其实只有三个，第一就是自己调用自己；第二是每次调用参数需要并且必须改变；第三就是有一个终止条件，通常也是通过参数检查实现。另外，本质上而言，递归就是一种循环，只不过它将循环结束机制放在内部，显得更为巧妙和简洁而已。

有了上述的概念和认知，我们就可以比较容易的理解CTE中对于递归操作的设计和实现了。

1 它使用一个Recuresive关键字，来声明当前的CTE是一个递归方式构造的记录集

2 需要一个初始记录，并且使用Union ALL的方式来对记录进行扩展

3 扩展部分，会实现对自身CTE的调用

4 本例子中，是对自己执行一个Select 查询，但查询参数是变化的(基于当前和上一步传入)，并同时进行终止条件的检查

上面的例子我们可以看到，在单一的SQL语句中，由于循环和判断机制的缺乏，确实可以使用递归巧妙的实现类似于循环的机制。但如果理解递归操作的底层实现原理，就会知晓，作为一种函数调用，它通常是通过调用栈来实现的，很深的递归栈，会占用大量的内存资源，因为在递归结束之前，相关的指令和数据引用都不会释放。所以这种简洁和巧妙，是有一些工程上的代价的，在实际运用中，需要根据实际情况进行考虑和权衡，谨慎使用。

在实践中，CTE递归的应用场景，主要包括序列数据、生成数据和处理层次化数据等。上面的斐波那契数列的案例，确实也比较学术化，方便读者理解。但实际的业务和应用中，最常见的场景其实是层次化数据的处理，比如行政区划，公司组织架构等等。

我们再来看一个更加实用和业务化的，层次数据的示例：

// gcode 表
   id   | pid  |  name  
--------+------+--------
     11 |    0 | 北京市
 110108 |   11 | 海淀区
     51 |    0 | 四川省
   5101 |   51 | 成都市
 510104 | 5101 | 锦江区
 510107 | 5101 | 青羊区
   5103 |   51 | 自贡市
   5107 |   51 | 绵阳市
 510704 | 5107 | 游仙区


// 递归查询四川省

With RECURSIVE SC as (
select id, name from gcode where id = 51
union all 
select G.id, G.name from gcode G join SC on SC.id = G.pid   
)
select * from SC ;

// 正确的查询结果
   id   |  name  
--------+--------
     51 | 四川省
   5101 | 成都市
   5103 | 自贡市
   5107 | 绵阳市
 510104 | 锦江区
 510107 | 青羊区
 510704 | 游仙区

笔者在编写和测试这个查询中，遇到了两个坑：

Recursive不能引用另一个CTE

本来笔者的构想，是使用CTE来构造这个原始数据表，但不知道为什么，不能在后续的递归CTE声明中，引用这个数据，最后无奈只能创建一个实际的数据表。看来Recursive还是有一些限制的，但逻辑上好像讲不通啊，说明CTE并不能完全模拟一个表结构。

要特别注意递归查询条件的构造方式

笔者编写这个CTE犯了一个小错误，就是将递归查询的主表写反了（请仔细观察示例中正确的方式）：

select SC.id, SC.name from SC join gcode G on SC.id = G.pid

这也是一个挺奇怪的事情，一般情况下，join使用那个作为主表，一般觉得差别是不大的，但在Recursive CTE中，必须要使用引用表作为主表来关联递归CTE。如果相反，其实也是能够执行的，但会陷入一个死循环！

什么叫CTE的实体化(Materialization)

笔者在查阅CTE的官方技术文档过程中，发现了有这么一个段落： Common Table Expression Materialization (CTE实体化)。简单的理解，就是定义CTE后，执行的查询，其结果会被缓存下列，被后续的引用所使用。如果有多次引用，则作为缓存，它可以提高查询效率。

这个操作在逻辑上没有问题，但在实际执行前的优化阶段，却可能导致不期望的处理方式，有的是优化并CTE缓存，有的则是需要和父查询一起考虑优化评估。所以PG提供了一个选项，可以在定义CTE时，指定MATERIALIZED来强制单独计算；或者指定 NOT MATERIALIZED 来强制将其合并到父查询中。两者可能会导致不同的查询成本和代价，需要根据现实情况(如数据构成)来进行选择。

CTE和数据修改结合有哪些使用场景和问题

我们前面已经提到，CTE可以和数据修改语句结合起来使用。它一般涉及两者情况，在CTE内部使用数据修改和在CTE外部使用。我们通过一些示例来帮助对其进行理解，也能够同时展示使用CTE进行数据修改的一些典型的应用场景。

数据移动

比如下面是一个使用CTE辅助进行数据移动的例子：

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

此例子中，先是使用Delete语句，结合查询条件，删除原表中的数据，并将删除的记录插入到另一个表中，达到了逻辑上“移动数据”的效果。由于是单一的语句，它们肯定是作为一个事务来进行处理的。

在CTE内部使用数据修改，要点在于数据修改语句中，需要包括Returing子句，来返回数据修改的结果，通常会用作后续操作。

关联操作

上面的例子中，后续操作使用了前面操作的结果，但实际上CTE不同的部分，可以完全没有任何数据上的关联（但可能有业务逻辑上的关联），这时就可以不同Returning，只需要前面的语句能够正确执行就可以了。比如下面这个用例：

WITH t AS ( DELETE FROM foo where flag=1) DELETE FROM bar where flag = 1;

这个语句分别从两个表中删除数据，但使用CTE将它们从逻辑上联系了起来关联执行。需要注意，这个语句的执行结果，是第二个删除操作涉及的记录数量。

操作结果

考察下面两个语句：

// 返回操作前数据
WITH t AS (
    UPDATE products SET price = price * 1.05 RETURNING *
) SELECT * FROM products

// 返回操作影响的数据
WITH t AS (
    UPDATE products SET price = price * 1.05 RETURNING *
) SELECT * FROM t;

这两个语句可以完成不同的业务要求。前者返回修改前的数据集；后者返回修改涉及到的记录集。

递归操作

原则上递归的CTE是不能在内容中使用数据修改语句的。但在某些情况下，可以在数据修改语句中使用递归查询CTE的结果，这个在逻辑上不冲突。

重复操作

CTE修改数据是有一些限制的，特别是对于反复操作的情况。例如单个语句中不能两次更新相同的行，一般只有其中一个修改生效，但是很难或者无法可靠地预测哪一个生效。在相同的语句中删除已经更新过的行的情况类似。所以，通常应该避免尝试在单个语句中两次修改单个行，特别是避免编写可能影响到主语句或兄弟子语句所改动的行的CTE，这些语句的结果是不好预测的。

小结

本文探讨了PostgreSQL中一个非常重要的概念和技术-公共表表达式(CTE)。它是使用With子句定义的查询或者操作结果的命名记录集，并且在SQL语句中可以为后续子句所使用。CTE可以简化查询语句编写，构造操作流程，使SQL程序更加容易维护和移植。

文中还讨论了CTE的一般使用和扩展应用，包括递归查询，数据更改操作，并讨论了一些典型的使用场景和用法。

转载自:https://juejin.cn/post/7372041743699378230