使用connect by子句进行递归插入

Question

我有一张表格，其中的数据是以下方式展示的层级关系（右侧）。创建了左侧所示的层次结构。这些表格存储在 Oracle 11g 中。

层级树                       Tree Table  
--------------          Element Parent
                        ------  ------
P0                      P0  
    P1                  P1      P0
        P11             P2      P0
            C111        P11     P1
            C112        P12     P1
        P12             P21     P2
            C121        P22     P2
            C122        C111    P11
    P2                  C112    P11
        P21             C121    P12
            C211        C122    P12
            C212        C211    P21
        P22             C212    P21
            C221        C221    P22
            C222        C222    P22

我的数据表格如下所示。它包含所有叶子节点的值。
Data Table

Element Value  
C111    3  
C112    3  
C121    3  
C122    3  
C211    3  
C212    3  
C221    3  
C222    3  
P11     6  

我需要生成一个 insert 语句，最好是单个 insert 语句，它将基于孩子们的值之和插入数据表中的行. 请注意，我们只需要为那些数据表中不存在的值计算其父项的总和。

插入后的数据表（期望）

Element Value
C111    3
C112    3
C121    3
C122    3
C211    3
C212    3
C221    3
C222    3
P11     6
-- 需要插入的行
P12     6
P21     6
P22     6
P1      12
P2      12
P0      24

- BigBoss

Answer 1

如果所有叶节点都在同一高度（这里是lvl=4），您可以使用ROLLUP编写简单的CONNECT BY查询：

SQL> SELECT lvl0,
  2         regexp_substr(path, '[^/]+', 1, 2) lvl1,
  3         regexp_substr(path, '[^/]+', 1, 3) lvl2,
  4         SUM(VALUE) sum_value
  5    FROM (SELECT sys_connect_by_path(t.element, '/') path,
  6                 connect_by_root(t.element) lvl0,
  7                 t.element, d.VALUE, LEVEL lvl
  8             FROM tree t
  9             LEFT JOIN DATA d ON d.element = t.element
 10            START WITH t.PARENT IS NULL
 11           CONNECT BY t.PARENT = PRIOR t.element)
 12   WHERE VALUE IS NOT NULL
 13     AND lvl = 4
 14   GROUP BY lvl0, ROLLUP(regexp_substr(path, '[^/]+', 1, 2),
 15                         regexp_substr(path, '[^/]+', 1, 3));

LVL0 LVL1  LVL2   SUM_VALUE
---- ----- ----- ----------
P0   P1    P11            6
P0   P1    P12            6
P0   P1                  12
P0   P2    P21            6
P0   P2    P22            6
P0   P2                  12
P0                       24

插入操作应该是这样的:

INSERT INTO data (element, value) 
(SELECT coalesce(lvl2, lvl1, lvl0), sum_value
   FROM <query> d_out
  WHERE NOT EXISTS (SELECT NULL
                      FROM data d_in
                     WHERE d_in.element = coalesce(lvl2, lvl1, lvl0)));

如果叶节点的高度未知/无限，则情况会更加复杂。上述方法行不通，因为ROLLUP需要确切知道要考虑多少列。

在这种情况下，您可以在自连接中使用树结构：

SQL> WITH HIERARCHY AS (
  2     SELECT t.element, path, VALUE
  3       FROM (SELECT sys_connect_by_path(t.element, '/') path,
  4                    connect_by_isleaf is_leaf, ELEMENT
  5                FROM tree t
  6               START WITH t.PARENT IS NULL
  7              CONNECT BY t.PARENT = PRIOR t.element) t
  8       LEFT JOIN DATA d ON d.element = t.element
  9                       AND t.is_leaf = 1
 10  )
 11  SELECT h.element, SUM(elements.value)
 12    FROM HIERARCHY h
 13    JOIN HIERARCHY elements ON elements.path LIKE h.path||'/%'
 14   WHERE h.VALUE IS NULL
 15   GROUP BY h.element
 16   ORDER BY 1;

ELEMENT SUM(ELEMENTS.VALUE)
------- -------------------
P0                       24
P1                       12
P11                       6
P12                       6
P2                       12
P21                       6
P22                       6

- Vincent Malgrat

---

啊，自连接选项。这就是我想要编写的，但我不知道如何操作。你让它看起来如此简单。 - Mike Meyers

@Vincent：我花了将近40分钟才理解这个查询。但现在我已经将它加入到我的基本登录中，并且可以看到它正在工作。 - BigBoss

@Vincent：我正在使用你建议的第二个查询，因为级别不固定，对于某些树节点它也会有所变化。 - BigBoss

Answer 2

这里有另一种使用SQL MODEL语句的选项。我从Vincent的答案中吸取了一些提示（使用regexp_subsr）来简化我的代码。

WITH子句中的第一部分重新调整数据并提取出每个级别的层次结构。

查询末尾的model语句将数据从最低级别向上移动。如果有超过四个级别，则需要添加其他列，但无论值在哪个级别保持，此方法都应该有效。

不确定这种方法是否适用于所有情况，因为我对MODEL语句并不那么熟悉，但至少在这种情况下似乎是有效的。

with my_hierarchy_data as (
select 
    element,
    value, 
    path, 
    parent,
    lvl0,
    regexp_substr(path, '[^/]+', 1, 2) as lvl1,
    regexp_substr(path, '[^/]+', 1, 3) as lvl2,
    regexp_substr(path, '[^/]+', 1, 4) as lvl3
from ( 
  select 
    element,
    value, 
    parent,
    sys_connect_by_path(element, '/') as path, 
    connect_by_root element as lvl0
  from 
    tree
    left outer join data using (element)
  start with parent is null
  connect by prior element = parent
  order siblings by element
  )
)
select 
    element,
    value, 
    path, 
    parent,
    new_value,
    lvl0, 
    lvl1, 
    lvl2, 
    lvl3
from my_hierarchy_data
model
return all rows
partition by (lvl0)
dimension by (lvl1, lvl2, lvl3)
measures(element, parent, value, value as new_value, path)
rules sequential order (
    new_value[lvl1, lvl2, null] = sum(value)[cv(lvl1), cv(lvl2), lvl3 is not null],
    new_value[lvl1, null, null] = sum(new_value)[cv(lvl1), lvl2 is not null, null],
    new_value[null, null, null] = sum(new_value)[lvl1 is not null, null, null]
)

您可以使用的插入语句为：

INSERT INTO data (elelment, value)
select element, newvalue
from <the_query>
where value is null;

- Mike Meyers

---

说实话，我的第一个想法是使用模型子句，但我没能搞定 =) - Vincent Malgrat

@Mike：你的答案也很好。我选择了Vincent的答案，因为它提供了简单性。 我还没有评估这些答案的性能。但如果必要的话，我会这样做。 - BigBoss

@BigBoss 我必须承认，文森特的查询非常简洁，让我印象深刻。 - Mike Meyers