MySQL使用lead函數計算從日期到日期結束的持續時間

Question

我有一個表格儲存使用者不同會話的記錄（訂閱、取消訂閱、離開、線上）。我可以使用以下給定的查詢來計算每個會話的持續時間。 有一種情況，假設用戶在“2022 年 5 月 15 日晚上 11:00:00”開始在線會話，之後第二天他在“2022 年 5 月 16 日下午 02:00”離開：上午 00 點” 總線上時間為 3 小時，我得到的日期是 2022 年 5 月 15 日的最後一行。

但我需要這樣 5月15日至「2022年5月15日23:59:59」線上時間為1小時，5月16日至「2022年5月16日00:00:00至2022年5月16日02: 00” :00 AM”，在線2小時。因此，作為回應，它應該返回5 月15 日的1 小時和5 月16 日的2 小時，而不是5 月15 日總共返回3 小時。

我正在使用lead函數從created_at列獲取持續時間，有什麼方法可以限制lead函數計算持續時間直到下一個created_at直到23:59:59。

這是我的工作查詢。我正在使用最新的 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;

這是一些範例資料。

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