각 키 값에 대해 가장 최근의 타임 스탬프가있는 행을 어떻게 선택할 수 있습니까?

각 키 값에 대해 가장 최근의 타임 스탬프가있는 행을 어떻게 선택할 수 있습니까?

---

센서 데이터 테이블이 있습니다. 각 행에는 센서 ID, 타임 스탬프 및 기타 필드가 있습니다. 다른 필드 중 일부를 포함하여 각 센서에 대한 최신 타임 스탬프가있는 단일 행을 선택하고 싶습니다.

해결책은 센서 ID별로 그룹화 한 다음 max (timestamp)별로 다음과 같이 주문하는 것이라고 생각했습니다.

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

이로 인해 "sensorField1이 group by 절에 나타나거나 집계에 사용되어야합니다."라는 오류가 발생합니다.

이 문제에 접근하는 올바른 방법은 무엇입니까?

---

완전성을 위해 가능한 또 다른 해결책은 다음과 같습니다.

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 ( 여기에 더 많은 정보 )에서 작동하지만 다른 엔진도 생각합니다. 명확하지 않은 경우 센서 ID 및 타임 스탬프 (가장 오래된 것부터 가장 오래된 것 순)별로 테이블을 정렬 한 다음 각 고유 센서 ID에 대한 첫 번째 행 (예 : 최신 타임 스탬프)을 반환합니다.

내 사용 사례에서는 ~ 1K 센서에서 ~ 10M 판독 값이 있으므로 타임 스탬프 기반 필터에서 테이블 자체와 결합하는 것은 매우 리소스 집약적입니다. 위의 작업은 몇 초가 걸립니다.

---

그룹에 있거나 집계 함수에 사용되는 열만 선택할 수 있습니다. 조인을 사용하여이 작업을 수행 할 수 있습니다.

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)와 left.timestamp < right.timestamp조인하고 조인 조건으로 추가 할 수 있습니다. 그런 다음 행을 선택, 어디는 right.id것입니다 null. Voila, 센서 당 최신 항목이 있습니다.

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와 많은 값이있는 경우 리소스를 많이 사용한다는 점에 유의하십시오! 따라서 각 센서가 매분 값을 수집하는 일종의 측정 자료에는 이것을 권장하지 않습니다. 그러나 "가끔"만 변경되는 항목의 "개정"을 추적해야하는 사용 사례에서는 쉽게 진행할 수 있습니다.

---

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

---

나는 거의 같은 문제를 가지고 있었고 이러한 유형의 문제를 쿼리하기 쉽게 만드는 다른 솔루션으로 끝났습니다.

센서 데이터 테이블이 있습니다 (약 30 개 센서에서 1 분 데이터).

SensorReadings->(timestamp,value,idSensor)

and I have a sensor table that has lots of mostly static stuff about the sensor but the relevant fields are these:

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

The tvLastupdate and tvLastValue are set in a trigger on inserts to the SensorReadings table. I always have direct access to these values without needing to do any expensive queries. This does denormalize slightly. The query is trivial:

SELECT idSensor,Description,tvLastUpdate,tvLastValue 
FROM Sensors

I use this method for data that is queried often. In my case I have a sensor table, and a large event table, that have data coming in at the minute level AND dozens of machines are updating dashboards and graphs with that data. With my data scenario the trigger-and-cache method works well.

---

There is one common answer I haven't see here yet, which is the Window Function. It is an alternative to the correlated sub-query, if your DB supports it.

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;

I acually use this more than correlated sub-queries. Feel free to bust me in the comments over effeciancy, I'm not too sure how it stacks up in that regard.

참고URL : https://stackoverflow.com/questions/17327043/how-can-i-select-rows-with-most-recent-timestamp-for-each-key-value