在同一张表上进行传递式SQL查询

Question

嘿，考虑下面的表格和数据...

in_timestamp | out_timestamp | name  | in_id | out_id | in_server | out_server | status
timestamp1   | timestamp2    | data1 |id1   | id2    | others-server1   | my-server1 | success
timestamp2   | timestamp3    | data1 | id2   | id3    | my-server1   | my-server2 | success
timestamp3   | timestamp4    | data1 | id3   | id4    | my-server2   | my-server3 | success
timestamp4   | timestamp5    | data1 | id4   | id5    | my-server3   | others-server2 | success

以下数据表示某些数据在服务器之间执行过程的日志。 例如，一些数据从“外部服务器1”流向一堆“我的服务器”，最终到达目标“其他服务器2”。

问题： 1）我需要以可呈现的形式将此日志提供给客户端，而他不需要了解有关一堆“我的服务器”的任何信息。 我只需要提供数据进入我的基础设施的时间戳和离开的时间戳； 深入以下信息。

in_timestamp (of 'others_server1' to 'my-server1')
out_timestamp (of 'my-server3' to 'others-server2')
name 
status

我想为相同的事情编写SQL！有人可以帮忙吗？ 注意：可能并不总是有3个“my-servers”。这取决于情况。例如，对于data2，可能涉及4个“my-server”！

2）是否有其他替代SQL的选择？我的意思是存储过程/等等？

3）优化？（记录数量非常庞大！目前每天约为500万条。我们应该显示一周内的记录。）

提前感谢您的帮助！：）

- MiKu

---

如果结果中存在多种状态，您如何定义“状态”？ - Mark Byers

'status' => 成功在同一行中提到的2个服务器之间传递数据。 :) 因此，如果整个路径成功，则为数据的总体成功交付。 :) - MiKu

Answer 1

WITH RECURSIVE foo AS
        (
        SELECT  *, in_timestamp AS timestamp1, 1 AS hop, ARRAY[in_id] AS hops
        FROM    log_parsing.log_of_sent_mails
        WHERE   in_server = 'other-server1'
        UNION ALL
        SELECT  t_alias2.*, foo.timestamp1, foo.hop + 1, 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  *
FROM    foo
ORDER BY
        hop DESC
LIMIT 1

- Quassnoi

Answer 2

你的表具有分层结构（邻接列表）。使用递归CTE，在PostgreSQL v8.4及更高版本中可以高效地查询。Quassnoi 写了一篇 博客文章 关于如何实现它。这是一个相当复杂的查询，但他用很类似你所需的示例很好地解释了它。特别是如果你看他的最后一个示例，他演示了一种查询方法，可以使用数组从第一个节点到最后一个节点获取完整路径。

- Mark Byers

Answer 3

• @其他读者：

  首先请参考Mark Byers发布的第一个答案。我使用“回答”而不是“评论”他的帖子，因为我需要使用表格/链接等，这在对答案进行评论时不可用。 :)

• @Mark Byers：

感谢您提供的链接...它真的帮助了我，我能够找到生成服务器之间路径的方法...看看我能做什么。

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}|

* 观察时间戳。这是必需的，因为我不希望客户了解内部基础设施。所以对于他来说，时间戳1和时间戳5之间的时间差才是重要的。

有什么线索可能可以实现它吗！？

p.s. 我也会尝试联系Quassnoi。 :)

- MiKu

Answer 4

一种解决方法——如果数据是稳定的（例如，一旦插入就不会改变），则可以在插入时通过计算传递关系（例如通过触发器或执行插入的应用程序）来动态生成。

例如，在您的表中有一个新列“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和状态等方面更加复杂，但仍然是一个简单的选择）

第二个选项当然是更直接的循环计算结果报告-如果您不确定如何进行SQL BFS实现，则可以在Google或“堆栈”（现在是否已经成为接受的动词？）中查找。

- DVK

---

第一选项：生成内存计算的问题在于这些日志都是在不同的“我的服务器”上生成的，但是这些“我的服务器”都存在于集中式数据库中。因此，我可以想象实时分布式计算，但我不是那么专业，无法尝试...数据库听起来对我来说有点可能..:D第二选项：同样，我想避免在应用程序级别执行此操作，原因是数据库中有大量数据。我想避免将所有这些数据加载到应用程序中，然后进行处理。 :)如果我误解了您或误解了您的评论，请纠正我！ :) - MiKu