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

Par souci d'exhaustivité, voici une autre solution possible :

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;

Je pense que ça s'explique assez bien, mais voici plus d'infos si vous le souhaitez, ainsi que d'autres exemples. C'est tiré du manuel MySQL, mais la requête ci-dessus fonctionne avec tous les SGBDR (implémentant la norme sql'92).

Answer 2

Dans Postgres, cela peut être fait d'une manière relativement élégante à l'aide de SELECT DISTINCT comme suit :

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

Plus d'informations aquí . Je pense que cela fonctionne également pour d'autres versions de SQL, mais apparemment pas pour MySQL ( lien - merci pour le conseil @silentsurfer)

Au cas où ce ne serait pas évident, il s'agit de trier le tableau par ID de capteur et horodatage (du plus récent au plus ancien), puis de renvoyer la première ligne (c'est-à-dire le dernier horodatage) pour chaque ID de capteur unique.

Dans mon cas d'utilisation, je dispose d'environ 10 millions de relevés provenant d'environ 1 000 capteurs, de sorte qu'essayer de joindre la table avec elle-même sur un filtre basé sur l'horodatage est très gourmand en ressources ; ce qui précède ne prend que quelques secondes.

Answer 3

Vous pouvez uniquement sélectionner les colonnes qui se trouvent dans le groupe ou qui sont utilisées dans une fonction d'agrégation. Vous pouvez utiliser une jointure pour que cela fonctionne

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 4

Vous pouvez joindre la table avec elle-même (sur l'id du capteur), et ajouter left.timestamp < right.timestamp comme condition d'adhésion. Ensuite, vous choisissez les lignes, où right.id es null . Voilà, vous avez la dernière entrée par capteur.

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)

Mais notez bien que cette méthode sera très gourmande en ressources si vous avez un petit nombre d'identifiants et de nombreuses valeurs ! Je ne le recommanderais donc pas pour une sorte d'outil de mesure, où chaque capteur collecte une valeur chaque minute. Cependant, dans un cas d'utilisation, où vous avez besoin de suivre les "révisions" de quelque chose qui change juste "parfois", c'est facile.

Answer 5

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

---

Huit ans plus tard, cette idée vient d'être votée, et je dois donc préciser que c'est l'ancienne façon de faire. La nouvelle façon utilise le row_number() ou une fonction de fenêtrage APPLY jointure latérale.