postgresql Get latest value before date

user_7823702 0
Viewed 59

Let's say I have the following 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')

Basically, per each day, I want to get the sum of stock_amount for each unique item_id but it should exclude the current day's stock amount. The item_id chosen should be the latest one. This is to calculate the starting stock on each day. So the response in this case would be:

       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

Any help would be greatly appreciated.

postgresql ruby-on-rails
1 Answers

Using nested subqueries like this:

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

This query sorts in descending order and uses the first row.

user_138410160
Related