文档章节

SQL优化(五) PostgreSQL (递归)CTE 通用表表达式

郭俊_Jason
 郭俊_Jason
发布于 2016/04/12 21:02
字数 2694
阅读 518
收藏 27

  原创文章,转载请务必将下面这段话置于文章开头处。
  本文转发自Jason’s Blog原文链接 http://www.jasongj.com/sql/cte/

CTE or WITH

WITH语句通常被称为通用表表达式(Common Table Expressions)或者CTEs。

WITH语句作为一个辅助语句依附于主语句,WITH语句和主语句都可以是SELECT,INSERT,UPDATE,DELETE中的任何一种语句。

例讲CTE

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辅助语句,regional_sales和top_regions。前者算出每个区域的总销售量,后者了查出所有销售量占所有地区总销售里10%以上的区域。主语句通过将这个CTEs及订单表关联,算出了顶级区域每件商品的销售量和销售额。

当然,本例也可以不使用CTEs而使用两层嵌套子查询来实现,但使用CTEs更简单,更清晰,可读性更强。

在WITH中使用数据修改语句

文章开头处提到,WITH中可以不仅可以使用SELECT语句,同时还能使用DELETE,UPDATE,INSERT语句。因此,可以使用WITH,在一条SQL语句中进行不同的操作,如下例所示。

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;

本例通过WITH中的DELETE语句从products表中删除了一个月的数据,并通过RETURNING子句将删除的数据集赋给moved_rows这一CTE,最后在主语句中通过INSERT将删除的商品插入products_log中。

如果WITH里面使用的不是SELECT语句,并且没有通过RETURNING子句返回结果集,则主查询中不可以引用该CTE,但主查询和WITH语句仍然可以继续执行。这种情况可以实现将多个不相关的语句放在一个SQL语句里,实现了在不显式使用事务的情况下保证WITH语句和主语句的事务性,如下例所示。

WITH d AS ( DELETE FROM foo ), u as ( UPDATE foo SET a = 1 WHERE b = 2 ) DELETE FROM bar;

WITH使用注意事项

  1. WITH中的数据修改语句会被执行一次,并且肯定会完全执行,无论主语句是否读取或者是否读取所有其输出。而WITH中的SELECT语句则只输出主语句中所需要记录数。
  2. WITH中使用多个子句时,这些子句和主语句会并行执行,所以当存在多个修改子语句修改相同的记录时,它们的结果不可预测。
  3. 所有的子句所能“看”到的数据集是一样的,所以它们看不到其它语句对目标数据集的影响。这也缓解了多子句执行顺序的不可预测性造成的影响。
  4. 如果在一条SQL语句中,更新同一记录多次,只有其中一条会生效,并且很难预测哪一个会生效。
  5. 如果在一条SQL语句中,同时更新和删除某条记录,则只有更新会生效。
  6. 目前,任何一个被数据修改CTE的表,不允许使用条件规则,和ALSO规则以及INSTEAD规则。

WITH RECURSIVE

WITH语句还可以通过增加RECURSIVE修饰符来引入它自己,从而实现递归

WITH RECURSIVE实例

WITH RECURSIVE一般用于处理逻辑上层次化或树状结构的数据,典型的使用场景是寻找直接及间接子结点。

定义下面这样的表,存储每个区域(省、市、区)的id,名字及上级区域的id

CREATE TABLE chinamap ( id INTEGER, pid INTEGER, name TEXT );

需要查出某个省,比如湖北省,管辖的所有市及市辖地区,可以通过WITH RECURSIVE来实现,如下

WITH RECURSIVE result AS
(
  SELECCT
    id,
    name
  FROM  chinamap
  WHERE id = 11
  UNION ALL SELECT origin.id, result.name || ' > ' || origin.name FROM result JOIN chinamap origin ON origin.pid = result.id ) SELECT id, name FROM result;

结果如下

id | name  -----+-------------------------- 11 | 湖北省 110 | 湖北省 > 武汉市 120 | 湖北省 > 孝感市 130 | 湖北省 > 宜昌市 140 | 湖北省 > 随州市 150 | 湖北省 > 仙桃市 160 | 湖北省 > 荆门市 170 | 湖北省 > 枝江市 180 | 湖北省 > 神农架市 111 | 湖北省 > 武汉市 > 武昌区 112 | 湖北省 > 武汉市 > 下城区 113 | 湖北省 > 武汉市 > 江岸区 114 | 湖北省 > 武汉市 > 江汉区 115 | 湖北省 > 武汉市 > 汉阳区 116 | 湖北省 > 武汉市 > 洪山区 117 | 湖北省 > 武汉市 > 青山区 (16 rows)

WITH RECURSIVE 执行过程

从上面的例子可以看出,WITH RECURSIVE语句包含了两个部分
- non-recursive term(非递归部分),即上例中的union all前面部分
- recursive term(递归部分),即上例中union all后面部分

执行步骤如下
1. 执行non-recursive term。(如果使用的是union而非union all,则需对结果去重)其结果作为recursive term中对result的引用,同时将这部分结果放入临时的working table中
2. 重复执行如下步骤,直到working table为空:用working table的内容替换递归的自引用,执行recursive term,(如果使用union而非union all,去除重复数据),并用该结果(如果使用union而非union all,则是去重后的结果)替换working table

以上面的query为例,来看看具体过程
1.执行

SELECT id, name FROM chinamap WHERE id = 11

结果集和working table为

11 | 湖北

2.执行

SELECT origin.id, result.name || ' > ' || origin.name FROM result JOIN chinamap origin ON origin.pid = result.id

结果集和working table为

110 | 湖北省 > 武汉市 120 | 湖北省 > 孝感市 130 | 湖北省 > 宜昌市 140 | 湖北省 > 随州市 150 | 湖北省 > 仙桃市 160 | 湖北省 > 荆门市 170 | 湖北省 > 枝江市 180 | 湖北省 > 神农架市

3.再次执行recursive query,结果集和working table为

111 | 湖北省 > 武汉市 > 武昌区 112 | 湖北省 > 武汉市 > 下城区 113 | 湖北省 > 武汉市 > 江岸区 114 | 湖北省 > 武汉市 > 江汉区 115 | 湖北省 > 武汉市 > 汉阳区 116 | 湖北省 > 武汉市 > 洪山区 117 | 湖北省 > 武汉市 > 青山区

4.继续执行recursive query,结果集和working table为空
5.结束递归,将前三个步骤的结果集合并,即得到最终的WITH RECURSIVE的结果集

严格来讲,这个过程实现上是一个迭代的过程而非递归,不过RECURSIVE这个关键词是SQL标准委员会定立的,所以PostgreSQL也延用了RECURSIVE这一关键词。

WITH RECURSIVE 防止死循环

从上一节中可以看到,决定是否继续迭代的working table是否为空,如果它永不为空,则该CTE将陷入无限循环中。
对于本身并不会形成循环引用的数据集,无段作特别处理。而对于本身可能形成循环引用的数据集,则须通过SQL处理。

一种方式是使用UNION而非UNION ALL,从而每次recursive term的计算结果都会将已经存在的数据清除后再存入working table,使得working table最终会为空,从而结束迭代。

然而,这种方法并不总是有效的,因为有时可能需要这些重复数据。同时UNION只能去除那些所有字段都完全一样的记录,而很有可能特定字段集相同的记录即应该被删除。此时可以通过数组(单字段)或者ROW(多字段)记录已经访问过的记录,从而实现去重的目的。

WITH RECURSIVE 求最短路径

定义无向有环图如下图所示
Non-directional cycle graph

定义如下表并存入每条边的权重

CREATE TABLE graph ( id char, neighbor char, value integer ); INSERT INTO graph VALUES('A', 'B', 3), ('A', 'C', 5), ('A', 'D', 4), ('B', 'E', 8), ('B', 'C', 4), ('E', 'C', 7), ('E','F', 10), ('C', 'D', 3), ('C', 'F', 6), ('F','D', 5);

计算思路如下:
- 因为是无向图,所以首先要将各条边的id和neighbor交换一次以方便后续计算。
- 利用WITH RECURSIVE算出所有可能的路径并计算其总权重。
- 因为该图有环,为避免无限循环,同时为了计算路径,将经过的结点存于数据中,当下一个结点已经在数据中时,说明该结点已被计算。
- 最终可算出所有可能的路径及其总权重

实现如下

WITH RECURSIVE edges AS ( SELECT id, neighbor, value FROM graph UNION ALL SELECT neighbor, id, value FROM graph ), all_path (id, neighbor, value, path, depth, cycle) AS ( SELECT id, neighbor, value, ARRAY[id], 1, 'f'::BOOLEAN FROM edges WHERE id = 'A' UNION ALL SELECT all_path.id, edges.neighbor, edges.value + all_path.value, all_path.path || ARRAY[edges.id], depth + 1, edges.id = ANY(all_path.path) FROM edges JOIN all_path ON all_path.neighbor = edges.id AND NOT cycle ), a_f AS ( SELECT rank() over(order by value) AS rank, path || neighbor AS path, value, depth FROM all_path WHERE neighbor = 'F' ) SELECT path, value, depth FROM a_f WHERE rank = 1;

WITH RECURSIVE 使用限制

  • 如果在recursive term中使用LEFT JOIN,自引用必须在“左”边
  • 如果在recursive term中使用RIGHT JOIN,自引用必须在“右”边
  • recursive term中不允许使用FULL JOIN
  • recursive term中不允许使用GROUP BY和HAVING
  • 不允许在recursive term的WHERE语句的子查询中使用CTE的名字
  • 不支持在recursive term中对CTE作aggregation
  • recursive term中不允许使用ORDER BY
  • LIMIT / OFFSET不允许在recursive term中使用
  • FOR UPDATE不可在recursive term中使用
  • recursive term中SELECT后面不允许出现引用CTE名字的子查询
  • 同时使用多个CTE表达式时,不允许多表达式之间互相访问(支持单向访问)
  • 在recursive term中不允许使用FOR UPDATE

CTE 优缺点

  • 可以使用递归 WITH RECURSIVE,从而实现其它方式无法实现或者不容易实现的查询
  • 当不需要将查询结果被其它独立查询共享时,它比视图更灵活也更轻量
  • CTE只会被计算一次,且可在主查询中多次使用
  • CTE可极大提高代码可读性及可维护性
  • CTE不支持将主查询中where后的限制条件push down到CTE中,而普通的子查询支持

SQL优化系列

© 著作权归作者所有

郭俊_Jason
粉丝 17
博文 11
码字总数 39197
作品 0
徐汇
私信 提问
加载中

评论(4)

郭俊_Jason
郭俊_Jason

引用来自“月亮上好冷”的评论

With语法中不能有除查询以外的操作!刚测试的,不知道你怎么可以的,怪哉!

引用来自“郭俊_Jason”的评论

你用的什么版本?如果是8,那就可能是版本问题

引用来自“月亮上好冷”的评论

10g
看样子你用的是oracle。我这篇文章更多针对PostgreSQ。具体Oracle的语法可能不太一样,具体可以参考Oracle官方文档
月亮上好冷
月亮上好冷

引用来自“月亮上好冷”的评论

With语法中不能有除查询以外的操作!刚测试的,不知道你怎么可以的,怪哉!

引用来自“郭俊_Jason”的评论

你用的什么版本?如果是8,那就可能是版本问题
10g
郭俊_Jason
郭俊_Jason

引用来自“月亮上好冷”的评论

With语法中不能有除查询以外的操作!刚测试的,不知道你怎么可以的,怪哉!
你用的什么版本?如果是8,那就可能是版本问题
月亮上好冷
月亮上好冷
With语法中不能有除查询以外的操作!刚测试的,不知道你怎么可以的,怪哉!
PostgreSQL Oracle 兼容性 - connect by 2

标签 PostgreSQL , Oracle , 树形查询 , 递归 , connect by , tablefunc , connectby 背景 Oracle connect by语法经常用于有树形关系的记录查询,PostgreSQL使用CTE递归语法,可以实现同样的...

德哥
2018/10/05
0
0
这些特性,Postgre SQL秒杀其他数据库

你可能会问自己 “为什么选择PostgreSQL ?” 开源数据库我们有好几种选择(本文参考 MySQL, MariaDB 和 Firebird ), 那么 PostgreSQL 具有哪些其它开源数据库不具备的特性呢? PostgreSQL 宣...

三墩IT人
2016/02/04
0
0
PostgreSQL 递归应用实践 - 非“传销”的高并发实时藤、树状佣金分配体系

标签 PostgreSQL , 佣金分配 , 树状 , 藤状 , 递归查询 , 传销 背景 早在十年前,PostgreSQL 8点几的版本就支持了递归查询,递归查询的应用非常的广泛,如下: 《PostgreSQL 递归妙用案例 - ...

德哥
04/14
0
0
Citus 7.2 发布,基于 PostgreSQL 的集群数据库

Citus 7.2 发布了。CitusDB 是一个基于最新 PostgreSQL 构建的分布式数据库。CitusDB 可对 PostgreSQL 数据库进行伸缩以适合大数据的处理。可在集群中进行自动分片和碎片复制,运行在云端或者...

达尔文
2018/01/28
1K
0
PostgreSQL培训系列直播—第四章:应用开发者指南

内容概要 1、基本SQL语句用法 2、数据类型、操作符 3、数据库对象类型 4、内置函数 5、自定义函数sql, plpgsql 6、高级SQL用法与应用场景 7、事务隔离级别 8、锁 9、触发器、事件触发器、规则...

德哥
04/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

解决vim打开之后乱码的问题

在Windows中的文档,传输到Linux系统中(使用rz命令),出现乱码 root@localhost ~]# rpm -qf `which iconv` glibc-common-2.17-105.el7.x86_64 [root@localhost ~]# rpm -ihv /mnt/Packages......

寰宇01
39分钟前
2
0
aldi 2017年1月记录

../../tools/CBLAS/lib/cblas_LINUX.a ../../tools/lapack-3.4.2/liblapacke.a \../../tools/lapack-3.4.2/librefblas.a -lgfortran \../../tools/lapack-3.4.2/liblapack.a \../../......

MtrS
40分钟前
2
0
Choerodon如何进行日志收集与告警

作者:董文启 应用程序日志是由软件应用程序记录的事件文件, 它一般包含错误,信息事件和警告。一个良好的日志系统有助于快速发现问题,定位问题,同时也为业务分析起到一定的作用。 传统E...

Choerodon
52分钟前
3
0
js二维码生成插件“jquery.qrcode.min.js”

<!doctype html> <html> <head> <meta charset="utf-8" /> <title>生成二维码</title> <script type='text/javascript' src='http://cdn.staticfile.org/jquery/2.1.1/jquery.min.js'></scri......

泉天下
55分钟前
1
0
Spring AOP之同一个对象方法内部自调用导致事务失效问题

对于像我这种喜欢滥用AOP的程序员,遇到坑也是习惯了,不仅仅是事务,其实只要脱离了Spring容器管理的所有对象,对于SpringAOP的注解都会失效,因为他们不是Spring容器的代理类,SpringAOP,就切入不...

xiaomin0322
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部