Extract differences of consecutive values ​​in MySQL 5.7

Question

Name	date	Hour	count
Mills	2022-07-17	twenty three	12
Mills	2022-07-18	00	15
Mills	2022-07-18	01	20
Mills	2022-07-18	02	twenty two
Mills	2022-07-18	03	25
Mills	2022-07-18	04	20
Mills	2022-07-18	05	twenty two
Mills	2022-07-18	06	25
Mike	2022-07-18	00	15
Mike	2022-07-18	01	20
Mike	2022-07-18	02	twenty two
Mike	2022-07-18	03	25
Mike	2022-07-18	04	20

My current input table stores count information recorded continuously every hour of the day. I need to extract the difference of consecutive count values, but since I'm forced to use MySQL 5.7, I'm having trouble doing this.

The query I wrote is as follows:

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

This does not give accurate results.

I would like to get the following output:

Name	date	Hour	count	difference
Mills	2022-07-17	twenty three	12	0
Mills	2022-07-18	00	15	3
Mills	2022-07-18	01	20	5
Mills	2022-07-18	02	twenty two	2
Mills	2022-07-18	03	25	3
Mills	2022-07-18	04	20	5
Mills	2022-07-18	05	twenty two	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	twenty two	2
Mike	2022-07-18	03	25	3
Mike	2022-07-18	04	20	5
Mike	2022-07-18	05	twenty two	2
Mike	2022-07-18	06	25	3

Please suggest what I'm missing.

Answer 1

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;

View the demo from db-fiddle.

Answer 2

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

• If your previous name is the same as your current name
• Then calculate the count difference
• Otherwise assign 0

It will work with the ABS a> function, applying absolute values ​​to the differences.

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;

See the demo here.

---

In MySQL 8.0 you can use something like LAG to get the output smoothly. It will be the same as:

• ABS Apply the absolute difference value,
• COALESCE is used to remove 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

See the demo here.