sql - Oracle SQL 通过 sibling 识别 sibling 并计算相关比率

Question

我有一组相关记录及其相关比率的链接组。不幸的是，并非所有比率都被捕获，而是可以从其他比率导出。因为我可能需要一个 A -> D 的比率，但是我没有直接的比率来引用，但我可以从下面的例子中通过取 A->C (0.5) 然后 C-> D (3) 因此 0.5 * 3 = 1.5。或者我可以通过反转 A->B (1/2 = 0.5) 的比率来推导出 B，然后取 A->C (0.5) 的比率 (1/2) * 0.5 = 0.25 等等.

例如

FROM_UNIT_ID  TO_UNIT_ID  RATIO
A             B           2
A             C           0.5
C             D           3

我想要做的是计算所有可能路径及其比率的完整列表，如下所示。

FROM_UNIT_ID  TO_UNIT_ID  RATIO
A             B           2
A             C           0.5
C             D           3
A             D           1.5
B             C           0.25
B             D           0.75
B             A           0.5
C             A           2
C             B           4
D             A           0.666666667
D             B           1.333333333
D             C           0.333333333

使用以下 stack overflow question我已经能够推导出一个配给的所有 sibling 来获取可能路径的列表，但我一直无法调整这个查询来计算比率。我正在使用 Oracle 11g。

SELECT
       CONNECT_BY_ROOT( FROM_UNIT_ID ) AS FROM_UNIT_ID,
       TO_UNIT_ID
FROM   (
  SELECT FROM_UNIT_ID, TO_UNIT_ID, RATIO FROM RATIOS 
  UNION
  SELECT TO_UNIT_ID, UNIT_ID, 1/RATIO AS RATIO FROM RATIOS 
)
WHERE  CONNECT_BY_ROOT( FROM_UNIT_ID ) <> TO_UNIT_ID
CONNECT BY NOCYCLE
       PRIOR TO_UNIT_ID = FROM_UNIT_ID
ORDER BY FROM_UNIT_ID, TO_UNIT_ID;

Answer 1

在 Oracle 11.2 及更高版本(其中递归 WITH 子句可用)中，您可以这样做(假设输入表中的数据不自相矛盾):

with
  test_data (from_unit_id, to_unit_id, ratio) as (
    select 'A', 'B', 2   from dual union all
    select 'A', 'C', 0.5 from dual union all
    select 'C', 'D', 3   from dual
  )
, symm_data (x, y, r) as (
    select case when h.source = 1 then from_unit_id else to_unit_id   end,
           case when h.source = 1 then to_unit_id   else from_unit_id end,
           case when h.source = 1 then ratio        else 1/ratio      end
    from   test_data cross join 
                     (select 1 as source from dual union all select 2 from dual) h
  )
, all_nodes (x) as (
    select distinct x
    from   symm_data
  )
, rec (x, y, r) as (
    select x, x, 1
      from all_nodes
    union all
    select r.x, s.y, r.r * s.r
      from rec r join symm_data s on r.y = s.x
  )
  cycle x, y set cycle to 'Y' default 'N'
select x as from_unit_id, y as to_unit_id, round(r, 4) as ratio 
from   rec
where  cycle = 'N' and x != y
order  by x, y
;

输出:

FROM_UNIT_ID TO_UNIT_ID        RATIO
------------ ------------ ----------
A            B                     2
A            C                    .5
A            D                   1.5
B            A                    .5
B            C                   .25
B            D                   .75
C            A                     2
C            B                     4
C            D                     3
D            A                 .6667
D            B                1.3333
D            C                 .3333

注意 - 我使用了 ROUND在最后 SELECT ;通常应该只在最终报告中使用，否则你不应该在任何地方对数字进行四舍五入。