postgresql：基于区间的子分组列

lpwwtiir 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(496)

我有以下表格：

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)

几乎有 5M 带有标记的旅行模式的点：

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)

但是我想知道 subway 基于间隔的模式（英寸 trajectories 表），即有多少点落在 1-5 seconds, 5-10 seconds, 10- 20seconds 及以上 20 seconds

sql postgresql Aggregate

来源：https://stackoverflow.com/questions/62514638/postgresql-subgrouping-column-based-on-intervals

1条答案

按热度按时间

pieyvz9o1#

可以使用条件聚合：

SELECT COUNT(*),
       COUNT(*) FILTER (WHERE end_timestamp >= start_timestamp AND end_timestamp < start_timestamp + interval '5 second'),
       COUNT(*) FILTER (WHERE end_timestamp >= start_timestamp + interval '5 second' AND end_timestamp < start_timestamp + interval '10 second'),
       . . .
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

赞(0）回复(0）举报 2021-07-24

我来回答

postgresql：基于区间的子分组列

1条答案

相关问题

热门标签

最新问答