MySQL how to calculate average value of max/min N records by group

This is a brief sample of how to calculate the average value of max/min N records by group in MySQL.

For example: a table fruit has 3 volumes; name, date, price.

apple  2015-10-011.10

orange 2015-10-012.50

banana 2015-10-013.40

apple  2015-10-021.20

orange 2015-10-022.40

banana 2015-10-022.20

apple  2015-10-031.30

orange 2015-10-032.30

banana 2015-10-033.10

apple  2015-10-041.40

orange 2015-10-042.20

banana 2015-10-043.30

apple  2015-10-051.50

orange 2015-10-052.10

banana 2015-10-053.50

We want to get the average price of the latest 3 days for each fruit, for example,

apple: the average value = 1.50 (2015-10-05) + 1.40 (2015-10-04) + 1.30 (2015-10-03)    = 1.40

orange: the average value = 2.10 (2015-10-05) + 2.20 (2015-10-04) + 2.30 (2015-10-03)  = 2.20

apple: the average value = 3.50 (2015-10-05) + 3.30 (2015-10-04) + 3.10 (2015-10-03)     = 3.30

Sample SQL:

create table fruit(name varchar(20), date char(10), price float(4,2), constraint pk_fruit primary key(name, date));

insert into fruit values('apple',  '2015-10-01', 1.10);

insert into fruit values('orange', '2015-10-01', 2.50);

insert into fruit values('banana', '2015-10-01', 3.40);

insert into fruit values('apple',  '2015-10-02', 1.20);

insert into fruit values('orange', '2015-10-02', 2.40);

insert into fruit values('banana', '2015-10-02', 3.20);

insert into fruit values('apple',  '2015-10-03', 1.30);

insert into fruit values('orange', '2015-10-03', 2.30);

insert into fruit values('banana', '2015-10-03', 3.10);

insert into fruit values('apple',  '2015-10-04', 1.40);

insert into fruit values('orange', '2015-10-04', 2.20);

insert into fruit values('banana', '2015-10-04', 3.30);

insert into fruit values('apple',  '2015-10-05', 1.50);

insert into fruit values('orange', '2015-10-05', 2.10);

The query SQL:

mysql> set @num := 0;

mysql> set @type := '';

mysql> select name, date, avg(price)

    ->  from (select a.*

    ->  from fruit a inner join (

    ->    SELECT name, date, case when @mid = name then @row:=@row+1 else @row:=1 end rownum, @mid:=name mid FROM fruit order by name, date desc) b

    ->    on b.name = a.name and b.date = a.date

    -> where b.rownum<=3) as tmp

    -> group by name;

+--------+------------+------------+

| name   | date       | avg(price) |

+--------+------------+------------+

| apple  | 2015-10-05 |   1.400000 |

| banana | 2015-10-05 |   3.300000 |

| orange | 2015-10-05 |   2.200000 |

+--------+------------+------------+

3 rows in set (0.00 sec)

The original reference:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/