How to calculate running minimum based on dynamic criteria?

user_15150264 0
Viewed 69

I have the below table.

         time        | pid  | qty | event 
---------------------+------+-----+-------
 2021-11-27 16:15:35 | 2207 |  1  | start  
 2021-11-27 16:15:12 | 2206 |  1  | stop   
 2021-11-27 16:00:11 | 2207 |  2  | stop   
 2021-11-27 15:51:43 | 2206 |  1  | start  
 2021-11-27 15:46:49 | 2206 |  4  | stop   
 2021-11-27 15:42:47 | 2206 |  4  | start  
 2021-11-27 15:41:36 | 2206 |  1  | stop   
 2021-11-27 15:41:29 | 2208 |  3  | start  
 2021-11-27 15:41:15 | 2207 |  2  | start  
 2021-11-27 15:39:58 | 2206 |  1  | start

which can be created with:

CREATE TABLE simple (
    time  TIMESTAMPTZ UNIQUE NOT NULL,
    pid   BIGINT,
    qty   BIGINT,
    event TEXT
);
INSERT INTO simple VALUES
    ('2021-11-27 16:15:35' , 2207 ,  1  , 'start'),
    ('2021-11-27 16:15:12' , 2207 ,  1  , 'stop '),  
    ('2021-11-27 16:00:11' , 2207 ,  2  , 'stop '),  
    ('2021-11-27 15:51:43' , 2206 ,  1  , 'start'),  
    ('2021-11-27 15:46:49' , 2206 ,  4  , 'stop '),  
    ('2021-11-27 15:42:47' , 2206 ,  4  , 'start'),  
    ('2021-11-27 15:41:36' , 2206 ,  1  , 'stop' ), 
    ('2021-11-27 15:41:29' , 2208 ,  3  , 'start'),  
    ('2021-11-27 15:41:15' , 2207 ,  2  , 'start'),  
    ('2021-11-27 15:39:58' , 2206 ,  1  , 'start');

At the timestamp of each row, I would like to calculate the minimum qty across all live (non-stopped) start events up until that row, which looks like the below:

         time        | pid  | qty | event | min 
---------------------+------+-----+-------+-----
 2021-11-27 16:15:35 | 2207 |  1  | start | 1 -- 2207 min pid again
 2021-11-27 16:15:12 | 2206 |  1  | stop  | 3 -- 2208 min pid, only one not stopped
 2021-11-27 16:00:11 | 2207 |  2  | stop  | 1
 2021-11-27 15:51:43 | 2206 |  1  | start | 1 -- 2206 min pid again
 2021-11-27 15:46:49 | 2206 |  4  | stop  | 2
 2021-11-27 15:42:47 | 2206 |  4  | start | 2 
 2021-11-27 15:41:36 | 2206 |  1  | stop  | 2  -- 2206 stopped, now 2207 is min pid
 2021-11-27 15:41:29 | 2208 |  3  | start | 1  
 2021-11-27 15:41:15 | 2207 |  2  | start | 1  -- min pid is still 2206
 2021-11-27 15:39:58 | 2206 |  1  | start | 1  -- first

I've tried a variety of approaches and it seems like my best bet is to define a custom aggregating function, though I lack the technical expertise to do so. Any helpful pointers are welcome!

time-series postgresql sql
3 Answers

You can use a subquery:

select e.*, (select min(e1.qty) from events e1 where e1.time <= e.time and not exists 
    (select 1 from events e2 where e2.time <= e.time and e2.time >= e1.time and e2.pid = e1.pid and e2.event='stop')) 
from events e
user_73267380

You can use window functions.

In the cte list, we first calculate :

  • the resulting quantity of pid for each row in table simple as total
  • the range of time where this resulting quantity is valid and unchanged as time_interval

Then we join the cte list with the table simple while keeping the rows where the time_interval in list contains the time in simple, and we calculate the minimum value of total grouped by time of the simple table :

WITH list AS
(
SELECT sum(CASE WHEN event = 'start' THEN qty ELSE -qty END) 
           OVER (PARTITION BY pid 
                 ORDER BY time ASC 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS total
     , tstzrange( time
                , first_value(time) 
                      OVER (PARTITION BY pid
                            ORDER BY time ASC
                            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
                           )
                ) AS time_interval
  FROM simple
)
SELECT time, min(total)
  FROM simple AS s
  LEFT JOIN list AS l
    ON l.time_interval @> s.time
 WHERE l.total > 0
 GROUP BY time
 ORDER BY time DESC

You can check the result here.

user_80600170

here is another way :

select * from simple s1
cross join lateral ( 
   select min(sumqty) minqty from (
      select sum(case when event='start' then 1 else -1 end * qty) as sumqty
      from simple s2
      where s2.time <= s1.time
      group by pid
      ) t where sumqty > 0
) t order by time desc

db<>fiddle here

user_13674540
Related