在同一個表中查找最接近的匹配項

我有一個帶有時間戳數據的表。對於所有行，我想找到最接近 20 分鐘後以及 15 到 25 分鐘後的行。

例如，如果桌子像

CREATE TABLE foo(id,ts)
AS VALUES 
 ( 1::int, '2017.10.27T10:12:15'::timestamp with time zone ),
 ( 2,      '2017.10.27T10:24:17' ),
 ( 3,      '2017.10.27T10:30:22' ),
 ( 4,      '2017.10.27T10:40:12' ),
 ( 5,      '2017.10.27T10:52:16' ),
 ( 6,      '2017.10.27T10:53:11' );

然後我執行一個查詢

select t1.id as base t2.id as after
from table t1, table t2 where ?? 

我想得到答案：

base after
1    3
2    4
3    5

對於 4,5 和 6 作為基礎，我沒有得到任何結果，因為沒有符合我的條件的行

對於給定的時間戳，很容易得到這個：

select id from table where timestamp > $mytimestamp+'00:15:00'
 and  timestamp < $mytimestamp + '00:25:00' 
order by 
  abs(extract ( epoch from (timestamp -($mytimestamp + '00:20:00'))) 
limit 1;

但是如何對錶中的所有行執行此操作？

您可以使用 aCROSS JOIN LATERAL ( ... LIMIT 1 )來完成此操作，btree_gist以及<->

CREATE EXTENSION IF NOT EXISTS btree_gist;

SELECT f1.id AS base, f2.id AS after
FROM foo AS f1
CROSS JOIN LATERAL (
 SELECT id, ts
 FROM foo AS f2
 WHERE f2.ts BETWEEN f1.ts+'15 minutes' AND f1.ts+'25 minutes'
 ORDER BY f1.ts+'20 minutes' <-> f2.ts ASC
 LIMIT 1
) AS f2(id,ts);

base | after 
------+-------
   1 |     3
   2 |     4
   3 |     5
(3 rows)

這裡有很多技巧。我們<->用作您*“最接近”的距離運算符。本質上，它可以使用 GiST 索引來通過 KNN 確定這一點。我們還使用 aCROSS JOIN LATERAL ... LIMIT 1來獲取所有潛在角色的列表，然後將其限制為僅由上面的 knn 運算符確定的“最接近”的角色。*

這將在ts.

CREATE INDEX ON foo USING gist ( ts );

另請參閱在時間戳上使用的另一個問題<->

引用自：https://dba.stackexchange.com/questions/189704