sql - 如何查找按两列分组的列的先前值？

Question

我想从下表中找到按 fname 和 lname 分组的新代码和以前的代码。

fname | lname | code |
----------------------
jack  | jonas | 987  |  
nick  | fun   | 563  |  
jack  | jonas | 674  |
jack  | jonas | 672  |

输出表:

fname | lname | new_code | prev_code |
-------------------------------------
jack  | jonas | 987      | 674

PS:我的案例不支持“distinct on”。我想可以使用 left join 或 pivot。

Answer 1

在 Postgres 中，您可以使用 distinct on 和 lag() 获取每个名称的最新对:

select distinct on (lname, fname) t.*
from (select t.*,
             lag(code) over (partition by lname, fname order by datecol) as prev_code
      from t
     ) t
where prev_code is not null
order by lname, fname, date desc;

你也可以在没有子查询的情况下这样做:

select distinct lname, fname,
       first_value(code) over (partition by lname, fname order by datecol desc),
       nth_value(code, 2) over (partition by lname, fname order by datecol desc)
from t;

但是，当第二个代码存在时，您仍然需要一个子查询来过滤值。

或者，您可以使用数组:

select lname, fname,
       (array_agg(code order by datecol desc))[1] as code,
       (array_agg(code order by datecol desc))[2] as prev_code
from t
group by lname, fname
having count(*) >= 2;