保留由C升到B再升到A的过程，但是若是由A变到C或者B，只保留A

select  userid,level, time,
        first_value(time) OVER(PARTITION BY userid,level ORDER BY time) as firstTime,
        last_value(time) OVER(PARTITION BY userid,level ORDER BY time) as lastTime
         

from
(
select  userid,time,level,level_1_next,rn_asc,rn_desc,rn_1_time,level_1_next_1_last
        ,case when level_1_next_1_last<level then 1 else null end as drop
from
(
select  userid,time,level,level_1_next,rn_asc,rn_desc,rn_1_time
        ,LAG(level_1_next,1,level) OVER(PARTITION BY userid,level ORDER BY time) AS level_1_next_1_last
        
from
--rn_asc往下取一个
(select  userid,time,level,level_1_next,rn_asc,rn_desc
         ,LEAD(rn_asc) OVER(PARTITION BY userid ORDER BY time) AS rn_1_time
         
from
    (--level往下取一个,按照userid和level分组按时间正序和倒序排序
    select userid,time,level,
        LEAD(level) OVER(PARTITION BY userid ORDER BY time) AS level_1_next,
        row_number() OVER(PARTITION BY userid,level ORDER BY time) AS rn_asc,
        row_number() OVER(PARTITION BY userid,level ORDER BY time desc) AS rn_desc
    from lst_lag_test
    )a0
)a1
where  (rn_asc=1) or (level>level_1_next and level_1_next in ("A","B","C") and rn_1_time=1) or (rn_desc=1)
)b
)c
where drop is null
group by userid,level,time

表lst_lag_test内容如下：

userid	time	level
Mary	20180701	C
Mary	20180702
Mary	20180703	C
Mary	20180704	B
Mary	20180705	C
Mary	20180706	B
Mary	20180707	B
Mary	20180708	B
Mary	20180709	A
Mary	20180710	A
Mary	20180711	B
Mary	20180712	A
Mary	20180713	A
Mary	20180718	C
Mary	20180805	A
Mary	20180806	A
Mary	20180807	B
Mary	20180808	A
Mary	20180809	A
Mary	20180810	B
Mary	20180811	B
Mary	20180812	B
Mary	20180813	C
Mary	20180814	B
Mary	20180815	C
Mary	20180816
Mary	20180817	C
Mary	20180818	C
Peter	20180801	C
Peter	20180803	C
Peter	20180805
Peter	20180807	C
Peter	20180809	B
Peter	20180810	C
Peter	20180811	B
Peter	20180812	B
Peter	20180813	B
Peter	20180814	A
Peter	20180815	A
Peter	20180816	B
Peter	20180817	A
Peter	20180818	A
Tom	20180701	A
Tom	20180702
Tom	20180703	C
Tom	20180704	B
Tom	20180705	C
Tom	20180706	B
Tom	20180707	B
Tom	20180708	B
Tom	20180709	B
Tom	20180710	C
Tom	20180711	B
Tom	20180712	C
Tom	20180713	C
Tom	20180718	C

 

最上面的代码跑完后的结果：

userid	level	time	firsttime	lasttime
Mary		20180702	20180702	20180702
Mary		20180816	20180702	20180816
Mary	A	20180709	20180709	20180709
Mary	A	20180809	20180709	20180809
Mary	B	20180704	20180704	20180704
Mary	B	20180708	20180704	20180708
Mary	C	20180701	20180701	20180701
Peter		20180805	20180805	20180805
Peter	A	20180814	20180814	20180814
Peter	A	20180818	20180814	20180818
Peter	B	20180809	20180809	20180809
Peter	B	20180813	20180809	20180813
Peter	C	20180801	20180801	20180801
Peter	C	20180807	20180801	20180807
Tom		20180702	20180702	20180702
Tom	A	20180701	20180701	20180701
Tom	B	20180704	20180704	20180704
Tom	B	20180711	20180704	20180711
Tom	C	20180703	20180703	20180703