Time Series function past last record update

Hi,

I am currently attempting to retrieve active inventory status at a daily level for a given date range, where the inventory items are only updated periodically. As a simple example here is a sample data record update history.

create table inv_test( 
  item_id varchar(128) not null,
  description varchar(255),
  price NUMERIC(8,2),
  date timestamp,
  active boolean
);

insert into inv_test(item_id, description, price, date, active) 
values(123, 'Some Item', 15, '03/01/2017'::TIMESTAMP, TRUE);  
insert into inv_test(item_id, description, price, date, active) 
values(123, 'Some Item', 12.45, '04/02/2017'::TIMESTAMP, TRUE);
insert into inv_test(item_id, description, price, date, active) 
values(123, 'Some Item', 10.50, '04/05/2017'::TIMESTAMP, TRUE);
insert into inv_test(item_id, description, price, date, active) 
values(123, 'Some Item', 10.50, '04/08/2017'::TIMESTAMP, FALSE);

The output for 2017-04-01 through 2017-04-30 I desire is below. Note gaps before start date range, between records and after last record is filled. Also, the item status was set to inactive on 04/08 so I don't wish to include this record.

ITEM_ID DESCRIPTION PRICE DATE
123 Some Item 15.00 04/01/2017
123 Some Item 12.45 04/02/2017
123 Some Item 12.45 04/03/2017
123 Some Item 12.45 04/04/2017
123 Some Item 10.50 04/05/2017
123 Some Item 10.50 04/06/2017
123 Some Item 10.50 04/07/2017

I have gotten close using the query below where the gaps before and between records are filled, however I have not come up with a way to fill records after the last update. Is this something that is possible.

SELECT item_id, description, price, date
FROM (
        SELECT item_id, TS_LAST_VALUE(description) as description, TS_LAST_VALUE(price) as price, active, slice_time as date
        FROM etl_temp.inv_test 
        TIMESERIES slice_time AS '1 days' OVER (PARTITION BY item_id, active ORDER BY date)
        order by slice_time asc
)ts
where date between '2017-04-01' and '2017-04-30'
and active
order by date
ITEM_ID DESCRIPTION PRICE DATE
123 Some Item 15.00 04/01/2017
123 Some Item 12.45 04/02/2017
123 Some Item 12.45 04/03/2017
123 Some Item 12.45 04/04/2017
123 Some Item 10.50 04/05/2017

Missing last two records here after last update

Comments

  • Full disclosure: I'm not a time series analytic function expert.

    I think rather than partitioning on the active field, you want the TS_FIRST_VALUE(active) instead. If the slices only change at the day grain and you only want active segments... I think this would work.

    If it were to re-activate - you should get that then also but not the days of NOT active.

    SELECT ts.item_id, ts.description, ts.price, ts.date
    FROM (
            SELECT item_id, TS_LAST_VALUE(description) as description, TS_LAST_VALUE(price) as price, TS_FIRST_VALUE(active) as active, slice_time as date
            FROM etl_temp.inv_test 
            TIMESERIES slice_time AS '1 days' OVER (PARTITION BY item_id ORDER BY date)
            order by slice_time asc
    )ts
    where ts.date between '2017-04-01' and '2017-04-30'
    and ts.active
    order by date;
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file