Using sql, find the previous row on base of condition-CodePudding

I have a table that stores the records for users' different sessions(subscribe, unsubscribe,away, online). I have to calculate the time duration for each session. I can get the previous row using the LAG() function, but LAG() use offset, and I don't know what the offset is. I have to get that previous row where event NOT IN ('subscribe', 'unsubscribe'). Like in the attached image, for row number 8 with event subscribe, the previous row should be row number 4 with event away. I am using the latest version of MYSQL.

Here is mysql query so far I write, getting results, but I want to get previous row on base of condition, instead of setting a hardcoded offset to 2 in LAG() function.

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');

CodePudding user response：

See the next query:

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

This query divides the rowset to groups, each group starts from an event which is not 'subscribe' or 'unsubscribe'.

So for any row you can easily find the id value for previous row where event NOT IN ('subscribe', 'unsubscribe') with simple MIN(id) OVER (PARTITION BY group_no). Of course for non-zero group number.