PostgresQL window function over blocks of continuous IDs

Question

I have a table with partially consecutive integer ids, i.e. there are blocks such as 1,2,3, 6,7,8, 10, 23,24,25,26.

the gap size is dynamic
the length of the blocks is dynamic

I am breaking my head about a simple solution that selects from the table and includes a column where the value corresponds to the first id of the respective block.

I.e. something like this

select id, first(id) over  first from table;

The result should look as following

| id | first |
|----|-------|
| 1  | 1     |
| 2  | 1     |
| 3  | 1     |
| 6  | 6     |
| 7  | 6     |
| 8  | 6     |
| 10 | 10    |
| 23 | 23    |
| 24 | 23    |
| 25 | 23    |
| 26 | 23    |

Afterwards i could use this column nicely with the partition by window function clause.

What I came up with so far always looked similar to this and didn't succeed:

WITH foo AS (
    SELECT LAG(id) OVER (ORDER BY id)  AS previous_id,
           id                          AS id,
           id - LAG(id, 1, id) OVER (ORDER BY id) AS first_in_sequence
    FROM table)
SELECT *,
       FIRST_VALUE(id) OVER (ORDER BY id) AS first
FROM foo
ORDER BY id;

Defining a custom postgres function would also be an acceptable solution.

Thanks for any advice,

Marti

user_1995738 · Answer

In Postgres you can create a custom aggregate. Example:

create or replace function first_in_series_func(int[], int)
returns int[] language sql immutable
as $$ 
    select case 
        when $1[2] is distinct from $2- 1 then array[$2, $2]
        else array[$1[1], $2] end; 
$$;

create or replace function first_in_series_final(int[])
returns int language sql immutable
as $$
    select $1[1]
$$;

create aggregate first_in_series(int) (
    sfunc = first_in_series_func,
    finalfunc = first_in_series_final,
    stype = int[]
);

Db<>fiddle.

Read in the docs: User-Defined Aggregates

user_2302032 · Answer

Here is an idea how this could be done. An implicit cursor is not horribly efficient though.

create or replace function ff()
returns table (r_id integer, r_first integer)
language plpgsql as
$$
declare
  running_previous integer;
  running_id integer;
  running_first integer := null;
begin 
    for running_id in select id from _table order by id loop
        if running_previous is distinct from running_id - 1 then
            running_first := running_id;
        end if;
        r_id := running_id;
        r_first := running_first;
        running_previous := running_id;
        return next;
    end loop;
end
$$;
-- test
select * from ff() as t(id, first);

PostgresQL window function over blocks of continuous IDs

2 Answers