sql - 在数组中查找与给定子数组匹配的位置

Question

给定这张表:

CREATE TABLE datasets.travel(path integer[], path_timediff double precision[]);

INSERT INTO datasets.travel
VALUES (array[50,49,49,49,49,50], array[NULL,438,12,496,17,435]);

我正在 PostgreSQL 中寻找某种函数或查询，对于给定的输入 array[49,50] 将在 path 中找到匹配的连续索引值即 [5,6] 和 path_timediff 中的相应元素，即示例中的 435(数组索引 6).

我的最终目的是找到 path 中出现的所有此类 [49,50] 以及 path_timediff 中的所有相应元素。我该怎么做？

Answer 1

假设您的表中有一个您未显示的主键:

CREATE TABLE datasets.travel (
  travel_id     serial PRIMARY KEY
, path          integer[]
, path_timediff float8[]
);

这是 generate_subscripts() 的一种方式在 LATERAL 连接中:

SELECT t.travel_id, i+1 AS position, path_timediff[i+1] AS timediff
FROM  (SELECT * FROM datasets.travel WHERE path @> ARRAY[49,50]) t
     , generate_subscripts(t.path, 1) i
WHERE  path[i:i+1] = ARRAY[49,50];

这会找到所有 匹配项，而不仅仅是第一个。

i+1 适用于长度为 2 的子数组。用 i + array_length(sub_array, 1) - 1 概括。

子查询不是绝对必要的，但可以在 (path) 上使用 GIN 索引进行快速预选:

(SELECT * FROM datasets.travel WHERE path @> ARRAY[49,50])

相关:

• How to access array internal index with postgreSQL?
• Parallel unnest() and sort order in PostgreSQL
• PostgreSQL unnest() with element number