row_number() but only increment value after a specific value in a column

user_12168508 0
Viewed 163

Query: SELECT (row_number() OVER ()) as grp, * from tbl

Edit: the rows below are returned by a pgrouting shortest path function and it does have a sequence.

seq grp   id
1    1      8
2    2      3
3    3      2
4    4      null
5    5      324
6    6      82
7    7      89
8    8      null
9    9      1
10   10     2
11   11     90
12   12     null

How do I make it so that the grp column is only incremented after a null value on id - and also keep the same order of rows

seq grp   id 
1    1      8
2    1      3
3    1      2
4    1      null
5    2      324
6    2      82
7    2      89
8    2      null
9    3      1
10   3      2
11   3      90
12   3      null
postgresql sql
1 Answers

demo:db<>fiddle

Using a cumulative SUM aggregation is a possible approach:

SELECT
    SUM(                                       -- 2
       CASE WHEN id IS NULL THEN 1 ELSE 0 END  -- 1
    ) OVER (ORDER BY seq) as grp,
    id
FROM mytable
  1. If the current (ordered!) value is NULL, then make it 1, else 0. Now you got a bunch of zeros, delimited by a 1 at each NULL record. If you'd summerize these values cumulatively, at each NULL record, the sum increased.
  2. Execution of the cumulative SUM() using window functions

This yields:

0   8
0   3
0   2
1   null
1   324
1   82
1   89
2   null
2   1
2   2
2   90
3   null

As you can see, the groups start with the NULL records, but you are expecting to end it.

This can be achieved by adding another window function: LAG(), which moves the records to the next row:

SELECT
    SUM(
        CASE WHEN next_id IS NULL THEN 1 ELSE 0 END
    ) OVER (ORDER BY seq) as grp,
    id
FROM (
    SELECT
        LAG(id) OVER (ORDER BY seq) as next_id,
        seq,
        id
    FROM mytable
) s

The result is your expected one:

1   8
1   3
1   2
1   null
2   324
2   82
2   89
2   null
3   1
3   2
3   90
3   null
user_39842210
Related