Как посчитать последовательность при помощи oracle?

Question

[zotovby]

Привет! у меня есть табличка, необходимо посчитать сколько раз подряд повторялось нулевое значение.

DAY_ID	ARTICLE	QTY
01.01.2014	000032	1
02.01.2014	000032	0
03.01.2014	000032	0
04.01.2014	000032	1
05.01.2014	000032	0
06.01.2014	000032	0
07.01.2014	000032	0
08.01.2014	000032	1
09.01.2014	000032	1
10.01.2014	000032	0
11.01.2014	000032	0
12.01.2014	000032	1
13.01.2014	000032	0

что бы получилось на выходе вот так

DAY_ID	ARTICLE	QTY	streak
01.01.2014	000032	1	
02.01.2014	000032	0	1
03.01.2014	000032	0	2
04.01.2014	000032	1	
05.01.2014	000032	0	1
06.01.2014	000032	0	2
07.01.2014	000032	0	3
08.01.2014	000032	1	
09.01.2014	000032	1	
10.01.2014	000032	0	1
11.01.2014	000032	0	2
12.01.2014	000032	1	
13.01.2014	000032	0	1

я пробовал конструкции вроде sum ( ) over ()
но вышло не то совсем

UPD. там есть поле c датой. сортировка по нему осуществляется

Answer 1

[Владимир]

Функции RANK() и DENSE_RANK()
www.sql-tutorial.ru/ru/book_rank_dense_rank_functi...

Answer 2

[Serg]

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

Answer 3

[xtender]

with tab1 as (
   select to_date('01.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
   select to_date('02.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('03.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('04.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
   select to_date('05.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('06.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('07.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('08.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
   select to_date('09.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
   select to_date('10.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('11.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual union all
   select to_date('12.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 1 as QTY from dual union all
   select to_date('13.01.2014','dd.mm.yyyy') as    DAY_ID, '000032' as ARTICLE, 0 as QTY from dual
)
select 
   t.DAY_ID
  ,t.ARTICLE
  ,t.QTY
  ,decode(qty,0,count(*) over(partition by s order by day_id)) zero_n
from 
   ( 
    select
       tab1.*
      ,row_number()over(order by day_id) - decode(qty,0,count(*)over(partition by qty order by day_id)) s
    from tab1
   ) t
order by day_id