Select continuous groups by field, using the specified order

Question

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

specified order of rows group (there are 4 of them)
count the number of records in each group
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

Edit your question and show the results that should be produced. — Gordon Linoff, Sep 09 '17 at 20:55
"taking into account the specified order of records (there are 4 of them)"? There are obviously more than 4 records, and *taking into account* does not define anything. Please describe your objective in a meaningful way. And you are not showing the desired *result* for the example, either. — Erwin Brandstetter, Sep 10 '17 at 03:25

score 1 · Answer 1 · answered Sep 11 '17 at 02:45

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:

Select continuous groups by field, using the specified order

1 Answers1