ID | String | Is_Sep | Rank | Rank_DESC
1 | XX | 0 | 1 | 3
2 | XX | 0 | 2 | 2
3 | XX | 1 | 3 | 1
4 | XX | 0 | 1 | 3
5 | XX | 0 | 2 | 2
6 | XX | 1 | 3 | 1
7 | YY | 0 | 1 | 4
8 | YY | 0 | 2 | 3
9 | YY | 0 | 3 | 2
10 | YY | 1 | 4 | 1
11 | ZZ | 0 | 1 | 2
12 | ZZ | 1 | 2 | 1
13 | ZZ | 0 | 1 | 3
14 | ZZ | 0 | 2 | 2
15 | ZZ | 1 | 3 | 1
Any idea of how to do it?
Thanks!
This is a kind of gaps-and-island problem, where is_sep
marks the end of each island.
I would use a window sum to define the groups, and then row_number()
:
select
id,
string,
sep,
row_number() over(partition by string, grp order by id) rn,
row_number() over(partition by string, grp order by id desc) rn_desc
from (
select
t.*,
sum(is_sep) over(partition by string order by id desc) grp
from mytable t
) t
order by id
Note that, for this sample data, you don't really need string
in the partition, since is_sep
also indicates the transition between string
values. So this works equally well:
select
id,
string,
is_sep,
row_number() over(partition by grp order by id) rn,
row_number() over(partition by grp order by id desc) rn_desc
from (
select
t.*,
sum(is_sep) over(order by id desc) grp
from mytable t
) t
order by id
id | string | is_sep | rn | rn_desc -: | :----- | -----: | -: | ------: 1 | XX | 0 | 1 | 3 2 | XX | 0 | 2 | 2 3 | XX | 1 | 3 | 1 4 | XX | 0 | 1 | 3 5 | XX | 0 | 2 | 2 6 | XX | 1 | 3 | 1 7 | YY | 0 | 1 | 4 8 | YY | 0 | 2 | 3 9 | YY | 0 | 3 | 2 10 | YY | 1 | 4 | 1 11 | ZZ | 0 | 1 | 2 12 | ZZ | 1 | 2 | 1 13 | ZZ | 0 | 1 | 3 14 | ZZ | 0 | 2 | 2 15 | ZZ | 1 | 3 | 1