Как я могу выбрать строки с самой последней меткой времени для каждого значения ключа?

У меня есть таблица данных датчика. Каждая строка имеет идентификатор датчика, временную метку и другие поля. Я хочу выбрать одну строку с последней меткой времени для каждого датчика, включая некоторые другие поля.

Я думал, что решение будет состоять в группировке с помощью идентификатора датчика, а затем упорядочить по max (timestamp) следующим образом:

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

Это дает мне ошибку, говоря, что "sensorField1 должен появляться в предложении group by или использоваться в совокупности".

Каков правильный подход к решению этой проблемы?

Ответ 1

Ради полноты вот еще одно возможное решение:

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, но приведенный выше запрос работает со всеми СУБД (реализующими стандарт sql'92).

Ответ 2

Это можно сделать относительно элегантно, используя SELECT DISTINCT, следующим образом:

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

Вышесказанное работает для PostgreSQL (немного больше информации здесь), но я думаю, что и другие движки. В случае, если не очевидно, что это делает, сортирует таблицу по идентификатору датчика и метке времени (от самой новой к самой старой), а затем возвращает первую строку (т.е. последнюю метку времени) для каждого уникального идентификатора датчика.

В моем случае у меня ~ 10M показаний с датчиков ~ 1K, поэтому попытка соединить таблицу с самим собой на основе фильтра на основе временных меток очень ресурсоемка; вышеупомянутое занимает пару секунд.

Ответ 3

Вы можете выбирать только столбцы, которые находятся в группе или используются в агрегатной функции. Вы можете использовать соединение, чтобы получить эту работу

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

Ответ 4

Вы можете объединить таблицу с самим собой (по идентификатору датчика) и добавить 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)

Но учтите, что это будет очень ресурсоемким, если у вас мало идентификаторов и много значений! Таким образом, я бы не рекомендовал это для какого-либо измерительного оборудования, где каждый датчик собирает значение каждую минуту. Однако в сценарии использования, где вам нужно отслеживать "ревизии" чего-то, что меняется просто "иногда", это легко.

Ответ 5

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

Ответ 6

У меня была в основном одна и та же проблема, и в итоге я нашел другое решение, которое делает проблему такого типа тривиальной для запроса.

У меня есть таблица данных датчиков (данные за 1 минуту от 30 датчиков)

SensorReadings->(timestamp,value,idSensor)

и у меня есть таблица датчиков, в которой много статических данных о датчике, но уместны следующие поля:

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

TvLastupdate и tvLastValue устанавливаются в триггере при вставках в таблицу SensorReadings. У меня всегда есть прямой доступ к этим значениям без необходимости делать дорогостоящие запросы. Это немного денормализуется. Запрос тривиален:

SELECT idSensor,Description,tvLastUpdate,tvLastValue 
FROM Sensors

Я использую этот метод для данных, которые часто запрашиваются. В моем случае у меня есть таблица датчиков и большая таблица событий, в которой есть данные, поступающие на уровне минут, и десятки машин обновляют информационные панели и графики с этими данными. С моим сценарием данных хорошо работает метод триггера и кеша.

Ответ 7

как ответил @fancyPants

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable stmt_outer
WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable stmt_inner WHERE outer.sensorID = inner.sensorID)

это называется Correlated Subqueries и отличается от обычных вложенных подзапросов
т.е.: каждый подзапрос выполняется один раз для каждой строки внешнего запроса.
Это означает, что внутренний подзапрос:

(SELECT MAX(timestamp) FROM sensorTable inner WHERE outer.sensorID = inner.sensorID)

будет выполняться для каждой строки, в результате столбец будет содержать max (timestamp), который затем сравнивается с внешним столбцом, чтобы выбрать только один отдельный sensor_id внешнего оператора

Ответ 8

Есть один общий ответ, который я еще не видел здесь - это оконная функция. Это альтернатива коррелированному подзапросу, если ваша БД его поддерживает.

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;

Я использую это больше, чем коррелированные подзапросы. Не стесняйтесь меня в комментариях по поводу эффективности, я не слишком уверен, как это складывается в этом отношении.