Question 214358 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there a Postgres SQL function that allows me to store the result of each row to be used in the next row?

I have the following table in PostgresSQL, and I would like to find a function or logic to store the result of the first row in a new column to be used in the second row and so on.

The formula is (stock + pending-sells), so for the first row, the result is "2," and that should become my new stock value for the second row. Therefore, the result is 2 + 0 + 0 = 2. For the next row (row 3), my new stock is (2 + 1 – 0), resulting in 3, and the same with the following rows.

Is there a way to achieve this with SQL, or do I need to try outside with Python?

idx date ID stock pending sells
0 2023-07-01 187760 1 1 0
1 2023-08-01 187760 1 0 0
2 2023-09-01 187760 1 1 0
3 2023-10-01 187760 1 0 1
4 2023-11-01 187760 1 0 0
5 2023-12-01 187760 1 0 0

I tried with lag() and lead() with no luck so far.

The "expected result" column with the formula (stock+pending-sells) for this table

idx date ID stock pending sells expected_results
0 2023-07-01 187760 1 1 0 2
1 2023-08-01 187760 2 0 0 2
2 2023-09-01 187760 2 1 0 3
3 2023-10-01 187760 3 0 1 2
4 2023-11-01 187760 2 0 0 2
5 2023-12-01 187760 2 0 0 2

The "expected result" column is calculated by considering the result of the previous row’s calculation as the new stock for the next row.

I tried with this

with cte_ as
(
SELECT 
nc.date,
nc.id,
RANK() OVER (PARTITION BY nc.id ORDER BY nc.date ASC) AS ranking,
nc.stock,
nc.pending,
nc.sells,
nc.stock + nc.pending - nc.sells AS formula
FROM test_table nc 
where nc.id='187760'
)
select *,
lag(c.formula) OVER (PARTITION BY c.id ORDER BY c.date),
lag(c.formula) OVER (PARTITION BY c.id ORDER BY c.date)+c.pending-c.sells as test
from cte_ as c

But the result column is

results
NULL
2
2
1
0

Thank you.

Best regards.

Tags:

2

Answers


  1. 0

    The following query returns the results as shown in the original post:

    WITH t(idx, date, id, stock, pending, sells) AS (
  VALUES (0, '2023-07-01'::date, 187760, 1, 1, 0),
         (1, '2023-08-01'::date, 187760, 1, 0, 0),
         (2, '2023-09-01'::date, 187760, 1, 1, 0),
         (3, '2023-10-01'::date, 187760, 1, 0, 1),
         (4, '2023-11-01'::date, 187760, 1, 0, 0),
         (5, '2023-12-01'::date, 187760, 1, 0, 0))
SELECT idx, date, id, stock, pending, sells,
       stock + sum(pending) OVER stock_group - sum(sells) OVER stock_group AS expected_results
  FROM t
  WINDOW stock_group AS (PARTITION BY id ORDER BY idx)
  ORDER BY idx;

    This query uses sum as a window function with an implicit window frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to aggregate pending and sells to be added the current stock.

    This query addresses the revised post:

    WITH t(idx, date, id, stock, pending, sells) AS (
  VALUES (0, '2023-07-01'::date, 187760, 1, 1, 0),
         (1, '2023-08-01'::date, 187760, 1, 0, 0),
         (2, '2023-09-01'::date, 187760, 1, 1, 0),
         (3, '2023-10-01'::date, 187760, 1, 0, 1),
         (4, '2023-11-01'::date, 187760, 1, 0, 0),
         (5, '2023-12-01'::date, 187760, 1, 0, 0))
SELECT idx, date, id,
       stock + coalesce(sum(pending) OVER stock_group_prior - sum(sells) OVER stock_group_prior, 0) AS stock,
       pending, sells,
       stock + coalesce(sum(pending) OVER stock_group_current - sum(sells) OVER stock_group_current, 0) AS expected_results
  FROM t
  WINDOW stock_group_prior AS (PARTITION BY id ORDER BY idx ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
         stock_group_current AS (PARTITION BY id ORDER BY idx)
  ORDER BY idx;
    Login or Signup to reply.
  2. 0

    It makes no sense to do simple arithmetic calculations like this outside of the database.

    It is wrong to have the stock column populated with the value 1 in your table. If you are recalculating stock based solely on movements, then you need a starting value that you apply your movements to. Most systems use the current (perpetual) inventory as the starting point and calculate backwards.

    I used it as the initial stock level with first_value().

    select idx, date, id, 
       coalesce(
         first_value(stock) over w1 
           + sum(pending) over w1
           - sum(sells) over w1
         , stock) as stock,
       pending, sells,
       first_value(stock) over w2 
         + sum(pending) over w2
         - sum(sells) over w2 as expected_result
  from test_table
window w1 as (partition by id order by date
              rows between unbounded preceding and 1 preceding),
       w2 as (partition by id order by date);

    Working fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search