Транзитивный SQL-запрос к той же таблице

Ваша таблица имеет иерархическую структуру (списки смежности). Это можно эффективно запрашивать в PostgreSQL версии 8.4 и более поздних версиях, используя рекурсивные CTE. Quassnoi написал запись в блоге о том, как это реализовать. Это довольно сложный запрос, который вам нужно написать, но он хорошо объясняет его примерами, очень похожими на то, что вам нужно. Особенно, если вы посмотрите на его последний пример, он демонстрирует запрос, чем получает полный путь от первого узла к последнему, используя массив.

Один из способов сделать это — если данные СТАБИЛЬНЫ (например, никогда не изменяются после вставки), — это вычислить переходные отношения НА ЛЕТУ (например, с помощью триггера или приложения, которое выполняет вставку) в момент времени. вставить время.

Например. у вас есть новый столбец "start_ts" в вашей таблице; при вставке записи:

in_timestamp | out_timestamp | name  | in_id | out_id | in_server | out_server | status
timestamp3   | timestamp4    | data1 | id3   | id4    | my-server2   | my-server3 | success

... тогда ваша логика автоматически находит запись с name=data1 и out_id=id3 и клонирует ее start_ts во вновь вставленную запись. Вам также может понадобиться некоторая специальная логика для распространения последнего статуса в зависимости от того, как вы вычисляете эти транзитивные значения.

Кстати, вам не обязательно искать предыдущую (name=data1 и out_id=id3) запись - вы можете сохранить значение start_ts в самих метаданных записи данных во время обработки.

Тогда окончательный отчет просто select start_ts, out_ts from T where out_server=others_server2 (конечно сложнее в плане out_server и статуса, но все же один простой выбор)

Второй вариант — это, конечно, более простой цикл, вычисляющий результирующий отчет — google или «stack» (теперь это общепринятый глагол?) для реализаций SQL BFS, если вы не знаете, как это сделать.

• @Другие читатели:

  Сначала обратитесь к 1-му ответу, опубликованному Марком Байерсом. Я использовал «ответ», а не «комментирование» его сообщения, так как мне нужно было использовать таблицы/ссылки и т. д., которые недоступны при комментировании ответов. :)

• @Марк Байерс:

Спасибо за ссылку... Это действительно помогло мне, и я смог выяснить, как сгенерировать путь между серверами... Посмотрите, что я смог сделать.

in_id   | in_timestamp  | out_timestmap | name  | hops_count    | path  |
id1     | timestamp1    | timestamp2    | data1 | 1             | {id1} |
id2     | timestamp2    | timestamp3    | data1 | 2             | {id1,id2} |
id3     | timestamp3    | timestamp4    | data1 | 3             | {id1,id2,id3} |
id4     | timestamp4    | timestamp2    | data1 | 4             | {id1,id2,id3,id4} |

* путь генерируется с использованием 'in_id'

Я использовал следующий запрос...

WITH RECURSIVE foo AS
        (
        SELECT  t_alias1, 1 AS hops_count, ARRAY[in_id] AS hops
        FROM    log_parsing.log_of_sent_mails t_alias1
        WHERE   in_server = 'other-server1'
        UNION ALL
        SELECT  t_alias2, foo.hops_count + 1 AS hops_count, hops || in_id
        FROM    foo
        JOIN    log_parsing.log_of_sent_mails t_alias2
        ON      t_alias2.in_id = (foo.t_alias1).out_id 
        )
SELECT  (foo.t_alias1).in_id,
        (foo.t_alias1).name,
        (foo.t_alias1).in_timestamp,
        hops_count,
        hops::VARCHAR AS path
FROM    foo   ORDER BY   hops

Но я еще не мог достичь конечной стадии. Вот что я хочу получить в конечном итоге...

in_id   | in_timestamp  | out_timestmap | name  | hops_count    | path  |
id4     | timestamp1    | timestamp5    | data1 | 4             | {id1,id2,id3,id4}|

* соблюдать временную метку. Это необходимо, так как я не хочу, чтобы клиент знал о внутренней инфраструктуре. Поэтому для него важна временная задержка между timestamp1 и timestamp5.

Любая подсказка, как, возможно, я мог бы достичь этого!?

p.s. Я бы также попытался связаться с Quassnoi. :)