MySQL center averages over a three-year interval for individual unordered record values

Question

I have a table that looks like this:

+--------+----------+------+-----------+
|  make  |  model   | year | avg_price |
+--------+----------+------+-----------+
| Subaru | Forester | 2013 |     18533 |
| Ford   | F-150    | 2014 |     27284 |
| Ford   | F-150    | 2010 |     18296 |
| Subaru | Forester | 2012 |     16589 |
| Ford   | F-150    | 2013 |     25330 |
| Ford   | F-150    | 2011 |     20366 |
| Subaru | Forester | 2008 |      7256 |
| Ford   | F-150    | 2015 |     33519 |
| Ford   | F-150    | 2012 |     23033 |
| Subaru | Forester | 2011 |     15789 |
+--------+----------+------+-----------+

Using MySQL, I want to add a new column with a three-year average price focused on a record year. It should look like this:

+--------+----------+------+-----------+---------------------+
|  make  |  model   | year | avg_price | 3_yr_center_average |
+--------+----------+------+-----------+---------------------+
| Subaru | Forester | 2013 |     18533 |               17561 |
| Ford   | F-150    | 2014 |     27284 |               28711 |
| Ford   | F-150    | 2010 |     18296 |               19331 |
| Subaru | Forester | 2012 |     16589 |               16970 |
| Ford   | F-150    | 2013 |     25330 |               25216 |
| Ford   | F-150    | 2011 |     20366 |               20565 |
| Subaru | Forester | 2008 |      7256 |                7256 |
| Ford   | F-150    | 2015 |     33519 |               30401 |
| Ford   | F-150    | 2012 |     23033 |               22910 |
| Subaru | Forester | 2011 |     15789 |               16189 |
+--------+----------+------+-----------+---------------------+

It seems that this should be straightforward if the data was ordered and everything was the same make and model. In fact, the desktop has more than 4000 unique combinations of model year, and all of them are not sorted by year.

, - . , , , , .

MySQL ! .

Answer 1

, . :

SELECT make, model, year, avg_price,
    (avg_price + last_price + next_price) / (1.0 + last_cnt + next_cnt) AS 3_yr_center_average
FROM
(
    SELECT t1.make, t1.model, t1.year, t1.avg_price,
        COALESCE(t2.avg_price, 0) AS last_price,
        COALESCE(t3.avg_price, 0) AS next_price,
        CASE WHEN t2.avg_price IS NOT NULL THEN 1 ELSE 0 END AS last_cnt,
        CASE WHEN t3.avg_price IS NOT NULL THEN 1 ELSE 0 END AS next_cnt
    FROM yourTable t1
    LEFT JOIN yourTable t2
        ON t1.make = t2.make AND t1.model = t2.model AND t1.year = t2.year + 1
    LEFT JOIN yourTable t3
        ON t1.make = t3.make AND t1.model = t3.model AND t1.year = t3.year - 1
) t
ORDER BY
    make, model, year;

, , , ( ) . . , , . , Subaru Forester 2013 17561, 18533 2013 16589.