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"

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

标签： mysqlsqldategroup-bygaps-and-islands

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'

DB Fiddle 上的演示（MariaDB 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 | 我