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);
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_number
s:
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
loc | first_hour | last_hour --: | ---------: | --------: 1 | 10 | 12 2 | 13 | 14 1 | 15 | 16