[SQL]高级SQL-递归(1)

递归 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被生成。这一点也和上一点有相关:递归其实是被处理成循环。

一个小小的例子是：
假设$A$已经在结果的集合里面，但是下一步又产生一个新的$A$。

• 对于union，它可以发现这个集合并不会增加新元素，那么这个SQL引擎内部的循环就结束了。对于我们top user，这个递归的SQL也就结束了。
• 对于union all, 它可以发现这个包(Bag)又多了一个新元素$A$，再下一步又会新生成一个$A$，这个过程会一直进行下去。对于SQL引擎，这就变成了一个无止境的循环。对于我们top user，这个递归SQL无法终止。

如果我们的数据库的行为是上述那样，这时候我们可以猜想：这个递归SQL会被内部处理成一个while循环，结束的条件是这个集合/包没有新的元素再被生成。

我们会在下一篇文章会遇到真正的例子以及这方面很多练习。

第一部分数据集 - Uni Schema

Uni Schema 数据集在　https://hyper-db.de/interface...　可以直接使用。另外在这个网页不允许进行写操作:insert, update, delete之类的transactional query。当然create table和drop table也不被允许。

架构 Schema:

下载:
https://db.in.tum.de/teaching...

Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。

课件：

• https://db.in.tum.de/teaching...
• https://db.in.tum.de/teaching...

书：　https://db.in.tum.de/teaching...

前导课(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)

$$tran_{A, B}(R) = \{(a, b) | \exists k \in \mathbb{N} (\exists \Gamma 1, \dots, \Gamma k \in R \\ (\Gamma 1.A = \Gamma 2.B \wedge \dots \Gamma k-1.A = \Gamma k.B \wedge \Gamma 1.A=a \wedge \Gamma k.B = b))\}$$

也就是找出一条路径。

• 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;