PostgreSQL window function: Add value in CASE running sum gets below threshold

Question

For context:

I am playing with a filling level sensor, a water tank and a watering pump and have the following problem:

I have an initial filling value for my tank.
Each time step, I am subtracting a certain amount (the outflow) from this init value via a window function, in order to get a time series with the filling level.
Should that running sum get below a lower threshold, it gets "refilled" to an upper threshold for the refill.
From there, I want to keep subtracting until it hits the lower value again, then refill, etc.
I would like to sum up the refill amount over the series

Something like the following:

Assuming I start from 10, and refill from 5 as lower limit to 8 as upper limit. The table should look something like this, with running sum being the amount of water in the tank, refill_amount as the amount that was needed to refill, and/or refill_running_sum to get a final, total amount for refilling:

 time_step | running_sum | refill_amount | refill_running_sum   |
 ----------+-------------+---------------+----------------------|      
      1          10             0                 0         
      2           9             0                 0         
      3           8             0                 0         
      4           7             0                 0         
      5           6             0                 0         
      6  -  -  -  5  -   -   -  3  -   -   -   -  3  -  --refill
      7           8             0                 3         
      8           7             0                 3         
      9           6             0                 3         
      10 -  -  -  5  -   -   -  3  -   -   -   -  6  -  --refill     
      11          8             0                 6        
      12          7             0                 6         
      13          6             0                 6         
      14 -  -  -  5  -   -   -  3  -   -   -   -  9  -  --refill     
      15          8             0                 9

I got as far as the SQL below dbfiddle here:

Using a CASE statement, I get to refill the amount once and get the first amount of water to refill. But I would need a CTE with a case for every single refill.

Apologies as I know I'm missing SQL vocabulary here to properly approach this. Yet thank you for any pointers!

    /* Generate some mockup series with init+threshold limits */ 
    WITH vals AS ( 
        SELECT 
            generate_series(1,30,1) AS time_step
            ,10 AS init_val
            ,8 AS upper_limit_refill
            ,5 AS lower_limit_refill
            ,random() AS random_val
    ), 
    /* Create the window function subtracting a mockup val */
    example_series AS ( 
        SELECT 
            *   
            ,init_val-sum(random_val) OVER(ORDER BY time_step ASC) AS running_amount
        FROM vals
    )
    SELECT 
        time_step
        ,running_amount
        /* I manage to get the amount needed to refill ONCE, but I'd need a new CTE and CASE for each refill like this*/
        ,CASE 
            WHEN running_amount < lower_limit_refill 
            THEN upper_limit_refill +  running_amount -sum(random_val) OVER(ORDER BY time_step ASC  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
            ELSE 0 
        END AS refill_once
        ,CASE 
            WHEN running_amount < lower_limit_refill 
            THEN upper_limit_refill - running_amount
            ELSE 0 
        END AS refill_to_limit_amount

    FROM example_series

user303861 · Accepted Answer · 2024-09-02 15:49:04Z

I don't know if window function could be used here. I'd go with a simple recursive :

 WITH vals AS ( 
   SELECT generate_series(1,30,1) AS time_step
         ,10.0 AS init_val
         ,8.0 AS upper_limit_refill
         ,5.0 AS lower_limit_refill
         ,(random())::numeric AS random_val
  ),
vCalc as (
   with recursive vRec as (
    select time_step, 
           init_val, 
           0::numeric refill,
           random_val,  
           init_val - random_val as final_val 
     from vals 
     where time_step = 1
    union all
      select v.time_step, 
            r.final_val as init_val, 
            case when r.final_val < lower_limit_refill then upper_limit_refill - r.final_val else 0 end refill, 
            v.random_val,  
            r.final_val - v.random_val + case when r.final_val < lower_limit_refill then upper_limit_refill - r.final_val else 0 end as final_val
    from vRec r
    inner join vals v on v.time_step = r.time_step + 1
)
select * from vrec
)
select * from vCalc;

Stack Exchange Network

PostgreSQL window function: Add value in CASE running sum gets below threshold

1 Answer 1

Hot Network Questions

PostgreSQL window function: Add value in CASE running sum gets below threshold

1 Answer 1

Related

Hot Network Questions