MySQL Consecutive Rows – Selecting Consecutive Rows with Same Values and One That Differs in MySQL

innodbMySQLmysql-8.0

So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this:

userID        status     Duration_in_this_status (min) 
------  -------------------   ------
Brown         idle             40
Brown         idle             ?

The ? inidcated that it is the time from this 

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
-> Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle

till now.

I tried to modify the answer from the above question but all I could managae was create a variable @next_time and increase it when the if goes to @groupNumber +1 but I couldn't work it out because all it does it increases the year by one.

   SELECT userID, status, TIMESTAMPDIFF(minute, MIN(tStamp), MAX(tStamp)) AS 
duration
    FROM (
       SELECT
       t.*
        , @groupNumber := IF(@prev_userID != userID OR @prev_status != 
status, @groupNumber + 1 and @next_time=@next_time+1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_status := status
    , @next_time   := tStamp
    FROM t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_status := NULL) var_init_subquery
    ORDER BY userID, tStamp
) sq
GROUP BY gn, userID, statu

So can any one help me to get that next value using these user-defined variables? Thanks

MySQL version 8.0.16

Best Answer

If you are on version 8.x (or above), you can take advantage of window functions like LAG/LEAD. This functions return a value of previous or next next row inside a partition, following an specific order.

Using your data:

SELECT 
    userID, tStamp, status,
    CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status
         THEN 0 ELSE 1 END AS rst
FROM
   t;

returns:

userID | tStamp              | status | rst
:----- | :------------------ | :----- | --:
Brown  | 2017-10-18 03:20:28 | idle   |   1
Brown  | 2017-10-18 03:25:28 | idle   |   0
Brown  | 2017-10-18 04:00:28 | active |   1
Brown  | 2017-10-18 04:10:28 | active |   0
Brown  | 2017-10-18 04:35:28 | idle   |   1
Brown  | 2017-10-18 04:45:28 | idle   |   0
Jason  | 2017-10-18 03:20:00 | idle   |   1

Notice rst flagged each status change. Now using this news values you can easily find time differences between those rows where rst=1.

WITH ct AS
(
    SELECT 
        userID, tStamp, status,
        CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status
             THEN 0 ELSE 1 END AS rst
    FROM
       t
)
SELECT
    userID, CONCAT("From ", status, " to ",
                   LEAD(status) OVER (PARTITION BY userID ORDER BY tStamp)) status, 
    timestampdiff(MINUTE, tStamp, 
                  LEAD(tStamp) OVER (PARTITION BY userID ORDER BY tStamp)) tDiff
FROM
    ct
WHERE
    rst = 1;

userID | status              | tDiff
:----- | :------------------ | ----:
Brown  | From idle to active |    40
Brown  | From active to idle |    35
Brown  | null                |  null
Jason  | null                |  null

db<>fiddle here

Related Question