CODEWITHSUNDEEP
sqlteradata
Justin Klevs
Justin Klevs

Reputation: 651

Add Counter Column for Contiguous Run of Equal Value

I believe I have a pretty straightforward question, but was not able to find the answer I was looking for by searching through previously asked questions on the site.

I am attempting to add a counter column to my data, such as in the example below. I would like it to track consecutive occurances of a single value in a specific column of my data. Even though, ideally, the counter would function by group (ID #), it is not completely necessary, as the chance of a consecutive repeat value between two IDs is essentially 0. 

    # ID Date   Value  Consecutive_Repeat
    # 1  01-01    a       0
    # 1  01-02    b       0
    # 1  01-03    b       1
    # 1  01-04    a       0
    # 2  01-01    x       0
    # 2  01-02    x       1
    # 2  01-03    x       2
    # 2  01-04    a       0   
    # 3  01-01    b       0
    # 3  01-02    b       1
    # 3  01-03    a       0
    # 3  01-04    b       0
    # 4  01-01    c       0
    # 4  01-02    c       1
    # 4  01-03    c       2
    # 4  01-04    c       3

Upvotes: 0

Views: 62

Answers (2)

dnoeth
dnoeth

Reputation: 60513

It's a bit more complicated than a simple ROW_NUMBER, you can apply a Teradata extension to Standard SQL, RESET WHEN:

Row_Number() 
Over (PARTITION BY id 
      ORDER BY date
      RESET WHEN Min(value) -- start whenever there's a a new value
                 Over (PARTITION BY id
                       ORDER BY date
                       ROWS BETWEEN 1 Preceding AND 1 Preceding) <> value) -1

Upvotes: 2

Andrew
Andrew

Reputation: 8758

You would just use the row_number function.

ROW_NUMBER() over (partition by id,value order by date)

If you want it to start with zero, just subtract one from it.

Upvotes: 0

Related Questions