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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.