代码之家 › 专栏 › 技术社区 › Mislav

mysql查询返回最新观察的n'th lag

sql mysql

Mislav · 技术社区 · 6 年前

我有以下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'th lag。

我发现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

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

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 *

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);