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
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.
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.