센서 데이터 테이블이 있습니다. 각 행에는 센서 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 판독 값이 있으므로 타임 스탬프 기반 필터에서 테이블 자체를 조인하는 것은 매우 리소스 집약적입니다. 위의 작업은 몇 초가 걸립니다.
답변
테이블 자체 (센서 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와 많은 값이있는 경우 리소스를 많이 사용한다는 점에 유의하십시오! 따라서 각 센서가 매분 값을 수집하는 일종의 측정 자료에는 이것을 권장하지 않습니다. 그러나 “가끔”만 변경되는 항목의 “개정”을 추적해야하는 사용 사례에서는 쉽게 진행할 수 있습니다.
답변
그룹에 있거나 집계 함수에 사용되는 열만 선택할 수 있습니다. 조인을 사용하여이 작업을 수행 할 수 있습니다.
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
답변
여기에서 아직 보지 못한 일반적인 대답이 하나 있는데, 바로 창 기능입니다. DB가 지원하는 경우 상관 하위 쿼리의 대안입니다.
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;
나는 이것을 상호 관련된 하위 쿼리보다 더 많이 사용합니다. 효율성에 대한 의견에서 자유롭게 저를 체포하십시오. 그 점에서 어떻게 쌓여 있는지 잘 모르겠습니다.
답변
나는 거의 같은 문제를 가지고 있었고 이러한 유형의 문제를 쿼리하기 쉽게 만드는 다른 솔루션으로 끝났습니다.
센서 데이터 테이블이 있습니다 (약 30 개 센서의 1 분 데이터).
SensorReadings->(timestamp,value,idSensor)
센서에 대한 대부분의 정적 항목이 많은 센서 테이블이 있지만 관련 필드는 다음과 같습니다.
Sensors->(idSensor,Description,tvLastUpdate,tvLastValue,...)
tvLastupdate 및 tvLastValue는 SensorReadings 테이블에 삽입 할 때 트리거에 설정됩니다. 저는 항상 값 비싼 쿼리를 수행 할 필요없이 이러한 값에 직접 액세스 할 수 있습니다. 이것은 약간 비정규 화됩니다. 쿼리는 간단합니다.
SELECT idSensor,Description,tvLastUpdate,tvLastValue
FROM Sensors
자주 쿼리되는 데이터에이 방법을 사용합니다. 제 경우에는 분 단위로 들어오는 데이터가있는 센서 테이블과 대형 이벤트 테이블이 있고 수십 대의 컴퓨터가 해당 데이터로 대시 보드와 그래프를 업데이트하고 있습니다. 내 데이터 시나리오에서는 트리거 및 캐시 방법이 잘 작동합니다.
