Mutually-referencing fields in window functions

Question

I have a table that contains a list of (year,points) and an initial number of points (100 in this example). I want to list each year with the (running) amount of points consumed:

create table test (
    year int
    ,points int
)

INSERT INTO test
SELECT generate_series(2000,2004),generate_series(10,50,10);

The result should look like this:

year	points	available	consumed	remaining
2000	10	100	10	90
2001	20	90	20	70
2002	30	70	30	40
2003	40	40	40	0
2004	50	0	0	0

The available column is just COALESCE( LAG(remaining) OVER (ORDER BY year) ,100), consumed is calculated with something like LEAST(points,available) and remaining is available-consumed.

So my query could look like this but of course it is not valid because every field depends on another one:

WITH config AS (
    SELECT 100 AS initial
)
select year, points
    ,available
    ,consumed
    ,remaining
FROM test
CROSS JOIN config
CROSS JOIN LATERAL (
   SELECT LEAST(points,available) AS consumed
) y
CROSS JOIN LATERAL (
   SELECT available-consumed AS remaining
) z
CROSS JOIN LATERAL (
    SELECT COALESCE( LAG(remaining) OVER w ,initial) AS available
    WINDOW w AS (ORDER BY year)
) x

What is the proper way of writing such constructs ? Should I use SUM() OVER () to calculate some kind of running total?

userQZpQ · Answer

You can compute:

"available" field, subtracting 100 from a running sum, implemented using the SUM window function with ordering on the "points" field and frame specification hitting all preceding rows till current one (not included). The COALESCE function will take care of the difference at first row.
"remaining" field, equal to available - points. Since this value can become negative, you can use the GREATEST function to cap it to 0.
"consumed" field, equal to available - remaining

WITH added_available AS (
    SELECT *,
           100 - COALESCE(SUM("points") OVER(ORDER BY "points" ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS available
    FROM test
), added_remaining AS (
    SELECT *,
           GREATEST(available - points, 0) AS remaining
    FROM added_available
)
SELECT year_,
       points,
       available,
       available - remaining AS consumed,
       remaining
FROM added_remaining

Check the demo here.

Mutually-referencing fields in window functions

1 Answers