To create a calculated column based on its previous value in SQL-CodePudding

I have a very difficult problem to solve and I've been at it for days. Our data warehouse is Redshift. This is easy for python and such, but building it in SQL is driving me nuts.

Sample database with week number, total replenishment (additional stock coming in), and estimated unit sold (the ideal forecasted units sold when having enough inventory) :

SELECT 'W1' AS weeknum, 0 AS replenish, 20 AS est_units_sold
UNION ALL (SELECT 'W2' AS weeknum, 0 AS replenish, 20 AS est_units_sold)
UNION ALL (SELECT 'W3' AS weeknum, 0 AS replenish, 20 AS est_units_sold)
UNION ALL (SELECT 'W4' AS weeknum, 50 AS replenish, 20 AS est_units_sold)
UNION ALL (SELECT 'W5' AS weeknum, 0 AS replenish, 20 AS est_units_sold)
UNION ALL (SELECT 'W6' AS weeknum, 0 AS replenish, 30 AS est_units_sold)
UNION ALL (SELECT 'W7' AS weeknum, 0 AS replenish, 30 AS est_units_sold)
UNION ALL (SELECT 'W8' AS weeknum, 30 AS replenish, 20 AS est_units_sold)
UNION ALL (SELECT 'W9' AS weeknum, 0 AS replenish, 20 AS est_units_sold);

The data looks like this

What I need to create is a column for the beginning inventory of each week, giving W1 beginning inventory (basically today's inventory), for example, 30 units.

Sodo code:

Week(n) inventory = Week(n-1) inventory - MIN(Week(n-1) inventory, Week(n-1) est_units_sold)   Week(n) replenish

The MIN(Week(n-1) inventory, Week(n-1) est_units_sold) part is about the actual units sold taking into account inventory, like if we have only 10 in the inventory and the ideal forecasted units sold is 20, we would only sell 10. What I am stuck on is when creating the inventory col, the formula has to refer to itself in the previous row. I couldn't get around this blocker.

Desired result:

CodePudding user response：

With the simplification of an integer weeknum (it can be solved with a string value like 'W?' also), you can do it with a recursive cte:

WITH RECURSIVE cte AS (
  SELECT *, 30 AS inv FROM data WHERE weeknum = 1
  UNION ALL
  SELECT d.*,
         c.inv - LEAST(c.inv, c.est_units_sold)   d.replenish
  FROM data d INNER JOIN cte c
  ON c.weeknum = d.weeknum - 1
)
SELECT * FROM cte;

See the demo.

For your sample data:

WITH RECURSIVE cte AS (
  SELECT *, 30 AS inv FROM data WHERE weeknum = 'W1'
  UNION ALL
  SELECT d.*,
         c.inv - LEAST(c.inv, c.est_units_sold)   d.replenish
  FROM data d INNER JOIN cte c
  ON SUBSTRING(c.weeknum, 2)::int = SUBSTRING(d.weeknum, 2)::int - 1
)
SELECT * FROM cte;

See the demo.

CodePudding user response：

To avoid going recursive which can be slow for large datasets you need to unroll the logic. Here's a solution that uses window functions to perform the query.

Set up:

create table test as (
  SELECT 'W1' AS weeknum, 0 AS replenish, 20 AS est_units_sold, 30 as inventory
UNION ALL (SELECT 'W2' AS weeknum, 0 AS replenish, 20 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W3' AS weeknum, 0 AS replenish, 20 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W4' AS weeknum, 50 AS replenish, 20 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W5' AS weeknum, 0 AS replenish, 20 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W6' AS weeknum, 0 AS replenish, 30 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W7' AS weeknum, 0 AS replenish, 30 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W8' AS weeknum, 30 AS replenish, 20 AS est_units_sold, null as inventory)
UNION ALL (SELECT 'W9' AS weeknum, 0 AS replenish, 20 AS est_units_sold, null as inventory)
);

The query would look like (left the intermediate calculations in the result so you can see the logic):

select *,
    30   tot_replen - tot_sold - 
        min(overage) over(order by weeknum rows unbounded preceding) as inventory
from ( select weeknum, replenish, est_units_sold,
    coalesce(sum(est_units_sold) over (order by weeknum rows between unbounded preceding and 1 preceding), 0) as tot_sold,
    sum(replenish) over(order by weeknum rows unbounded preceding) as tot_replen,
    least(coalesce( inventory, 
             sum(coalesce(inventory,0)) over (order by weeknum rows between unbounded preceding and 1 preceding) - 
             sum(est_units_sold) over (order by weeknum rows between unbounded preceding and 1 preceding)   
             sum(replenish) over(order by weeknum rows between unbounded preceding and 1 preceding)
            ), 0) as overage
from test) as sub
;