Utilisation d'une fonction LAG() conditionnelle non nulle en MySQL

Question

J'ai essayé d'appliquer quelques solutions provenant ici, mais ma question semble être quelque peu différente de celle de l'auteur du post.

---

J'ai un grand jeu de données data dans MySQL :

id          date          val
aaaaa       2021-01-01    TRUE
aaaaa       2021-01-02    FALSE
aaaaa       2021-01-03    FALSE
aaaaa       2021-01-04    TRUE
aaaaa       2021-01-05    FALSE
aaaaa       2021-01-06    TRUE
aaaaa       2021-01-07    FALSE
...
aaaaa       2021-12-31    FALSE
aaaab       2021-01-01    TRUE
aaaab       2021-01-02    FALSE
...
zzzzz       2021-12-31    FALSE

Ici, id est une donnée de type chaîne de caractères, date varie de 2021-01-01 à 2021-12-31 sans aucun jour manquant, et val contient une valeur booléenne, TRUE ou FALSE. data est trié par id, date.

J'aimerais ajouter deux colonnes, lagged_date et date_diff.

• lagged_date contient la date précédente de l'identifiant id où val = TRUE.
• date_diff calcule la différence du nombre de jours entre date et lagged_date dans cette ligne.

Idéalement, mon jeu de données final devrait ressembler à ceci :

id          date          val        lagged_date     date_diff
aaaaa       2021-01-01    TRUE       NULL            NULL
aaaaa       2021-01-02    FALSE      2021-01-01      1
aaaaa       2021-01-03    FALSE      2021-01-01      2
aaaaa       2021-01-04    TRUE       2021-01-01      3
aaaaa       2021-01-05    FALSE      2021-01-04      1
aaaaa       2021-01-06    TRUE       2021-01-04      2
aaaaa       2021-01-07    FALSE      2021-01-06      1
...
aaaaa       2021-12-31    FALSE      2021-12-25      6
aaaab       2021-01-01    TRUE       NULL            NULL
aaaab       2021-01-02    FALSE      2021-01-01      1
...

(Remarquez que ces données sont également triées par id, date)

J'ai essayé la requête suivante :

SELECT *,
       MAX(val) OVER (
          PARTITION BY id, val
          ORDER BY date
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS lagged_date,
       DATE_DIFF(date, lagged_date, DAY) AS date_diff
  FROM data

mais la colonne lagged_date ne produit pas la sortie désirée, produisant uniquement la val retardée. J'ai également essayé MAX(date), mais en vain.

Toute aide est appréciée.

Answer 1

Utilisez une sous-requête pour trouver la date précédente pour chaque 'id' où 'TRUE' est vrai, puis utilisez la fonction DATE_DIFF.

SELECT t1.id, t1.date, t1.val,
  (SELECT t2.date FROM data t2 WHERE t2.id = t1.id AND t2.val = TRUE AND t2.date < t1.date ORDER BY t2.date DESC LIMIT 1) as lagged_date,
  DATEDIFF(t1.date, (SELECT t2.date FROM data t2 WHERE t2.id = t1.id AND t2.val = TRUE AND t2.date < t1.date ORDER BY t2.date DESC LIMIT 1)) as date_diff
FROM data t1