Time：2020-2-18

# Recursive

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:

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;

## Detailed explanation of MySQL — knowledge sorting

[continuously update, organize and use by yourself] B-tree and B + tree The most easy to understand introduction of b-tree index – xiaohouye – blog Garden (cnblogs. Com) B-tree non leaf nodes are composed ofKeyword + data, so data can be obtained during traversal.And B+All tree nodes will appear in leaf nodes, and non leaf […]