Pretend we live in a crazy world where there can be extreme fluctuations in the price of gasoline over very short amounts of time. Imagine we’re tracking the gas prices as they change in a table named TS_TEST in a Vertica database.
Here is a sample set of data from our database:
Code: Select all
dbadmin=> SELECT item, price_time, price
dbadmin-> FROM ts_test
dbadmin-> WHERE price_time BETWEEN timestamp '2012-07-26 08:00' AND timestamp '2012-07-26 08:25'
dbadmin-> ORDER
dbadmin-> BY item, price_time, price;
item | price_time | price
---------+---------------------+-------
Reg Gas | 2012-07-26 08:00:00 | 3.75
Reg Gas | 2012-07-26 08:00:15 | 3.81
Reg Gas | 2012-07-26 08:00:20 | 3.50
Reg Gas | 2012-07-26 08:01:04 | 3.57
Reg Gas | 2012-07-26 08:01:47 | 3.59
Reg Gas | 2012-07-26 08:02:12 | 4.10
Reg Gas | 2012-07-26 08:02:39 | 3.60
Reg Gas | 2012-07-26 08:02:52 | 4.00
Reg Gas | 2012-07-26 08:03:29 | 4.01
Reg Gas | 2012-07-26 08:03:46 | 3.89
Reg Gas | 2012-07-26 08:04:45 | 3.95
Reg Gas | 2012-07-26 08:05:01 | 3.95
Reg Gas | 2012-07-26 08:05:28 | 4.00
Reg Gas | 2012-07-26 08:06:35 | 4.02
Reg Gas | 2012-07-26 08:06:54 | 3.91
Reg Gas | 2012-07-26 08:07:19 | 3.80
Reg Gas | 2012-07-26 08:07:19 | 3.80
Reg Gas | 2012-07-26 08:07:29 | 3.89
Reg Gas | 2012-07-26 08:07:35 | 3.89
Reg Gas | 2012-07-26 08:09:43 | 3.75
Reg Gas | 2012-07-26 08:09:43 | 3.75
Reg Gas | 2012-07-26 08:10:31 | 3.75
Reg Gas | 2012-07-26 08:10:31 | 3.75
Reg Gas | 2012-07-26 08:10:35 | 3.76
Reg Gas | 2012-07-26 08:10:35 | 3.76
Reg Gas | 2012-07-26 08:11:20 | 3.86
Reg Gas | 2012-07-26 08:11:20 | 3.86
Reg Gas | 2012-07-26 08:11:35 | 3.87
Reg Gas | 2012-07-26 08:11:59 | 3.90
Reg Gas | 2012-07-26 08:12:01 | 3.91
Reg Gas | 2012-07-26 08:12:20 | 3.95
Reg Gas | 2012-07-26 08:12:23 | 3.96
Reg Gas | 2012-07-26 08:13:00 | 4.50
Reg Gas | 2012-07-26 08:13:22 | 4.55
Reg Gas | 2012-07-26 08:13:53 | 4.67
Reg Gas | 2012-07-26 08:14:05 | 4.87
Reg Gas | 2012-07-26 08:14:13 | 4.87
Reg Gas | 2012-07-26 08:14:45 | 4.90
Reg Gas | 2012-07-26 08:14:50 | 4.96
Reg Gas | 2012-07-26 08:15:00 | 5.00
Reg Gas | 2012-07-26 08:15:20 | 5.01
Reg Gas | 2012-07-26 08:15:23 | 5.05
Reg Gas | 2012-07-26 08:17:13 | 5.15
Reg Gas | 2012-07-26 08:20:12 | 5.16
Reg Gas | 2012-07-26 08:24:43 | 5.10
Reg Gas | 2012-07-26 08:25:00 | 5.11
(46 rows)
I’d like to see the price of gasoline every 1 minute starting at 8:00 am and ending at 8:25 am. The following query using the TIMESERIES clause of the SELECT statement will give me that information:
Code: Select all
dbadmin=> SELECT item, slice_time, ts_first_value(price, 'const') price
dbadmin-> FROM ts_test
dbadmin-> WHERE price_time BETWEEN timestamp '2012-07-26 08:00' AND timestamp '2012-07-26 08:25'
dbadmin-> TIMESERIES slice_time AS '1 minute' OVER (PARTITION BY item ORDER BY price_time)
dbadmin-> ORDER
dbadmin-> BY item, slice_time, price;
item | slice_time | price
---------+---------------------+-------
Reg Gas | 2012-07-26 08:00:00 | 3.75
Reg Gas | 2012-07-26 08:01:00 | 3.50
Reg Gas | 2012-07-26 08:02:00 | 3.59
Reg Gas | 2012-07-26 08:03:00 | 4.00
Reg Gas | 2012-07-26 08:04:00 | 3.89
Reg Gas | 2012-07-26 08:05:00 | 3.95
Reg Gas | 2012-07-26 08:06:00 | 4.00
Reg Gas | 2012-07-26 08:07:00 | 3.91
Reg Gas | 2012-07-26 08:08:00 | 3.89
Reg Gas | 2012-07-26 08:09:00 | 3.89
Reg Gas | 2012-07-26 08:10:00 | 3.75
Reg Gas | 2012-07-26 08:11:00 | 3.76
Reg Gas | 2012-07-26 08:12:00 | 3.90
Reg Gas | 2012-07-26 08:13:00 | 4.50
Reg Gas | 2012-07-26 08:14:00 | 4.67
Reg Gas | 2012-07-26 08:15:00 | 5.00
Reg Gas | 2012-07-26 08:16:00 | 5.05
Reg Gas | 2012-07-26 08:17:00 | 5.05
Reg Gas | 2012-07-26 08:18:00 | 5.15
Reg Gas | 2012-07-26 08:19:00 | 5.15
Reg Gas | 2012-07-26 08:20:00 | 5.15
Reg Gas | 2012-07-26 08:21:00 | 5.16
Reg Gas | 2012-07-26 08:22:00 | 5.16
Reg Gas | 2012-07-26 08:23:00 | 5.16
Reg Gas | 2012-07-26 08:24:00 | 5.16
Reg Gas | 2012-07-26 08:25:00 | 5.11
(26 rows)
There is a lot more fun to be had with the TIMESERIES clause. Please read about it in the Vertica “SQL Reference Manual” beginning on page 861!