Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
INTERPOLATE PREVIOUS VALUE

Hi all,
I use INTERPOLATE PREVIOUS VALUE by time in query and I didn't understand the results,
CREATE TABLE data.timevalue ( unit_id int, field_id int, time_ timestamptz, value_ numeric(37,15), )
the data in the table are in different times for the different fields in the same unit.
I run 2 queries :
*Query 1
SELECT value_ ,time_ FROM data.timevalue WHERE time_ >='2019-09-21 07:43:00.000000' AND unit_id = 17 AND time_ <='2019-09-21 14:59:00.000000' AND field_id = 24;
*Query 2
SELECT time_ , field_Temp.value_ AS Temp FROM data.timevalue FULL OUTER JOIN ( SELECT value_ ,time_ FROM data.timevalue WHERE time_ >= '2019-09-21 07:43:00.000000' and unit_id = 17 AND time_ <='2019-09-21 14:59:00.000000' AND field_id = 24 )--Temp AS field_Temp ON time_ INTERPOLATE PREVIOUS VALUE field_Temp.time_ WHERE time_ >= '2019-09-21 07:43:00.000000' AND time_ <='2019-09-21 14:59:00.000000' AND unit_id = 17;
the results:
And now the questions:
1.In the png in the results, where did the data from the yellow part come from?
2.Is it correct to execute query # 2 when I have a lot of field_id like field =24 with different time, sometimes about the same time and sometimes a difference of a few single seconds and I need one report to all of them?
Thank you very much for your help!!
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
You must have one or more entries in the
timevalue
table whoseunit_id
is <> 17 or whosefield_id
is <> 24 and whosevalue_
is 25.6.and whose
time_
is between '2019-09-21 08:53:00.0' and '2019-09-21 08:53:45.8'Can you paste (not as image, but as text) the rows from
timevalue
withtime_
between '2019-09-21 07:43:00' and '2019-09-21 14:59:00', withunit_id
17 andfield_id
24 and with a few otherunit_id
s and perhaps a few otherfield_id
s, so we can illustrate the behaviour on a data cutout?I found the raw with the value 25.6!! Tank a lot @marcothesane
now for the next qution 2:
this is the bast way to get all full report for all the fields in one unit_id? in one unit there is 20 different fields in the example I select only tow fields .
I add attach file ,example of the data.
Can anyone look at the query and tell me if it's a reasonable solution to a problem I have?