The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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
0
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.