我有一個表來存盤用戶不同會話的記錄（訂閱、取消訂閱、離開、在線）。我必須計算每個會話的持續時間。我可以使用 LAG() 函式獲取上一行，但是 LAG() 使用偏移量，我不知道偏移量是什么。我必須得到前一行where event NOT IN ('subscribe', 'unsubscribe')。就像在附圖中一樣，對于帶有事件訂閱的第 8 行，前一行應該是帶有事件的第 4 行。我正在使用最新版本的 MYSQL。
到目前為止，這是我撰寫的 mysql 查詢，得到了結果，但我想根據條件獲取前一行，而不是在 LAG() 函式中將硬編碼的偏移量設定為 2。
select * from (
select `id`, `user_id`, `event`, `created_at`,
date(created_at) as date,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND, created_at, LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at))) as duration,
LAG(event,2) OVER (PARTITION BY user_id ORDER BY created_at) AS previous_event,
CASE
WHEN event = 'subscribe' and LAG(event,2) OVER (PARTITION BY user_id ORDER BY created_at) = 'away' THEN 'away'
WHEN event = 'subscribe' and LAG(event,2) OVER (PARTITION BY user_id ORDER BY created_at) = 'online' THEN 'online'
WHEN event = 'subscribe' and LAG(event,2) OVER (PARTITION BY user_id ORDER BY created_at) IS NULL THEN 'online'
ELSE event END as status
from `user_websocket_events` where event in ('online','away','unsubscribe','subscribe') and created_at between '2022-04-06 00:00:00' and '2022-04-07 23:59:59' and user_id in(19)
) as `Developer`
where created_at between '2022-04-06 00:00:00' and '2022-04-06 23:59:59';
#data for testing
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9606, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9609, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9610, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9611, 19, 'away');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9613, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9614, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9616, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9618, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9634, 19, 'online');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9635, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9636, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9637, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9638, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9639, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9640, 19, 'subscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9641, 19, 'away');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9642, 19, 'unsubscribe');
INSERT INTO user_websocket_events (id, user_id, event) VALUES (9643, 19, 'subscribe');
uj5u.com熱心網友回復：
請參閱下一個查詢：
SELECT *,
SUM(event NOT IN ('subscribe', 'unsubscribe')) OVER (ORDER BY id) group_no
FROM user_websocket_events
ORDER BY 1
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d313c095f5c034a0a03cf70defd8344a
此查詢將行集劃分為組，每個組從不是“訂閱”或“取消訂閱”的事件開始。
因此，對于任何行，您都可以使用 simple輕松找到前一行id的值。當然對于非零組號。event NOT IN ('subscribe', 'unsubscribe')MIN(id) OVER (PARTITION BY group_no)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/461714.html