sql - Oracle中如何根据上一行的计算结果通过CASE语句动态计算？

Question

我正在尝试通过依赖于前一行计算结果的 CASE 语句进行计算。我正在使用的数据是分层数据。我的最终目标是构建结果数据以符合 Modified Preorder Tree Traversal algorithm .

这是我的原始数据:

+-------+--------+
| id    | parent |
+-------+--------+
| 1     | (null) |
+-------+--------+
| 600   | 1      |
+-------+--------+
| 690   | 600    |
+-------+--------+
| 6990  | 690    |
+-------+--------+
| 6900  | 690    |
+-------+--------+
| 69300 | 6900   |
+-------+--------+
| 69400 | 6900   |
+-------+--------+

这就是我想要的最终结果。我很乐意详细说明为什么这是我正在寻找的，与 MPTT 等相关。

+-------+-----------+-----+------+--+--+--+--+
| id    | parent_id | lft | rght |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 1     |           | 1   | 14   |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 600   | 1         | 2   | 13   |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 690   | 600       | 3   | 12   |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 6900  | 690       | 4   | 9    |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 6990  | 690       | 10  | 11   |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 69300 | 6900      | 5   | 6    |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+
| 69400 | 6900      | 7   | 8    |  |  |  |  |
+-------+-----------+-----+------+--+--+--+--+

到目前为止，这是我的 SQL 代码的样子。它计算了我认为我在下面描述的算法需要的许多字段。这是企业设置中的“组织”数据，这就是 orgn 缩写在我的代码中很常见的原因。

这是我认为可以成功将其转换为 MPTT 格式的算法:

-If level is root (lvl=1), lft = 1, rght = subnodes*2 + 2
-If level is the next level down (lvl = prev_lvl+1), and prev_parent != parent (meaning this is the first sibling)
    -lft = parent_lft+1
-If lvl = prev_lvl, so we are on the same level (don’t know if this is a true sibling of the same parent yet)
    -if parent = prev_parent, lft=prev_rght+1 (true sibling, just use previous sibling’s right + 1)
    -if parent != prev_parent, lft=parent_lft+1 (same level, not true sibling, so use parent’s left + 1)

-rght=(subnodes*2) + lft + 1

我目前的 SQL 代码:

WITH tab1 (
    id,
    parent_id
) AS (
    SELECT
        1,
        NULL
    FROM
        dual
    UNION ALL
    SELECT
        600,
        1
    FROM
        dual
    UNION ALL
    SELECT
        690,
        600
    FROM
        dual
    UNION ALL
    SELECT
        6990,
        690
    FROM
        dual
    UNION ALL
    SELECT
        6900,
        690
    FROM
        dual
    UNION ALL
    SELECT
        69300,
        6900
    FROM
        dual
    UNION ALL
    SELECT
        69400,
        6900
    FROM
        dual
),t1 (
    id,
    parent_id,
    lvl
) AS (
    SELECT
        id,
        parent_id,
        1 AS lvl
    FROM
        tab1
    WHERE
        parent_id IS NULL
    UNION ALL
    SELECT
        t2.id,
        t2.parent_id,
        lvl + 1
    FROM
        tab1 t2,
        t1
    WHERE
        t2.parent_id = t1.id
)
    SEARCH BREADTH FIRST BY id SET order1,orgn_subnodes AS (
    SELECT
        id AS id,
        COUNT(*) - 1 AS subnodes
    FROM
        (
            SELECT
                CONNECT_BY_ROOT ( t1.id ) AS id
            FROM
                t1
            CONNECT BY
                PRIOR t1.id = t1.parent_id
        )
    GROUP BY
        id
),orgn_partial_data AS (
    SELECT
        orgn_subnodes.id AS id,
        orgn_subnodes.subnodes,
        parent_id,
        lvl,
        LAG(lvl,1) OVER(
            ORDER BY
                order1
        ) AS prev_lvl,
        LAG(parent_id,1) OVER(
            ORDER BY
                order1
        ) AS prev_parent,
        CASE
                WHEN parent_id IS NULL THEN 1
            END
        lft,
        CASE
                WHEN parent_id IS NULL THEN ( subnodes * 2 ) + 2
            END
        rght,
        order1
    FROM
        orgn_subnodes
        JOIN t1 ON orgn_subnodes.id = t1.id
) SELECT
    *
  FROM
    orgn_partial_data;

结果是:

+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| id    | subnodes | parent_id | lvl | prev_lvl | prev_parent | lft | rght | order1 |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 1     | 6        |           | 1   |          |             | 1   | 14   | 1      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 600   | 5        | 1         | 2   | 1        |             |     |      | 2      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 690   | 4        | 600       | 3   | 2        | 1           |     |      | 3      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 6900  | 2        | 690       | 4   | 3        | 600         |     |      | 4      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 6990  | 0        | 690       | 4   | 4        | 690         |     |      | 5      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 69300 | 0        | 6900      | 5   | 4        | 690         |     |      | 6      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+
| 69400 | 0        | 6900      | 5   | 5        | 6900        |     |      | 7      |
+-------+----------+-----------+-----+----------+-------------+-----+------+--------+

我不关心树中“兄弟节点”的顺序。此外，如果您发现我开始使用的 SQL 没有用，您可以发布一个不使用任何 SQL 的答案。我发帖只是为了展示我认为我需要执行算法步骤的信息。

我将接受任何 Oracle 代码(数据库过程、SELECT 语句等)作为答案。

如果需要，请询问更多详细信息!

Answer 1

我认为开始的帖子有错字，对于 69400 应该是 (7, 8) 而不是 (4, 8)。

获得结果的规范方法是使用递归过程/函数。下面的方法使用过程和临时表，但您可以通过函数返回集合来实现相同的目的。

临时表

create global temporary table tmp$ (id int, l int, r int) on commit delete rows;

包

create or replace package pkg as
  procedure p(p_id in int);
end pkg;
/
sho err

包体

create or replace package body pkg as

  seq int;

  procedure p_(p_id in int) as
  begin
    seq := seq + 1;
    insert into tmp$(id, l, r) values (p_id, seq, null);
    for i in (select id from tab1 where parent_id = p_id order by id) loop
      p_(i.id);
    end loop;
    seq := seq + 1;    
    update tmp$ set r = seq where id = p_id;
  end;

  procedure p(p_id in int) as
  begin
    seq := 0;
    p_(p_id);
  end;  

end pkg;
/
sho err

在 SQL*PLus 中测试

SQL> exec pkg.p(1);

PL/SQL procedure successfully completed.

SQL> select * from tmp$;

        ID          L          R
---------- ---------- ----------
         1          1         14
       600          2         13
       690          3         12
      6900          4          9
     69300          5          6
     69400          7          8
      6990         10         11

7 rows selected.

更新

没有全局变量的独立过程

create or replace procedure p(p_id in int, seq in out int) as
begin
  seq := seq + 1;
  insert into tmp$(id, l, r) values (p_id, seq, null);
  for i in (select id from tab1 where parent_id = p_id order by id) loop
    p(i.id, seq);
  end loop;
  seq := seq + 1;
  update tmp$ set r = seq where id = p_id;
end;
/

在 SQL*PLus 中测试

SQL> var n number
SQL> exec :n := 0;

PL/SQL procedure successfully completed.

SQL> exec p(1, :n);

PL/SQL procedure successfully completed.

SQL> select * from tmp$;

        ID          L          R
---------- ---------- ----------
         1          1         14
       600          2         13
       690          3         12
      6900          4          9
     69300          5          6
     69400          7          8
      6990         10         11

7 rows selected.