with tmp1 as (    select         t1.day        ,t1.value        ,row_number() over(order by t1.day) as rn    from (        select '2015-01-01' as day, 1 as value union all        select '2015-02-01' as day, 1 as value union all        select '2015-05-01' as day, 1 as value union all        select '2015-08-01' as day, 2 as value union all        select '2015-11-21' as day, 2 as value union all        select '2016-01-21' as day, 3 as value union all        select '2016-05-21' as day, 3 as value union all        select '2016-08-21' as day, 5 as value union all        select '2016-11-21' as day, 6 as value    ) t1) -- 将记录按日期排序生成次序列,tmp2 as (    select         case when t2.day is null then '0001-01-01' else t1.day end as day        ,t1.value        ,row_number() over(order by case when t2.day is null then '0001-01-01' else t1.day end) as rn    from tmp1 t1    left join tmp1 t2        on t1.rn = (t2.rn + 1)    where nvl(t1.value,'') <> nvl(t2.value,'')    order by day) -- 去重后并再次排序的数据，逻辑:按序列+1进行自关联，并把关联后两个值相等的记录剔除，然后再次按时间排序，生成新的次序列select     t1.day                                as start_day    ,nvl(date_add(t2.day,-1),'2999-12-31') as end_day    ,t1.value                              as change_valuefrom tmp2 t1left join tmp2 t2    on t1.rn = (t2.rn - 1);

 

+-------------+-------------+---------------+--+|  start_day  |   end_day   | change_value  |+-------------+-------------+---------------+--+| 0001-01-01  | 2015-07-31  | 1             || 2015-08-01  | 2016-01-20  | 2             || 2016-01-21  | 2016-08-20  | 3             || 2016-08-21  | 2016-11-20  | 5             || 2016-11-21  | 2999-12-31  | 6             |+-------------+-------------+---------------+--+