MySQL how to calculate between the current entry and the porpoise one-CodePudding

I hope my terminologies are correctly but here goes

I need to calculate the data transferred between entries(MB/s). I have a time series that has the timestamp and sum of the data sent and received as utilisation.

enter image description here

Table utilisation has a component called 'network'

Time                            utilisation
2021-11-19 11:14:13             291815568369
2021-11-19 11:14:53             291820740719
2021-11-19 11:15:33             291826081965
2021-11-19 11:16:13             291831134637

So to get an accurate answer I need to know the difference in time and the difference in utilisation

utilisation rate = (291820740719 - 291815568369 ) / (11:14:53 -11:14:13)

To get the above table I have used this query

SELECT
  time AS "time",
  utilisation
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = 'network'
ORDER BY time

CodePudding user response：

If your goal is to calculate the formula between every 2 consecutive rows I would do it with a stored procedure using a cursor (you can read about it here: https://dev.mysql.com/doc/refman/8.0/en/cursors.html

then inside the loop you will do your calculation each time between the new fetched row and the previous one (make sure you deal with the first round)

CodePudding user response：

This seems to work. The aim is to calculate the data rates up and down.

SELECT
  time AS "time",
   ( utilisation - LAG(utilisation,1) OVER ( ORDER BY time) ) /  (  time - LAG(time,1) OVER ( ORDER BY time) ) time_usage
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = 'network'
ORDER BY time