在更多/更少特定時間點返回值

在 PostgreSQL 10.0 中，我有一個包含兩列的大表：

CREATE TABLE xs (
 ts timestamp with time zone NOT NULL,
 x integer NOT NULL
)

現在，如果我有 100 個給定的時間點，我如何準確地返回 100 個在（ ）這些時間點x之前的 -es （對於這些時間點中的每一個）？<=

對於一個單一的，這將非常容易：

SELECT x FROM xs WHERE ts <= '2018-03-13 11:41:47.167973+00' LIMIT 1

但是如何有效地為 100 人做到這一點呢？就像是：

SELECT x FROM xs 
 WHERE ts FIRST_LESS_THAN_EQUAL_IN
   ('2018-03-13 11:41:47.167973+00', 
    '2018-03-13 11:41:47.198564+00', 
    '2018-03-13 11:41:47.555668+00',
     ...
    '2018-03-13 11:41:57.766888+00')

顯然，在 上有一個索引ts。

我建議使用LATERAL子查詢，最好使用 aLEFT [OUTER] JOIN來保留所有輸入行（顯示 NULL 值以防根本不匹配）：

SELECT v.reference_ts, t.most_recent_x
FROM  (
  VALUES
     (timestamptz '2018-03-13 11:41:47.167973+00') -- type cast in first row
   , ('2018-03-13 11:41:47.198564+00') 
   , ('2018-03-13 11:41:47.555668+00')
   , ('2018-03-13 11:41:57.766888+00')
  ) AS v(reference_ts)
LEFT JOIN LATERAL (
  SELECT x AS most_recent_x
  FROM   xs
  WHERE  ts <= v.reference_ts  -- ts is unique (?!); see below
  ORDER  BY ts DESC
  LIMIT  1
  ) AS t ON true;

VALUES表達式是將 100 個時間戳放入我們可以使用的派生表中的一種*方法。*獨立VALUES表達式可能需要在第一行進行顯式類型轉換。看：

• 更新多行時強制轉換 NULL 類型

還有其他方法可以傳遞您的值，例如數組（或轉換為的數組文字timestamptz[]）和unnest()查詢：

• 使用大 IN 優化 Postgres 查詢

關於LATERAL子查詢：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

不要忘記，ORDER BY否則你會得到任意結果——比如 @a1ex07 評論。

如果ts不是唯一的，則返回哪個重複項是未定義的，除非您添加更多ORDER BY項目以使其具有確定性。喜歡：從任何一組同行ORDER BY ts DESC, x DESC中獲得最大的行。x並使索引匹配。

一個索引(ts)（就像你提到的那樣）對性能有奇效。

或者，理想情況下，(ts DESC, x)如果滿足僅索引掃描的先決條件。

• PostgreSQL 是否在此 JOIN 中使用僅索引掃描？

dbfiddle在這裡

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