In a mysql table where I record, on the one hand a volume, and on the other hand the date and time that record was taken... I can't make a mysql query that tells me the variation in % of the volume between the capture most recent and the one from X minutes/hours ago, for example:
ID Sujeto Volumen Fecha
---- ------ --------- -------------------
1587 HGT 1651 2018-05-17 09:55:27
1762 HGT 1725 2018-05-17 11:56:54
Starting from the fact that I have that data in the table, I can get the most recent volume with:
select volumen from tabla order by id desc limit 1
And I can get the volume from X minutes ago with something like this:
select volumen from tabla where fecha >= 2018-05-17 09:55:00 order by id asc limit 1
What I don't get right is to put it together in the same mysql query that returns me, directly, that between those values there is a positive variation of 4.28%.
The query you're looking for (updated with the comment where you say you only want to calculate the increment on the most current record) is as follows:
The inner query gets only the last record of the searched subject:
To each record of this query, the data obtained from the immediately previous record is added through this nested query:
In the main query, the percentage increase is calculated with the typical percentage increase formula:
With the two data of your example the output would be:
Finally,
WHERE Volumen_a IS NOT NULL
it prevents the first records from being displayed, for which the increment cannot be calculated because there is no previous record.You can see how it works online at this link .