Quels vélos étaient en service le 8 décembre?

Votre exigence peut être exprimée de manière plus centrée sur les données en trouver des vélos dont le dernier statut connu le 8 décembre ou avant était en direct .

C'est une façon (à mon humble avis, la manière la plus lisible) d'exprimer que dans SQL:

select bike_id
from bike_live_log bll
where updated_on = (
    select max(updated_on)
    from bike_live_log
    where bike_id = bll.bike_id
    and updated_on <= '2018-12-08'
)
and is_live

La sous-requête (corelated) trouve la date de la dernière mise à jour le ou avant le 8 décembre pour la ligne actuelle de la requête externe. S'il n'y a pas de telle ligne, null sera renvoyé, ce qui ne correspondra à aucune ligne de la requête externe, donc seuls les vélos qui ont des données le 8 décembre ou avant seront retournés.

Je vais essayer de vous aider à passer à la dernière étape. Vous êtes vraiment, vraiment proche!

Vous avez eu raison de trouver la date la plus récente updated_on pour chaque bike_id . Peu importe le nombre de fois qu'un vélo a été allumé ou éteint; vous ne vous souciez vraiment que du statut le plus récent avant la date qui vous intéresse.

Avec votre requête actuelle, vous savez déjà quand chaque bike_id a été mis à jour pour la dernière fois avant le 8 décembre.

Ensuite, vous pouvez utiliser ces informations pour savoir quelle était la valeur is_live pour chacune de ces valeurs bike_id à partir de cette last_update_date .

Vous pouvez le faire en utilisant votre requête existante en tant que sous-requête, ou en CTE si vous préférez, et rejoindre à nouveau votre table principale. Vos critères JOIN seront bike_id à bike_id et updated_on à last_update_date . En vous inscrivant aux dates, vous ne retournerez qu'un seul enregistrement pour chaque bike_id , et cet enregistrement sera celui qui vous intéresse.

Une fois votre JOIN assemblé, il vous suffira d'ajouter une clause WHERE pour limiter votre jeu de résultats aux lignes où is_live = ' TRUE ', qui ne renverra que bike_id de 1 .

tl; dr

Voici le code MySQL dont vous avez besoin:

SELECT bike_id FROM (
  SELECT * FROM (
    SELECT * FROM bikes 
    WHERE updated_on < '20181209'
    ORDER BY updated_on DESC, id DESC
  ) AS sub 
  GROUP BY bike_id
) AS sub2
WHERE is_live = true

Pourquoi cela fonctionne?

Nous devons décomposer le question un peu. J'aime partir de la position "Comment puis-je obtenir les informations dont j'ai besoin dans un format qui a du sens pour moi (en tant qu'être humain)?".

Donc, la première chose que j'ai faite a été d'obtenir un liste de tous les vélos avec des dates updated_on avant le 9 décembre (c'est-à-dire mis à jour le 8 décembre ou avant). J'ai également commandé ceci par le champ updated_on afin que je (en tant qu'être humain) puisse facilement voir le "dernier" enregistrement qui me dira le statut le plus récent de chaque vélo le 8 décembre ou avant: p >

SELECT * FROM (
  SELECT * FROM bikes 
  WHERE updated_on < '20181209'
  ORDER BY updated_on DESC, id DESC
) AS sub 
GROUP BY bike_id

À partir de là, je peux voir qu'il y a 5 enregistrements de changements d'état de vélo avant le 9 décembre. Je peux facilement voir que pour chaque vélo il y a plusieurs enregistrements de «mise à jour», mais je peux maintenant commencez par le haut de la liste et chaque identifiant de vélo que je rencontre est le statut le 8 décembre.

De plus, j'ai inclus un order_by pour l'identifiant d'enregistrement. Cela est nécessaire car il peut y avoir plusieurs mises à jour par jour. La date elle-même ne nous dira pas laquelle de ces mises à jour était la plus récente LE MÊME JOUR, nous utilisons donc l'ID pour le déterminer. (en supposant que ce soit chronologiquement incrémentiel).

Nous avons maintenant une liste de tous les statuts et identifiants de vélo dans la base de données avant le 9 décembre, nous devons limiter cela à un seul enregistrement par vélo. C'est facile, nous pouvons encapsuler la requête d'origine avec une nouvelle requête avec une directive Group By sur le bike_id.

SELECT * FROM bikes 
WHERE updated_on < '20181209'
ORDER BY updated_on DESC, id DESC

Group By dans MySQL sélectionne le premier enregistrement pour lequel il rencontre chaque groupe. Nous avons déjà ordonné la liste par date et id dans la requête d'origine, donc le premier enregistrement pour chaque bike_id sera le dernier statut pour cela vélo à partir du 8 décembre.

Il ne nous reste plus qu'à sélectionner le bike_id et filtrer les vélos non vivants en utilisant WHERE is_live = true sur cette dernière requête. C'est ainsi que nous aboutissons à la requête au début de cette réponse:

SELECT bike_id FROM (
  SELECT * FROM (
    SELECT * FROM bikes 
    WHERE updated_on < '20181209'
    ORDER BY updated_on DESC, id DESC
  ) AS sub 
  GROUP BY bike_id
) AS sub2
WHERE is_live = true

J'espère que cela vous aidera.

SELECT
bike_id,
MAX(CASE WHEN last_live_date <= '2018-12-08' AND ( last_not_live_date < last_live_date OR last_not_live_date IS NULL) THEN 1 ELSE 0 END) AS final_status
FROM
(
SELECT
    bike_id,
    MAX(CASE WHEN is_live = TRUE THEN updated_on END) AS last_live_date,
    MAX(CASE WHEN is_live = FALSE THEN updated_on END) AS last_not_live_date
FROM `BIKE_LIVE_LOG`
WHERE updated_on <= '2018-12-08'
GROUP BY bike_id
) AS a
GROUP BY bike_id
HAVING final_status = 1;

with A as (select * from bike_live_log
where updated_on <= '2018-12-08'),
B as (select bike_id,max(updated_on) as updated from A
     group by bike_id)
     select A.bike_id from A inner join B
     on A.updated_on = B.updated
     where is_live = True;