查找 B 类和 C 类之间发生的所有 A 类日志事件

发布于 2024-12-22 13:07:55 字数 841 浏览 1 评论 0原文

我正在尝试找到一种好方法来查找事件类型 B 和 C 之间同时发生的所有类型 A 事件。

此外，事件 B 和 C 必须共享 process_id。

每个 process_id 组将有一个 B 事件和一个 C 事件。

表结构如下所示：

CREATE TABLE IF NOT EXISTS `eventlog` (  
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
  `time` datetime NOT NULL,  
  `process_id` varchar(20) NOT NULL,  
  `event` varchar(25) DEFAULT NULL,  
  `data` varchar(45) DEFAULT NULL,  
)

我的尝试如下所示：

SELECT q3.time, q3.event, q3.process_id, q3.data   
FROM `eventlog` as q1, `eventlog` as q2, `eventlog` as q3 
WHERE q1.process_id=q2.process_id AND q1.process_id=q3.process_id 
AND q1.event='EVENTB' AND q2.event='EVENTC' AND q3.event='EVENTA'  
AND q3.time BETWEEN q1.time AND q2.time

当我运行此命令时，它会挂起。关于更有效或固定的方法来做到这一点有什么想法吗？

谢谢！

原文

I am trying to find a nice way to find all events of type A that occur at a time between event types B and C.

Also, events B and C must share a process_id.

Each process_id group will have one B event and one C event.

Table structure looks like this:

CREATE TABLE IF NOT EXISTS `eventlog` (  
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
  `time` datetime NOT NULL,  
  `process_id` varchar(20) NOT NULL,  
  `event` varchar(25) DEFAULT NULL,  
  `data` varchar(45) DEFAULT NULL,  
)

My attempt looked like this:

SELECT q3.time, q3.event, q3.process_id, q3.data   
FROM `eventlog` as q1, `eventlog` as q2, `eventlog` as q3 
WHERE q1.process_id=q2.process_id AND q1.process_id=q3.process_id 
AND q1.event='EVENTB' AND q2.event='EVENTC' AND q3.event='EVENTA'  
AND q3.time BETWEEN q1.time AND q2.time

When I run this, it hangs. Any thoughts on a more efficient, or fixed way to do this?

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情丝乱 2024-12-29 13:07:55

您可以使用分组依据来获取每个 process_id 的开始和结束时间。然后，这可以用于连接回事件日志表并选择所需的记录。

SELECT * FROM eventlog e

JOIN (
    SELECT 
        process_id, 
        MIN(CASE event WHEN 'EVENTB' THEN time END) start_time,
        MAX(CASE event WHEN 'EVENTC' THEN time END) end_time
    FROM eventlog
    GROUP BY process_id
) t
    ON e.time BETWEEN t.start_time AND t.end_time

WHERE
    e.event = 'EVENTA'

You can use a group by to get start and end times for each process_id. This can then be used to join back to the eventlog table and select the required records.

SELECT * FROM eventlog e

JOIN (
    SELECT 
        process_id, 
        MIN(CASE event WHEN 'EVENTB' THEN time END) start_time,
        MAX(CASE event WHEN 'EVENTC' THEN time END) end_time
    FROM eventlog
    GROUP BY process_id
) t
    ON e.time BETWEEN t.start_time AND t.end_time

WHERE
    e.event = 'EVENTA'

回复收藏 0 原文

~没有更多了~