SQL query to find correct pairs of engine running

user_5810008 0
Viewed 55

I got data in an existing table with events like active (a) and inactive (i). Its something like logging if a component is active or inactive. Due to an old interface, there are no correct pairs of the component.

Hier is the short example database

"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"

For example, the engine nr 1 got started (active) 2020-05-10 16:30:00 and stopped at 2020-05-19 16:36:01 (inactive). But I got an additional entry with active at 2020-05-18 16:34:05.

I have to find the correct pairs, when the engine is running. This would be in the example: 2020-05-10 16:30:00 and 2020-05-19 16:36:01. The list includes not just one engine, there can be a lot more of them.

I'm looking for a query string to get the correct pair (Result 1) OR a string to just get the needed events (Result 2). I don't know what's more easy?

Result 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"

Result 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"

I tried around with sub queries and join but didn't get it working. Anybody got an idea or hint how to deal with it?

gaps-and-islands group-by date mysql sql
1 Answers

This is a gaps and island problem. I would recommend lag() and a window sum() to define the groups. Basically, a new group starts for every 'A' that is preceded by a 'I'.

This gives you the first resultset:

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

The second resultset requires less nesting:

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 (MariaDB 10.3):

component_number | start_timestamp     | 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

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
user_106767160
Related