PostgreSQL中的CTE(公共表表达式)知多少？ - (中级)

  • 数据库杂记

    2023-07-18 06:58:12
  • 数据库开发技术
  • 原创


前言

PostgreSQL中的CTE: 公共表表达式是一种更好的临时表. 用于较大查询的辅助语句.  用于只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。   (ref:  http://postgres.cn/docs/14/queries-with.html)

这也是PostgreSQL的一个非常重要的功能。有很多商用关系数据库甚至还没有这个特色功能。

仔细再读了下这篇文档，感觉组织的不是很理想。试图重新组织一下，便于理解其中的使用方法。

CTE的主要好处什么？有了它：

  • 提高复杂查询的可读性。CTE 可以将复杂查询分解成简单易读的小块语句，结构清晰。

  • 实现类似于递归式的查询，对于某些层次结构或者图状树状结构数据的查询非常有帮助。(做了几个例子，简单递增数列，fibonacci数列，阶乘)

实作与实例

CTE的基本语法

文档来源： http://postgres.cn/docs/14/sql-select.html

1[ WITH [ RECURSIVE ] with_query [, ...] ]
2with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
3        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
4        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]

WITH列表中的所有查询都会被计算。这些查询实际充当了在FROM列表中可以引用的临时表。在 FROM中被引用多次的WITH查 询只会被计算一次，除非另有说明，否则NOT MATERIALIZED。

再简化点就是：

1WITH cte_name (col1, col2, ...) AS (
2    cte_query_definition
3)
4sql_statement;

  • WITH表示定义 CTE，因此 CTE 也称为WITH查询；

  • cte_name 指定了 CTE 的名称，后面是可选的字段名；

  • 括号内是 CTE 的内容，可以是SELECT语句，也可以是INSERT、UPDATE、DELETE语句；

  • sql_statement 是主查询语句，可以引用前面定义的 CTE。该语句同样可以是SELECT、INSERT、UPDATE或者DELETE。

准备数据

 1CREATE TABLE employees (empno int primary key, 
 2    depid int,
 3    gender char(1),
 4    age int,
 5    city varchar(32),
 6    manager int,
 7    salary int
 8);
 9
10CREATE TABLE departments (depid int primary key, depname varchar(64));
11
12INSERT INTO departments VALUES
13(1, 'global'), (2, 'develop'), (3, 'personnel'), (4, 'sales');
14
15INSERT INTO employees VALUES 
16(0, 1, 'm', 45, 'Beijing', 0, 55080), 
17(1, 2, 'm', 38, 'Beijing', 0, 27000), 
18(2, 2, 'm', 25, 'Beijing', 1, 10000), 
19(3, 2, 'f', 26, 'Beijing', 1, 11000), 
20(4, 2, 'f', 29, 'Beijing', 1, 13000), 
21(5, 2, 'm', 30, 'Shanghai', 1, 22000), 
22(6, 3, 'm', 30, 'Beijing', 0, 22000), 
23(7, 3, 'f', 25, 'Nanjing', 6, 11500), 
24(8, 4, 'm', 40, 'Beijing', 0, 35000), 
25(9, 4, 'f', 31, 'Hangzhou', 8, 20050), 
26(10, 4, 'm', 32, 'Shenzhen', 8, 20300),
27(11, 2, 'f', 35, 'Guangzhou', 1, 21300),
28(12, 4, 'm', 30, 'Beijing', 8, 20050);

WITH中的SELECT查询

现在有这么一个查询，就是要查询每个部门以及它的平均工资。注意，要求输出的是部门的名称，而不是部门ID。

 1WITH dept_avg(depid, avg_salary) AS (
 2  SELECT depid,
 3         AVG(salary) AS avg_salary
 4    FROM employees
 5   GROUP BY depid
 6)
 7SELECT d.depname,
 8       da.avg_salary
 9  FROM departments d
10  JOIN dept_avg da
11    ON (d.depid = da.depid)
12 ORDER BY d.depname;
13
14   depname  |     avg_salary
15-----------+--------------------
16 develop   | 17383.333333333333
17 global    | 55080.000000000000
18 personnel | 16750.000000000000
19 sales     | 23850.000000000000
20(4 rows)

在这里，我们可以首先从employees表里头直接使用一个CTE，将所有部门以及该部门的平次工资查询得到，将此子查询命名为：dept_avg(depid, avg_salary)。

然后，将上述CTE与表：departments进行表连接，从而得到每一个depid对应的部门名称。这样的查询结构非常清晰。当然，还有其它方式也可以实现相同的功能，CTE的使用让代码更具有可读性。

从上边的语法我们也可以看到，一个WITH关键字后边可以跟随多个with_query定义的CTE，后边定义的CTE可以引用前边定义的CTE，举例如下：

 1WITH s1(n) as (
 2SELECT 1
 3), s2(n) as (
 4SELECT n+1 FROM s1
 5), s3(n) as (
 6SELECT n+1 FROM s2
 7)
 8SELECT * FROM s1, s2, s3;
 9
10 n | n | n
11---+---+---
12 1 | 2 | 3
13(1 row)

看看这个例子，蛮有意思，CTE s2引用了CTE s1中的列n的值：1, 并加1，得到它的列n，而CTE s3又引用了CTE s2，并将其结果加1。这样下去，可以不断创建出新的CTE，层层引用。基于这种思想，可以伪造出类似于递归式的查询。

不过，PostgreSQL支持递归式的CTE。

递归CTE

递归 CTE 允许在它的定义中引用其自身，理论上来说可以实现非常复杂的计算功能，最常见的情况就是遍历层次的查询、树状结构的查询。可选的RECURSIVE修饰符将WITH从单纯的语法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出。

抽象出来的语法形式是这样子的：

1WITH RECURSIVE with_query AS(
2    cte_query_initial -- 初始化部分
3    UNION [ALL]
4    cte_query_iterative  -- 递归部分
5) SELECT * FROM with_query;

有这么个语法，我们可以很快构造出一些数列形式的结果输出：

先来个递增简单数列

 1-- 1, 2, 3, 4, ..., 8
 2WITH RECURSIVE t(n) AS (
 3  SELECT 1
 4  UNION ALL
 5  SELECT n+1 FROM t WHERE n < 8
 6)
 7SELECT n FROM t;
 8
 9 n
10---
11 1
12 2
13 3
14 4
15 5
16 6
17 7
18 8
19(8 rows)

来个Fibonacci数列试试：

 1-- fn指的就是f(n),  fn_1指的就是f(n+1)
 2WITH RECURSIVE fibonacci (n, fn, fn_1) AS (
 3  -- 初始值: n = 1, F(1) = 1, F(2) = 1
 4  SELECT 1::BIGINT AS n, 1::BIGINT AS fn, 1::BIGINT AS fn_1
 5  UNION ALL
 6  -- 形成递归部分: F(n+2) = F(n) + F(n+1)
 7  SELECT n + 1, fn_1, fn + fn_1
 8  FROM fibonacci
 9  WHERE n < 10  -- 改变此值生成更多的序列值
10)
11SELECT n, fn AS fibonacci_number FROM fibonacci;
12
13 n  | fibonacci_number
14----+------------------
15  1 |                1
16  2 |                1
17  3 |                2
18  4 |                3
19  5 |                5
20  6 |                8
21  7 |               13
22  8 |               21
23  9 |               34
24 10 |               55
25(10 rows)

这个数列的CTE求解过程大概如下 ：

  • 先给出一行初始值n = 1时，F(1) = 1, F(2) = 1，也就是fn, fn_1,  有初始行的值：1, 1, 1

  • 第一次执行“递归”查询，判断 n = 2时，它小于10， 于是有了值：2，1,  1

  • 重复执行递归查询，直到n = 10终止，这时临时表应该有10条数据。

  • 执行主查询，取的是n, fn两列作为结果，n为行的序号。

如果上边的Fibanacci数列咱们会了，我们也可以试一下阶乘值数列。

阶乘值：

 1-- fn指的就是f(n)
 2WITH RECURSIVE factorial (n, fn) AS (
 3  -- 初始值: n = 1, F(1) = 1
 4  SELECT 1::BIGINT AS n, 1::BIGINT AS fn 
 5  UNION ALL
 6  -- 形成递归部分: F(n+1) = F(n) * (n+1)
 7  SELECT n + 1, fn * (n+1)
 8  FROM factorial
 9  WHERE n < 8  -- 改变此值生成更多的序列值
10)
11SELECT n, fn AS factorial_number FROM factorial;
12
13 n | factorial_number
14---+------------------
15 1 |                1
16 2 |                2
17 3 |                6
18 4 |               24
19 5 |              120
20 6 |              720
21 7 |             5040
22 8 |            40320
23(8 rows)

构造递归CTE最基本的要点就是要设计好初始值部分和递归部分的表达式形式，最后通过WHERE条件来限制生成序列值的个数。

以上只是使用递归CTE来牛刀小试一下数学中常见有有规律的数列值的生成。

递归CTE实例：

表数据：

 1CREATE TABLE testarea(id int, name varchar(32), parentid int);
 2INSERT INTO  testarea VALUES
 3(1, '中国', 0),
 4(2, '辽宁', 1),
 5(3, '山东', 1),
 6(4, '沈阳', 2),
 7(5, '大连', 2),
 8(6, '济南', 3),
 9(7, '和平区', 4),
10(8, '沈河区', 4),
11(9, '北京', 1),
12(10, '海淀区', 9),
13(11, '朝阳区', 9),
14(12, '苏家坨', 10);

我们现在就想输出，给定一个地点的id，把它的完整的地区名称输出来，比如id = 12的时候，希望输出的结果是：

"北京 海淀区 苏家坨"

要解决这个问题，我们直观感觉，就是要借助于递归CTE。

一种解法：

 1WITH RECURSIVE r AS (
 2    SELECT * FROM testarea WHERE id = 12 
 3    UNION ALL
 4    SELECT a.* FROM testarea a, r WHERE a.id = r.parentid
 5)
 6SELECT string_agg(name, ' ') FROM (SELECT * FROM r ORDER BY id) t;
 7       string_agg
 8-------------------------
 9 中国 北京 海淀区 苏家坨
10(1 row)

另一种解法：

 1WITH RECURSIVE area_path (id, area_name, path) AS
 2(
 3  SELECT id, name, name::VARCHAR(256) AS path
 4    FROM testarea
 5   WHERE parentid = 0
 6   UNION ALL
 7  SELECT e.id, e.name, CONCAT(ep.path, ' ', e.name)::VARCHAR(256)
 8    FROM area_path ep
 9    JOIN testarea e ON ep.id = e.parentid
10)
11SELECT * FROM area_path ORDER BY id;
12
13 id | area_name |          path
14----+-----------+-------------------------
15  1 | 中国      | 中国
16  2 | 辽宁      | 中国 辽宁
17  3 | 山东      | 中国 山东
18  4 | 沈阳      | 中国 辽宁 沈阳
19  5 | 大连      | 中国 辽宁 大连
20  6 | 济南      | 中国 山东 济南
21  7 | 和平区    | 中国 辽宁 沈阳 和平区
22  8 | 沈河区    | 中国 辽宁 沈阳 沈河区
23  9 | 北京      | 中国 北京
24 10 | 海淀区    | 中国 北京 海淀区
25 11 | 朝阳区    | 中国 北京 朝阳区
26 12 | 苏家坨    | 中国 北京 海淀区 苏家坨
27(12 rows)

初始值：从parentid=0的那行起，得到 area_name,  path的初始值： (1, 中国，中国)

递归部分：利用前值的path，拼接新行的name。直到条件结束。

可能这道应用还有很多种别的解法，有兴趣可以多琢磨下。

WITH中CTE带有DML语句

除了SELECT语句之外，INSERT、UPDATE或者DELETE语句也可以与 CTE 一起使用。我们可以在 CTE 中使用 DML 语句，也可以将 CTE 用于 DML 语句。

如果在 CTE 中使用 DML 语句，我们可以将CUD影响的结果作为一个临时表，然后在其他语句中使用。

我们接着用前边的表：employees来展开。

跟踪DELETE示例

 1-- 创建一个历史表：
 2create table employees_history (like employees including all);
 3-- 或者用更易读的形式：create table employees_history as select * from employees where false;
 4
 5with deletes as (
 6  delete from employees
 7   where empno = 12
 8   returning *
 9)
10insert into employees_history
11select * from deletes;
12
13select * from employees_history;
14 empno | depid | gender | age |  city   | manager | salary
15-------+-------+--------+-----+---------+---------+--------
16    12 |     4 | m      |  30 | Beijing |       8 |  20050

上边是记录了CTE中delete的那些记录的结果。先是建了一张历史表（两种方法都可以）。然后deletes 这个CTE记录并返回所有empno=12的记录, 最后insert操作将这些个记录插入到历史表当中。

跟踪INSERT示例

我们试着将上条记录添加到原表：

1with inserts as (
2  insert into employees
3  values (12, 4, 'm', 30, 'Beijing', 8, 20050)
4  returning *
5)
6insert into employees_history
7select * from inserts;

这时，我们再查询employees_history，会有两条记录，一条是前边记录delete的，另一条是记录现在insert的。

1select * from employees_history;
2 empno | depid | gender | age |  city   | manager | salary
3-------+-------+--------+-----+---------+---------+--------
4    12 |     4 | m      |  30 | Beijing |       8 |  20050
5    12 |     4 | m      |  30 | Beijing |       8 |  20050
6(2 rows)

跟踪UPDATE示例:

CTE 中的UPDATE语句有些不同，因为更新的数据分为更新之前的状态和更新之后的状态。

 1delete from employees_history; -- 清除历史数据
 2
 3with updates as (
 4  update employees
 5     set salary = salary + 1000
 6   where empno = 12
 7   returning *
 8)
 9insert into employees_history
10select * from employees where empno = 12;
11
12select empno, salary from employees_history;
13 empno | salary
14-------+--------
15    12 |  20050
16(1 row)
17
18select empno, salary from employees where empno=12;
19 empno | salary
20-------+--------
21    12 |  21050
22(1 row)

你会发现，employees_history这里的empno=12，对应的salary还是原来的值20050，而不是更新以后的值。

什么原因？这是因为CTE中的update所在的SQL语句整个是一个事务，主查询中的employees使用的是修改之前的状态。这一点需要注意。

要想跟踪修改后的值，直接使用updates中的值就好。

 1delete from employees_history; -- 清除历史数据
 2
 3with updates as (
 4  update employees
 5     set salary = salary + 1000
 6   where empno = 12
 7   returning *
 8)
 9insert into employees_history
10select * from updates;
11
12select empno, salary from employees_history;
13
14 empno | salary
15-------+--------
16    12 |  22050
17(1 row)

小结：

CTE是PostgreSQL数据库的一个很重要的功能。在实际的开发应用当中，如果使用恰当，可以收到意想不到的效果。当然，还有一些插件，如内置的tablefunc(话说，PG最不缺的就是插件），能够支持connectby函数，直接支持层次树结构的描述。有兴趣的话可以自行摸索，后续如果有时间，我也尝试着列一些典型的用法在里边。

总之页言，CTE的用法是迈向中高级SQL用法的一条路子。多实践和多应用，非常有效。



请使用浏览器的分享功能分享到微信等