PostgreSQL数组遍历查询：按时间戳匹配对应位置元素

嘿，我完全明白你的需求——要从position表中取出基础坐标和旋转值，同时根据传入的时间戳，精准匹配到start_timestamps和end_timestamps数组对应时间范围内的translate_x、translate_y元素对吧？

先说说你之前尝试的问题：你把PL/pgSQL的流程控制语法直接写到了普通SELECT语句里，这是行不通的，普通查询语句不能直接用DECLARE、BEGIN或者FOR LOOP这类块语法。我们可以用PostgreSQL自带的数组处理函数来优雅解决这个问题。

正确的查询写法 #

我们可以利用unnest()函数配合WITH ORDINALITY，把数组拆成带位置索引的行记录，这样就能关联对应位置的时间戳和偏移量，再筛选符合条件的记录：

SELECT
  p.x_position,
  p.y_position,
  p.rotation,
  tx.val AS translate_x,
  ty.val AS translate_y
FROM
  "public"."position" p
-- 拆分start_timestamps数组并带上位置索引
JOIN unnest(p.start_timestamps) WITH ORDINALITY st(val, idx) ON true
-- 关联同索引的end_timestamps元素
JOIN unnest(p.end_timestamps) WITH ORDINALITY et(val, idx) ON st.idx = et.idx
-- 关联同索引的translate_x元素
JOIN unnest(p.translate_x) WITH ORDINALITY tx(val, idx) ON st.idx = tx.idx
-- 关联同索引的translate_y元素
JOIN unnest(p.translate_y) WITH ORDINALITY ty(val, idx) ON st.idx = ty.idx
WHERE
  -- 匹配传入时间戳在当前索引的时间范围内
  st.val <= $1 AND et.val > $1
  -- 可选：如果需要指定特定数据行，比如筛选id=683，可以加上
  -- AND p.id = 683;

代码说明 #

• WITH ORDINALITY会给每个拆出来的数组元素添加一个idx字段，表示它在原数组中的位置（PostgreSQL数组默认是1-based索引，但这不影响我们匹配对应位置的元素，因为所有数组的索引是同步关联的）
• 四个unnest通过idx关联，保证同一位置的时间戳和偏移量一一对应
• $1就是你前端传入的时间戳参数，比如'2019-03-05 12:00:00'::timestamp

测试示例 #

当你传入时间戳'2019-03-05 12:00:00'::timestamp时，这条查询会返回：

完全符合你想要的结果。

另一种简化写法（针对单条目标数据行） #

如果你已经明确要查询特定的position行（比如已知id或layout_id），可以用generate_subscripts生成数组索引，写法更简洁：

SELECT
  x_position,
  y_position,
  rotation,
  translate_x[idx] AS translate_x,
  translate_y[idx] AS translate_y
FROM
  "public"."position" p,
  generate_subscripts(p.start_timestamps, 1) AS idx
WHERE
  start_timestamps[idx] <= $1
  AND end_timestamps[idx] > $1
  -- 可选：指定特定行
  -- AND p.id = 683;

这个写法直接通过索引取对应位置的偏移量元素，逻辑更直观。

内容的提问来源于stack exchange，提问作者Anton Hoerl