我有一个看起来像这样的表：

+--------+----------+------+-----------+
|  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 |
+--------+----------+------+-----------+

使用MySQL,我想添加一个新的列,其中三年的平均价格以记录年为中心.完成后应该如下所示：

+--------+----------+------+-----------+---------------------+
|  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 |
+--------+----------+------+-----------+---------------------+

如果订购数据并且所有内容都是相同的品牌和型号,这似乎应该是直截了当的.现实情况是,工作台有超过4000个独特的制造模型年份组合,并且它们都是按年份未按顺序排列的.

因此,查询不能依赖于有序记录,或者相邻记录与下一条记录有任何关联.查询需要过滤不同的品牌模型和年份然后在三年间隔中心平均而不打嗝,当它平均差价的第一年或最后一年时,它将缺少三年中的一个或两年.

任何MySQL提示将不胜感激！谢谢.

解决方法:

对于每个品牌和型号,我们可以尝试加入两次,将前一年和前几年与当前年份分成一行.然后,子查询并取三年的平均价格：

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;

Demo