Extract difference in consecutive values for MySQL 5.7

user_943978 0
Viewed 62
Name Date Hours Count
Mills 2022-07-17 23 12
Mills 2022-07-18 00 15
Mills 2022-07-18 01 20
Mills 2022-07-18 02 22
Mills 2022-07-18 03 25
Mills 2022-07-18 04 20
Mills 2022-07-18 05 22
Mills 2022-07-18 06 25
MIKE 2022-07-18 00 15
MIKE 2022-07-18 01 20
MIKE 2022-07-18 02 22
MIKE 2022-07-18 03 25
MIKE 2022-07-18 04 20

My current input table stores information for counts recorded in each hour of the day consecutively. I need to extract the difference in values for consecutive counts but I'm having trouble doing it since I'm forced to use MySQL 5.7.

I have written the query as follows:

SET @cnt := 0;
SELECT Name, Date, Hours, Count, (@cnt := @cnt - Count) AS DiffCount
FROM Hourly
ORDER BY Date;

which is not giving exact results.

I expect to have the following output:

Name Date Hours Count Diff
Mills 2022-07-17 23 12 0
Mills 2022-07-18 00 15 3
Mills 2022-07-18 01 20 5
Mills 2022-07-18 02 22 2
Mills 2022-07-18 03 25 3
Mills 2022-07-18 04 20 5
Mills 2022-07-18 05 22 2
Mills 2022-07-18 06 25 3
MIKE 2022-07-18 00 15 0
MIKE 2022-07-18 01 20 5
MIKE 2022-07-18 02 22 2
MIKE 2022-07-18 03 25 3
MIKE 2022-07-18 04 20 5
MIKE 2022-07-18 05 22 2
MIKE 2022-07-18 06 25 3

Please suggest what I'm missing.

mysql sql
2 Answers

Try the following:

SET @count=(select Count_ from Hourly order by Name,Date_,Hours LIMIT 1);
Set @Name=(select Name from Hourly order by Name,Date_,Hours LIMIT 1);

select  Name,Date_,Hours,Count_,
ABS(curr_count-lag_count) as DiffCount
From
(
  select Name,Date_,Hours,Count_, 
  Case When @Name = Name Then @count Else Count_ End as lag_count
  , @count:=Count_ curr_count, @Name:=Name
  From Hourly order by Name,Date_,Hours
) D
Order By Name,Date_,Hours;

See a demo from db-fiddle.

user_127059120

In MySQL 5.7 you can update your variable inline in order to contain your updated value of "Count". Since when the value of "Name" changes you need to reset your variable, you can use another variable that contains the previous "Name" value. Then you use an IF function to check:

  • if your previous name is equal to your current name
  • then take the difference in counts
  • else assign 0

It would be combined with the ABS function, which applies the absolute value on the difference.

SET @cnt  := NULL;
SET @name := NULL;

SELECT Date,
       Hours,
       ABS(IF(@name = Name, 
              @cnt := @cnt - Count,
              0)                    ) AS DiffCount,
       (@name := Name)                AS Name,
       (@cnt := Count)                AS Count
FROM tab
ORDER BY Name DESC,
         Date, 
         Hours;

Check the demo here.

In MySQL 8.0 you can use a window fuction like LAG to get your output smoothly. It would be combined with:

  • ABS that applies the absolute value on the difference,
  • COALESCE employed for removing the first null value.
SELECT *, 
       COALESCE(ABS(
           Count - LAG(Count) OVER(
                       PARTITION BY Name
                       ORDER     BY Date, Hours
                   )
       ), 0) AS Diff 
FROM tab
ORDER BY Name DESC, 
         Date, 
         Hours

Check the demo here.

user_124928900
Related