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:

Please suggest what I'm missing.

CodePudding user response：

Try the following:

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

select  Name,Date_,Hours,Count_,
ABS(curr_count-lag_count) as DiffCount
From
(
  select Name,Date_,Hours,Count_, 
  @count lag_count, @count:=Count_ curr_count
  From Hourly order by Date_,Hours
) D;

See a demo from db-fiddle.

CodePudding user response：

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.