题目:力扣
方法一:
- 将两表union,打上标记;
- 用行号与date相减的时间作为标记,标记一致的为连续的;
- 展示即可
select period_state,start_date,end_date
from(
select type as period_state,diff, min(date) as start_date, max(date) as end_date
from
(
select type, date, subdate(date,row_number()over(partition by type order by date)) as diff,
row_number()over(partition by type order by date) as rn
from
(
select 'failed' as type, fail_date as date from Failed
union all
select 'succeeded' as type, success_date as date from Succeeded
) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
) t
order by start_date
select period_state, min(date) start_date, max(date) end_date
from
(select period_state,date,date_sub(date,interval row_number()over(partition by period_state order by date) day) cnt from
(select fail_date date,'failed' period_state from failed
union select success_date,'succeeded' period_state from succeeded) t
where date like'2019-%')c
group by period_state,cnt
order by start_date
方法二:with函数
with tb as (select 'failed' as state, fail_date as all_date from failed
union
select 'succeeded' as state, success_date as all_date from succeeded)
select state as period_state, min(all_date) as start_date, max(all_date) as end_date
from (select *, rank() over(order by all_date)- rank() over (partition by state order by all_date) as diff from tb where all_date between '2019-01-01' and '2019-12-31') as temp
group by state, diff
order by start_date