INTERPOLATE PREVIOUS VALUE
reli
Vertica Customer ✭
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
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?