Tengo las siguientes tablas:

 SELECT * FROM trajectories LIMIT 10; user_id | session_id | timestamp | lat | lon | alt ---------+-------------------+------------------------+-----------+------------+------ 11 | 10020071017220238 | 2007-10-18 02:51:38+01 | 37.780927 | 113.677553 | 2160 11 | 10020071017220238 | 2007-10-18 02:51:39+01 | 37.78093 | 113.677627 | 2160 11 | 10020071017220238 | 2007-10-18 02:51:40+01 | 37.780932 | 113.677698 | 2160 11 | 10020071017220238 | 2007-10-18 02:51:41+01 | 37.780938 | 113.677772 | 2159 11 | 10020071017220238 | 2007-10-18 02:51:42+01 | 37.780945 | 113.677845 | 2159 11 | 10020071017220238 | 2007-10-18 02:51:43+01 | 37.780952 | 113.677918 | 2159 11 | 10020071017220238 | 2007-10-18 02:51:44+01 | 37.780962 | 113.67799 | 2159 11 | 10020071017220238 | 2007-10-18 02:51:45+01 | 37.780973 | 113.67806 | 2159 11 | 10020071017220238 | 2007-10-18 02:51:46+01 | 37.78098 | 113.678128 | 2159 11 | 10020071017220238 | 2007-10-18 02:51:47+01 | 37.780992 | 113.678192 | 2157 (10 rows) SELECT * FROM labels WHERE travel mode = 'subway' LIMIT 10 user_id | session_id | start_timestamp | end_timestamp | travelmode ---------+------------+------------------------+------------------------+------------ 11 | 0 | 2008-06-18 04:46:10+01 | 2008-06-18 04:54:59+01 | subway 11 | 0 | 2008-08-01 02:51:47+01 | 2008-08-01 03:37:43+01 | subway 11 | 0 | 2008-08-01 03:59:36+01 | 2008-08-01 04:30:20+01 | subway 11 | 0 | 2008-09-16 00:58:43+01 | 2008-09-16 01:07:14+01 | subway 11 | 0 | 2008-09-16 11:49:05+01 | 2008-09-16 12:03:05+01 | subway 11 | 0 | 2008-09-18 00:41:41+01 | 2008-09-18 00:50:43+01 | subway 11 | 0 | 2008-09-18 10:43:23+01 | 2008-09-18 10:53:03+01 | subway 11 | 0 | 2008-09-19 10:46:56+01 | 2008-09-19 10:56:10+01 | subway 11 | 0 | 2008-09-21 23:58:45+01 | 2008-09-22 00:07:41+01 | subway 11 | 0 | 2008-09-22 11:14:52+01 | 2008-09-22 11:24:30+01 | subway (10 rows)

Hay casi 5M de puntos con modo de viaje etiquetado:

 SELECT COUNT(*) FROM trajectories t JOIN labels l ON t.user_id = l.user_id WHERE t.timestamp >= l.start_timestamp AND t.timestamp <= l.end_timestamp count --------- 4931303 (1 row)

Pero luego quiero saber la tasa de captura del modo subway en función de los intervalos (en la tabla de trajectories ), es decir, cuántos puntos caen entre 1-5 seconds, 5-10 seconds, 10- 20seconds segundos y más 20 seconds