MySQL utilise la fonction lead pour calculer la durée de la date à la fin de la date

Question

J'ai une table qui stocke les enregistrements des différentes sessions d'un utilisateur (abonnement, désabonnement, départ, en ligne). Je peux calculer la durée de chaque session en utilisant la requête ci-dessous. Il existe un scénario, disons qu'un utilisateur démarre une session en ligne le « 15 mai 2022 à 23:00:00 » et après cela, le lendemain, il quitte le « 16 mai 2022 à 14:00 : 00 AM ». Le temps total de connexion est de 3 heures et la date que j'obtiens est le 15 mai 2022 pour la dernière ligne.

Mais j'ai besoin de ça Le temps de connexion du 15 mai à "23:59:59 le 15 mai 2022" est de 1 heure, et du 16 mai à "00:00:00 le 16 mai 2022 à 02h00 le 16 mai 2022" 00 ":00 AM", en ligne pendant 2 heures Donc en réponse il devrait revenir 1 heure du 15 mai et 2 heures du 16 mai au lieu de 3 heures au total pour le 15 mai

J'utilise la fonction lead pour obtenir la durée de la colonne create_at, existe-t-il un moyen de limiter la fonction lead pour calculer la durée jusqu'à la prochaine création_at jusqu'à 23:59:59.

Voici ma requête d'emploi. J'utilise la dernière version de MySQL(8).

select `id`, `user_id`, `status`, `created_at`,
 SEC_TO_TIME(TIMESTAMPDIFF(SECOND, created_at,
LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at))) as duration,
 date(created_at) as date from `user_websocket_events` as `all_status`
 where created_at between '2022-05-15 00:00:00' and '2022-05-16 23:59:59' and `status` is not null
 and user_id in (69) order by `id` asc;

Voici quelques exemples de données.

INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10816, 69, 'subscribe', 'online', null, '2022-05-15 12:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10817, 69, 'away', 'away', null, '2022-05-15 20:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10818, 69, 'online', 'online', null, '2022-05-15 22:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10819, 69, 'away', 'away', null, '2022-05-16 02:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10820, 69, 'unsubscribe', 'unsubscribe', null, '2022-05-16 03:57:31', '2022-05-14 10:57:37');

Answer 1

Séances divisées par jour à l'aide d'un calendrier dynamique

with recursive calendar as (
      select timestamp('2022-05-01 00:00') start_time, timestamp('2022-05-01 23:59:59')  end_time, 1 id 
      union all
      select start_time + interval 1 day, end_time + interval 1 day, id+1
      from calendar
      where id < 100
)
select e.id,  e.status, date(greatest(c.start_time, e.created_at)) date,
   greatest(c.start_time, e.created_at) as created_at,
   least(c.end_time, e.ended_at) as ended_at
from (
   select `id`, `user_id`, `status`, `created_at`,
     -- a session end is restricted to the end of the requierd interval 
     LEAD(created_at, 1, '2022-05-16 23:59:59') OVER (PARTITION BY user_id ORDER BY created_at) as ended_at
   from `user_websocket_events` 
   where 
     -- only sessions started within the requierd interval 
     created_at between '2022-05-15 00:00:00' and '2022-05-16 23:59:59' and `status` is not null
     and user_id in (69) 
   ) e
join calendar c on c.start_time < e.ended_at and e.created_at < c.end_time
order by id;

db<>violon