ruby-on-rails - postgresql 获取日期之前的最新值

Question

假设我有以下 Inventory table 。

id  item_id  stock_amount           Date
(1,   1,        10,         '2020-01-01T00:00:00')
(2,   1,         9,         '2020-01-02T00:00:00')
(3,   1,         8,         '2020-01-02T10:00:00')
(4,   3,        11,         '2020-01-03T00:00:00')
(5,   3,        13,         '2020-01-04T00:00:00')
(6,   4,         7,         '2020-01-05T00:00:00')
(7,   2,        12,         '2020-01-06T00:00:00')

基本上，每天，我想得到 stock_amount 的总和对于每个独特的 item_id但应该不包括当日的存货量。选择的 item_id 应该是最新的。这是为了计算每天的起始库存。因此，在这种情况下，响应将是:

       Date            starting_amount
'2020-01-01T00:00:00'         0
'2020-01-02T00:00:00'         10
'2020-01-03T00:00:00'         8 
'2020-01-04T00:00:00'         19 -- # -> 11 + 8 (id 5 + id 3)
'2020-01-05T00:00:00'         21 -- # -> 13 + 8
'2020-01-06T00:00:00'         28 -- # -> 7 + 13 + 8

任何帮助将不胜感激。

Answer 1

使用这样的嵌套子查询:

select
  Date,
  coalesce(sum(stock_amount), 0) starting_amount
from
(
  select
    row_number() over(partition by i1.Date, item_id order by i2.Date desc) i,
    i1.Date,
    i2.item_id,
    i2.stock_amount
  from
    (select distinct date_trunc('day', Date) as Date from Inventory) i1
    left outer join
    Inventory i2
    on i2.Date < i1.Date
) s
where i = 1
group by Date
order by Date

此查询按降序排序并使用第一行。