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!
CodePudding user response:
You can use window functions.
In the cte list
, we first calculate :
- the resulting quantity of pid for each row in table
simple
astotal
- 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
, CASE
WHEN event = 'start'
THEN tstzrange( time
, first_value(time)
OVER (PARTITION BY pid
ORDER BY time ASC
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
)
)
ELSE null :: tstzrange
END AS time_interval
FROM simple
)
SELECT time, min(total)
FROM simple AS s
LEFT JOIN list AS l
ON l.time_interval @> s.time
GROUP BY time
ORDER BY time DESC
You can check the result here.
CodePudding user response:
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
CodePudding user response:
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