每個外鍵的最近值

我有一個帶有主鍵、時間戳、值、外鍵的表。我想為列表中的每個外鍵找到最接近時間戳的值。

不幸的是，一個外鍵從給定時間戳到第一個值時間戳的差距可能是另一個 FK 的這個差距的許多倍。我曾經做過這樣的事情，但它只給我一行，而不是每個 fk 一行。

SELECT *
FROM   data_item
WHERE  fk_fc_id IN (35246,35247) 
AND    di_timestamp > '2013-11-01 00:00:00'
ORDER  BY di_timestamp ASC
LIMIT  1; 

就像我需要在每個 FK 的基礎上採取限制，但無法弄清楚如何做到這一點。

您可以使用視窗函式來實現您的目標。 lag()並且lead()可以幫助您進行查詢，例如

SELECT lag(di_timestamp) OVER ordering, lead(di_timestamp) OVER ordering
 FROM data_item
WHERE fk_fc_id IN (35246,35247)
WINDOW ordering AS (ORDER BY di_timestamp 
                   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

如果有的話，這將返回前一個和下一個時間戳。

要為給定時間戳之後並最接近它的每個列表獲取一行*..*最佳使用，標準 SQL 的 Postgres 特定擴展：fk_fc_idDISTINCT ONDISTINCT

SELECT DISTINCT ON (fk_fc_id)
      *
FROM   data_item
WHERE  fk_fc_id IN (35246,35247)
AND    di_timestamp > '2013-11-01 00:00:00' 
ORDER  BY fk_fc_id, di_timestamp;

如果您需要按 排序的結果di_timestamp，請將其包裝在子查詢中：

SELECT *
FROM  (
  SELECT DISTINCT ON (fk_fc_id)
         *
  FROM   data_item
  WHERE  fk_fc_id IN (35246,35247)
  AND    di_timestamp > '2013-11-01 00:00:00' 
  ORDER  BY fk_fc_id, di_timestamp
  ) sub
ORDER  BY di_timestamp;

或者您可以使用視窗函式row_number()（可能更慢，標準 SQL）：

SELECT *
FROM  (
  SELECT *, row_number() OVER (PARTITION BY fk_fc_id
                               ORDER BY di_timestamp) AS rn
  FROM   data_item
  WHERE  fk_fc_id IN (35246,35247)
  AND    di_timestamp > '2013-11-01 00:00:00' 
  ORDER  BY fk_fc_id, di_timestamp;
 ) sub
WHERE  rn = 1
ORDER  BY di_timestamp;

還有更多的方法。此相關答案中的更多詳細資訊：

如何有效地獲取“最新的相應行”？

更多關於 SO 的相關問題：

選擇每個 GROUP BY 組中的第一行？

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