Comment puis-je sélectionner les lignes avec l'horodatage le plus récent pour chaque valeur clé ?

Question

J'ai un tableau de données de capteurs. Chaque ligne contient un identifiant de capteur, un horodatage et d'autres champs. Je veux sélectionner une seule ligne avec le dernier horodatage pour chaque capteur, y compris certains des autres champs.

Je pensais que la solution serait de grouper par l'identifiant du capteur et ensuite d'ordonner par max(timestamp) comme ceci :

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

Cela me donne une erreur disant que "sensorField1 doit apparaître dans la clause group by ou être utilisé dans un agrégat".

Quelle est la bonne façon d'aborder ce problème ?

Answer 1

Il y a une réponse commune que je n'ai pas encore vue ici, c'est la fonction fenêtre. C'est une alternative à la sous-requête corrélée, si votre base de données la prend en charge.

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;

En fait, je l'utilise davantage que les sous-requêtes corrélées. N'hésitez pas à m'attaquer dans les commentaires au sujet de l'efficacité, je ne suis pas trop sûr de ce qu'il en est à cet égard.

Answer 2

J'avais pratiquement le même problème et j'ai fini par trouver une solution différente qui rend ce type de problème trivial à interroger.

J'ai un tableau de données de capteurs (données d'une minute provenant d'environ 30 capteurs).

SensorReadings->(timestamp,value,idSensor)

et j'ai une table de capteur qui contient beaucoup d'informations principalement statiques sur le capteur, mais les champs pertinents sont les suivants :

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

Les valeurs tvLastupdate et tvLastValue sont définies dans un déclencheur lors des insertions dans la table SensorReadings. J'ai toujours un accès direct à ces valeurs sans avoir besoin d'effectuer des requêtes coûteuses. Cela entraîne une légère dénormalisation. La requête est triviale :

SELECT idSensor,Description,tvLastUpdate,tvLastValue 
FROM Sensors

J'utilise cette méthode pour les données qui sont souvent interrogées. Dans mon cas, j'ai une table de capteurs et une grande table d'événements, qui reçoivent des données à la minute près ET des dizaines de machines mettent à jour des tableaux de bord et des graphiques avec ces données. Avec mon scénario de données, la méthode de déclenchement et de mise en cache fonctionne bien.

Answer 3

Je voulais également donner la réponse en utilisant le not exists clause :

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable t1
where not exists
( select * from sensorTable t2 where t1.sensorId=t2.sensorId
  and t1.timestamp < t2.timestamp );

ce qui, en fonction de votre SGBD/optimiseur SQL, peut être un choix efficace et bon.