sql - 如何在一行中获取 postgreSQL 中的 2 个最新值？

Question

我有这个项目表:

orderitemid  orderid itemid quantity price      createdate
1                1     12     5     15.5         2016-12-04 11:35:02.06629
2                1     17     5     13.2         2016-12-04 11:32:02.06629
3                2     12     2     12.5         2016-12-05 11:35:02.06629
4                2     17     1     12.6         2016-12-05 11:35:02.06629
5                2     18     15    14.5         2016-12-04 11:35:02.06629
6                3     12     45     3           2015-12-04 11:35:02.06629

我有一个查询，它给出了每个项目的最新订单，因此:

select distinct on (itemid) *
from orderitems
order by itemid,createdate

这给出:

orderitemid  orderid itemid quantity price      createdate
3                2     12     2     12.5         2016-12-05 11:35:02.06629
4                2     17     1     12.6         2016-12-05 11:35:02.06629
5                1     18     15    14.5         2016-12-04 11:35:02.06629

现在我想要的是在同一行中获取每个项目的有关该项目先前订单的信息。基本上是比较最近的项目订单和第二个最近的项目订单

这就是我想要的:

orderitemid   itemid quantity price    2ndquantity 2ndprice 2ndorderitemid  
3               12     2     12.5         5         15.5         1
4               17     1     12.6         1         13.2         2 
5               18     15    14.5         

我如何修改我的查询来做到这一点？

Answer 1

with ranked as (
  select orderitemid, orderid, itemid, quantity, price, createdate, 
         row_number() over (partition by itemid order by createdate desc) as rn
  from orderitems
)
select r1.*, r2.quantity as "2ndquantity", r2.price as "2ndprice",
       r2.orderitemid as "2ndorderitemid"
from ranked r1 
  left join ranked r2 on r1.itemid = r2.itemid and r2.rn = 2
where r1.rn = 1;  

CTE 计算第 1 项和第 2 项，最后的选择然后通过连接将它们组合在一起。请注意，您需要左连接，因为可能没有第二行，在这种情况下，该项目根本不会显示。

在线示例:http://rextester.com/SDBZ21144