從表中一一選擇行，將一列值與數組中的值進行比較

我在 PLPGSQL 程式碼塊中有一組時間戳：timestamp_array.

對於每個元素，timestamp_array我想獲取元素本身 ( timestamp_element) 和表 () 中的一些列，該表my_table也具有時間戳列，但我不想獲取表時間戳列本身，而是時間戳數組元素。條件是我只想為每個數組元素獲取一個表行，即數組中的時間戳元素與表時間戳列之間的最近匹配（到過去）。

我想要實現的是：

FOR EACH timestamp_element IN ARRAY timestamp_array
LOOP
 EXECUTE $EXE$
   SELECT my_table.some_other_columns...
   FROM my_table
   WHERE my_table.timestamp <= $1
   ORDER BY my_table.timestamp DESC
   LIMIT 1
 $EXE$
 USING timestamp_element
 INTO tmp_array;

 EXECUTE
   INSERT INTO temporary_table
   VALUES ($1, array_to_string($2, ', '))
 USING timestamp_element, tmp_array;
END LOOP;

我不知道前面的程式碼是否 100% 正確，但這只是為了更好地解釋我想要實現的目標。顯然，重點是只用一個查詢來完成這項工作，而不是用一個查詢數組元素。

如果我有一個實際案例my_table：

有一個timestamp_array：

[
 2020-02-15 12:00:00,
 2020-03-22 00:00:00,
 2020-06-14 17:00:00
]

我想要以下內容temporary_table，而不對每個數組元素使用一個獨立的查詢：

unnest()數組並LATERAL在您的表上執行子查詢：

SELECT a.timestamp_element, t.col1, t.col2, ...
FROM   unnest(timestamp_array) AS a(timestamp_element)
LEFT   JOIN LATERAL (
  SELECT *
  FROM   my_table t
  WHERE  t.timestamp <= a.timestamp_element
  ORDER  BY t.timestamp DESC
  LIMIT  1
  ) t ON true;

純 SQL。當然，您可以將它嵌套在 PL/pgSQL 塊中。

但是您當然不需要EXECUTE為此使用動態 SQL。

如果不是，也許添加另一個ORDER BY表達式來打破關係並獲得確定的結果。my_table.timestamp``UNIQUE

如果my_table很大，請確保有一個索引(timestamp)以使其快速。

LEFT JOIN .. ON true保留所有timestamp_element結果，即使在my_table.

看：

• LATERAL JOIN 和 PostgreSQL 中的子查詢有什麼區別？
• 優化 GROUP BY 查詢以檢索每個使用者的最新行

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