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.idnull 。 瞧,你得到了每個傳感器的最新條目。

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的讀數,因此嘗試將表連接到基於時間戳的過濾器上是非常耗費資源的; 以上需要幾秒鐘。





greatest-n-per-group