sql - 可能的递归或分析查询。这可以通过 SQL 完成还是需要脚本？

Question

不确定这是否可以通过标准查询实现，但这是我希望我的数据的样子。

START    END    SETTLE    NEW_SETTLE    CORRECTION_FACT    STORED_SETTLE
1        2      120       NULL          1                  120
2        3      127       119           1.0084             128.0668
3        4      NULL      125           1.0245344          NULL

计算如下:

CORRECTION_FACT

基本上，如果 NEW SETTLE 为空或这是第 1 行，则校正因子始终为 1。如果行 > 1，则校正因子为 PREV ROW STORED_SETTLE/NEW_SETTLE 例如。 120/119

STORED_SETTLE

这将始终是 CORRECTION_FACTOR * SETTLE。这个值在插入下一行之前是未知的，所以有时它会是 NULL。棘手的部分是，这取决于 CORRECTION_FACT，它也是一个计算值，CORRECTION_FACT 取决于 STORED_SETTLE。

就我拥有的值(value)观而言，我有 SETTLE、NEW_SETTLE、START 和 END。 CORRECTION_FACT 和 STORED_SETTLE 将始终需要计算。

所以这里的问题是我可以使用某种递归查询或分析函数来执行此操作，还是必须编写脚本来填充？

Answer 1

with T1 as
  (select 1 strt, 2 en, 120  settle, null new_settle from dual union all
   select 2 strt, 3 en, 127  settle, 119  new_settle from dual union all
   select 3 strt, 4 en, null settle, 125  new_settle from dual
  )
select strt
     , en
     , settle
     , new_settle
     , correction_fact
     , stored_settle 
  from t1
  model
   dimension by(row_number() over (order by strt) RowNumber)
   measures(strt, en, settle, new_settle
          , cast(null as number) correction_fact
          , cast(null as number) stored_settle)
   rules automatic order
   (
     correction_fact[1]=1,
     correction_fact[RowNumber>1] = decode(new_settle[cv()], null,
                  1,stored_settle[cv()-1]/new_settle[cv()]),                           
     stored_settle[rownumber]=(correction_fact[cv()]*settle[cv()])
   );

结果:

  STRT         EN     SETTLE NEW_SETTLE CORRECTION_FACT STORED_SETTLE
---------- ---------- ---------- ---------- --------------- -------------
     1          2        120                          1           120
     2          3        127        119      1.00840336    128.067227
     3          4                   125      1.02453782