创建层次结构时"connect by"是否可以执行运行计算

Question

我试图创建一个查询，可以在我的数据中创建一个层次结构的事件。但是，由于每个生产记录可能出现多次，因此我需要为每个层次结构创建权重，以便知道哪一个是正确的，然后可以过滤指定的结果。

此权重将是每个节点的距离之和，定义为起始和发生之间的差值的绝对值之和。

如果在初始化和发生之间存在完美的关系，我会将其用作connectby语句中的标准，但由于情况并非如此，我想知道是否有可能将每个节点的绝对值求和，以便我可以获得"准确性"。测量用作进一步分析的过滤柱?

<表类> 戳 MGRID init occ tbody><<tr>100空2001/1/12016/4/12100空2001/1/12017/2/11100空2001/1/12017/7/191011002017/2/112017/2/211021002017/2/112017/2/111031002016/5/122016/5/122011032016/5/122016/5/122021012017/2/202017/2/212031002017/7/202017/7/19

使用递归子查询:

WITH data (prod, mgrid, init, occ, lvl, path, weight) AS (
SELECT prod,
mgrid,
init,
occ,
1,
'/' || prod,
ABS(occ-init)
FROM   mytab
WHERE  mgrid IS NULL
UNION ALL
SELECT m.prod,
m.mgrid,
m.init,
m.occ,
lvl + 1,
d.path || '/' || m.prod,
d.weight + ABS(m.occ - m.init)
FROM   mytab m
INNER JOIN data d
ON (d.prod = m.mgrid)
)
SELECT *
FROM   data
WHERE  lvl > 1
ORDER BY prod;

对于您的样本数据:

CREATE TABLE mytab (PROD, MGRID, init, occ) AS
SELECT 100, NULL, DATE '2001-01-01', DATE '2016-04-12' FROM DUAL UNION ALL
SELECT 100, NULL, DATE '2001-01-01', DATE '2017-02-11' FROM DUAL UNION ALL
SELECT 100, NULL, DATE '2001-01-01', DATE '2017-07-19' FROM DUAL UNION ALL
SELECT 101, 100,  DATE '2017-02-11', DATE '2017-02-21' FROM DUAL UNION ALL
SELECT 102, 100,  DATE '2017-02-11', DATE '2017-02-11' FROM DUAL UNION ALL
SELECT 103, 100,  DATE '2016-05-12', DATE '2016-05-12' FROM DUAL UNION ALL
SELECT 201, 103,  DATE '2016-05-12', DATE '2016-05-12' FROM DUAL UNION ALL
SELECT 202, 101,  DATE '2017-02-20', DATE '2017-02-21' FROM DUAL UNION ALL
SELECT 203, 100,  DATE '2017-07-20', DATE '2017-07-19' FROM DUAL;

输出:

<表类>戳MGRIDINITOCC级路径重量tbody><<tr>10110011-FEB-1721-FEB-172/100/101559010110011-FEB-1721-FEB-172/100/101605310110011-FEB-1721-FEB-172/100/101589510210011-FEB-1711-FEB-172/100/102558010210011-FEB-1711-FEB-172/100/102604310210011-FEB-1711-FEB-172/100/102588510310012-MAY-1612-MAY-162/100/103558010310012-MAY-1612-MAY-162/100/103604310310012-MAY-1612-MAY-162/100/103588520110312-MAY-1612-MAY-163/100/103/201558020110312-MAY-1612-MAY-163/100/103/201604320110312-MAY-1612-MAY-163/100/103/201588520210120-FEB-1721-FEB-173/100/101/202605420210120-FEB-1721-FEB-173/100/101/202589620210120-FEB-1721-FEB-173/100/101/202559120310020-JUL-1719-JUL-172/100/203558120310020-JUL-1719-JUL-172/100/203588620310020-JUL-1719-JUL-172/100/2036044