Thread: Query to match location transitions

Query to match location transitions

From
sub3
Date:
Hi,

I was hoping someone could help me build a better query.  I have a table of
time/locations.  Occasionally, we have multiple timestamps for the same
location.  I would like to remove those extra timestamps and only show the
transition from one location to another. So...

create table time_locations ( id integer, timestamp double precision, location integer
)

Data:
1,1197605841,1
2,1197608001,2
3,1197609802,2
4,1197611951,2
5,1199145360,2
6,1199145480,3
7,1199147280,3
8,1199149140,3
9,1199151300,1
10,1199152000,3

I would like to return a table like:
1197605841,1,1197608001,2
1199145360,2,1199145480,3
1199149140,3,1199151300,1
1199151300,1,1199152000,3

The only way I can think of to do this would be a procedure which would do a
large loop over the
table (sorted by time) returning a row when last.location <> this.location. 
However, when I try this on a
large table, it seems like the 'select into' doesn't order & ruins the whole
solution.
Is there a query approach?

Thanks.
-- 
View this message in context: http://www.nabble.com/Query-to-match-location-transitions-tp20506709p20506709.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.



Re: Query to match location transitions

From
Tomasz Myrta
Date:
sub3 napisal 14.11.2008 20:12:
> Hi,
> 
> I was hoping someone could help me build a better query.  I have a table of
> time/locations.  Occasionally, we have multiple timestamps for the same
> location.  I would like to remove those extra timestamps and only show the
> transition from one location to another. So...
> 
> create table time_locations (
>   id integer,
>   timestamp double precision,
>   location integer
> )

Try to not use data type name for column name.


> Data:
> 1,1197605841,1
> 2,1197608001,2
> 3,1197609802,2
> 4,1197611951,2
> 5,1199145360,2
> 6,1199145480,3
> 7,1199147280,3
> 8,1199149140,3
> 9,1199151300,1
> 10,1199152000,3
> 
> I would like to return a table like:
> 1197605841,1,1197608001,2
> 1199145360,2,1199145480,3
> 1199149140,3,1199151300,1
> 1199151300,1,1199152000,3
> 
> The only way I can think of to do this would be a procedure which would do a
> large loop over the
> table (sorted by time) returning a row when last.location <> this.location. 
> However, when I try this on a
> large table, it seems like the 'select into' doesn't order & ruins the whole
> solution.

Select into is used for fetching single row result. You need rather 
for..in loop

> Is there a query approach?


Example below gives the same result as described:

CREATE OR REPLACE FUNCTION location_changes( last_time OUT double precision, last_location OUT integer, new_time OUT
doubleprecision, new_location OUT integer
 
) RETURNS setof RECORD AS $$
BEGIN  for new_location, new_time in select location,timestamp  from time_locations order by timestamp  loop    if
last_location<>new_locationthen      return next;    end if;    last_location=new_location;    last_time=new_time;  end
loop;
END;
$$ language 'plpgsql';

select * from location_changes();

-- 
Regards,
Tomasz Myrta