Time Series function past last record update

BryanChapman · April 2017

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

DarinAfni · April 2017

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;

Time Series function past last record update

Comments

Leave a Comment