Maddy Mc Maddy Mc - 2 months ago 15
SQL Question

How to generate sequence like

+---+------------+
| V | output |
+---+------------+
| y | 1 |
| y | 2 |
| y | 3 |
| N | 0 |
| y | 1 |
| y | 2 |
| N | 0 |
| N | 1 |
+---+------------+

Answer

Let me assume that you have a column (say, id) that has the ordering information. Then, you want to identify groups of "Y"s and "N"s that appear together and then enumerate them.

You can do this using a difference of row numbers trick:

select t.v,
       row_number() over (partition by v, seqnum_id - seqnum_vid order by id) as output
from (select t.*,
             row_number() over (order by id) as seqnum_id,
             row_number() over (partition v by order by id) as seqnum_vid
      from t
     ) t;

Explaining how this works is usually tricky. I recommend that you run the subquery to see what the sequence numbers look like and why the difference is constant for the groups you want to identify.