TIMESERIES clause

[wlam]

For a query containing a TIMESERIES clause, if the slice_time is not used, can you remove the timeseries clause.

i.e. Are the following two queries the same?

select x,y,z from foo
timeseries XX as '30 second' over (partition by x,y order by z)


select x,y,z from foo

[JimKnicely]

Hi wlam,

I don't think the syntax for this statement is correct:

• select x,y,z from foo
  timeseries XX as '30 second' over (partition by x,y order by z)

That is, you can't select z if it's not part of the partition column list...

Example:

Code: Select all

dbadmin=> select * from ts_test order by 1, 2, 3;
 item | price |     price_time
------+-------+---------------------
 Gas  |  3.99 | 2012-04-18 00:12:00
 Gas  |  4.01 | 2012-04-18 00:12:20
 Gas  |  4.04 | 2012-04-18 00:12:29
 Gas  |  4.04 | 2012-04-18 00:12:50
(4 rows)

I can't do this:

Code: Select all

dbadmin=> select item
dbadmin->      , price
dbadmin->      , price_time
dbadmin->   from ts_test
dbadmin->   timeseries slice_time as '5 seconds' over (partition by item, price order by price_time)
dbadmin->  order by 1, 2;
ERROR:  column "ts_test.price_time" must appear in the PARTITION BY list of Timeseries clause or be used in a Time Series Aggregate Function

But I can do this:

Code: Select all

dbadmin=> select item
dbadmin->      , slice_time
dbadmin->      , ts_first_value(price, 'const') price_time
dbadmin->   from ts_test
dbadmin->   timeseries slice_time as '5 seconds' over (partition by item order by price_time)
dbadmin->  order by 1, 2;
 item |     slice_time      | price_time
------+---------------------+------------
 Gas  | 2012-04-18 00:12:00 |       3.99
 Gas  | 2012-04-18 00:12:05 |       3.99
 Gas  | 2012-04-18 00:12:10 |       3.99
 Gas  | 2012-04-18 00:12:15 |       3.99
 Gas  | 2012-04-18 00:12:20 |       4.01
 Gas  | 2012-04-18 00:12:25 |       4.01
 Gas  | 2012-04-18 00:12:30 |       4.04
 Gas  | 2012-04-18 00:12:35 |       4.04
 Gas  | 2012-04-18 00:12:40 |       4.04
 Gas  | 2012-04-18 00:12:45 |       4.04
 Gas  | 2012-04-18 00:12:50 |       4.04
(11 rows)

[wlam]

Thank you for your response.

But does the appearance of SLICE_TIME affect the other cells of the result set?

i.e. Would columns a and b always produce the same results in both queries below?
Is there any scenario where the appearance of SLICE_TIME affects other adjacent projections?

select a, b, SLICE_TIME
from c
TIMESERIES SLICE_TIME as '30 seconds' over (partition by a,b order by ts)


vs

select a, b
from c
TIMESERIES SLICE_TIME as '30 seconds' over (partition by a,b order by ts)

[JimKnicely]

To my understanding of the function, the values of a and b will not change. They will always display the values at the time of the time slice. The values of a and b have to also correspond to actual values in the table. Vertica won't derive some arbitrary value for a and b based on a time slice. You'd have to introduce analytic functions for that...

[id10t]

Hi, wlam!

I don't know your needs but may be next example will be useful (see INTERPOLATE PREVIOUS VALUE):

Stock table definition:

Code: Select all

CREATE TABLE Stock
(
    item               VARCHAR(15),
    price              FLOAT,
    item_ts            TIMESTAMP
);

Customer table definition:

Code: Select all

CREATE TABLE Customer
(
    name               VARCHAR(25),
    item               VARCHAR(15),
    purchase_ts        TIMESTAMP
);

Stock DATA:

Code: Select all

foo,3.5,2000-01-01 00:00:00
foo,3.7,2000-01-01 08:00:00
foo,3.8,2000-01-01 16:00:00
bar,4.5,2000-01-01 00:00:00
bar,4.8,2000-01-01 08:00:00
bar,4.9,2000-01-01 16:00:00
tik,7.1,2000-01-01 07:32:00
tik,7.4,2000-01-01 13:00:00
tik,7.2,2000-01-01 17:00:11

Curstomer DATA:

Code: Select all

Anna,tik,2000-01-01 09:32:13
Fred,foo,2000-01-01 13:12:00
Josh,bar,2000-01-01 11:32:27
Fred,tik,2000-01-01 14:15:12

Query:

Code: Select all

SELECT   name,
         customer.item,
         price,
         purchase_ts
FROM     Customer LEFT OUTER JOIN Stock
ON       Customer.item = Stock.item
AND      Customer.purchase_ts INTERPOLATE PREVIOUS VALUE Stock.item_ts
ORDER BY Customer.name;

Result:

Code: Select all

 name | item | price |     purchase_ts     
------+------+-------+---------------------
 Anna | tik  |   7.1 | 2000-01-01 09:32:13
 Fred | tik  |   7.4 | 2000-01-01 14:15:12
 Fred | foo  |   3.7 | 2000-01-01 13:12:00
 Josh | bar  |   4.8 | 2000-01-01 11:32:27

PS Also, I think it possible solve with LAG/LEAD analytic function