Count Consecutive Duplicate Values By Group

I have searched the site a bit for a solution to this question but have been unable to find an answer that fits precisely what I am looking for. I am attempting to count consecutiv

Solution 1:

This is a gap-and-islands problem. One method is the difference of row_number()s to identify the groups.

select t.*,
       dense_rank() over (partitionby id orderby (seqnum - seqnum_value), value) as grp,
       row_number() over (partitionby id, (seqnum - seqnum_value), valueorderbydate) as grp_seqnum
from (select t.*,
             row_number() over (partitionby id orderbydate) as seqnum,
             row_number() over (partitionby id, valueorderbydate) as seqnum_v
      from t
     ) t;

This is a bit tricky to understand the first time you see it. If you run the subquery and stare at the results long enough, you'll get why the difference is constant for adjacent values.

EDIT:

I think Jorge is right. Your data doesn't have the same value repeated, so you can just do:

select t.*,
       row_number() over (partitionby id, valueorderbydate) as grp_seqnum
from t;

Solution 2:

When the values are actually increasing all the time then this should work:

row_number() over (partitionby id, valueorderbydate) -1

Otherwise Teradata has an extension to Standard SQL for cases like this:

row_number() 
over (partitionby id
      orderbydate
      RESET WHENMIN(value)  -- previous value not equal to current OVER (partitionby id
                       orderbydaterowsbetween1 preceding and1 preceding) <>value
     ) -1