[SQL] advanced SQL recursion (1)

Time:2020-2-18

Recursive

This article adopts and introduces the SQL recursion syntax of PostgreSQL.
Recursion is also a place where syntax syntax is different in a database. Maybe every database implements recursion, but key wordskey wordDissimilarity. For example, one.OracleSQL for: UsingCONNECT BYKeywords to implement recursion.

This article (or my SQL article series) mainly introduces how to use SQL statements, and focuses on using datasets to solve some query problems with SQL. In this article, we will first introduce the structure of SQL recursion, and then we will practice with multiple datasets to deepen our impression.

SQL recursive structure

with recursive tmp_name (att1, att2, ...) as (
    -- basic case
    union -- union / union all
    -- recursive case
)

You can be a little more careful:

with recursive tmp_name (att1, att2, ...) as (
    -- basic case
    select ... from ...
    union -- union / union all
    -- recursive case
    select ... from ..., tmp_name 
)

The key isrecursive caseWe are usingtmp_name, and this is the table we are defining. This process is the core of recursion (when we define this table, we also use the table itself).with recursiveThis keyword recursively creates a temporary tabletmp_name, we can use it later.

This definition is similar to the transitive closure and St connectivity in graph theory.

SQL recursive internal processing

Within the SQL Engine, recursion is not really handled recursively. “Recursively processing” means to create a new call stack at a time, and then continue the operation of the same function with different parameters. We canblack boxTo test: write a non terminating recursion, but it is only non terminating in the end, not stack overflow. This can indirectly show that recursion is handled internally as a similarwhileThe cycle.

union vs. union all

  • union: merge two sets to remove duplicate rows (i.e. set semantics), and sort the default rule (first column ascending).
  • union all: merge two sets without removing duplicate rows (i.e. bag semantics) and sorting.

Obviouslyunion allIt does relatively little, so it’s faster (if the amount of data is the same, there’s no duplicate generation). howeverunion allIt is likely that in the case of recursion, SQL cannot be terminated because there are (repeated) tuples generated constantly. This is also related to the previous point:Recursion is actually treated as a loop

A small example is:
Suppose $a $is already in the result set, but the next step is to generate a new $a $.

  • aboutunion, it can find that the set merge will not add new elements, so the internal cycle of the SQL Engine is over. For ustop user, the recursive SQL is over.
  • aboutunion all, it can find that there is another new element $a $in the bag, and then another $a $will be generated. This process will continue. For the SQL Engine, this becomes an endless loop. For ustop user, this recursive SQL cannot be terminated.

If the behavior of our database is as above, then we can guess: this recursive SQL will be internally processed into awhileThe loop ends when no new elements are generated for the collection / package.

We’ll come across real examples and lots of practice in the next article.

Part I dataset – uni schema

The uni schema dataset can be used directly at https://hyper-db.de/interface. In addition, write operations are not allowed on this page:insert, update, deleteTransactional query like this. Of coursecreate tableanddrop tableAnd not allowed.

Schema:
[SQL] advanced SQL recursion (1)

[SQL] advanced SQL recursion (1)

Download:
https://db.in.tum.de/teaching…

Schma and most SQL statements come from pro. Alfons Kemper, Ph.D.’s courseware and books.

Courseware:

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

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

Forerunner (voraussetzen)

  • Find voraussetzen in der Wiener Kreis:
select vorgaenger
from voraussetzen, vorlesungen
where nachfolger = vorlnr and titel = 'Der Wiener Kreis';
  • Find voraussetzen in der Wiener Kreis:
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))\}$$

That is to find a path.

  • Der Wiener Kreis is all (direct and indirect) premises:
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'
        )
    )

perhaps

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 is all (direct and indirect) premises:
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'

This is another way to express it.

Voraussetzen, counting path length

  • Calculate the minimum number of semesters required to complete these courses:
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';

perhaps

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;

Draw a tree:

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;

Output (it is only recommended to run PostgreSQL in shell, but it is not necessarily a tree with other interfaces):

             text              
-------------------------------
               *
              ***
             *****
            *******
           *********
          ***********
         *************
        ***************
       *****************
      *******************
     *********************
    ***********************
   *************************
  ***************************
 *****************************
               *
(16 rows)

Use here||replaceconcate

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;