递归 Recursion
我们这一篇文章采用并介绍PostgreSQL的SQL递归(Recursion)语法。
递归同时是一个数据库之内有语法syntax差异的地方，可能每一个数据库都实现了递归，但是关键词key word不一样。比如一个Oracle的SQL：使用CONNECT BY关键词去实现递归。
这篇文章(或者我的SQL文章系列)主要介绍如果使用SQL语句，而且重点在使用数据集来用SQL来解决一些查询问题。这一篇文章我们会先大致介绍一下SQL递归的结构，然后会用多种数据集进行练习，加深印象。
SQL递归结构
with recursive tmp_name (att1, att2, ...) as (
-- basic case
union -- union / union all
-- recursive case
)
可以再稍微仔细一些：
with recursive tmp_name (att1, att2, ...) as (
-- basic case
select ... from ...
union -- union / union all
-- recursive case
select ... from ..., tmp_name
)
重点是recursive case中我们在使用tmp_name，而这个正是我们正在定义的表格。这个过程就是递归的核心(我们在定义这个表格的时候，也用到了这个表格自己)。with recursive这个关键词用递归的方式新建了一个暂时的表格tmp_name，我们可以在之后用。
这个定义其实和图论(Graph theory)中的传递闭包(Transitive closure)和可到达性(st-connectivity)很类似。
SQL递归内部处理
在SQL引擎内部，递归并不是真正的被"递归地处理"。"递归地处理"指：每一次新建一个call stack，然后继续进行同一个函数使用不同参数的运算。这个我们可以很black box地去测试：写一个不终止的递归，但是它最后仅仅只是不终止，而不是stack overflow。这可以间接的说明递归是被内部处理成一个类似while的循环。
union vs. union all
union: 对两个集合进行并集操作，除去重复行(即集合语义 Set Semantics)，同时进行默认规则(第一列升序)的排序。union all: 对两个集合进行并集操作，不除去重复行(即包语义 Bag Semantics)，不进行排序。
很显然union all做的操作相对较少，所以它速度更快(如果数据量相同，也无重复生成的前提下)。但是union all很可能在递归的情况导致SQL无法终止，原因就是不断有(重复的)tuple被生成。这一点也和上一点有相关:递归其实是被处理成循环。
一个小小的例子是：
假设已经在结果的集合里面，但是下一步又产生一个新的
。
- 对于
union，它可以发现这个集合并不会增加新元素，那么这个SQL引擎内部的循环就结束了。对于我们top user，这个递归的SQL也就结束了。 - 对于
union all, 它可以发现这个包(Bag)又多了一个新元素，再下一步又会新生成一个
top user，这个递归SQL无法终止。
如果我们的数据库的行为是上述那样，这时候我们可以猜想：这个递归SQL会被内部处理成一个while循环，结束的条件是这个集合/包没有新的元素再被生成。
我们会在下一篇文章会遇到真正的例子以及这方面很多练习。
第一部分数据集 - Uni Schema
Uni Schema 数据集在 https://hyper-db.de/interface.html 可以直接使用。另外在这个网页不允许进行写操作:insert, update, delete之类的transactional query。当然create table和drop table也不被允许。
架构 Schema:
下载:
https://db.in.tum.de/teaching/ws1920/grundlagen/uni_mysql.sql?lang=de
Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。
课件：
- https://db.in.tum.de/teaching/bookDBMSeinf/folien/?lang=de
- https://db.in.tum.de/teaching/bookDBMSeinf/folien/pdf/Kapitel4.pdf?lang=de
书： https://db.in.tum.de/teaching/bookDBMSeinf/?lang=de
前导课(voraussetzen)
- 找Der Wiener Kreis这门课的前导课(voraussetzen)：
select vorgaenger
from voraussetzen, vorlesungen
where nachfolger = vorlnr and titel = 'Der Wiener Kreis';
- 找Der Wiener Kreis这门课的前导课(voraussetzen)的前导课(voraussetzen)：
select v1.vorgaenger
from voraussetzen v1, voraussetzen v2, vorlesungen vorl
where v1.nachfolger = v2.vorgaenger and v2.nachfolger = vorl.vorlnr and vorl.titel = 'Der Wiener Kreis';
- 传递闭包(Transitive closure)
也就是找出一条路径。
- Der Wiener Kreis是所有(直接和间接)前提们：
with recursive transVorl (vorg, nachf) as (
select vorgaenger, nachfolger
from voraussetzen
union all
select t.vorg, v.nachfolger
from transVorl t, voraussetzen v
where t.nachf = v.vorgaenger
)
select titel
from vorlesungen
where vorlnr in (
select vorg
from transVorl
where nachf in (
select nachf
from vorlesungen
where titel = 'Der Wiener Kreis'
)
)
或者
with recursive transVorl (vorg, nachf) as (
select vorgaenger, nachfolger
from voraussetzen
union all
select t.vorg, v.nachfolger
from transVorl t, voraussetzen v
where t.nachf = v.vorgaenger
)
select distinct v1.titel
from vorlesungen v1, transVorl t, vorlesungen v2
where v1.vorlnr = t.vorg and t.nachf = v2.vorlnr and v2.titel = 'Der Wiener Kreis'
- Bioethik是所有(直接和间接)前提们：
with recursive voraussetzen_rec as (
select vorlnr
from vorlesungen v
where titel = 'Bioethik'
union all
select vor.vorgaenger
from voraussetzen_rec v, voraussetzen vor
where v.vorlnr = vor.nachfolger
)
select v.titel
from vorlesungen v, voraussetzen_rec vor
where v.vorlnr = vor.vorlnr and v.titel != 'Bioethik'
这个也是换了另外一种思路去表达。
前导课(voraussetzen), 对路径长度计数
- 计算完成这些课至少需要多少学期：
with recursive transVorl (vorg, nachf, len) as (
select vorgaenger, nachfolger, 1
from voraussetzen
union all
select t.vorg, v.nachfolger, t.len + 1
from transVorl t, voraussetzen v
where t.nachf = v.vorgaenger
)
select max(t.len) + 1 as MinStudySemester
from transVorl t, vorlesungen v
where v.titel = 'Der Wiener Kreis';
或者
with recursive transVorl (vorlnr, len) as (
select vorlnr, 1
from vorlesungen
where titel = 'Der Wiener Kreis'
union all
select v.vorgaenger, t.len + 1
from transVorl t, voraussetzen v
where t.vorlnr = v.nachfolger
)
select max(len) MinStudySemester
from transVorl;
画一颗树：
with recursive tree (step, text) as (
select 0, ' *'
union all
select step + 1,
(case
when step = length(text) / 2 then concat(repeat(' ', length(text) / 2), '*')
else concat(
substring(text from 0 for length(text) - (2 * step + 1)),
repeat('*', 2 * step + 3))
end)
from tree
where step <= length(text) / 2
)
select t.text
from tree t
order by step;
输出(这里只建议用shell运行postgresql，用其他接口不一定看得出是树)：
text
-------------------------------
*
***
*****
*******
*********
***********
*************
***************
*****************
*******************
*********************
***********************
*************************
***************************
*****************************
*
(16 rows)
这里用||代替concate：
with recursive tree (step, text) as (
select 0, ' *'
union all
select step + 1,
(case
when step = length(text) / 2 then repeat(' ', length(text) / 2) || '*'
else
substring(text from 0 for length(text) - (2 * step + 1)) || repeat('*', 2 * step + 3)
end)
from tree
where step <= length(text) / 2
)
select t.text
from tree t
order by step;
该文章遵循创作共用版权协议 CC BY-NC 3.0，要求署名、非商业 、保持一致。在满足创作共用版权协议 CC BY-NC 3.0 的基础上可以转载，但请以超链接形式注明出处。文章仅代表作者的知识和看法，如有不同观点，可以回复并讨论。
