Issue with continous aggregates

TimescaleDB and PostgreSQL
1

I’m trying to implement the following as a continuous aggregate table, but the lag function is not accessible. How can this be rewritten?

CREATE MATERIALIZED VIEW IF NOT EXISTS exchange.hkcandles_m1
WITH (timescaledb.continuous) AS
(
SELECT
ts_bucket,
ticker,
( Lag (close, 1) OVER (ORDER BY ts_bucket) + Lag (open, 1) OVER (ORDER BY ts_bucket)) / 2 AS open,
(open + high + low + close) / 4 AS close
FROM
exchange.candles_m1
GROUP BY ticker, ts_bucket, open, high, low, close
ORDER BY ts_bucket
); (edited)

there is no real aggregation, there is a 1-1 row correspondence between the source (exchange.candles_m1) and the destination (exchange.hkcandles_m1). But to do the calculation, I always need the previous row

I tried this as well:

CREATE MATERIALIZED VIEW IF NOT EXISTS exchange.hkcandles_m1
WITH (timescaledb.continuous) AS
(
SELECT
t1.ts_bucket,
t1.ticker AS ticker,
(t2.close + t2.open) / 2 AS open,
(t1.open + t1.high + t1.low + t1.close) / 4 AS close
FROM
exchange.candles_m1 t1
JOIN exchange.candles_m1 t2 ON t1.ts_bucket = t2.ts_bucket + INTERVAL '1 minute'
AND t1.ticker = t2.ticker
GROUP BY t1.ticker, t1.ts_bucket, t1.open, t1.high, t1.low, t1.close, t2.close, t2.open
ORDER BY t1.ts_bucket
)

but it says that only one hypertable is allowed in a continuous aggregate

2

Just a suggestion as per my understanding:

In continuous aggregates, the use of window functions like the LAG function is not supported directly. However, you can achieve a similar result by utilizing a self-join and filtering the rows appropriately. Here’s an example of how you can rewrite your query without using the LAG function:

CREATE MATERIALIZED VIEW IF NOT EXISTS exchange.hkcandles_m1
WITH (timescaledb.continuous) AS
(
SELECT
  t1.ts_bucket,
  t1.ticker AS ticker,
  (t2.close + t2.open) / 2 AS open,
  (t1.open + t1.high + t1.low + t1.close) / 4 AS close
FROM
  exchange.candles_m1 t1
JOIN
  exchange.candles_m1 t2 ON t1.ts_bucket = t2.ts_bucket + INTERVAL '1 minute' AND t1.ticker = t2.ticker
WHERE
  t1.ts_bucket > (SELECT MIN(ts_bucket) FROM exchange.candles_m1)  -- Exclude the earliest ts_bucket
GROUP BY
  t1.ticker, t1.ts_bucket, t1.open, t1.high, t1.low, t1.close, t2.close, t2.open
ORDER BY
  t1.ts_bucket
);

In this rewritten query, we introduced a WHERE clause to exclude the earliest ts_bucket value, simulating the effect of using LAG function to access the previous row.

Please note that continuous aggregates have certain limitations, and complex calculations involving the previous row might not be directly achievable in this context. You may need to consider alternative approaches or perform these calculations outside the continuous aggregate, depending on your specific use case.

3

As an alternative you can just make the lag part out of the materialized view and build a regular view to include the lag part.

Always think in the materialized views as index to travel data and complex calculations can be done after querying the base data with views.

1 Like
4

This will not work:

ERROR: invalid continuous aggregate query
Detail: CTEs, subqueries and set-returning functions are not supported by continuous aggregates.

This proposed code is identical to what I got out of ChatGPT, asking the same question.

5

Yes you are right @thomasd3, actually I even followed chatgpt while finding your scenario.

Why don’t you try solution proposed by @jonatasdp once, it should work.