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.

x
-
1
2
3
4

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.

x a b
- - -
1 1 0
2 2 1
3 4 3
4 8 7

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.

Answer 1

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.