I would like to generate reports of GPS capturing rate by travel modes. I have in a table modes the type of travel modes used by users. So for example, the following is the sample data for travel mode by user 10 for different trips. And then for each user and for each trip, user's GPS traces are recorded in

PostgreSQL: aggregate records by time interval

I would like to generate reports of GPS capturing rate by travel modes.

I have in a table modes the type of travel modes used by users.

CREATE TABLE modes
(
    user_id integer NOT NULL,
    trip_id int,
    start_time timestamp with time zone NOT NULL,
    end_time timestamp with time zone NOT NULL,
    travelmode text ,
    PRIMARY KEY (user_id, start_time, end_time)
)

So for example, the following is the sample data for travel mode by user 10 for different trips.

INSERT INTO modes (user_id, trip_id, start_time, end_time, travelmode)
VALUES (10,1000,'2008-06-18 13:28:18+01','2008-06-18 13:32:20+01','bus'),
      (10,1001,'2008-06-18 14:47:35+01','2008-06-18 15:05:31+01','bus'),
      (10,1002,'2008-08-01 02:51:47+01','2008-08-01 03:37:43+01','metro'),
      (10,1003,'2008-08-01 03:59:36+01','2008-08-01 04:30:20+01','metro'),
      (10,1004,'2008-08-01 05:20:07+01','2008-08-01 07:03:51+01','car'),
      (10,1005,'2008-08-01 07:17:08+01','2008-08-01 08:06:26+01','bus'),
      (10,1006,'2008-09-15 23:54:20+01','2008-09-16 00:02:44+01','bus'),
      (10,1007,'2008-09-16 00:10:22+01','2008-09-16 00:28:29+01','bus'),
      (10,1008,'2008-09-16 00:58:43+01','2008-09-16 01:07:14+01','metro')

And then for each user and for each trip, user’s GPS traces are recorded in a table plt_distinct:

CREATE TABLE plt_distinct
(
    user_id int,
    trip_id int, 
    logtime timestamp with time zone NOT NULL,
    lat double precision NOT NULL,
    lon double precision NOT NULL,
    alt double precision,
    PRIMARY KEY (trip_id, logtime)
)

Like so, for the user given in sample data above, following are the sample GPS traces for a particular trip:

INSERT INTO plt_distinct (user_id, trip_id, logtime, lat, lon, alt)
VALUES (10,1002,'2008-06-18 04:46:20+01',39.940474,116.346754,233),
      (10,1002,'2008-06-18 04:46:21+01',39.940491,116.346745,233),
      (10,1002,'2008-06-18 04:46:23+01',39.940526,116.346734,233),
      (10,1002,'2008-06-18 04:46:25+01',39.940573,116.346725,233),
      (10,1002,'2008-06-18 04:46:31+01',39.940815,116.346688,230),
      (10,1002,'2008-06-18 04:46:32+01',39.940861,116.346661,230),
      (10,1002,'2008-06-18 04:46:33+01',39.940941,116.346599,233),
      (10,1002,'2008-06-18 04:46:35+01',39.941109,116.34658,233),
      (10,1002,'2008-06-18 04:46:39+01',39.941464,116.346561,240),
      (10,1002,'2008-06-18 04:46:40+01',39.941558,116.346521,246),
      (10,1002,'2008-06-18 04:46:42+01',39.941816,116.346438,259)

The given sample are traces by metro mode. For analysis purposes, I am interested in aggregating the GPS traces interval for each mode (especially metro as GPS is not available underground).

I make available, these tables and the sample data in this DB-fiddle.

The expected result is something like this:

+-----------------------+---------------+-----------------+-----------------+----------------+
| count of metro(total) | interval (1s) | interval (2-5s) | interval(6-10s) | interval(>10s) |
+-----------------------+---------------+-----------------+-----------------+----------------+
|                    10 |             4 |               5 |               1 |              0 |
+-----------------------+---------------+-----------------+-----------------+----------------+

Answer

You should use LAG() and Filter clause to achieve this:

Try This:

select 
count(*) filter (where time_>0) "count of metro(total)",
count(*) filter (where time_=1) "interval (1s)",
count(*) filter (where time_ between 2 and 5) "interval (2-5s)",
count(*) filter (where time_ between 6 and 10) "interval(6-10s)",
count(*) filter (where time_ >10) "interval(>10s)"
from 
(
select 
coalesce(extract (epoch from (t1.logtime- lag(t1.logtime) over (partition by t1.trip_id order by t1.trip_id, t1.logtime))),0) as "time_"
                                                       
from plt_distinct t1 

inner join modes t2 on t1.user_id=t2.user_id and t1.trip_id=t2.trip_id 
where t2.travelmode='metro'
) t

DEMO of Fiddle

Advertisement

Answer