Time Series and gap filling after last record update

Moderator: NorbertKrupa

Post Reply
bryan.chapman
Newbie
Newbie
Posts: 1
Joined: Wed Apr 26, 2017 4:17 pm

Time Series and gap filling after last record update

Post by bryan.chapman » Wed Apr 26, 2017 4:19 pm

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***

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Time Series and gap filling after last record update

Post by JimKnicely » Sat Apr 29, 2017 3:09 am

Hi,

Is this what you want?

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


Example:

dbadmin=> SELECT item_id, description, price, date
dbadmin-> FROM (
dbadmin(> SELECT item_id, TS_LAST_VALUE(description) as description, TS_LAST_VALUE(price) as price, TS_LAST_VALUE(active) as active, slice_time as date
dbadmin(> FROM inv_test
dbadmin(> TIMESERIES slice_time AS '1 days' OVER (PARTITION BY item_id ORDER BY date)
dbadmin(> order by slice_time asc
dbadmin(> )ts
dbadmin-> where date between '2017-04-01' and '2017-04-30'
dbadmin-> and active
dbadmin-> order by date;
item_id | description | price | date
---------+-------------+-------+---------------------
123 | Some Item | 15.00 | 2017-04-01 00:00:00
123 | Some Item | 12.45 | 2017-04-02 00:00:00
123 | Some Item | 12.45 | 2017-04-03 00:00:00
123 | Some Item | 12.45 | 2017-04-04 00:00:00
123 | Some Item | 10.50 | 2017-04-05 00:00:00
123 | Some Item | 10.50 | 2017-04-06 00:00:00
123 | Some Item | 10.50 | 2017-04-07 00:00:00
(7 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Analytics”