Recursive column in sql (postgresql)

Question

I have a equation:

result[n] = least(0, x[n] + result[n-1])

which should produce such a table

  x  | result
--------------
 100 |     0  // least(100+0, 0) = 0
  50 |     0  // least(50+0, 0) = 0
-100 |  -100  // least(-100+0, 0) = -100
  50 |   -50  // least(50-100, 0) = -50

I'd like to somehow use it in SQL query for table of x values. I've been thinking about lag function like this:

select
    *,
    greatest(0, lag(result) over (order by i) + "table1"."value") as result
from
    table1

but of course it won't work since table has no result column.

Do you have any idea how to achieve such a recursion in SQL (PostgreSQL)?

See [Can window function LAG reference the column which value is being calculated?](https://stackoverflow.com/q/34338991/1995738) — klin, Oct 22 '18 at 16:59

score 0 · Accepted Answer · answered Oct 22 '18 at 16:52

SQL tables represent unordered sets. You can do this, but I need to assume that you have a column specifying the ordering.

If so, you can use a recursive CTE:

with recursive t as (
      select v.*
      from (values (1, 100), (2, 50), (3, -100), (4, 50)) v(id, x)
    ),
    cte(id, x, result) as (
     select id, x, least(x, 0)
     from t
     where id = 1
     union all
     select t.id, t.x, least(t.x + cte.result, 0)
     from cte join
          t
          on t.id = cte.id + 1
    )
select cte.*
from cte
order by id;

Here is a db<>fiddle.

Recursive column in sql (postgresql)

1 Answers1