Jak mogę wybrać wiersze z najnowszą sygnaturą czasową dla każdej wartości klucza?


86

Mam tabelę danych czujnika. Każdy wiersz zawiera identyfikator czujnika, znacznik czasu i inne pola. Chcę wybrać jeden wiersz z najnowszą sygnaturą czasową dla każdego czujnika, w tym niektóre inne pola.

Pomyślałem, że rozwiązaniem byłoby pogrupowanie według identyfikatora czujnika, a następnie uporządkowanie według max (znacznika czasu) w następujący sposób:

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

To daje mi błąd mówiąc, że „sensorField1 musi występować w klauzuli group by lub być używane w agregacji”.

Jaki jest właściwy sposób rozwiązania tego problemu?


1
Jakiego silnika DB używasz?
juergen d

1
Podczas gdy poniższe odpowiedzi przy użyciu JOINs na wartości Max (timestamp) powinny działać, sugerowałbym dołączenie do SensorReadingId, jeśli masz go na sensorTable.
Thomas Langston

Odpowiedzi:


94

Ze względu na kompletność, oto inne możliwe rozwiązanie:

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;

Myślę, że to całkiem zrozumiałe, ale tutaj jest więcej informacji, jeśli chcesz, a także inne przykłady. Pochodzi z podręcznika MySQL, ale powyższe zapytanie działa z każdym RDBMS (implementującym standard sql'92).


56

Można to zrobić w stosunkowo elegancki sposób, korzystając z SELECT DISTINCT:

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

Powyższe działa dla PostgreSQL (więcej informacji tutaj ), ale myślę, że również inne silniki. Jeśli nie jest to oczywiste, posortuje tabelę według identyfikatora czujnika i sygnatury czasowej (od najnowszego do najstarszego), a następnie zwraca pierwszy wiersz (tj. Najnowszy znacznik czasu) dla każdego unikalnego identyfikatora czujnika.

W moim przypadku mam ~ 10 milionów odczytów z ~ 1K czujników, więc próba dołączenia do tabeli samego siebie na filtrze opartym na sygnaturze czasowej jest bardzo wymagająca; powyższe zajmuje kilka sekund.


To rozwiązanie jest naprawdę szybkie.
Włączono

Szybki i łatwy do zrozumienia. Dziękuję za wyjaśnienie przypadku użycia, ponieważ mój jest dość podobny.
Stef Verdonk,

Niestety, to nie działa w przypadku MySQL ( link )
silentsurfer

21

Możesz dołączyć do tabeli ze sobą (na identyfikatorze czujnika) i dodać left.timestamp < right.timestampjako warunek łączenia. Następnie wybierasz rzędy, gdzie right.idjest null. Voila, masz najnowszy wpis dla każdego czujnika.

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)

Ale pamiętaj, że będzie to bardzo wymagające zasobów, jeśli masz niewielką liczbę identyfikatorów i wiele wartości! Więc nie polecałbym tego dla jakiegoś rodzaju pomiarów, w których każdy czujnik zbiera wartość co minutę. Jednak w przypadku użycia, w którym trzeba śledzić „wersje” czegoś, co zmienia się tylko „czasami”, jest to łatwe.


To jest szybsze niż inne odpowiedzi, przynajmniej w moim przypadku.
rain_

@rain_ To naprawdę zależy od przypadku użycia. Dlatego nie ma „uniwersalnej odpowiedzi” na to pytanie.
dognose

19

Możesz wybrać tylko kolumny, które są w grupie lub są używane w funkcji agregującej. Możesz użyć sprzężenia, aby to zadziałało

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

... lub select * from sensorTable where (sensorID, timestamp) in (select sensorID, max(timestamp) from sensorTable group by sensorID).
Arjan

Myślę, że „LEFT JOIN” jest również stosowane, a nie tylko „INNER JOIN”; a część „i s1.timestamp = s2.mts” nie jest niezbędna IMHO. A jednak radzę stworzyć indeks na dwóch polach: sensorID + timestamp - szybkość zapytań rośnie świetnie!
Igor

4
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

2

Jest jedna powszechna odpowiedź, której jeszcze nie widziałem, a jest nią funkcja okna. Jest to alternatywa dla skorelowanego zapytania podrzędnego, jeśli Twoja baza danych je obsługuje.

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;

W rzeczywistości używam tego bardziej niż skorelowanych pod-zapytań. Zapraszam do komentowania skuteczności, nie jestem pewien, jak się to układa pod tym względem.


0

Miałem głównie ten sam problem i skończyło się na innym rozwiązaniu, które sprawia, że ​​tego typu problem jest trywialny do zapytania.

Mam tabelę danych z czujników (1 minuta danych z około 30 czujników)

SensorReadings->(timestamp,value,idSensor)

i mam tabelę czujników, która ma wiele głównie statycznych informacji o czujniku, ale odpowiednie pola to:

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

TVLastupdate i tvLastValue są ustawiane w wyzwalaczu podczas wstawiania do tabeli SensorReadings. Zawsze mam bezpośredni dostęp do tych wartości bez konieczności wykonywania kosztownych zapytań. To trochę się denormalizuje. Zapytanie jest trywialne:

SELECT idSensor,Description,tvLastUpdate,tvLastValue 
FROM Sensors

Używam tej metody do danych, które są często wyszukiwane. W moim przypadku mam tabelę czujników i dużą tabelę zdarzeń, które zawierają dane przychodzące na poziomie minutowym ORAZ dziesiątki maszyn aktualizują pulpity nawigacyjne i wykresy o te dane. W moim scenariuszu danych metoda wyzwalania i pamięci podręcznej działa dobrze.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.