Calculate difference between two avg based on same value from different columns

I have a table with football odds that looks like below:

fixture_id	H_odds	D_odds	A_odds	ev_tstamp	updated
120000	1.40	1.50	1.60	132000	12
120000	1.20	1.20	1.20	132000	11
120000	1.20	1.20	1.20	132000	10
120000	1.10	1.20	1.20	132000	9
180000	1.20	1.30	1.60	132000	12
180000	1.10	1.20	1.20	132000	11
180000	1.50	1.80	1.50	132000	10

I would like to get for each fixture_id from group of ids difference between the odds where update value is MIN and MAX and then the odds itself where update value is MAX.

So based on this example, the outcome should be:

fixture_id	H_odds	D_odds	A_odds	ev_tstamp	updated	dif_h	dif_d	dif_a
120000	1.40	1.50	1.60	132000	12	0.3	0.3	0.4
180000	1.20	1.30	1.60	132000	12	-0.3	-0.5	0.1

The only way I can think of is something like this:

select t.H_odds  from avg_odds t where
t.updated = (select min(t2.updated) from avg_odds t2 where
             t2.fixture_id = t.fixture_id and t2.updated= t.updated ) 
- 
t.H_odds from avg_odds t where
t.updated = (select max(t2.updated) from avg_odds t2 where
             t2.fixture_id = t.fixture_id and t2.updated= t.updated ) as dif_h 
where fixture_id in ($list_of_ids)

Is there a easier solution?

Answer

You can do this by joining the table on itself:

select
    t_max.*,
    (t_max.H_odds - t_min.H_odds) as dif_h,
    (t_max.D_odds - t_min.D_odds) as dif_d,
    (t_max.A_odds - t_min.A_odds) as dif_a
from
(
    select
        fixture_id,
        min(updated) min_updated,
        max(updated) max_updated
    from
        test
    group by
        fixture_id
) as t1
join test as t_min on (t_min.fixture_id = t1.fixture_id and t_min.updated = t1.min_updated)
join test as t_max on (t_max.fixture_id = t1.fixture_id and t_max.updated = t1.max_updated)

Working example.

In MySQL >= 8 there may be a solution using window-functions, but im not too sure about that since the fields you need can not be fetched by an aggregate function itself, they are just identified by an aggregate (namely min and max) + fixture_id.

You’d need something like this:

SELECT
    MIN(updated) over (partition by fixture_id)
...

This get max updated for all grouped ‘fixture_id’ (but you dont want ‘min(updated)’, you want ‘H_odds’ etc. of the row having ‘min(updated) AND fixture_id = “fixture_id of max(updated) with same fixture_id’) – which, please correct me if im wrong, does not exist. But since you dont use MySql >= 8 anyways this is not an option.

And please get unique ids 🙂

Important

You need to index your table:

// recommended if you plan to narrow down your results by this fields - which i assume
create index t_f on test (fixture_id);
create index t_u on test (updated);

// MUST HAVE
create unique index t_fu on test (fixture_id, updated);

If you dont do so, there will be temptables on your filesystem – (very very slow)

Working example.

Update: A rather unpleasant MySQL >= 8 window-function version (using filesort – DO NOT USE).