Is there a way to order by value increase compared to older entries in MYSQL?

I want to order each ID by the percentage increase in a three day timeframe. If there is no entry three days ago, it should move up the date until today’s date. In the following I have explained the layout for the database environment.

I have a table in MYSQL with the below structure (NOTE: The ID column is not unique.).

CREATE TABLE test (
  ID INT(3) NOT NULL,
  value INT(3) NOT NULL,
  date DATE NOT NULL
);

​x
 
CREATE TABLE test (  ID INT(3) NOT NULL,  value INT(3) NOT NULL,  date DATE NOT NULL);​

Every day there will be new values inserted for n number of IDs. The table will then look somewhat like this:

ID	value	date
1	4	2020-12-08
1	4	2020-12-07
1	7	2020-12-06
1	5	2020-12-05
2	10	2020-12-08
2	8	2020-12-07
2	7	2020-12-06
3	10	2020-12-08
4	10	2020-12-08

For this example the SQL query should return the following order. ID 2 had the highest increase, so it is first, followed by no increase and last the decreased one.

ID	value [current] / value [3 days ago]
2	`10 / 7 = 1.43`
3	`10 / 10 = 1`
4	`7 / 7 = 1`
1	`4 / 7 = 0.57`

I hope my logic is sound. I have tried using the following query, however this only works if the subqueries return only one result which they should not.

SELECT
  (SELECT value from test GROUP BY ID ORDER BY date DESC) AS 'first',
  (SELECT value from test WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) GROUP BY ID ORDER BY date ASC) AS 'last'

 
SELECT  (SELECT value from test GROUP BY ID ORDER BY date DESC) AS 'first',  (SELECT value from test WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) GROUP BY ID ORDER BY date ASC) AS 'last'​

Here is the Fiddle for the example: http://sqlfiddle.com/#!9/ded7c92/2

This is a pretty integral part of my work, so any help will be greatly appreciated.

Answer

This is a little tricky, because MySQL does not have a “first”/”last” aggregation function. One method uses window functions:

select id, oldest_value, newst_value
from (select t.*,
             row_number() over (partition by id order by date) as seqnum,
             first_value(value) over (partition by id order by date) as oldest_value,
             first_value(value) over (partition by id order by date desc) as newest_value
      from test t
     ) t
where seqnum = 1
order by newest_value / oldest_value;

 
select id, oldest_value, newst_valuefrom (select t.*,             row_number() over (partition by id order by date) as seqnum,             first_value(value) over (partition by id order by date) as oldest_value,             first_value(value) over (partition by id order by date desc) as newest_value      from test t     ) twhere seqnum = 1order by newest_value / oldest_value;​

This requires MySQL 8+. Here is a db<>fiddle.

Advertisement

Answer