SQL优化(五)PostgreSQL(递归)CTE通用表表达式
原创文章,转载请务必将下面这段话置于文章开头处(保留超链接)。 本文转发自技术世界,原文链接 http://www.jasongj.com/sql/cte/
WITH语句通常被称为通用表表达式(Common Table Expressions)或者CTEs。
WITH语句作为一个辅助语句依附于主语句,WITH语句和主语句都可以是SELECT
,INSERT
,UPDATE
,DELETE
中的任何一种语句。
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中可以不仅可以使用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语句还可以通过增加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语句包含了两个部分
执行步骤如下
以上面的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这一关键词。
从上一节中可以看到,决定是否继续迭代的working table是否为空,如果它永不为空,则该CTE将陷入无限循环中。 对于本身并不会形成循环引用的数据集,无段作特别处理。而对于本身可能形成循环引用的数据集,则须通过SQL处理。
一种方式是使用UNION而非UNION ALL,从而每次recursive term的计算结果都会将已经存在的数据清除后再存入working table,使得working table最终会为空,从而结束迭代。
然而,这种方法并不总是有效的,因为有时可能需要这些重复数据。同时UNION只能去除那些所有字段都完全一样的记录,而很有可能特定字段集相同的记录即应该被删除。此时可以通过数组(单字段)或者ROW(多字段)记录已经访问过的记录,从而实现去重的目的。
定义无向有环图如下图所示