主页 / dba / 问题 / 189704
Accepted
MortenSickel
Asked: 2017-10-31 13:50:20 +0800 CST

在同一张表中查找最接近的匹配项

  • 772

我有一个带有时间戳数据的表。对于所有行，我想找到最接近 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;

但是如何对表中的所有行执行此操作？

postgresql postgresql-9.4

1 个回答

  1. Best Answer

    您可以使用 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 );

    另见另一个使用<->时间戳的问题

    • 1

相关问题