mysql - 单个查询以获取基于条件的事件和每个事件之后出现的单个事件？

Question

我们有一个存储事件的表（基于机器学习的警报）。每个警报都提供了一些关于未来可能发生的故障的信息。满足某些标准的警报是特殊的，计划维护是根据此类警报的信息安排的，以避免将来发生故障。

表架构类似于：

|id|fin|datetime|failure|
-------------------------
|1 |fin1|2016-04-02|0|
|2 |fin1|2016-05-02|1|
|3 |fin2|2017-05-02|0|
|4 |fin2|2017-05-12|0|
|5 |fin2|2017-05-01|1|
|6 |fin3|2017-12-01|1|

其中fin描述了失败或可能在不久的将来失败的部分，并且failure是二进制的，并指示事件是警报还是实际失败（我们需要在同一张表中）。

现在我们想要的是获得所有标记为计划维护的警报，以及每个此类警报之后的下一个故障（当然，与警报的 FIN 相同的 FIN）。

请注意，我们有ids根据另一个评估过程从另一个表中标记为计划维护的那些警报。您可以将其视为例如在列表中给出的planned_ids（例如，此列表可能[1,3]与上面的示例数据匹配）

现在我们通过在数据库中发出多个请求（但不是那么重要）来解决这个问题，首先通过使用例如，mysql获取所有标记为计划维护的事件（我们有这些事件）ids

select *
from events
where id in [planned_ids]
order by datetime asc -- we dont mind if this is ordered desc as well

然后对于每个这样的警报，我们会为同一 FIN（功能部分）获取下一个故障，该故障在该事件之后立即出现并且是故障。例如通过使用：

-- using $alert as kind of variable here to denote that the query
-- runs for each alert based on that same alert data i.e datetime and fin part
select *
from events
where fin=$alert.fin and datetime>=$alert.datetime and failure=1
order by datetime desc
limit 0,1

这行得通，但这是一种N+1问题。

我们可以在单个 sql 查询中为每个警报获取警报和单个下一个故障吗？

标签： mysqlsql

Answer 1

一个相关的子查询，也许：

select e.*,
       (select e2.id
        from events e2
        where e2.fin = e.fin and e2.datetime > e.datetime
        order by e2.datetime desc
        limit 1
       ) as next_event_id
from events e
where e.id in ( . . . )

如果您需要更多信息，可以将其用作子查询并连接回events表。

select p.*, nexte.*
from (select e.*,
             (select e2.id
              from events e2
              where e2.fin = e.fin and e2.datetime > e.datetime
              order by e2.datetime desc
              limit 1
             ) as next_event_id
      from events e
      where e.id in ( . . . )
     ) p left join
     events nexte
     on nexte.id = p.next_event_id;

根据有关相关查询性能的 MySQL 文档，它指出：

对于某些情况，会优化相关子查询。例如：

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

否则，它们效率低下并且可能很慢。将查询重写为连接可能会提高性能。

同样根据这篇文章：

我们已经证明，在某些情况下，相关子查询可能比批量聚合更好。在甲骨文。使用中小型数据集。在其他情况下，这是不正确的，因为 M 和 N 的大小，我们的两个算法复杂度变量增加，O(M log N) 将比 O(M + N) 差得多。

即使使用相关子查询来构造单个查询来获取我们的数据，似乎N+1仍然存在一种问题（尽管在性能方面不如N单独查询严重）。