with tab1 as (
select '01.01.2014' as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select '02.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '03.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '04.01.2014' as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select '05.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '06.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '07.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '08.01.2014' as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select '09.01.2014' as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select '10.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '11.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
select '12.01.2014' as DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
select '13.01.2014' as DAY_ID, '000032' as ARTICLE, 0 as QTY from dual
)
select DAY_ID,ARTICLE, QTY,
(count(1) OVER (PARTITION BY QTY, tmp order by DAY_ID)) as streak
from
(
select DAY_ID,ARTICLE,QTY,
(
rownum - (SUM(1) OVER (PARTITION BY QTY order by DAY_ID))
) AS tmp
from tab1
order by DAY_ID
)
order by DAY_ID
;
DAY_ID ARTICLE QTY STREAK
01.01.2014 000032 1 1
02.01.2014 000032 0 1
03.01.2014 000032 0 2
04.01.2014 000032 1 1
05.01.2014 000032 0 1
06.01.2014 000032 0 2
07.01.2014 000032 0 3
08.01.2014 000032 1 1
09.01.2014 000032 1 2
10.01.2014 000032 0 1
11.01.2014 000032 0 2
12.01.2014 000032 1 1
13.01.2014 000032 0 1
