Building a continuous aggregate on a continuous aggregate on a continuous aggregate

TimescaleDB and PostgreSQL
1

CREATE MATERIALIZED VIEW daily_element_views
WITH (timescaledb.continuous) AS
SELECT
company_uuid,
site_9char,
page_9char,
element_9char,
time_bucket(‘1 day’, view_start, ‘PDT’) AS element_date,
COUNT(*) AS total_views
FROM
view
GROUP BY
company_uuid,
site_9char,
page_9char,
element_9char,
element_date;

CREATE MATERIALIZED VIEW daily_page_views
WITH (timescaledb.continuous) AS
SELECT
company_uuid,
site_9char,
page_9char,
time_bucket(‘1 day’, element_date, ‘PDT’) AS page_date,
COUNT(*) AS total_views
FROM
daily_element_views
GROUP BY
company_uuid,
site_9char,
page_9char,
page_date;

CREATE MATERIALIZED VIEW daily_site_views
WITH (timescaledb.continuous) AS
SELECT
company_uuid,
site_9char,
time_bucket(‘1 day’, page_date, ‘PDT’) AS site_date,
COUNT(*) AS total_views
FROM
daily_page_views
GROUP BY
company_uuid,
site_9char,
site_date;

Attempting to create ‘daily_site_views’ on top of ‘daily_page_views’ which throws this error: “ERROR: time bucket function must reference a hypertable dimension column”.
Can’t figure out why this is happening as ‘daily_page_views’ is an aggregate on an aggregate itself so why wouldn’t I be able to add another layer to that?

2

Please, make sure you have exactly the same order and same column names. I remember some issues regarding the order of the columns too. So, try to use the standard, which goes from time_bucket, company_uuid, site_9char, count.

Example:

1 Like
3

That worked. Thank you!

1 Like