去重记录并做成拉链表

 

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_value
from tmp2 t1
left 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             |
+-------------+-------------+---------------+--+