sql - Add a proactive counter in SELECT -

i have table this:

row#    id    indicator  1      001      yes  2      001      no  3      001      no  4      001      no  5      001      no  6      001      yes  7      001      no  8      002      no  9      002      no  10     002      no  11     002      no  12     002      no  13     002      no  14     002      yes  15     002      no  16     003      no  17     003      yes  18     003      no  19     003      no  20     003      no  21     003      no  22     004      no  .        .      .  .        .      . 100     020      yes 

i want have counter count number of every 3 consecutive 'no' , group id. counter function triggers when sees "no". once 3 consecutive rows of "no" occur, counter = counter + 1. no double count allowed.

for instance:
id = 001, there 3 consecutive "no" row 2 row 4, counter value = 1.

since "no" row 2 row 4 have been used, row 3 row 5 can't counted event because of no double count rule.

for id = 002, there 3 consecutive "no" row 8 row 10 , row 11 row 13, counter value = 2.

the desired output this:

id    counter 001      1 002      2 003      1  .       .  .       . 100      0 

edit 1: row # not real column. it's explanation purpose.
edit 2: realized row# essential problem. let's make real column again. original data sample given above remain valid.

hive supports row_number(), can use enumerate values. logic may not obvious @ first, idea enumerate consecutive "n" values , divide result 3. latter piece logic getting number of sequences want.

the following version works assuming there 1 long sequence of "n"s. format of data in question:

select t.id, max(floor(seqnum / 3.0)) (select t.*,              row_number() on (partition id, indicator, grp order row#) seqnum       (select t.*,                    (row_number() on (partition id order row#) -                     row_number() on (partition id, indicator order row#)                    ) grp             table t            ) t      ) t indicator = 'n' group id; 

this can modified handle multiple sequences of "n"s given id, query little bit more complicated.

edit:

i realize following more general:

select t.id, sum(case when pmod(seqnum, 3) = 0 1 else 0 end)