请问怎么获得连续次数呢?
drop table test
/
create table test
(
id varchar2(20) not null,
day varchar2(20) not null,
value varchar2(20) not null
)
/
insert into test values('1', '20080309', 'b');
insert into test values('1', '20080306', 'b');
insert into test values('1', '20080305', 'b');
insert into test values('1', '20080304', 'a');
insert into test values('1', '20080303', 'a');
insert into test values('2', '20080309', 'b');
insert into test values('2', '20080306', 'b');
insert into test values('2', '20080305', 'a');
insert into test values('2', '20080304', 'a');
insert into test values('2', '20080303', 'a');
insert into test values('3', '20080309', 'a');
insert into test values('3', '20080306', 'b');
insert into test values('3', '20080305', 'b');
insert into test values('3', '20080304', 'a');
insert into test values('3', '20080303', 'a');
commit;
select id,count(*)
from (
select test.*
,row_number() over(partition by id order by day desc) rn
from test where value='b' and to_date(day,'yyyy-mm-dd')<=date '2008-03-30'
)
where to_date(day,'yyyy-mm-dd')+rn = date '2008-03-09' +1
group by id;
日期有间隔是因为周六周日没数据,想通过计算3月9日value=b的连续出现次数,得到数据如下:
id count(*)
1 1
2 1
请问应该怎么写sql