mysql - SQL查询以找到正确的引擎运行对

Question

我在现有表中获取数据，其中包含事件 (a) 和非事件 (i) 等事件。它类似于记录组件是否处于事件状态。由于旧界面，没有正确的组件对。

Hier 是简短的示例数据库

"id" "component_number" "timestamp" "status"
"1" "1" "2020-05-10 16:30:00" "A"
"2" "1" "2020-05-18 16:34:05" "A"
"3" "1" "2020-05-19 16:36:01" "I"
"4" "1" "2020-05-19 16:36:52" "A"
"5" "1" "2020-05-19 16:38:57" "I"
"6" "2" "2020-05-11 17:04:50" "A"
"7" "2" "2020-05-15 10:00:00" "A"
"8" "2" "2020-05-16 11:25:16" "I"

例如，引擎 nr 1 于 2020 年 5 月 10 日 16:30:00 启动(事件)并在 2020 年 5 月 19 日 16:36:01 停止(非事件)。但是我在 2020-05-18 16:34:05 获得了一个额外的事件条目。

当引擎运转时，我必须找到正确的配对。这将在示例中:
2020-05-10 16:30:00 和 2020-05-19 16:36:01。该列表不仅包括一个引擎，还可以有更多。

我正在寻找一个查询字符串来获取正确的对(结果 1)或一个字符串来获取所需的事件(结果 2)。不知道什么更容易？

结果 1:

"component_number" "start" "end"
"1" "2020-05-10 16:30:00" "2020-05-19 16:36:01"
"1" "2020-05-19 16:36:52" "2020-05-19 16:38:57"
"2" "2020-05-11 17:04:50" "2020-05-16 11:25:16"

结果 2:

"id" "component_number" "timestamp" "status"
"1" "1" "2020-05-10 16:30:00" "A"
"3" "1" "2020-05-19 16:36:01" "I"
"4" "1" "2020-05-19 16:36:52" "A"
"5" "1" "2020-05-19 16:38:57" "I"
"6" "2" "2020-05-11 17:04:50" "A"
"8" "2" "2020-05-16 11:25:16" "I"

我尝试了子查询并加入，但没有得到它的工作。有人知道如何处理它吗？

Answer 1

这是一个差距和孤岛问题。我会推荐lag()和一个窗口sum()定义组。基本上，每个 'A' 都会启动一个新组。前面有 'I' .

这为您提供了第一个结果集:

select 
    component_number, 
    min(timestamp) start_timestamp,
    max(timestamp) end_timestamp
from (
    select 
        t.*, 
        sum(case when status = 'A' and lag_status = 'I' then 1 else 0 end) 
            over(partition by component_number order by timestamp) grp
    from (
        select 
            t.*,
            lag(status) 
                over(partition by component_number order by timestamp) lag_status
        from mytable t
    ) t
) t
group by component_number, grp

第二个结果集需要较少的嵌套:

select id, component_number, timestamp, status
from (
    select 
        t.*,
        lag(status) 
            over(partition by component_number order by timestamp) lag_status
    from mytable t
) t
where status = 'I' or lag_status is null or lag_status = 'I'

Demo on DB Fiddle (玛丽亚数据库 10.3):

组件编号 |开始时间戳 | end_timestamp
---------------: | :----------------- | :-----------------
1 | 2020-05-10 16:30:00 | 2020-05-19 16:36:01
1 | 2020-05-19 16:36:52 | 2020-05-19 16:38:57
2 | 2020-05-11 17:04:50 | 2020-05-16 11:25:16

编号 |组件编号 |时间戳 |地位
-: | ---------------: | :----------------- | :-----
1 | 1 | 2020-05-10 16:30:00 |一种
3 | 1 | 2020-05-19 16:36:01 |一世
4 | 1 | 2020-05-19 16:36:52 |一种
5 | 1 | 2020-05-19 16:38:57 |一世
6 | 2 | 2020-05-11 17:04:50 |一种
8 | 2 | 2020-05-16 11:25:16 |一世