How to make a self referential window functions

Question

I have a table like this:

amount  type  app  owe
1       a     10   10
2       a      8   -2
3       a     20   12
4       i     30   10
5       a     40   10

owe is:

(type == 'a')?app - sum(owe) where amount < (amount for current row):max(app-sum(owe)where amount<(amount for current row),0)

So I'd need a window function on the column that the window function is on. There are these partition on rows between rows unlimited preceding and prior row, but it has to be on a different column, not the column I'm summing. Is there a way to reference the same column the window function is on

I tried an alias

case 
  when type = a 
    then app - sum(owe)over(ROWS BETWEEN UNBOUNDED PRECEDING AND 1 preceding) as owe
  else 
    greatest(0,app - sum(owe)over(ROWS BETWEEN UNBOUNDED PRECEDING AND 1 preceding)) 
end as owe

But since owe doesn't exist when I made it, I get:

owe doesn't exist.

Is there some other way?

Answer 1

You cannot do that with window functions. Your only chance using SQL is a recursive CTE:

WITH RECURSIVE tab_owe AS (
   SELECT amount, type, app,
          CASE WHEN type = 'a'
               THEN app
               ELSE GREATEST(app, 0)
          END AS owe
   FROM tab
   ORDER BY amount LIMIT 1
UNION ALL
   SELECT t.amount, t.type, t.app,
          CASE WHEN t.type = 'a'
               THEN t.app - sum(tab_owe.owe)
               ELSE GREATEST(t.app - sum(tab_owe.owe), 0)
          END AS owe
   FROM (SELECT amount, type, app
         FROM tab
         WHERE amount > (SELECT max(amount) FROM tab_owe)
         ORDER BY amount
         LIMIT 1) AS t
      CROSS JOIN tab_owe
   GROUP BY t.amount, t.type, t.app
)
SELECT amount, type, app, owe
FROM tab_owe;

(untested)

This would be much easier to write in procedural code, sou consider using a table function.

Answer 2

This is what I came up with. Of course, I'm not a real programmer, so I'm sure there's a smarter way:

    insert into mort (amount, "type", app)
values
(1,'a',10),
(2,'a',8),
(3,'a',20),
(4,'i',30),
(5,'a',40)
CREATE OR REPLACE FUNCTION mort_v ()
  RETURNS TABLE (
     zamount int,
     ztype text,
       zapp int,
       zowe double precision
) AS $$
DECLARE
   var_r record;
    charlie double precision;
    sam double precision;
BEGIN
  charlie = 0;
  FOR var_r IN(SELECT
                 amount,
                 "type",
                app

               FROM mort order by 1)
    LOOP
       zamount = var_r.amount;
     ztype = var_r.type;
                zapp = var_r.app;
                sam = var_r.app - charlie;

                if ztype = 'a' then
                zowe = sam;
                else
                zowe = greatest(sam, 0);
                end if;
                charlie = charlie + zowe;
       RETURN NEXT;
  END LOOP;
END; $$
LANGUAGE 'plpgsql';
select * from mort_v()

So with my limited skills you'll notice I had to add a 'z' in front of the columns that are already in the table so I can spit it out again. If your table has 30 columns you'd normally have to do this 30 times. But, I asked a real engineer and he mentioned that if you just spit out the primary key with the calculated column, you can just join it back to the original table. That's smarter than what I have. If there's an even better solution, that would be great. This does serve as a nice reference to how to do something like a cursor in postgre and how to make variables without a '@' in front like in mssqlserver.