sql - registros - ¿Cómo puedo seleccionar filas con la marca de tiempo más reciente para cada valor clave?

rownum oracle example

Question

Tengo una tabla de datos de sensores. Cada fila tiene un identificador de sensor, una marca de tiempo y otros campos. Quiero seleccionar una sola fila con la última marca de tiempo para cada sensor, incluidos algunos de los otros campos.

Pensé que la solución sería agrupar por ID de sensor y luego ordenar por max (timestamp) así:

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

Esto me da un error que dice que "sensorField1 debe aparecer en el grupo por cláusula o ser usado en un agregado".

¿Cuál es la forma correcta de abordar este problema?

Answer 1

Casi siempre tuve el mismo problema y terminé con una solución diferente que hace que este tipo de problema sea trivial de consultar.

Tengo una tabla de datos de sensores (datos de 1 minuto de aproximadamente 30 sensores)

SensorReadings->(timestamp,value,idSensor)

y tengo una tabla de sensores que tiene muchas cosas principalmente estáticas sobre el sensor, pero los campos relevantes son estos:

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

TvLastupdate y tvLastValue se configuran en un activador en las inserciones en la tabla de Lecturas del sensor. Siempre tengo acceso directo a estos valores sin necesidad de hacer consultas costosas. Esto se desnormaliza ligeramente. La consulta es trivial:

SELECT idSensor,Description,tvLastUpdate,tvLastValue 
FROM Sensors

Yo uso este método para los datos que se consultan a menudo. En mi caso, tengo una tabla de sensores y una tabla de eventos de gran tamaño, con datos que llegan al nivel de minutos Y docenas de máquinas están actualizando cuadros de mandos y gráficos con esos datos. Con mi escenario de datos, el método de activación y almacenamiento en caché funciona bien.

Answer 2

Esto se puede hacer de una manera relativamente elegante usando SELECT DISTINCT , de la siguiente manera:

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

Lo anterior funciona para PostgreSQL (más información here ) pero creo que también otros motores. En caso de que no sea obvio, lo que hace es ordenar la tabla por ID de sensor y marca de tiempo (de más reciente a más antigua), y luego devuelve la primera fila (es decir, la última marca de tiempo) para cada ID de sensor único.

En mi caso de uso, tengo ~ 10M lecturas de ~ 1K sensores, por lo que tratar de unirme a la tabla consigo mismo en un filtro basado en la marca de tiempo requiere una gran cantidad de recursos; Lo anterior lleva un par de segundos.

Answer 3

Por el bien de la integridad, aquí hay otra solución posible:

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable s1
WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID)
GROUP BY sensorID;

Creo que es bastante autoexplicativo, pero here''s más información si lo desea, así como otros ejemplos. Es del manual de MySQL, pero la consulta anterior funciona con todos los RDBMS (implementando el estándar sql''92).

Answer 4

Puede unirse a la tabla consigo mismo (en la identificación del sensor) y agregar left.timestamp < right.timestamp como condición de unión. Luego escoges las filas, donde right.id es null . Voila, tienes la última entrada por sensor.

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)

¡Pero tenga en cuenta que esto requerirá muchos recursos si tiene una pequeña cantidad de ID y muchos valores! Por lo tanto, no recomendaría esto para algún tipo de Medición, donde cada Sensor recopila un valor cada minuto. Sin embargo, en un caso de uso, donde necesita hacer un seguimiento de "Revisiones" de algo que cambia solo "a veces", es fácil.

Answer 5

Solo puede seleccionar columnas que están en el grupo o que se usan en una función agregada. Puedes usar una unión para que esto funcione

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

Answer 6

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