这是一个缺口和孤岛问题。我建议你 lag() 还有一扇Windows 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小提琴演示（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