第一列等于1的不查询。
查询第一行等于3的,和上一行也等于3的,(就是找到第一个出现3的行),这两个等于3的行之间不能有等于1的并且相隔年份大于一年。
用模式匹配sql加开窗函数
with t(dt,sign_in) as(
select date'2022-03-01',3 from dual union all
select date'2022-02-16',3 from dual union all
select date'2021-07-19',3 from dual union all
select date'2021-06-29',3 from dual union all
select date'2021-04-26',null from dual union all
select date'2021-02-23',3 from dual union all
select date'2021-02-10',null from dual union all
select date'2020-06-29',1 from dual union all
select date'2020-02-16',3 from dual union all
select date'2019-07-19',3 from dual union all
select date'2019-06-29',3 from dual union all
select date'2019-04-26',null from dual union all
select date'2019-02-23',3 from dual union all
select date'2019-02-10',null from dual union all
select date'2018-06-29',1 from dual
), t2 as (
select * from t where sign_in is not null),
t3 as (
select dt,sign_in,cnt,lag(dt,case when cnt=0 then 0 else cnt-1 end) over(order by dt) lg from t2
match_recognize(
order by dt
measures count(decode(sign_in,1,null,1)) cnt
ALL ROWS PER MATCH
pattern(a* b)
define a as sign_in = next(sign_in)
))
select * from t3
where dt-lg>=365
;
先把空行都剔除,然后模式匹配进行连续统计,发现有1的时候断开,重新进行统计,借用lag开窗函数获取上n行的值,这个n就是连续统计值减1.
至于模式匹配,这玩意解释起来太费劲了,看看这篇翻译文档吧(不过估计你也看着头疼。。。)
Oracle 12c 的新功能:模式匹配查询 - Oracle开发 - ITPUB论坛-专业的IT技术社区
Oracle 12c 的新功能:模式匹配查询 ,ITPUB论坛-专业的IT技术社区
http://www.itpub.net/forum.php?mod=viewthread&tid=2057442&page=1&extra=#pid23315171