sql with-recursive 语句如何解释? [英] How sql with-recursive statement interpreted?

问题描述

我想就理解递归"的工作原理寻求一些帮助.更准确地说，为什么锚查询(非递归术语)没有复制到 CTE 的子调用中.我尽力去理解，但我不确定.

I would like to ask get some help about understanding how "with recursive" works. More precisely WHY the anchor query (the non-recursive term) isn't replicated into the sub call of the CTE. I tried my best to understand alone but I'm not sure.

首先让我们以我发现的最简单的PostgreSQL为例(将1加到100):

First of all let's take the example of PostgreSQL which is the simplest one I found (make the sum of 1 to 100) :

WITH RECURSIVE t(n) AS (
      VALUES (1)
      UNION ALL
        SELECT n+1 FROM t WHERE n < 100)

    SELECT sum(n) FROM t;

我的代码演练(我使用了下面的链接):

My code walkthrough ( I used links below) :

1. 评估非递归项.对于联合 [...].

1. Evaluate the non-recursive term. For UNION [...].

在递归查询的结果中包含所有剩余的行，并将它们放在临时工作表中.

Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.

只要工作表不为空，重复这些步骤:

So long as the working table is not empty, repeat these steps:

• 评估递归项，用工作表的当前内容代替递归自引用.对于 UNION [...].在递归查询的结果中包含所有剩余的行，并将它们放入临时中间表中.

• Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION [...]. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.

将工作表的内容替换为中间表的内容，然后清空中间表."

Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table."

LVL 0 :

1. 非递归部分

1. non-recursive part

• CTE:(N) 1
• 工作台:(N) 1

递归部分

• CTE:(N) 1
• 工作台:(N) 1
• 中间表 (N) 2

(我认为这是我搞砸的部分)- 替换工作台

(this is the part I mess around I think) - substitution of WORKING TABLE

因此递归 t 将使用 WORKING TABLE 执行 SELECT n+1 并将结果放入中间表.

So the recursive t will use WORKING TABLE to do SELECT n+1 and put the result in INTERMEDIATE TABLE.

1. 联合所有

1. UNION ALL

• CTE:(N) 1 2
• 工作台:(N) 2
• 中间表:已清洁

那我们就通过t的调用进入下一个lvl吧?(因为 END 条件 WHERE n <100 = FALSE)

Then we go into the next lvl by the call of t right? (because END condition WHERE n < 100 = FALSE)

等级 1:

我们知道因为 postgreSQL 说只要工作表不为空，就重复递归步骤"所以它会重复步骤 2. 和 3.(如果我是正确的)直到 END 条件然后做 SUM.

We know coz postgreSQL says it "So long as the working table is not empty, repeat the recursive steps" So it will repeat the step 2. and 3. (if i'm correct) until END condition then do the SUM.

但是如果我只是遍历 t 的下一个 lvl 的调用，我们不应该先执行 VALUES(1) 吗?

BUT if I just walkthrough the call of the next lvl of t should we not do VALUES(1) first ?

我真的很困惑这怎么可能.

I'm really confused about how it is possible.

最好的问候，Falt4rm

Best regards, Falt4rm

• PostgreSQL:查询-with
• Microsoft Technet:CTE 的结构

推荐答案

这里没有发生递归"，我认为这就是您感到困惑的地方.

There is no "recursion" taking place here and I think that this is where you get confused.

来自 PostgreSQL 文档:http://www.postgresql.org/docs/9.4/static/queries-with.html

From the PostgreSQL documentation: http://www.postgresql.org/docs/9.4/static/queries-with.html

Note: Strictly speaking, this process is iteration not recursion, 
but RECURSIVE is the terminology chosen by the SQL standards committee.

为了解释这句话，一个 WITH RECURSIVE 可以被视为一个简单的 WHILE 循环.

To paraphrase this sentence, a WITH RECURSIVE can be viewed as a simple WHILE loop.

WITH RECURSIVE t(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;

这里是一些定制的伪代码来详细解释这个过程

Here is some custom-made pseudo-code to explain this process in detail

# Step 1: initialisation
LET cte_result = EMPTY
LET working_table = VALUES (1)
LET intermediate_table = EMPTY

# Step 2: result initialisation, merge initialisation into cte_result
cte_result = cte_result UNION working_table

# Step 3: iteration test
WHILE (working_table is not empty) DO
    # Step 4: iteration select, we substitute the self-reference with working_table
    intermediate_table = SELECT n+1 FROM working_table WHERE n < 100

    # Step 5: iteration merge, merge the iteration result into cte_result
    cte_result = cte_result UNION intermediate_table

    # Step 6: iteration end, prepare for next iteration
    working_table = intermediate_table
    intermediate_table = EMPTY
END WHILE

# Step 7: return
RETURN cte_result

并使用示例

# Step 1: initialisation
cte_result: EMPTY    | working_table: 1        | intermediate_table: EMPTY

# Step 2: result initialisation
cte_result: 1        | working_table: 1        | intermediate_table: EMPTY

# Step 3: iteration test
count(working_table) = 1 # OK
# Step 4: iteration select
cte_result: 1             | working_table: 1        | intermediate_table: 2
# Step 5: iteration merge
cte_result: 1, 2          | working_table: 1        | intermediate_table: 2
# Step 6: iteration end
cte_result: 1, 2          | working_table: 2        | intermediate_table: EMPTY

# Step 3: iteration test
count(working_table) = 1 # OK
# Step 4: iteration select
cte_result: 1, 2         | working_table: 2        | intermediate_table: 3
# Step 5: iteration merge
cte_result: 1, 2, 3      | working_table: 2        | intermediate_table: 3
# Step 6: iteration end
cte_result: 1, 2, 3      | working_table: 3        | intermediate_table: EMPTY

# … 97 more iterations and you get this state
cte_result: 1, 2, …, 100  | working_table: 100       | intermediate_table: EMPTY

# Step 3: iteration test
count(working_table) = 1 # OK
# Step 4: iteration select, the iteration query does not return any rows due to the WHERE clause
cte_result: 1, 2, …, 100  | working_table: 100       | intermediate_table: EMPTY
# Step 5: iteration merge, nothing is merged into the cte_result
cte_result: 1, 2, …, 100  | working_table: 100       | intermediate_table: EMPTY
# Step 6: iteration end
cte_result: 1, 2, …, 100  | working_table: EMPTY | intermediate_table: EMPTY

# Step 3: iteration test
count(working_table) = 0 # STOP

# Step 7: return
cte_result: 1, 2, …, 100

所以 CTE 的结果是从 1 到 100 的所有数字.

So the result of the CTE is all numbers from 1 to 100.

这篇关于sql with-recursive 语句如何解释?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！