Oracle SQL to Mimic Nested Window Function

Question

My actual problem involves a larger row source and more involved math, but this is a small example that still exhibits the challenge faced. Using Oracle 19c.

Suppose we have a table X with four rows of data as follows.

Further, suppose we want to derive two columns, a and b, from X such that

a = x + sum(previous rows of b ordered by x)
b = a - 1.

Where if there are no previous rows, then the sum is 0.

Therefore, the new table would have rows like the following.

The following is invalid SQL, but provides an example of what is being attempted.

with
  X AS
  (
    select 1 x from dual
    union all select 2 from dual
    union all select 3 from dual
    union all select 4 from dual
  )
  , A AS
  (
    select
      x
      , x + sum(b) over (order by x range between unbounded preceding and 1 preceding) AS a
      , a - 1 AS b
    from x
  )
  select * from A
;

Perhaps a hierarchical query may help, but not sure on what it is to connect by.

Any ideas would be appreciated. Thanks in advance.

score 1 · Accepted Answer · answered Feb 10 '21 at 01:06

You can do this using a recursive CTE:

with X AS (
    select 1 x from dual
    union all select 2 from dual
    union all select 3 from dual
    union all select 4 from dual
  ),
     cte(x, a, b, b_sum) as (
      select x, x as a, x - 1 as b, x - 1 as b_sum
      from x
      where x = 1
      union all
      select x.x, x.x + cte.b_sum, x.x + cte.b_sum - 1, cte.b_sum + (x.x + cte.b_sum - 1)
      from cte join
           x
           on x.x = cte.x + 1
     )
select *
from cte;

Here is a db<>fiddle.

Oracle SQL to Mimic Nested Window Function

1 Answers1