Sample Header Ad - 728x90

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

0 votes
1 answer
51 views
For context: I am playing with a filling level sensor, a water tank and a watering pump and have the following problem: 1. I have an initial filling value for my tank. 2. 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. 3. Should that running sum get below a lower threshold, it gets "refilled" to an upper threshold for the refill. 4. From there, I want to keep subtracting until it hits the lower value again, then refill, etc. 5. I would like to sum up the refill amount over the series Something like the following: Example plot 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
Asked by user22827835 (1 rep)
Aug 26, 2024, 04:43 PM
Last activity: Sep 2, 2024, 03:49 PM