CODEWITHSUNDEEP
sqlpostgresqlselect
sh4rkyy
sh4rkyy

Reputation: 402

Group adjacent rows with same value

With the following table: https://www.db-fiddle.com/f/avcRnMG6SVAoRuV5Rf4znF/2

create table tbl
   (id integer,
    start integer,
    stop integer,
    type integer);
    
    INSERT INTO tbl values
    
    (101, 1, 3, 10),
    (101, 3, 6, 15),
    (101, 6, 10, 17),
    (101, 10, 40, 20),
    (101, 40, 100, 20),
    (101, 100, 200, 20),
    (101, 200, 500, 55);

I want to group adjacent rows with same value with the boundary start and stop values. So instead of 3 rows with type 20 in the results it should be

101, 10, 200, 20

I tried something like this but it's far from good, is there any smart, short solution there?

SELECT 
    id, 
   case when lag(type) OVER (partition by id ORDER BY start ) = type 
   then lag(start) OVER (partition by id ORDER BY start) else start end as from
   , case when lead(type) OVER (partition by id ORDER BY start ) = type 
   then lead(stop) OVER (partition by id ORDER BY start) else stop end as to
   , type
   
from tbl

Upvotes: 0

Views: 679

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The type values seem to be steadily increasing, so they don't repeat. So, it looks like a simple group by would suffice:

select id, min(start), max(stop), type
from tbl
group by id, type;

If the types can be interspersed, then you need to treat this as a gaps-and-islands problem, using more sophisticated queries.

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12494

There might be a more concise way to do this, but this is how I normally solve for this kind of a requirement. You can uncomment the other selects at the bottom to see what each step does.

fiddle here

  1. Annotate records where type changes
  2. Use sum() to assign rows to groupings where type stays the same in consecutive rows
  3. Collapse the start and stop values for repeated groups

with changes as (
  select *, 
         case 
           when lag(type) over (partition by id
                                    order by start) = type then 0
           else 1
         end as changed
    from tbl
), groups as (
  select *,
         sum(changed) over (partition by id
                                order by start) as grp
    from changes
), combined as (
  select id, 
         min(start) as start,
         max(stop) as stop,
         type 
    from groups
   group by id, grp, type
)
--select * from changes order by start;
--select * from groups order by start;
select * from combined order by start;

Upvotes: 1

Related Questions