arrays - 根据条件和数组元素位置循环遍历 postgresql 表返回中的数组

Question

我在 posgresql 数据库的表中有以下行:

INSERT INTO "public"."position" ("id",
                                 "layout_id",
                                 "dining_table_id",
                                 "x_position",
                                 "y_position",
                                 "translate_x",
                                 "translate_y",
                                 "rotation",
                                 "start_timestamps",
                                 "end_timestamps")
VALUES (683, 32, 683, 1288, 0, E'{0,25}', E'{134,-98}', 0, E'{"2019-03-05 10:24:00","2019-04-05 10:24:00"}', E'{"2019-03-05 21:00:00","2019-04-05 21:00:00"}');

我想做一个查询，它返回我:

• x_position
• y_position
• 轮换

和

• 翻译_x
• 翻译_y

但只有在满足以下条件时才会出现在这些列中:

如果给定的时间戳(来自前端，应该是查询条件的一部分)大于或等于 start_timestamps 且小于 end_timestamps 数组元素，它们在数组中的位置与translate_x 和 translate_y 数组元素。

例如，如果给定的时间戳是:2019-03-05 12:00:00值为 0(位置 0)的 translate_x 列的数组元素和应返回值为 134(位置 0)的 translate_y 列的数组元素，因为 2019-03-05 12:00:00 小于 end_timestamps 列数组元素(位置 0)并且大于或等于 start_timestamps 列数组元素(位置 0)。

我的问题是如何相应地查询表？ (我希望我的表结构有意义)

我的尝试:

const result = await this.db.query(`
SELECT 
       p.x_position,
       p.y_position,
       p.rotation,
FROM POSITION p 
DECLARE 

s int8 := 0;
x int;

BEGIN
FOR x IN s..p.start_timestamps.length LOOP IF p.start_timestamps[x] <= $1
AND p.end_timestamps[x] > $1 THEN RETURN p.translate_x[x], p.translate_y[x] END LOOP;`
[timestamp]);

Answer 1

如果我没理解错的话，你可以这样做:

select * from (
    select i,id,layout_id,dining_table_id,x_position,y_position,translate_x[i],
           translate_y[i],start_timestamps[i],end_timestamps[i] from (
                          select generate_subscripts(translate_x,1) i,* from position
                  ) a
   ) b where start_timestamps<='2019-03-05 12:00:00'::timestamp 
            and end_timestamps>'2019-03-05 12:00:00'::timestamp

它应该可以工作，但是如果您可以更改数据库的定义，您应该创建一个新表，例如:

position_periods : (id_position integer ,start_timestamp timestamp,end_timestamp timestamp,translate_x integer,translate_y integer)