主页 / dba / 问题 / 300114
Accepted
Héctor
Asked: 2021-09-25 05:46:09 +0800 CST

从表中一一选择行，将一列值与数组中的值进行比较

  • 772

我在 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：

时间戳 可乐 ...
2020-02-13 23:12:07 12 ...
2020-03-27 10:37:01 15 ...
2020-06-14 16:32:44 7 ...
2020-06-14 17:01:57 33 ...

有一个timestamp_array：

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

我想要以下内容temporary_table，而不对每个数组元素使用一个独立的查询：

时间戳 可乐 ...
2020-02-15 12:00:00 12 ...
2020-03-22 00:00:00 12 ...
2020-06-14 17:00:00 7 ...
postgresql plpgsql

1 个回答

  1. Best Answer

    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.timestampUNIQUE

    如果my_table很大，请确保有一个索引(timestamp)以使其快速。

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

    看：

    • 1

相关问题