sql window function not giving me the right output

user_3495042 0
Viewed 56
A     B   C   D
1pm   a   1   1
2pm   a   2   2
3pm   b   1   1
4pm   b   2   2
5pm   a   3   1
6pm   a   4   2

When I do row_number() over (partition by B order by A) as C ., I get the column C. How do I get column D?

presto sql
1 Answers

You need to assign a group to the "adjacent" values. One simple method is the difference of row numbers:

select a, b, 
       row_number() over (partition by b, (seqnum_a - seqnum_ab) order by a) as d
from (select t.*,
             row_number() over (order by a) as seqnum_a,
             row_number() over (partition by b order by a) as seqnum_ab
      from t
     ) t;

The difference of row numbers is one solution to some types of gaps-and-islands problems (basically what you are asking for). Why it works is a little tricky to explain. I find that if someone sees the results of the subquery, they will usually get why the difference identifies the adjacent rows.

user_11440350
Related