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