postgres -在T1中为T2中的每一行选择时间戳之前的最新行

Question

我反复问了几个问题，但没有找到答案。因此，我希望有一个答案/链接到一个类似的解决方案。我有两张桌子:车辆和活动。每辆车都有多个GPS定位点--比如说，玩具示例中有4辆车，有11排GPS定位。但是对于玩具测试来说，只有两个事件。

例如，车辆(每行都有唯一的gid，每辆车都有vehicle_id，还有时间戳date_time和位置geom)：

SELECT gid, vehicle_id, date_time, geom FROM test_v vehicles ORDER BY 2,3,1;


   gid   | vehicle_id |      date_time      |                        geom                        
---------+------------+---------------------+----------------------------------------------------
 1257854 |        742 | 2009-06-06 19:02:01 | 01010000208A7D0000667CC002A7262741A444A87995324041
 1258089 |        742 | 2009-06-06 19:03:29 | 01010000208A7D00007ABCA45AD22227415A5ADEB5662E4041
 1258842 |        742 | 2009-06-06 19:07:23 | 01010000208A7D000083D5151C7414274196D0D21AE7284041
 1259213 |        742 | 2009-06-06 19:09:11 | 01010000208A7D000054160E1FE4052741D6E2E74CD2284041
 1257939 |       3055 | 2009-06-06 19:02:31 | 01010000208A7D0000327D6277FAF426418EA975ECB6504041
 1258898 |       3055 | 2009-06-06 19:07:41 | 01010000208A7D000003519FB5D5F6264166B1B943C3514041
 1259313 |       3425 | 2009-06-06 19:09:35 | 01010000208A7D0000418D23C296442741FCFCEF0069394041
 1258008 |       3426 | 2009-06-06 19:02:56 | 01010000208A7D000011958634824027414D5D3823A43C4041
 1258307 |       3426 | 2009-06-06 19:04:37 | 01010000208A7D0000C8017753AB38274175E727CBA43A4041
 1258744 |       3426 | 2009-06-06 19:06:56 | 01010000208A7D00005F9811D7903A274145135F0589384041
 1259018 |       3426 | 2009-06-06 19:08:13 | 01010000208A7D0000546B478B323A2741F4CB8DB6AD374041
(11 rows)

事件(唯一的gid、时间戳t_date和位置geom)：

SELECT gid, t_date, geom FROM test_c events;
  gid  |       t_date        |                        geom                        
-------+---------------------+----------------------------------------------------
 13009 | 2009-06-06 19:04:31 | 01010000208A7D000034F06A423F18274123DF6756B8194041
 13021 | 2009-06-06 19:08:53 | 01010000208A7D0000D093E2A470FE26413E935C3A5E304041

的想法是了解每次事件发生时的情况，哪些车辆更接近，哪些车辆后来优化了对事件的分配。

因此，我想加入这些表，以便为每一个事件得到最多4行(作为车辆的数量)，在事件发生前120分钟的时间内，每辆车的最后已知位置，直到活动时间，按两者之间的距离排序。我想把它们放在同一张桌子上，这样我就可以根据时间、地点、事件类型等对它们进行比较。

现在我被困在这里了。我知道如何在特定时间内找到车辆的最新记录(例如19:10)。但这不是我所需要的，因为它切断了时间19:10的车辆表。但是对于gid= 13009时间是19:04:31，所以可能包括一个比事件晚的位置。我想要的是最近的位置，直到事件的时间是不同的，显然每个事件。

我试过这个：

SELECT DISTINCT ON (1)
  v.vehicle_id
  , row_number() OVER() as gid
  , st_distance(v.geom, c.geom)::float4 as distance_m
  , c.gid as c_gid 
  , v.gid as v_gid
  , c.t_date as dt_c 
  , v.date_time as dt_v 
  , (c.t_date - v.date_time) as d_t
FROM (SELECT * FROM test_c ) as c
  ,  (SELECT * FROM test_v ORDER BY date_time DESC )  v
WHERE date_time >= (c.t_date -  '120 minute' :: INTERVAL) AND date_time <= (c.t_date + '0 minute' :: INTERVAL)
ORDER BY 1, 7  DESC , 5, 6 ,  c.geom <-> v.geom ASC ;

我得到的是：

 vehicle_id | gid | distance_m | c_gid |  v_gid  |        dt_c         |        dt_v         |   d_t    
------------+-----+------------+-------+---------+---------------------+---------------------+----------
        742 |   3 |     4748.6 | 13021 | 1258842 | 2009-06-06 19:08:53 | 2009-06-06 19:07:23 | 00:01:30
       3055 |   2 |    17125.8 | 13021 | 1258898 | 2009-06-06 19:08:53 | 2009-06-06 19:07:41 | 00:01:12
       3426 |   1 |    8515.65 | 13021 | 1259018 | 2009-06-06 19:08:53 | 2009-06-06 19:08:13 | 00:00:40
(3 rows)

我得到不正确的日期，每辆车只有一次(我猜是因为DISTINCT的缘故)，但我需要每个事件一次，并有适当的最后位置(因此在本例中，2个事件需要2次)。

理想的情况下，我也希望作为下一个步骤，为每辆车有3个最新的位置，但也许这是为以后。

Answer 1

这就是我所理解的：

select *
from (
    select distinct on (c.gid, v.vehicle_id)
        v.vehicle_id
        , row_number() over(order by v.gid) as gid
        , st_distance(v.geom, c.geom)::float4 as distance_m
        , c.gid as c_gid 
        , v.gid as v_gid
        , c.t_date as dt_c 
        , v.date_time as dt_v 
        , (c.t_date - v.date_time) as d_t
    from
        test_c c
        inner join 
        test_v v on
            date_time between c.t_date - '120 minute' :: interval and c.t_date
    order by c.gid, v.vehicle_id, v.date_time desc
) s
order by c_gid, distance_m
;
 vehicle_id | gid | distance_m | c_gid |  v_gid  |        dt_c         |        dt_v         |   d_t    
------------+-----+------------+-------+---------+---------------------+---------------------+----------
        742 |   8 |    10674.9 | 13009 | 1258089 | 2009-06-06 19:04:31 | 2009-06-06 19:03:29 | 00:01:02
       3426 |   5 |    18607.5 | 13009 | 1258008 | 2009-06-06 19:04:31 | 2009-06-06 19:02:56 | 00:01:35
       3055 |   4 |    28516.8 | 13009 | 1257939 | 2009-06-06 19:04:31 | 2009-06-06 19:02:31 | 00:02:00
        742 |  11 |     4748.6 | 13021 | 1258842 | 2009-06-06 19:08:53 | 2009-06-06 19:07:23 | 00:01:30
       3426 |  13 |    8515.65 | 13021 | 1259018 | 2009-06-06 19:08:53 | 2009-06-06 19:08:13 | 00:00:40
       3055 |  12 |    17125.8 | 13021 | 1258898 | 2009-06-06 19:08:53 | 2009-06-06 19:07:41 | 00:01:12