Doubt Regarding Getting Started example

TimescaleDB and PostgreSQL
1

Consider the example time series data (https://docs.timescale.com/getting-started/latest/add-data/#ingest-the-dataset) where the price of stock and the volume traded in a day till the time is being added in to the database. in the continuous aggregate the Open, high, Low and close of each day has been and highest of day can be calculated as the traded volume. Now if i am to create candles per hour. Then I have to calculate the volume traded in each hour which will be equal to max(day_volume) in the current bucket minus the max(day_volume) in the previous row. How can i make an continuous aggregate in such a case?

|         timestamp          | symbol |  price   | volume |
|----------------------------|--------|----------|--------|
| 2022-06-11 12:42:04.912+00 | SBIN   | 120.0000 |      5 |
| 2022-06-11 12:42:25.806+00 | SBIN   | 123.0000 |      6 |
| 2022-06-11 12:42:38.993+00 | SBIN   | 123.4500 |      8 |
| 2022-06-11 12:42:42.735+00 | SBIN   | 108.0000 |     12 |
| 2022-06-11 12:42:45.801+00 | SBIN   | 121.0000 |     14 |
| 2022-06-11 12:43:43.186+00 | SBIN   | 122.0000 |     16 |
| 2022-06-11 12:43:45.599+00 | SBIN   | 125.0000 |     17 |
| 2022-06-11 12:43:51.655+00 | SBIN   | 141.0000 |     20 |
| 2022-06-11 12:43:54.151+00 | SBIN   | 111.0000 |     24 |
| 2022-06-11 12:44:01.908+00 | SBIN   | 123.0000 |     27 |

should give a view like

|          time          | symbol |   high   |   open   |  close   |   low    | volume |
|------------------------|--------|----------|----------|----------|----------|--------|
| 2022-06-11 12:44:00+00 | SBIN   | 123.0000 | 123.0000 | 123.0000 | 123.0000 |   14   |
| 2022-06-11 12:43:00+00 | SBIN   | 141.0000 | 122.0000 | 111.0000 | 111.0000 |   10   |
| 2022-06-11 12:42:00+00 | SBIN   | 123.4500 | 120.0000 | 121.0000 | 108.0000 |    3   |
2

Hi @arvindsa90 ,

If you want to calculate the “max(day_volume) in the current bucket minus the max(day_volume) in the previous row” you can use a window function. The caveat is that window functions cannot be used to create the continuous aggregate BUT you can apply window function on an existing continuous aggregate. So you may do this:

  1. Create a continuous aggregate where the volume column is max(day_volume)
  2. When querying the continuous aggregate use the LAG() window function, eg.:
SELECT *, ("volume" - LAG("volume", 1) OVER (PARTITION BY symbol ORDER BY bucket)) AS volume
FROM candles_per_hour
2 Likes