原创文章,转载请务必将下面这段话置于文章开头处(保留超链接)。 本文转发自技术世界,原文链接 https://cloud.tencent.com/developer/article/1146293
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(多字段)记录已经访问过的记录,从而实现去重的目的。
定义无向有环图如下图所示
定义如下表并存入每条边的权重
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);
计算思路如下:
实现如下
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;