with t2 as (
select *,
lead(`start`) over (partition by name order by `end_time`) as next_start from your_table
)
,t3 as (
select name,start_time,end_time
from t2 where `end` != next_start or next_start is null
)
,t4 as (select * ,lag(`end_time`) over (partition by name order by `end_time`) as pre_end_time
from t3)
select name,step_end_time,
min(`start`) `start`,max(`end`) `end`,
min(start_time) start_time,max(end_time) end_time
from
(
select t2.*,step_end_time from
(
select name,end_time step_end_time,if(pre_end_time is null,'1970-01-01 00:00:00',pre_end_time) step_start_time from t4
)step
join
t2 on
step.name =
t2.name and t2.end_time <= step_end_time and t2.start_time > step_start_time
)tmp
group by name,step_end_time
order by name,step_end_time
小试一把，不知道行不行
hpkaiq 最近的时间轴更新
Select Language