Keep track of active objects over time

Question

I have a table of events (with id used as event id) like below (see also SQL fiddle here):

CREATE TABLE ext (
  key INT,
  id CHAR(1),
  pid INT,
  sid INT, 
  oid INT,
  event VARCHAR(3)
);

INSERT INTO ext (key, id, pid, sid, oid, event)
VALUES 
 (1, 'Q', 1, 81, 20, 'tsu'),
 (2, 'Q', 1, 81, 9, 'tsu'),
 (3, 'Q', 1, 81, 10, 'tsu'),
 (4, 'Q', 1, 81, 4, 'tsu'),
 (5, 'Q', 1, 81, 15, 'tsu'),
 (6, 'Q', 1, 81, 3, 'tsu'),
 (7, 'Q', 1, 81, 5, 'tsu'),
 (8, 'Q', 1, 81, 18, 'tsu'),
 (9, 'Q', 1, 81, 2, 'tsu'),
 (10, 'Q', 1, 81, 1, 'tsu'),
 (11, 'Q', 1, 81, 7, 'tsu'),
 (12, 'f', 2, 2, NULL, 's'),
 (13, 'Z', 2, 871, NULL, 'e'),
 (14, 'm', 3, 872, 2, 'pof'),
 (15, 's', 3, 873, 31, 'pom'),
 (16, 'R', 3, 874, 15, 'fc'),
 (17, 'R', 3, 874, 1, 'fc'),
 (18, 'R', 3, 874, 31, 'fc'),
 (19, 'R', 3, 874, 9, 'fc'),
 (20, 'R', 3, 874, 10, 'fc'),
 (21, 'R', 3, 874, 4, 'fc'),
 (22, 'R', 3, 874, 7, 'fc'),
 (23, 'R', 3, 874, 3, 'fc'),
 (24, 'R', 3, 874, 5, 'fc'),
 (25, 'R', 3, 874, 18, 'fc'),
 (26, 'R', 3, 874, 20, 'fc'),
 (27, 'k', 3, 876, NULL, 's'),
 (28, 'a', 3, 950, 31, 'rco'),
 (29, 'y', 3, 1285, 7, 'pof'),
 (30, 'N', 3, 1286, 22, 'pom'),
 (31, 'i', 3, 1299, 1, 'fc'),
 (32, 'i', 3, 1299, 5, 'fc'),
 (33, 'i', 3, 1299, 3, 'fc'),
 (34, 'i', 3, 1299, 20, 'fc'),
 (35, 'i', 3, 1299, 4, 'fc'),
 (36, 'i', 3, 1299, 9, 'fc'),
 (37, 'i', 3, 1299, 10, 'fc'),
 (38, 'i', 3, 1299, 22, 'fc'),
 (39, 'i', 3, 1299, 15, 'fc'),
 (40, 'i', 3, 1299, 18, 'fc'),
 (41, 'I', 3, 1407, 9, 'pof'),
 (42, 'T', 3, 1408, 19, 'pom'),
 (43, 'u', 3, 1575, 4, 'pof'),
 (44, 'V', 3, 1576, 30, 'pom'),
 (45, 'B', 3, 2019, NULL, 'e'),
 (46, 'h', 4, 60, NULL, 'e');

The events are ordered according to pid and sid columns (you can view them, for example, as days and hours respectively in the sense that pid is bigger time unit than sid so you should order first by pid and then by sid to obtain correct order). As you can see, some events (event = tsu or event = fc) have more than one row as they refer to many objects (oid), some refer to only one object (as the event of id = m), and some have only one row, but they actually refer to all previously observed object which are not 'dead'. They are some additional rules:

an object is dead when they hit the pof or rco event (as stated in event column)
when the object hits the pof event, there is another object that replaces it marked with pom event

I need to keep track of currently active objects. Therefore I'd like to "cross join" the rows where oid is NULL with all the active objects that can be deduced from the previous events, where by "cross join" I mean to replicate the row with oid = NULL for every currently active oid.

As the logic may be hard to grasp from the text, I have prepared the expected output (available on SQL fiddle here):

CREATE TABLE intermediate_result (
  id CHAR(1),
  pid INT,
  sid INT, 
  oid INT,
  event VARCHAR(3)
);

INSERT INTO intermediate_result (id, pid, sid, oid, event)
VALUES 
 ('Q', 1, 81, 20, 'tsu'),
 ('Q', 1, 81, 9, 'tsu'),
 ('Q', 1, 81, 10, 'tsu'),
 ('Q', 1, 81, 4, 'tsu'),
 ('Q', 1, 81, 15, 'tsu'),
 ('Q', 1, 81, 3, 'tsu'),
 ('Q', 1, 81, 5, 'tsu'),
 ('Q', 1, 81, 18, 'tsu'),
 ('Q', 1, 81, 2, 'tsu'),
 ('Q', 1, 81, 1, 'tsu'),
 ('Q', 1, 81, 7, 'tsu'),
 ('f', 2, 2, 20, 's'),
 ('f', 2, 2, 9, 's'),
 ('f', 2, 2, 10, 's'),
 ('f', 2, 2, 4, 's'),
 ('f', 2, 2, 15, 's'),
 ('f', 2, 2, 3, 's'),
 ('f', 2, 2, 5, 's'),
 ('f', 2, 2, 18, 's'),
 ('f', 2, 2, 2, 's'),
 ('f', 2, 2, 1, 's'),
 ('f', 2, 2, 7, 's'),
 ('Z', 2, 871, 20, 'e'), 
 ('Z', 2, 871, 9, 'e'),
 ('Z', 2, 871, 10, 'e'),
 ('Z', 2, 871, 4, 'e'),
 ('Z', 2, 871, 15, 'e'),
 ('Z', 2, 871, 3, 'e'),
 ('Z', 2, 871, 5, 'e'),
 ('Z', 2, 871, 18, 'e'),
 ('Z', 2, 871, 2, 'e'),
 ('Z', 2, 871, 1, 'e'),
 ('Z', 2, 871, 7, 'e'),
 ('m', 3, 872, 2, 'pof'),
 ('s', 3, 873, 31, 'pom'),
 ('R', 3, 874, 15, 'fc'),
 ('R', 3, 874, 1, 'fc'),
 ('R', 3, 874, 31, 'fc'),
 ('R', 3, 874, 9, 'fc'),
 ('R', 3, 874, 10, 'fc'),
 ('R', 3, 874, 4, 'fc'),
 ('R', 3, 874, 7, 'fc'),
 ('R', 3, 874, 3, 'fc'),
 ('R', 3, 874, 5, 'fc'),
 ('R', 3, 874, 18, 'fc'),
 ('R', 3, 874, 20, 'fc'),
 ('k', 3, 876, 15, 's'),
 ('k', 3, 876, 1, 's'),
 ('k', 3, 876, 31, 's'),
 ('k', 3, 876, 9, 's'),
 ('k', 3, 876, 10, 's'),
 ('k', 3, 876, 4, 's'),
 ('k', 3, 876, 7, 's'),
 ('k', 3, 876, 3, 's'),
 ('k', 3, 876, 5, 's'),
 ('k', 3, 876, 18, 's'),
 ('k', 3, 876, 20, 's'),
 ('a', 3, 950, 31, 'rco'),
 ('y', 3, 1285, 7, 'pof'),
 ('N', 3, 1286, 22, 'pom'),
 ('i', 3, 1299, 1, 'fc'),
 ('i', 3, 1299, 5, 'fc'),
 ('i', 3, 1299, 3, 'fc'),
 ('i', 3, 1299, 20, 'fc'),
 ('i', 3, 1299, 4, 'fc'),
 ('i', 3, 1299, 9, 'fc'),
 ('i', 3, 1299, 10, 'fc'),
 ('i', 3, 1299, 22, 'fc'),
 ('i', 3, 1299, 15, 'fc'),
 ('i', 3, 1299, 18, 'fc'),
 ('I', 3, 1407, 9, 'pof'),
 ('T', 3, 1408, 19, 'pom'),
 ('u', 3, 1575, 4, 'pof'),
 ('V', 3, 1576, 30, 'pom'),
 ('B', 3, 2019, 1, 'e'),
 ('B', 3, 2019, 5, 'e'),
 ('B', 3, 2019, 3, 'e'),
 ('B', 3, 2019, 20, 'e'),
 ('B', 3, 2019, 30, 'e'),
 ('B', 3, 2019, 19, 'e'),
 ('B', 3, 2019, 10, 'e'),
 ('B', 3, 2019, 22, 'e'),
 ('B', 3, 2019, 15, 'e'),
 ('B', 3, 2019, 18, 'e'),
 ('h', 4, 60, 1, 'e'),
 ('h', 4, 60, 5, 'e'),
 ('h', 4, 60, 3, 'e'),
 ('h', 4, 60, 20, 'e'),
 ('h', 4, 60, 30, 'e'),
 ('h', 4, 60, 19, 'e'),
 ('h', 4, 60, 10, 'e'),
 ('h', 4, 60, 22, 'e'),
 ('h', 4, 60, 15, 'e'),
 ('h', 4, 60, 18, 'e');

The SQL version is PostgreSQL 9.5.

user_939860 · Answer

This is very hard and inefficient to solve with SQL alone. I think your best option is a procedural solution.

Here is an implementation in PL/pgSQL.

CREATE OR REPLACE FUNCTION f_expand_oid_null()
  RETURNS TABLE(_id CHAR(1), _pid int, _sid int, _oid int, _event varchar(3))
  LANGUAGE plpgsql AS
$func$
DECLARE
   _key   int;
   _pof   int;  -- remember for subsequent 'pom'
BEGIN
   -- hold set of "active object IDs
   CREATE TEMP TABLE tmp_active_objects(
     oid int PRIMARY KEY
   , pid int
   , sid int
   , key int
   ) ON COMMIT DROP;

-- loop table rows in order
   FOR _key, _id, _pid, _sid, _oid, _event IN
      SELECT e.key, e.id, e.pid, e.sid, e.oid, e.event
      FROM   ext e
      ORDER  BY e.pid, e.sid, e.key
   LOOP
      IF _oid IS NULL THEN
         -- expand to ordered set of active objects
         RETURN QUERY
         SELECT _id, _pid, _sid, a.oid, _event 
         FROM   tmp_active_objects a
         ORDER  BY a.pid, a.sid, a.key;  -- keep original order of events
         -- returns nothing if no active objects
      ELSE
         RETURN QUERY VALUES (_id, _pid, _sid, _oid, _event);
         
         CASE _event
         WHEN 'rco' THEN
            DELETE FROM tmp_active_objects WHERE oid = _oid;
         WHEN 'pof' THEN
            _pof = _oid;   -- remember for subsequent 'pom'
         WHEN 'pom' THEN
            UPDATE tmp_active_objects
            SET    oid = _oid
            WHERE  oid = _pof;
         ELSE
            -- upsert active objects
            INSERT INTO tmp_active_objects (oid, pid, sid, key)
            VALUES (_oid, _pid, _sid, _key)
            ON CONFLICT (oid) DO
            UPDATE
            SET    (pid, sid, key)
                 = (EXCLUDED.pid, EXCLUDED.sid, EXCLUDED.key);
         END CASE;
      END IF;
   END LOOP;
END
$func$;

db<>fiddle here

Explaining everything is even more work than writing it. There are a couple of assumptions I made and pitfalls to avoid. In particular, every 'pof' row must be followed by a 'pom' row immediately.

If you are not firm with PL/pgSQL, consider a paid consultant. Or implement in a procedural language of your choice.

user_15085559 · Answer

I was finally able to get into the SQLFiddle to see the problem correctly.

Main request should be restated to say that you would like to cross join active objects with null values for oid while retaining all rows for all non-null oid values

Understanding:

The oid value represents the object
From the result data, active objects are dependent on time so to calculate active objects, we will depend on particular time Row IDs which will represent time.
The table inserts are time-related and are going into the future with later rows representing future times
Question: The relevance of a pom object replacement in light of the main request is still not fully understood and didn't have any bearing on the solution.

Solution:

For the solution, there are 3 parts -

An auto-incremented field added to your ext table. You already added it as an INT and called it Key. This will be used as a unique TimeID

With DistinctTimeIDs as
(
  select a.key as TimeID from ext a
)

Active objects - History does matter. In this case, we want all non-null oids before a particular TimeID without pof or rco events. This will for each TimeID in the dataset, list all Active oids before that TimeID.

ActiveObjects as 
( 
  select b.TimeID, a.oid from ext a inner join DistinctTimeIDs b on b.TimeID>=a.key
  where oid is not null 
  and not exists (select 'x' from ext where key



Null object value rows

With NullObjectValues as 
(
  select * from ext a 
  where a.oid is null 
)

Putting it all together, we will merge non-null object rows with null object rows cross joined with active objects for each TimeID
With DistinctTimeIDs as
(
  select a.key as TimeID from ext a
), ActiveObjects as 
( 
  select b.TimeID, a.oid from ext a inner join DistinctTimeIDs b on b.TimeID>=a.key
  where oid is not null 
  and not exists (select 'x' from ext where key

SQL Fiddle is http://sqlfiddle.com/#!17/2fb57/2

Keep track of active objects over time

2 Answers