在我上一个问题的答案之后做一个理智的检查。我意识到我没有为我的目标找到正确的逻辑。我有一张table trajectories 用户的时间戳gps序列：

CREATE TABLE trajectories
(
    user_id integer,
    session_id bigint NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    lat double precision NOT NULL,
    lon double precision NOT NULL,
    alt double precision,
    PRIMARY KEY (session_id, "timestamp")
);

例如：

INSERT INTO trajectories (user_id,session_id,timestamp,lat,lon,alt)
VALUES      (11,1010,'2007-06-26 11:32:29+01',37.780927,113.677553,2160),
      (11,1010,'2007-06-26 11:32:30+01',37.78093,113.677627,2160),
      (11,1010,'2007-06-26 11:32:33+01',37.780932,113.677698,2160),
      (11,1010,'2007-06-26 11:32:36+01',37.780938,113.677772,2159),
      (11,1010,'2007-06-26 11:32:37+01',37.780945,113.677845,2159),
      (11,1010,'2007-06-26 11:32:39+01',37.780952,113.677918,2159),
      (11,1010,'2007-06-26 11:32:47+01',37.780962,113.67799,2159),
      (11,1010,'2007-06-26 11:32:51+01',37.780973,113.67806,2159),
      (11,1010,'2007-06-26 11:32:55+01',37.78098,113.678128,2159),
      (11,1010,'2007-06-26 11:32:59+01',37.780992,113.678192,2157)

在我的第二张table上 labels 是用户在旅行期间使用的运输方式，如下所示：

CREATE TABLE labels
(
    user_id integer NOT NULL,
    session_id bigint,
    start_timestamp timestamp with time zone NOT NULL,
    end_timestamp timestamp with time zone NOT NULL,
    travelmode text COLLATE pg_catalog."default",
    PRIMARY KEY (user_id, start_timestamp, end_timestamp)
)

  INSERT INTO labels (user_id,session_id,start_timestamp,end_timestamp,travelmode)
VALUES (11,1010,'2007-06-26 11:32:29+01','2007-06-26 11:40:29+01','bus'),
       (11,1010,'2008-03-28 14:52:54+00','2008-03-28 15:59:59+00','train'),
       (11,1010,'2008-03-28 16:00:00+00','2008-03-28 22:02:00+00','train'),
       (11,1010,'2008-03-29 01:27:50+00','2008-03-29 15:59:59+00','train'),
       (11,1010,'2008-03-29 16:00:00+00','2008-03-30 15:59:59+01','train')

为了便于统计，我想知道每种模式的gps点数。在上面的表格中，我得到了 bus 模式使用：

SELECT COUNT(*) 
FROM trajectories t                                                                                                       
JOIN labels l                                                                                                                                    
ON t.user_id = l.user_id                                                                                                                        
WHERE travelmode='bus' AND t.timestamp BETWEEN l.start_timestamp AND  l.end_timestamp;
    count  
---------
   10
 (1 row)

gps点的采样率是不均匀的，所以我想得到点的数量 bus 间隔中的模式： 1 sec , 2-5sec , 6-10sec , above 10sec .
我的问题：

SELECT COUNT(*) count_all,
COUNT(*) FILTER (WHERE end_timestamp >= start_timestamp AND end_timestamp < start_timestamp + interval '1 second') one_sec_inv,
COUNT(*) FILTER (WHERE end_timestamp >= start_timestamp AND end_timestamp < start_timestamp + interval '5 second') two_to_5,
COUNT(*) FILTER (WHERE end_timestamp >= start_timestamp + interval '5 second' AND end_timestamp < start_timestamp + interval '10 second') five_to_10,
COUNT(*) FILTER (WHERE end_timestamp >= start_timestamp + interval '10 second' AND end_timestamp > start_timestamp + interval '15 second') ten_to_15
FROM trajectories t JOIN
labels l
ON t.user_id = l.user_id
WHERE travelmode='bus' AND  t.timestamp >= l.start_timestamp AND t.timestamp <= l.end_timestamp;

 count_all | one_sec_inv | two_to_5 | five_to_10 | ten_to_15 
-----------+-------------+----------+------------+-----------
   10      |           0 |        0 |          0 |   10
(1 row)

我承认我在这个问题上遗漏了正确的逻辑。克利里，有 2 points 间隔 one_sec_inv , 5 points 在 two_to_5 间隔和 1 point 在间歇期 five_to_10 分别。我如何解决它以实现目标？
我把这些table放在这把小提琴里。
编辑

SELECT * FROM trajectories
+---------+------------+------------------------+-----------+------------+------+
| user_id | session_id |       timestamp        |    lat    |    lon     | alt  |
+---------+------------+------------------------+-----------+------------+------+
|      11 |       1010 | 2007-06-26 11:32:29+01 | 37.780927 | 113.677553 | 2160 |
|      11 |       1010 | 2007-06-26 11:32:30+01 | 37.78093  | 113.677627 | 2160 |
|      11 |       1010 | 2007-06-26 11:32:33+01 | 37.780932 | 113.677698 | 2160 |
|      11 |       1010 | 2007-06-26 11:32:36+01 | 37.780938 | 113.677772 | 2159 |
|      11 |       1010 | 2007-06-26 11:32:37+01 | 37.780945 | 113.677845 | 2159 |
|      11 |       1010 | 2007-06-26 11:32:39+01 | 37.780952 | 113.677918 | 2159 |
|      11 |       1010 | 2007-06-26 11:32:47+01 | 37.780962 | 113.67799  | 2159 |
|      11 |       1010 | 2007-06-26 11:32:51+01 | 37.780973 | 113.67806  | 2159 |
|      11 |       1010 | 2007-06-26 11:32:55+01 | 37.78098  | 113.678128 | 2159 |
|      11 |       1010 | 2007-06-26 11:32:59+01 | 37.780992 | 113.678192 | 2157 |
+---------+------------+------------------------+-----------+------------+------+

预期产量：

count_all | one_sec_inv | two_to_5 | five_to_10 | ten_to_15 
        -----------+-------------+----------+------------+-----------
           10      |           2 |        6 |          1 |   0

编辑-2
@mikeorganek的回答没有产生预期的结果：

SELECT COUNT(*) count_all,
COUNT(*) FILTER (WHERE timestamp <= start_timestamp + interval '1 second') one_sec_inv,
COUNT(*) FILTER (WHERE timestamp >  start_timestamp + interval '1 seconds' 
                   AND timestamp <= start_timestamp + interval '5 seconds') two_to_5,
COUNT(*) FILTER (WHERE timestamp >  start_timestamp + interval '5 seconds' 
                   AND timestamp <= start_timestamp + interval '10 seconds') five_to_10,
COUNT(*) FILTER (WHERE timestamp >  start_timestamp + interval '10 second' 
                   AND timestamp <= start_timestamp + interval '15 second') ten_to_15
  FROM trajectories t 
  JOIN labels l
    ON t.user_id = l.user_id
 WHERE travelmode='bus' 
   AND t.timestamp >= l.start_timestamp 
   AND t.timestamp <= l.end_timestamp;

+-----------+-------------+----------+------------+-----------+
| count_all | one_sec_inv | two_to_5 | five_to_10 | ten_to_15 |
+-----------+-------------+----------+------------+-----------+
|        10 |           2 |        1 |          3 |         0 |
+-----------+-------------+----------+------------+-----------+