Timeseries Aggregate Function and NULL Values

Moderator: NorbertKrupa

Post Reply
billykopecki
Beginner
Beginner
Posts: 42
Joined: Thu Apr 19, 2012 9:03 pm

Timeseries Aggregate Function and NULL Values

Post by billykopecki » Wed Feb 13, 2013 4:02 pm

Hi all,

I'm trying to understand how the TS_FIRST_VALUE timeseries aggregate function works with NULL values.

For instance, if I have this data:

Code: Select all

dbadmin=> SELECT * FROM tick ORDER BY ts;
 symbol |         ts          |        bid
--------+---------------------+--------------------
 SYM    | 2013-02-14 04:00:00 | 10.000000000000000
 SYM    | 2013-02-14 04:00:05 | 10.500000000000000
 SYM    | 2013-02-14 04:00:10 |
 SYM    | 2013-02-14 04:00:15 | 11.000000000000000
(4 rows)
Can someone please explain why in the following query the bid3 column contains 3 NULL values?

Code: Select all

dbadmin=> SELECT slice_time,
dbadmin->        TS_FIRST_VALUE(bid, 'CONST') bid1,
dbadmin->        TS_FIRST_VALUE(bid IGNORE NULLS, 'CONST') bid2,
dbadmin->        TS_FIRST_VALUE(bid, 'LINEAR') bid3,
dbadmin->        TS_FIRST_VALUE(bid IGNORE NULLS, 'LINEAR') bid4
dbadmin->   FROM tick
dbadmin->   TIMESERIES slice_time AS '3 seconds' OVER(PARTITION by symbol ORDER BY ts)
dbadmin->  ORDER BY ts;
     slice_time      |        bid1        |        bid2        | bid3 | bid4
---------------------+--------------------+--------------------+------+-------
 2013-02-14 04:00:00 | 10.000000000000000 | 10.000000000000000 |   10 |    10
 2013-02-14 04:00:03 | 10.000000000000000 | 10.000000000000000 | 10.3 |  10.3
 2013-02-14 04:00:06 | 10.500000000000000 | 10.500000000000000 |      | 10.55
 2013-02-14 04:00:09 | 10.500000000000000 | 10.500000000000000 |      |  10.7
 2013-02-14 04:00:12 |                    | 10.500000000000000 |      | 10.85
 2013-02-14 04:00:15 | 11.000000000000000 | 11.000000000000000 |   11 |    11
(6 rows)
I'm confused :?

eugemz
Newbie
Newbie
Posts: 2
Joined: Wed Feb 13, 2013 1:35 pm

Re: Timeseries Aggregate Function and NULL Values

Post by eugemz » Wed Feb 13, 2013 8:59 pm

You are trying to do a linear interpolation with a null value and that will create a NULL value.

You have null in 00:10 and the previous value is 00:05, so all the slide_time that will use those 2 values to do an linear interpolation, will return null.

Does make sense?

billykopecki
Beginner
Beginner
Posts: 42
Joined: Thu Apr 19, 2012 9:03 pm

Re: Timeseries Aggregate Function and NULL Values

Post by billykopecki » Thu Feb 14, 2013 1:55 pm

Okay! I see it now! Thank you!

So with linear interpolation my calculated bid will be always be NULL when the time slice is > the ts with the last bid before the NULL bid and time slice is < first bid after the NULL bid. I think I got that right :shock:

Post Reply

Return to “vSQL”