3
votes

Rechercher l'enregistrement le plus proche de la date / heure spécifiée

J'essaie de récupérer les données d'une table qui est la plus proche d'une date et d'une heure spécifiées par exemple, comme vous pouvez voir ci-dessous un petit peu de données sur les portes et si elles sont ouvertes et fermées à une heure précise. Je devais récupérer la liste des portes regroupées par DoorTitle plus près d'une heure précise. L'exemple de données est ci-dessous

DoorTitle    Status    DateTime
Door_3       CLOSED    2019-04-04 12:23:42
Door_2       OPEN      2019-04-02 23:37:02
Door_1       CLOSED    2019-04-04 19:56:31

Disons que je veux connaître l'état des portes si elles sont ouvertes ou fermées à 2019-04-04 23:54:55 code > pour me donner les enregistrements regroupés par titre de porte les plus proches de cette heure. J'ai essayé cette requête mais cela me donne les enregistrements les plus proches de cette heure, il les sélectionne au hasard en vérifiant si DateHeure est inférieure à 2019-04-04 23:54:55 code>.

SELECT DoorTitle,Status,DateTime 
FROM Doors 
WHERE DateTime <= '2019-04-04 23:54:55'  
GROUP BY DoorTitle;

Les résultats que je veux après l'exécution de la requête sont la liste des portes regroupées par DoorTitle le plus proche de la date et de l'heure, c'est-à-dire 2019-04-04 23 : 54: 55 spécifié comme indiqué ci-dessous, il devrait renvoyer ces résultats mais la requête que j'utilise ne me renvoie pas cela.

Id    DoorTitle    Status    DateTime
1     Door_1       OPEN      2019-04-04 9:16:22
2     Door_2       CLOSED    2019-04-01 15:46:54
3     Door_3       CLOSED    2019-04-04 12:23:42
4     Door_2       OPEN      2019-04-02 23:37:02
5     Door_1       CLOSED    2019-04-04 19:56:31

Faites-moi savoir quelle requête SQL Je peux utiliser pour y parvenir et comment ma requête est fausse. Merci d'avance.

sql date mysql select greatest-n-per-group

0 commentaires

3 Réponses :

2
votes

N'utilisez pas d'agrégation. Utilisez le filtrage:

SELECT d.*
FROM Doors d
WHERE d.DateTime = (SELECT MAX(d2.DateTime)
                    FROM doors d2
                    WHERE d2.DoorTitle = d.DoorTitle AND
                          d2.DateTime <= '2019-04-04 23:54:55'
                   );

Les performances doivent être raisonnables avec un index sur portes (DoorTitle, DateHeure) .

0 commentaires

2
votes

J'utiliserais une sous-requête corrélée avec une condition NOT EXISTS :

SELECT DoorTitle, Status, DateTime
FROM Doors t
WHERE 
    DateTime < '2019-04-04 23:54:55'
    AND NOT EXISTS (
        SELECT 1
        FROM Doors t1
        WHERE  t1.DateTime < '2019-04-04 23:54:55' AND t1.DateTime > t.DateTime
    )

La condition NOT EXISTS garantit qu'il n'y a pas d'enregistrement le plus proche de la date cible.

0 commentaires

1
votes

Vous pouvez calculer la dernière date / heure par porte, puis joindre les résultats avec la table d'origine:

SELECT Doors.*
FROM (
    SELECT DoorTitle, MAX(DateTime) AS MaxDateTime
    FROM Doors
    WHERE DateTime <= '2019-04-04 23:54:55'
    GROUP BY DoorTitle
) AS sq
JOIN Doors ON sq.DoorTitle = Doors.DoorTitle AND sq.MaxDateTime = Doors.DateTime

0 commentaires