sql - 在 SQL 查询中选择相邻行

Question

以下是一个不太适合 RDBMS 的问题，我认为，但这就是我要处理的问题。

我正在尝试编写一个工具来搜索存储在数据库中的日志。有些行可能是:

Time             | ID | Object | Description 
2012-01-01 13:37 | 1  | 1      | Something happened
2012-01-01 13:39 | 2  | 2      | Something else happened
2012-01-01 13:50 | 3  | 2      | Bad
2012-01-01 14:08 | 4  | 1      | Good
2012-01-01 14:27 | 5  | 1      | Bad
2012-01-01 14:30 | 6  | 2      | Good

对象是一个外键。实际上，时间会随着 ID 的增加而增加，但这并不是一个实际的约束。实际上还有更多的领域。这是一个 Postgres 数据库 - 我也希望能够支持 SQLite，但我知道这很可能是不可能的。

现在，我希望能够对对象 2 发生的所有不良事件运行查询:

SELECT * FROM table WHERE Object = 2 AND Description = 'Bad';

但是查看结果周围的一些上下文行通常很有用 - 正如 grep 的 -C 选项在搜索文本日志时非常有用.对于上面的查询，如果我们想要在任一侧有一行上下文，除了第 3 行之外，我们还需要第 2 行和第 6 行。

如果原始查询返回多行，则需要检索更多上下文。请注意，上下文不是从与对象 1 关联的事件中检索的；我们只消除了对描述的限制。此外，所涉及的顺序以及由此决定什么与什么相邻的顺序是由时间场引起的。

这指定了我想要实现的目标，但相关的数据库相当大，至少与它运行的机器的能力相比是这样。

最常被引用的获取相邻行的解决方案要求您为每个结果运行一个额外的查询，我称之为基本查询；这不好，因为那可能有数千个查询。

我目前最不坏的解决方案是运行查询以检索可能是上下文的所有可能行的 ID - 在上面的示例中，这将是搜索与对象 2 相关的所有行。然后我得到匹配的 ID基本查询，扩展(使用所有可能 ID 的列表)到与基本查询或上下文匹配的行的 ID 列表，然后最终检索这些 ID 的数据。这有效，但不优雅且缓慢。从远程计算机使用该工具时速度特别慢，因为初始 ID 列表可能非常大，检索它然后仅通过 Internet 传输它可能是无节制的。

我尝试过的另一种解决方案是使用子查询或 View 来计算行的“缓冲区序列”。添加了该字段后的表格如下所示:

Time             | ID | Sequence | Object | Description 
2012-01-01 13:37 | 1  | 1        | 1      | Something happened
2012-01-01 13:39 | 2  | 1        | 2      | Something else happened
2012-01-01 13:50 | 3  | 2        | 2      | Bad
2012-01-01 14:08 | 4  | 2        | 1      | Good
2012-01-01 14:27 | 5  | 3        | 1      | Bad
2012-01-01 14:30 | 6  | 3        | 2      | Good

在此表上运行基本查询然后允许您通过添加或减去 Sequence 值来生成所需的 ID 列表。这消除了通过线路传输行负载的问题，但现在数据库必须运行这个复杂的子查询，而且速度慢得令人无法接受，尤其是在第一次运行时——给定用例，查询是零星的，缓存不是很有效.

如果我负责模式，我可能只是将这个字段存储在数据库中，但我不是，所以欢迎提出任何改进建议。谢谢!

Answer 1

您应该使用ROW_NUMBER 窗口函数

http://www.postgresql.org/docs/current/static/functions-window.html

邻接是一种抽象构造并依赖于显式排序(或 PARTITION OVER)...您是指具有前面时间戳的排序吗？

决定你如何决定你想要什么样的“相邻”，然后得到 ROW_NUMBER 超过那个标准。

一旦您拥有了，您只需JOIN 具有ROW_NUMBER +/- 1 的项目的每一行