Блог

У меня есть таблица событий (с id использованием идентификатора события), как показано ниже (см. Также SQL fiddle здесь):

 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');
 

События упорядочены по pid sid столбцам и (вы можете просматривать их, например, как дни и часы соответственно в том смысле, что pid — это большая единица времени, чем sid, поэтому вы должны упорядочивать сначала по pid, а затем по sid, чтобы получить правильный порядок). Как вы можете видеть, некоторые события ( event = tsu или event = fc ) имеют более одной строки, поскольку они относятся ко многим объектам ( oid ), некоторые относятся только к одному объекту (как событие id = m ), а некоторые имеют только одну строку, но на самом деле они относятся ко всем ранее наблюдавшимся объектам, которые не являются «мертвыми». Это некоторые дополнительные правила:

• объект мертв, когда они попадают в pof rco событие или (как указано в event столбце)
• когда объект попадает в pof событие, его заменяет другой объект, помеченный pom как событие

Мне нужно отслеживать текущие активные объекты. Поэтому я хотел бы «перекрестно соединить» строки where oid NULL со всеми активными объектами, которые могут быть выведены из предыдущих событий, где под «перекрестным соединением» я подразумеваю репликацию строки с oid = NULL для каждого текущего активного oid .

Поскольку логику может быть трудно понять из текста, я подготовил ожидаемый результат (доступен на SQL fiddle здесь):

 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');
 

Версия SQL — PostgreSQL 9.5.

Это очень сложно и неэффективно решить с помощью одного SQL. Я думаю, что ваш лучший вариант — процедурное решение.

Вот реализация в 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<> скрипка здесь

Объяснять все — это даже больше работы, чем писать. Есть пара допущений, которые я сделал, и подводных камней, которых следует избегать. В частности, за каждой строкой ‘pof’ должна немедленно следовать строка ‘pom’.

Если вы не уверены в PL / pgSQL, подумайте о платном консультанте. Или реализовать на процедурном языке по вашему выбору.

Я, наконец, смог войти в SQLFiddle, чтобы правильно увидеть проблему.

Основной запрос следует переформулировать, чтобы указать, что вы хотели бы перекрестно присоединять активные объекты с нулевыми значениями для oid, сохраняя при этом все строки для всех ненулевых значений oid

Понимание:

1. Значение oid представляет объект
2. Исходя из результирующих данных, активные объекты зависят от времени, поэтому для вычисления активных объектов мы будем зависеть от конкретных идентификаторов временных строк, которые будут представлять время.
3. Вставки таблиц связаны со временем и переносятся в будущее, а последующие строки представляют будущие времена
4. Вопрос: Актуальность замены объекта pom в свете основного запроса все еще не до конца понятна и не имеет никакого отношения к решению.

Решение:

Решение состоит из 3 частей —

1. Автоматически увеличиваемое поле, добавленное в вашу таблицу ext. Вы уже добавили его как INT и назвали его Key . Это будет использоваться как уникальный TimeID

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

2. Активные объекты — история имеет значение. В этом случае нам нужны все ненулевые oid до определенного TimeID без событий pof или rco. Это приведет к тому, что для каждого TimeID в наборе данных будут перечислены все активные oid до этого 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<b.TimeID and event in ('pof', 'rco') and oid=a.oid) 
  and a.key = (select MAX(c.key) from ext c where c.key<b.TimeID and c.event not in ('pof', 'rco') and c.oid=a.oid)
)
 

3. Нулевые строки значений объекта

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

Собрав все это вместе, мы объединим ненулевые строки объектов с нулевыми строками объектов, перекрестно соединенными с активными объектами для каждого 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<b.TimeID and event in ('pof', 'rco') and oid=a.oid) 
  and a.key = (select MAX(c.key) from ext c where c.key<b.TimeID and c.event not in ('pof', 'rco') and c.oid=a.oid)
), NullObjectValues as 
(
  select * from ext a 
  where a.oid is null 
)
select a.key ,a.id,a.pid,a.sid,a.oid,a.event from ext a where a.oid is not null
union all
select a.key ,a.id,a.pid,a.sid,b.oid,a.event from NullObjectValues a CROSS JOIN ActiveObjects b
where b.TimeID = a.key 
order by 1;
 

SQL Fiddle — это http://sqlfiddle.com /#!17/2fb57/2