CODEWITHSUNDEEP
sqlpostgresqlwindow-functionsgaps-and-islands
Sergey Kurdyukov
Sergey Kurdyukov

Reputation: 1

Select continuous groups by field, using the specified order

I need help solving the following tasks, where I need to use window functions. But I can not figure out how to get the id from a subquery sample to sort and apply aggregating functions:

Given table:

create temp table users(id bigserial, group_id bigint);
insert into users(group_id)
values (1), (1), (1), (2), (1), (3);

In this table, sorted by ID, you need: to allocate continuous groups on group_id taking into account the

  1. specified order of rows group (there are 4 of them)

  2. count the number of records in each group

  3. calculate the minimum record ID in the group

The result should be:

one of the columns is the group_id, another is the number of records, or the minimum id value, depending on the task. Rows should be sorted by id.

Output like this:

 group_id | count
----------+-------
        1 |     3
        2 |     1
        1 |     1
        3 |     1

Partial solution of the second task, without ordering:

SELECT COUNT(*), group_id
FROM ( SELECT id, id - ROW_NUMBER() OVER (PARTITION BY group_id ORDER 
BY id) AS res, group_id FROM users)new_table
GROUP BY group_id,res;

That returns:

 group_id | count 
----------+-------
        1 |     3
        3 |     1
        1 |     1
        2 |     1

Upvotes: 0

Views: 196

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

I would guess this is what you are looking for:

SELECT group_id
     , count(*) AS row_count  -- 2. count the number of records in each group
     , min(id)  AS min_id     -- 3. calculate the minimum record ID in the group
FROM  (
   SELECT id
        , group_id
        , id - row_number() OVER (PARTITION BY group_id ORDER BY id) AS res
   FROM   users
   ) sub
GROUP  BY group_id, res
ORDER  BY min_id;  -- 1. specified order of rows group

Of course, if there can be gaps in the serial column id, you have to use:

  row_number() OVER (ORDER BY id)
- row_number() OVER (PARTITION BY group_id ORDER BY id) AS res

Typically, gaps in serial columns have to be expected.

Related answers with more explanation and links:

Upvotes: 1

Related Questions