query - sql select record with greatest value

如何為每個鍵值選擇具有最新時間戳的行? (4)

您只能選擇組中的列或在聚合函數中使用的列。您可以使用聯接來實現此功能

select s1.* 
from sensorTable s1
inner join 
(
  SELECT sensorID, max(timestamp) as mts
  FROM sensorTable 
  GROUP BY sensorID 
) s2 on s2.sensorID = s1.sensorID and s1.timestamp = s2.mts

我有一張傳感器數據表。每行都有一個傳感器ID，一個時間戳和其他字段。我想為每個傳感器選擇一個具有最新時間戳的行，包括一些其他字段。

我認為解決方案是按傳感器ID進行分組，然後按max（timestamp）順序排序，如下所示：

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable 
GROUP BY sensorID 
ORDER BY max(timestamp);

這給了我一個錯誤，說“sensorField1必須出現在group by子句中或者在聚合中使用”。

解決這個問題的正確方法是什麼？

您可以將表連接到自身（在傳感器ID上），並將left.timestamp < right.timestamp添加為連接條件。然後你選擇行，其中right.id為null 。瞧，你得到了每個傳感器的最新條目。

http://sqlfiddle.com/#!9/45147/37

SELECT L.* FROM sensorTable L
LEFT JOIN sensorTable R ON
L.sensorID = R.sensorID AND
L.timestamp < R.timestamp
WHERE isnull (R.sensorID)

但請注意，如果您有少量ID和許多值，這將是非常耗費資源的！因此，我不建議將其用於某種測量材料，其中每個傳感器每分鐘收集一次值。但是在用例中，您需要跟踪“有時”更改的內容的“修訂”，這很容易。

為了完整起見，這是另一種可能的解決方案：

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable s1
WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID)
ORDER BY sensorID, timestamp;

我認為非常自我解釋，但如果您願意，可以提供更多信息，以及其他示例。它來自MySQL手冊，但上面的查詢適用於每個RDBMS（實現sql'92標準）。

這可以使用SELECT DISTINCT以相對優雅的方式完成，如下所示：

SELECT DISTINCT ON (sensorID)
sensorID, timestamp, sensorField1, sensorField2 
FROM sensorTable
ORDER BY sensorID, timestamp DESC;

以上適用於PostgreSQL（ here有更多信息），但我認為還有其他引擎。如果不明顯，它的作用是按傳感器ID和時間戳（從最新到最舊）對錶進行排序，然後返回每個唯一傳感器ID的第一行（即最新時間戳）。

在我的用例中，我從~1K傳感器讀取了大約10M的讀數，因此嘗試將表連接到基於時間戳的過濾器上是非常耗費資源的; 以上需要幾秒鐘。