如何为每个键值选择具有最近时间戳的行？

我有一张传感器数据表。每一行都有一个传感器 ID、一个时间戳和其他字段。我想为每个传感器选择一个具有最新时间戳的单行，包括其他一些字段。

我认为解决方案是按照传感器 ID 进行分组，然后按照 max (时间戳)进行排序，如下所示:

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

这给我带来一个错误: “ sensorField1必须出现在组 by 子句中，或者在聚合中使用。”

解决这个问题的正确方法是什么？

323064 次浏览
小开

只能选择组中的列或聚合函数中使用的列。您可以使用联接来使其工作

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
小开 
WITH SensorTimes As (
SELECT sensorID, MAX(timestamp) "LastReading"
FROM sensorTable
GROUP BY sensorID
)
SELECT s.sensorID,s.timestamp,s.sensorField1,s.sensorField2
FROM sensorTable s
INNER JOIN SensorTimes t on s.sensorID = t.sensorID and s.timestamp = t.LastReading

八年过去了，投票结果刚刚出来，所以我要指出，这是老办法了。这种新方法使用 row_number()窗函数或者 APPLY横向连接。

小开

您可以使用表本身(在传感器 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标准)。

小开

在 Postgres，这可以通过使用 SELECT DISTINCT以一种相对优雅的方式完成，具体如下:

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

更多信息。我怀疑它也适用于其他 SQL 风格，虽然显然不是 MySQL (链接-感谢提示@silentsurfer)

如果不是很明显，那么它将根据传感器 ID 和时间戳(从最新到最旧)对表进行排序，然后为每个唯一的传感器 ID 返回第一行(即最新的时间戳)。

在我的用例中，我有来自 ~ 1K 传感器的 ~ 10M 读数，因此尝试在基于时间戳的过滤器上将表与其本身连接起来是非常耗费资源的; 上述操作只需要几秒钟。

小开

我遇到了大部分相同的问题，最终得到了一个不同的解决方案，使得这种类型的问题对于查询来说很简单。

我有一个传感器数据表(来自大约30个传感器的1分钟数据)

SensorReadings->(timestamp,value,idSensor)

我有一个传感器表，里面有很多关于传感器的静态信息，但是相关的字段是这样的:

Sensors->(idSensor,Description,tvLastUpdate,tvLastValue,...)

TvLastupdate 和 tvLastValue 在插入到 SensorReadings 表时在触发器中设置。我总是可以直接访问这些值，而不需要执行任何昂贵的查询。这确实有点反常化。这个查询很简单:

SELECT idSensor,Description,tvLastUpdate,tvLastValue
FROM Sensors

对于经常被查询的数据，我使用这种方法。在我的例子中，我有一个传感器表和一个大型事件表，它们有数据进入分钟级别，并且有几十台机器正在用这些数据更新仪表板和图表。在我的数据场景中，触发器和缓存方法工作得很好。

小开

有一个常见的答案我还没有在这里看到，这是窗口函数。如果数据库支持的话，它可以替代相关子查询。

SELECT sensorID,timestamp,sensorField1,sensorField2
FROM (
SELECT sensorID,timestamp,sensorField1,sensorField2
, ROW_NUMBER() OVER(
PARTITION BY sensorID
ORDER BY timestamp
) AS rn
FROM sensorTable s1
WHERE rn = 1
ORDER BY sensorID, timestamp;

实际上，我使用这个比相关的子查询更多。请随意在评论中抨击我的效率，我不太确定在这方面它是如何堆积起来的。

小开

还想用 not exists条款给出答案:

SELECT sensorID,timestamp,sensorField1,sensorField2
FROM sensorTable t1
where not exists
( select * from sensorTable t2 where t1.sensorId=t2.sensorId
and t1.timestamp < t2.timestamp );

根据您的 DBMS/SQL 优化器的不同，这可能是一个高效的好选择。

小开

我知道这是一个古老的职位，但在我的情况下，我正在寻找一个解决方案，包括在方程的性能，因为我的文件有数百万行。

我动态地创建了一个临时表，其中包含搜索到的顶部值，然后将这个表与原始表连接起来。速度上的差异是巨大的:

CREATE TEMPORARY TABLE sensorTable_temp AS
(SELECT sensorID, MAX(timestamp) as max_t FROM sensorTable GROUP BY 1);


SELECT a.sensorID, a.timestamp, sensorFiled1, sensorFiled2
FROM sensorTable a, sensorTable_temp b
WHERE a.sensorID = b.sensorID AND a.timestamp=b.max_t;

临时表只对会话有效，因此在完成下一个句子之后不需要删除它。

当然，时间戳列中的索引也有很大帮助(但在我的例子中还不够)


提交答案