TimescaleDB continuous aggregates count DISTINCT

user_18269468 0
Viewed 144

Say I have a simple hypertable such as

device_id value1 timestamp
1 123 2022-04-04 10:10
2 431 2022-04-04 10:53
1 321 2022-04-04 10:12

And I then create a continuous aggregate with a time bucket width of 1 hour for example. How would I keep a count of the DISTINCT device ids that reported in for each hour? For example in this case the result would be 2 devices for the timestamp 2022-04-04 10:00

I effectively want something like this:

CREATE MATERIALIZED VIEW agg_hourly_devices WITH (timescaledb.continuous)
AS
SELECT sum(value1) as value1,
       time_bucket('1h', timestamp) as timestamp,
       count(distinct(device_id)) as device_count
FROM devices
GROUP BY time_bucket('1h', timestamp)

But TimescaleDB does not allow the use of DISTINCT on continuous aggregates, therefore this definition is invalid. Any work arounds?

timescaledb
1 Answers

You can group by device_id and then count the amount of values.

CREATE MATERIALIZED VIEW agg_hourly_devices 
WITH (timescaledb.continuous)
AS
    SELECT SUM(value1) AS myValue,
           time_bucket('1h', "timestamp") AS bucket_time,
           COUNT(value1) AS device_count
    FROM devices
    GROUP BY bucket_time, device_id
    ORDER BY bucket_time, device_id
user_34123840
Related