INTERPOLATE PREVIOUS VALUE

relireli 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: :smile:
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!!

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    You must have one or more entries in the timevalue table whose unit_id is <> 17 or whose field_id is <> 24 and whose value_ is 25.6.

  • marcothesanemarcothesane - Select Field - Administrator

    and whose time_ is between '2019-09-21 08:53:00.0' and '2019-09-21 08:53:45.8'

  • marcothesanemarcothesane - Select Field - Administrator

    Can you paste (not as image, but as text) the rows from timevalue with time_ between '2019-09-21 07:43:00' and '2019-09-21 14:59:00', with unit_id 17 and field_id 24 and with a few other unit_ids and perhaps a few other field_ids, so we can illustrate the behaviour on a data cutout?

  • relireli Vertica Customer

    @marcothesane said:
    and whose time_ is between '2019-09-21 08:53:00.0' and '2019-09-21 08:53:45.8'

    I found the raw with the value 25.6!! Tank a lot @marcothesane
    now for the next qution 2:

    SELECT time_ ,
           field_Temp.value_ AS Temp,
           field_ph.value_ as Ph
          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_
    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 = 23
                 )--ph 
    AS field_ph ON time_ INTERPOLATE PREVIOUS VALUE field_ph.time_
    WHERE time_  >= '2019-09-21 07:43:00.000000' AND time_ <='2019-09-21 14:59:00.000000' AND unit_id = 17;
    
    

    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.

  • relireli Vertica Customer

    Can anyone look at the query and tell me if it's a reasonable solution to a problem I have?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file