[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 tabledrop table也不被允许。

架构 Schema:
schema_de

schema_en

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

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

课件:

书: 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;
阅读 2k

1 声望
1 粉丝
0 条评论
1 声望
1 粉丝
文章目录
宣传栏