MySQL查询返回最新观察的第n个滞后

Question

我有以下 MySQL 表:

firm | Sales | year
A    | 100   | 2018
A    | 200   | 2017
A    | 300   | 2016
B    | 400   | 2017
B    | 500   | 2016
B    | 600   | 2015
C    | 700   | 2016
C    | 800   | 2015
C    | 900   | 2014

我正在尝试编写 MySQL 查询，该查询将为每个组(公司)返回上次观察(销售)或上次观察的第 n 个滞后。

我发现 MySQL 查询返回每个组的最后观察结果:

select * 
from (select * from mytable order by `Group`, firm, datum desc) x
group by `Group`

但我不知道如何修改代码以从最近的观察中返回一个滞后，即:

firm | Sales | year
A    | 200   | 2017
B    | 500   | 2016
C    | 800   | 2015

Answer 1

我认为最通用的方法是使用变量枚举值:

select t.*
from (select t.*,
             (@rn := if(@f = t.firm, @rn + 1,
                        if(@f := t.firm, 1, 1)
                       )
             ) as rn
      from mytable t cross join
           (select @f := '', @rn := 0) params
      order by t.firm, t.year desc
     ) t
where rn = 2;

您的版本有一个致命缺陷:它使用group by 和select *。您在选择中有未聚合的列，这些列不是 group by 键。这是损坏的 SQL，几乎在任何数据库中都会失败，并且在较新版本的 MySQL 中使用默认设置。

如果意图是“n”是最近一年之前的年数(好吧，偏移 1)，那么连接可以工作:

select t.*
from mytable t join
     (select firm, max(year) as max_year
      from mytable
      group by firm
     ) f
     on t.firm = f.firm and t.year = f.max_year - (2 - 1);