SQL query to group by chronological series

user_8833905 0
Viewed 191

I'm working on a business case where people visit locations at different times.

I need to group each series at the same location and return it as one row.

To illustrate, I've created and populated the table visit (see below).

select loc, hour from visit order by hour gives me

Loc Hour
1   10
1   11
1   12
2   13
2   14
1   15
1   16

I would like an SQL that returns the following:

Loc first last
1 10 12
2 13 14
1 15 16

Suggestions, anyone?

Table / data in example 

create table visit ( loc number(2), hour number(2) );

insert into visit (loc, hour) values (1,10);
insert into visit (loc, hour) values (1,11);
insert into visit (loc, hour) values (1,12);
insert into visit (loc, hour) values (2,13);
insert into visit (loc, hour) values (2,14);
insert into visit (loc, hour) values (1,15);
insert into visit (loc, hour) values (1,16);
gaps-and-islands aggregate-functions window-functions sql database
1 Answers

This is a typical gaps-and-islands problem, where you want to group together "adjacent" visits at the same location.

Here is one way to solve it using the difference between row_numbers:

select 
    loc,
    min(hour) first_hour,
    max(hour) last_hour
from (
    select
        t.*,
        row_number() over(order by hour) rn1,
        row_number() over(partition by loc order by hour) rn2
    from visit t
) t
group by loc, rn1 - rn2
order by first_hour

Demo on DB Fiddle:

loc | first_hour | last_hour
--: | ---------: | --------:
  1 |         10 |        12
  2 |         13 |        14
  1 |         15 |        16
user_106767160
Related