MySQL verwendet die Lead-Funktion, um die Dauer vom Datum bis zum Ende des Datums zu berechnen

Question

Ich habe eine Tabelle, die Aufzeichnungen verschiedener Sitzungen eines Benutzers speichert (Anmelden, Abmelden, Verlassen, Online). Ich kann die Dauer jeder Sitzung mithilfe der unten angegebenen Abfrage berechnen. Es gibt ein Szenario, sagen wir, ein Benutzer startet eine Online-Sitzung am „15. Mai 2022, 23:00:00 Uhr“ und verlässt diese am nächsten Tag am „16. Mai 2022, 14:00 Uhr: 00 Uhr“. Die gesamte Online-Zeit beträgt 3 Stunden und das Datum, das ich für die letzte Zeile erhalte, ist der 15. Mai 2022.

Aber ich brauche das Die Online-Zeit vom 15. Mai bis „23:59:59 Uhr am 15. Mai 2022“ beträgt 1 Stunde und vom 16. Mai bis „00:00:00 Uhr am 16. Mai 2022 bis 02:00 Uhr am 16. Mai 2022“ 00 „:00 Uhr“, online für 2 Stunden. Als Antwort sollte also 1 Stunde vom 15. Mai und 2 Stunden vom 16. Mai zurückgegeben werden, statt insgesamt 3 Stunden für den 15. Mai

Ich verwende die Lead-Funktion, um die Dauer aus der Spalte „created_at“ abzurufen. Gibt es eine Möglichkeit, die Lead-Funktion zu begrenzen, um die Dauer bis zum nächsten „created_at“ bis 23:59:59 zu berechnen?

Das ist meine Stellenanfrage. Ich verwende die neueste MySQL(8)-Version.

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;

Hier sind einige Beispieldaten.

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

使用动态日历表按天分割会话

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<>fiddle